MySQL 8.0 Connection

Ignition
1

Anyone install MySQL 8.0 and connect with Ignition? I get an error when I attempt. Wondering if maybe the java MySQL driver needs to be updated to work…

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.(ConnectionImpl.java:822)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.GeneratedConstructorAccessor91.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.6 (b2018012914)
Oracle Corporation 1.8.0_162

2

Have you verified your versions based on this chart?

3

There are some known issues with MySQL 8.0 and our current bundled JDBC driver. We’re working on a works-for-the-most-people solution, but you should also be aware that QA hasn’t fully tested MySQL 8.0 yet so it’s not guaranteed compatible.

4

You can install the latest MySQL JDBC driver yourself if you don’t want to wait for us to ship an updated version.

5

Thanks all, I’ll just roll back to 5.7 for now until the team vets the latest version and connector etc.

6

I just got burned with this as well on a new installation

7

Us too. Tried installing MySQL 8 with Ignition 7.9.5. Trying to install newest JDBC driver now but no luck getting it to connect

8

I had the same problem.
Just downladed mysql-connector-java-5.1.46.zip from MySQL and changed the driver for JDBC with the .jar file mysql-connector-java-5.1.46.jar. It worked.

9

I had the same problem. Followed davoodnasehi recommendation:
“Just downladed mysql-connector-java-5.1.46.zip from MySQL and changed the driver for JDBC with the .jar file mysql-connector-java-5.1.46.jar. It worked! Yey! ;-)”

10

is there any update on this matter ?
I tried to use mysql-connector-java-8.0.13 or mysql-connector-java-5.1.46 and I can’t get a connection with either of these using 7.9.10 :frowning:

11

There should be an error in the gateway logs - could you post it here (or get in contact with support?)
A default install of 7.9.10 should work with MySQL 5 and 8, but restoring a .gwbk or upgrading may not be getting you the required changes to connection urls, drivers, etc.

12

hello
I get the same
org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (java.lang.ClassCastException: java.math.BigInteger cannot be cast to java.lang.Long)
… as noted above
I have tried (and get to the same point)
with changing the driver to point to
mysql-connector-java-5.1.46
mysql-connector-java-6.0.6
mysql-connector-java-8.0.13
yes, it’s a restored GW but I do that all the time and I am pretty confident, that’s not my problem

I’ll slack Ethan tomorrow :slight_smile:
thank you

13

this is a late update I apologize.
I have resolved this error

in your KB there was a small mistake:
https://support.inductiveautomation.com/index.php?/Knowledgebase/Article/View/125/0/connection-errors-when-configuring-mysql-80-database-in-ignition

convertToNull should be used instead of CONVERT_TO_NULL

hope this helps

14

That fix worked. Thanks.