Setting up a JNDI MySQL datasource with a Magnolia CMS project

Published on October 10, 2016 by Lars Fischer



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 also easily be used 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.

Setting up a  JNDI MySQL datasource with a Magnolia CMS project

 

Prerequisites

It is assumed that you have already 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 the 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



Comments



{{item.userId}}   {{item.timestamp | timestampToDate}}

About the author Lars Fischer

Lars Fischer is an IT professional with more than 15 years of experience covering a wide range of project and technology know how. His main focuses in the last decade have been: enterprise level architecture and development, integration projects; content management and project lead and management. Since 2012, he has been working as a Senior Solution Architect within the Professional Services team at Magnolia, Basel. There he designs and develops solutions and architectures for large scale and mission critical customer projects. He has also acted as principal advisor for websites like Roche.com, the University of Zürich, Generali Switzerland, the Land Rover / Jaguar Club and the Canton of Basel Stadt. You can find more information on his personal website at http://lars-fischer.me.


See all posts on Lars Fischer

Demo site Contact us Free trial