Configuring Mysql DataSource with IBM WebSphere Application Server Liberty Profile

Question

I have a spring mvc application which i am deploying on IBM WebSphere Application Server Liberty Profile, the application is supposed to access a mysql database server that is hosted locally. I have added the datasource configuration as follows in the sever.xml file

 <dataSource id="springdb" jndiName="jdbc/springdb">
  <jdbcDriver javax.sql.XADataSource="com.mysql.cj.jdbc.Driver" libraryRef="mysqlJDBCLib"/>
  <properties databaseName="spring_db" password="**********" portNumber="3306" serverName="localhost" user="root"/>
</dataSource>

<library id="mysqlJDBCLib">
  <fileset dir="/opt/IBM/WebSphere/Liberty_16.0.0.4/usr/shared/resources/mysql" includes="mysql-connector-java-6.0.6.jar"/>
</library>

I am getting the following stack trace

Caused by: java.lang.RuntimeException: java.sql.SQLNonTransientException: DSRA4000E: A valid JDBC driver implementation class was not found for the jdbcDriver dataSource[springdb]/jdbcDriver[default-0] using the library mysqlJDBCLib. [/opt/IBM/WebSphere/Liberty_16.0.0.4/usr/shared/resources/mysql/mysql-connector-java-6.0.6.jar]
at com.ibm.ws.resource.internal.ResourceFactoryTrackerData$1.getService(ResourceFactoryTrackerData.java:123)
... 77 more
Caused by: java.sql.SQLNonTransientException: DSRA4000E: A valid JDBC driver implementation class was not found for the jdbcDriver dataSource[springdb]/jdbcDriver[default-0] using the library mysqlJDBCLib. [/opt/IBM/WebSphere/Liberty_16.0.0.4/usr/shared/resources/mysql/mysql-connector-java-6.0.6.jar]
at com.ibm.ws.jdbc.internal.JDBCDriverService.classNotFound(JDBCDriverService.java:196)
... 77 more
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource
at com.ibm.ws.classloading.internal.AppClassLoader.findClassCommonLibraryClassLoaders(AppClassLoader.java:499)
... 77 more

I have tried the following

1. Replacing com.mysql.cj.jdbc.Driver with com.mysql.jdbc.Driver and string got the same error

2. Replacing javax.sql.XADataSource with javax.sql.DataSource and still got the same error

3. Replacing javax.sql.XADataSource with javax.sql.MysqlConnectionPoolDataSource, same error

4. Switching out the mysql connector jar from mysql-connector-java-6.0.6.jar to mysql-connector-java-5.1.45-bin.jar, still same error (both the jars are there in the specified file path)

Answer 1

Background info about datasource config:

For all datasources (except for id="DefaultDataSource") the type of DataSource is selected in the following priority:

1. Use the type class configured on the <dataSource> element, if configured
2. javax.sql.ConnectionPoolDataSource This option is active in your case
3. javax.sql.DataSource
4. javax.sql.XADataSource

(According to Configuring relational database connectivity in Liberty)

If no class is defined for a type, then the next lowest priority will be checked.

By default, Liberty will scan for use the following priority for locating data source implementation class names:

1. Use the corresponding value configured on the <jdbcDriver> element
2. Use the default classes for a <properties.DRIVER_TYPE> element. Note that only some JDBC drivers have their own element types. For JDBC drivers that do not have their own properties element (such as MySQL) use the generic <properties> element
3. Based on the JDBC driver jar name, Liberty will try to guess the implementation class name. This option is active in your case

---

Reason why your configuration is not working
You've configured your <jdbcDriver> element to say "Use this specific class for XADataSource's", however, the <dataSource> element you've configured is not attempting to create an XADataSource. Using the priority order mentioned above, it will first try to create a ConnectionPoolDataSource (which Liberty has internally mapped to com.mysql.jdbc.jdbc2.optional.MysqlConnectionPoolDataSource.

To fix the problem:

1. Specify <dataSource .. type="javax.sql.XADataSource"> in your configuration so that Liberty will try to create an XADataSource instead of a ConnectionPoolDataSource. Then when it tries to create the XADataSource it will look at the class name you've configured on the <jdbcDriver> element.
2. Specify MySQL's XADataSource class name for the javax.sql.XADataSource property (which is com.mysql.cj.jdbc.MysqlXADataSource) instead of their java.sql.Driver implementation class name.

So your final configuration would look like this:

<dataSource id="springdb" jndiName="jdbc/springdb" type="javax.sql.XADataSource">
  <jdbcDriver javax.sql.XADataSource="com.mysql.cj.jdbc.MysqlXADataSource" libraryRef="mysqlJDBCLib"/>
  <properties databaseName="spring_db" password="**********" portNumber="3306" serverName="localhost" user="root"/>
</dataSource>

<library id="mysqlJDBCLib">
  <fileset dir="/opt/IBM/WebSphere/Liberty_16.0.0.4/usr/shared/resources/mysql" includes="mysql-connector-java-6.0.6.jar"/>
</library>

Answer 2

If you don't specify the type attribute in the dataSource config element, Liberty will try to infer the datasource class to load based on the driver jar filename. In your example, it is incorrectly inferring implementation class names based on a previous mySQL driver. It appears that mySQL has changed the package names of their implementations for DataSource, ConnectionPoolDataSource and XADataSource. I'll open an git issue for this. In the meantime, you can simply specify the type of datasource to create using the type attribute of the dataSource config element and then update your jdbcDriver config element with javax.sql.DataSource="com.mysql.cj.jdbc.MysqlDataSource" to point to the proper mysql driver class. If instead you need a conn pool or XA datasource instead, just update the type attribute of dataSource to identify the type and update jdbcDriver with the driver class impl.