Connection Errors when configuring MySQL 8.0 Database in Ignition
Posted by Alejandro Pena, Last modified by Alejandro Pena on 08/20/2018 09:19 AM
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
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
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
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:
Otherwise, we can delete the database connection entry in Ignition and create a new one which 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.
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.