This topic explains how to configure a MySQL database as an
external data source.
The MySQL JDBC Driver
LabKey Server requires the MySQL JDBC driver (called "MySQL Connector/J" in the MySQL documentation) to connect to MySQL databases. The MySQL JDBC driver JAR file (mysql.jar) must be copied to your Tomcat installation in the
<tomcat-home>/lib directory.
Detailed documentation about the driver is available at:
MySQL Connector/J Developer Guide.
Note that version 8.0 of the driver changed the class name from "com.mysql.jdbc.Driver" to "com.mysql.cj.jdbc.Driver". The newer version is recommended, but be sure to use the appropriate class name for the deployed version.
Configure the MySQL Data Source
Add a <Resource> element, to your installation's
labkey.xml configuration file. Use the configuration template below as a starting point. Replace USERNAME and PASSWORD with the correct credentials. If you are running LabKey Server against a remote installation of MySQL, change the url attribute to point to the remote server.
Note: This template applies to Tomcat 8 (or later). If you are using Tomcat 7, use the earlier template version in
the documentation archives.
<Resource name="jdbc/mySqlDataSource" auth="Container"
type="javax.sql.DataSource"
username="USERNAME"
password="PASSWORD"
driverClassName="com.mysql.cj.jdbc.Driver"
url="jdbc:mysql://localhost:3306/?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=CONVERT_TO_NULL"
maxTotal="15"
maxIdle="7"
useInformationSchema="true"
accessToUnderlyingConnectionAllowed="true"
validationQuery="/* ping */"
/>
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.
Define a New Schema
Now define a new schema from the MySQL data source. For details see
Set Up an External Schema.
Related Topics
com.mysql.jdbc.Driver Documentation