×
Maintenance Notice: labkey.org will be offline at 10:00 AM Pacific Time on Sunday, July 14th 2024 for maintenance. The estimated downtime should be no more than one hour. Please save your work before this time. We apologize for any inconvenience.
Premium Feature — Available with all Premium Editions of LabKey Server. Learn more or contact LabKey.

This topic explains how to configure LabKey Server to retrieve and display data from a MySQL database as an external data source. This topic assumes you have reviewed the general guidance here and provides specific parameters and details for this database type.

MySQL Version Support

We strongly recommend using MySQL 5.6 or higher; the MySQL JDBC driver that ships with LabKey Server (Connector/J version 8.3) claims compatibility "with all MySQL versions starting with MySQL 5.6"; older server versions may not work correctly.

Configure the MySQL Data Source

The new external data source must have a unique JNDI name that you will use in naming the properties you will define. In the example on this page, we use "externalMySqlDataSource", which will appear to users defining external schemas as "externalMySql". If you have more than one external data source, give each a unique name with the DataSource suffix ("firstExternalMySqlDataSource", "secondExternalMySqlDataSource", etc.). Learn more here.

In the <LABKEY_HOME>/config/application.properties file, add a new section with the name of the datasource and the parameters you want to define. Provide your own server/port, <DB_NAME>, <DB_USERNAME>, and <DB_PASSWORD> where indicated:

context.resources.jdbc.externalMySqlDataSource.driverClassName=com.mysql.cj.jdbc.Driver
context.resources.jdbc.externalMySqlDataSource.url=jdbc:mysql://localhost:3306/<DB_NAME>?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL
context.resources.jdbc.externalMySqlDataSource.username=<DB_USERNAME>
context.resources.jdbc.externalMySqlDataSource.password=<DB_PASSWORD>
context.resources.jdbc.externalMySqlDataSource.validationQuery=/* ping */

There are additional properties you can set, as shown in the template for the main "labkeyDataSource" in the application.properties file.

driverClassName

Use this as the driverClassName:

com.mysql.cj.jdbc.Driver

url

The url property for MySQL takes this form. Substitute the correct server/port and database name:

jdbc:mysql://localhost:3306/databaseName/?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=utf8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL

Note: the "zeroDataTimeBehavior=CONVERT_TO_NULL" parameter on the url above converts MySQL's special representation of invalid dates ("00-00-0000") into null. See the MySQL documentation for other options.

validationQuery

Use "/* ping */" as the trivial validation query for MySQL. :

context.resources.jdbc.@@extraJdbcDataSource@@.validationQuery=/* ping */

Define a New External Schema

To define a new schema from this data source see Set Up an External Schema.

Related Topics

Was this content helpful?

Log in or register an account to provide feedback


previousnext
 
expand allcollapse all