Setting up a JNDI MySQL datasource with a Magnolia CMS project

In this article I’ll show you how to configure your development project with a Magnolia author and a public instance using a MySQL database over JNDI.
This tutorial can easily be used to also with other databases like PostgreSQL by slightly adjusting the configuration shown below.

Using JNDI has the advantage that this data source is known to the Tomcat container and can be automatically monitored by tools like JavaMelody. As my initial goal was to monitor SQL statements executed by Magnolia I needed to configure a JNDI data source to enable JavaMelody to get access to the database avitvity involved.

Integrating JavaMelody into your Magnolia project will be the topic of the next article.

Prerequesites

It is assumed that you already have created the MySQL databases for author and public with a database account having sufficient access rights.
It is also assumed that you use Tomcat for deploying Magnolia and as always I will use IDEA Ultimate as Java IDE.

Database driver

Your Magnolia webapp module POM includes the needed database driver, eg

<!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>5.1.39</version>
</dependency>

Context file for Tomcat

In your Magnolia webapp module create the path

src/main/webapp/META-INF/context.xml

This file will be used to configure the JNDI datasource available to the container.

<?xml version="1.0" encoding="UTF-8"?>
<Context>
  <Resource name="jdbc/MagnoliaAuthor" auth="Container" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory" maxActive="50" validationQuery="SELECT 1" user="mgnl_admin" password="superuser" explicitUrl="true" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:330/mgnl_author"/>

  <Resource name="jdbc/MagnoliaPublic" auth="Container" type="com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource" factory="com.mysql.jdbc.jdbc2.optional.MysqlDataSourceFactory" maxActive="50" validationQuery="SELECT 1" user="mgnl_admin" password="superuser" explicitUrl="true" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/mgnl_public"/>
</Context>

As you can see we configure the databases mgnl_author and mgnl_public with the previously created database user mgnl_admin.

(Hint: You can use IDEA Ultimate to create the context.xml file).

Important: You may have to change the class names defined in the context resource above depending on the MySQL database driver you included in the Maven configuration above! For example, when using the 6.x drivers with the example shown above, Magnolia can't initialize the repository because teh connection to the database does not work.

Create Jackrabbit bundle configurations

In your Magnolia webapp module create the files

  • src/main/webapp/WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search-author.xml
  • src/main/webapp/WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search-public.xml

Create the datasource

For the author, create the Data Source for the author instance:

<DataSources>
  <DataSource name="magnolia">
    <param name="driver" value="javax.naming.InitialContext"/>
    <param name="url" value="java:comp/env/jdbc/MagnoliaAuthor"/>
    <param name="databaseType" value="mysql"/>
  </DataSource>
</DataSources>

This configuration part references the JNDI configuration prepared above called MagnoliaAuthor.

Important:

If you copied the configuration file from the template provided with Magnolia please adjust the persistence manager settings if you use a different name as „magnolia“ for your DataSource entry!

Now create the equivalent DataSource entry for the public instance configuration.

Reference the Jackrabbit configuration

In the magnolia.properties files files for your author and your public instance set the configuration value for the Jackrabbit configurations above:

Author Instance

magnolia.repositories.jackrabbit.config=WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search-author.xml

Public Instance

magnolia.repositories.jackrabbit.config=WEB-INF/config/repo-conf/jackrabbit-bundle-mysql-search-public.xml

Check if everything works

If the configuration is OK then starting both instances will work without error messages … if not, fix the errors stated in the log :-)

Resources

Below you find examples to help you with integrating and testing the configuration. Please use your own database and user names and your own passwords!

MySQL example database commands

# create user and databases, adjust privileges

CREATE USER 'mgnl_admin'@'localhost' IDENTIFIED BY 'superuser';

CREATE DATABASE mgnl_author CHARACTER SET utf8;
GRANT ALL ON mgnl_author .* TO 'mgnl_admin'@'localhost';

CREATE DATABASE mgnl_public CHARACTER SET utf8;
GRANT ALL ON mgnl_public .* TO 'mgnl_admin'@'localhost';

Magnolia Jackrabbit repository configuration

Check this file for a full example of a Magnolia Jackrabbit configuration file for MySQL with a JNDI data source.

More information

Lars Fischer

Read more posts by this author.

Subscribe to Lars-Fischer.me

Get the latest posts delivered right to your inbox.

or subscribe via RSS with Feedly!
comments powered by Disqus