Problems connecting Ignition to MySQL

I am receiving this error in my logs when I am trying to connect Ignition with MySQL. I have MySQL 8.0 and I believe I’ve connected to MySQL properly and obtained the right permissions.

org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225)
	at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.getConnectionInternal(DatasourceImpl.java:242)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceImpl.runTest(DatasourceImpl.java:196)
	at com.inductiveautomation.ignition.gateway.datasource.DatasourceManagerImpl$FaultedDatasourceRetryer$1.run(DatasourceManagerImpl.java:1014)
	at com.inductiveautomation.ignition.common.execution.impl.BasicExecutionEngine$TrackedTask.run(BasicExecutionEngine.java:565)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
	at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1062)
	at com.mysql.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:3556)
	at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2513)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2283)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:822)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
	at sun.reflect.GeneratedConstructorAccessor92.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
	at java.lang.reflect.Constructor.newInstance(Unknown Source)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:404)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:317)
	at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1247)
	at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1221)
	... 12 more
Caused by: java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long
	at com.mysql.jdbc.ConnectionImpl.buildCollationMapping(ConnectionImpl.java:1007)
	... 27 more


7.9.7 (b2018032914)
Oracle Corporation 1.8.0_171

This is an issue with the bundled JDBC driver (MySQL 8 changed enough things that it broke backwards compatibility). A new version of the driver will be in new installs in 7.9.9, but in the short term you can install ConnectorJ from MySQL directly: https://dev.mysql.com/downloads/connector/j/ and install it on your system (refer to this page for more info).

At a minimum, you’ll need to change the default ‘Extra Connection Properties’ to have zeroDateTimeBehavior=CONVERT_TO_NULL; and probably also add useSSL=false;allowPublicKeyRetrieval=true;