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.
We strongly recommend using MySQL 5.7 or higher.
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&useUnicode=true&characterEncoding=utf8&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.
Use this as the driverClassName:
com.mysql.cj.jdbc.Driver
The url property for MySQL takes this form. Substitute the correct server/port and database name:
jdbc:mysql://localhost:3306/databaseName/?autoReconnect=true&useUnicode=true&characterEncoding=utf8&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.
Use "/* ping */" as the trivial validation query for MySQL. :
context.resources.jdbc.@@extraJdbcDataSource@@.validationQuery=/* ping */
To define a new schema from this data source see Set Up an External Schema.
| previousnext |
| expand allcollapse all |
