Note: On versions prior to Ignition 7.9.9, you will continue to see an error in the Database Query Browser that it fails to list the table names with the changes made above. This error has been fixed in version 7.9.9 and any clean installations of that version will contain the changes that were referenced above by default. The changes do not get reflected if upgrading to version 7.9.9 and we would need to do the steps above.

Issue: In the event of configuring or upgrading your MySQL database to version 8.0, the connection would get faulted and the Ignition gateway would post the following error(s) in the logs:

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Unable to load authentication plugin 'caching_sha2_password'.)

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long)

Solution: Update the JDBC driver for MySQL, change the Classname, and Extra Connection Properties value

Background Information

When trying to set up your MySQL database on version 8.0, the connection faults and is unable to connect. This is an issue that is due to the JDBC driver that is bundled with Ignition by default. There were changes that were introduced in MySQL 8.0 that broke backward compatibility with the bundled JDBC driver. Ignition, by default, ships with JDBC drivers for major databases but there are cases when a new database version requires an updated JDBC driver to be released by the developer of the database. This, in turn, would cause the previous driver to not function correctly on a new database version. The solution to this problem is to update the MySQL JDBC driver to the latest version provided by Oracle.

Steps

1. Download the latest JDBC driver for MySQL

You can retrieve the latest JDBC driver (version 8.0.11 at the time of the writing) by going to the following link: https://dev.mysql.com/downloads/connector/j/. In the drop-down list for Select Operating System, select Platform Independent and Download the ZIP Archive. Once downloaded, extract the contents inside the ZIP archive and save the .jar file onto a directory that is easily accessible. This file is the driver that we will need on the next step.

2. Edit the JDBC driver entry

Open your Ignition Gateway Webpage interface and navigate to the JDBC drivers page. This is found under Configure > Databases > Drivers. Once in here, click on Edit on the MySQL ConnectorJ entry. Under Classname, change the value com.mysql.jdbc.Driver to com.mysql.cj.jdbc.Driver. Under the JAR File(s) section, click on Choose File. Navigate to the location that you stored the .jar file in the previous step to upload it onto the Ignition gateway. Afterward, under Driver Defaults & Instructions, the Default Connection Properties section will need to be set to the following value:

zeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=true;

3. Click on 'Save Changes' at the bottom of the page to save the changes to the MySQL JDBC driver entry.

4. Reinitialize the database connection

With the changes made to the MySQL JDBC driver, any new MySQL databases that you configure on the Ignition gateway will reflect the changes. If you have an existing database connection already configured in Ignition will need to modify the Extra Connection Properties value in the Edit Database Connection page for your database to the value specified earlier on the JDBC driver:

zeroDateTimeBehavior=CONVERT_TO_NULL;connectTimeout=120000;socketTimeout=120000;useSSL=false;allowPublicKeyRetrieval=true;

Otherwise, we can delete the database connection entry in Ignition and create a new one that will already have the changes made on the JDBC driver configured and would be set up normally.

Following these steps, you will successfully connect to your MySQL 8.0 database on your Ignition gateway.