MySQL 8 Support

KNIME Analytics Platform
1

I recently tried connecting to MySQL 8 via the MySQL Connector and it returned an error about the database being null, even though it was passed.
I removed the MySQL 5.1 JDBC driver from the package and added the MySQL 8.0 JDBC driver and then the database part worked, but there received an different error about the time zone not being set from MySQL 8. I set the global time-zone param and the connection now works.

So when will MySQL 8 be supported and is there a way to pass in parameters to the MySQL Connector. I know I could do that via the Database Connector, but it would be nice to be able to pass in options to either.

Thanks

2018-05-10 16:00:53,590 : DEBUG : pool-4-thread-7 : RegisteredDriversConnectionFactory : MySQL Connector : 0:45 : Opening database connection to “jdbc:mysql://xxx.xxx.xxx.xxx:3306/yyy?useOldAliasMetadataBehavior=true”…
2018-05-10 16:00:53,622 : DEBUG : KNIME-Worker-41 : Node : MySQL Connector : 0:45 : reset
2018-05-10 16:00:53,622 : ERROR : KNIME-Worker-41 : Node : MySQL Connector : 0:45 : Execute failed: Could not create connection to database: null
2018-05-10 16:00:53,622 : DEBUG : KNIME-Worker-41 : Node : MySQL Connector : 0:45 : Execute failed: Could not create connection to database: null
java.sql.SQLException: Could not create connection to database: null
at org.knime.database.connectors.mysql.MySQLConnectorNodeModel.execute(MySQLConnectorNodeModel.java:137)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1172)
at org.knime.core.node.Node.execute(Node.java:959)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:561)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:917)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2330)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2083)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:806)
at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:47)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:410)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:328)
at org.knime.core.node.port.database.connection.CachedConnectionFactory.createConnection(CachedConnectionFactory.java:347)
at org.knime.core.node.port.database.connection.CachedConnectionFactory.createConnection(CachedConnectionFactory.java:328)
at org.knime.core.node.port.database.connection.CachedConnectionFactory$1.call(CachedConnectionFactory.java:220)
at org.knime.core.node.port.database.connection.CachedConnectionFactory$1.call(CachedConnectionFactory.java:1)
at org.knime.core.util.ThreadUtils$CallableWithContextImpl.callWithContext(ThreadUtils.java:344)
at org.knime.core.util.ThreadUtils$CallableWithContext.call(ThreadUtils.java:244)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.NullPointerException
at com.mysql.jdbc.ConnectionImpl.getServerCharset(ConnectionImpl.java:2997)
at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1936)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1865)
at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1228)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2253)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2284)
… 20 more

2

Hi,
thanks for the detailed information. We are in the process of rewriting the database integration in KNIME. With the coming summer release we will provide a preview of it. We plan to support MySQL8 with the MySQL connector of the new framework but not with the existing one. The new framework will provide more features in the dedicated connector nodes including support for JDBC parameters. Let us now what you think about it once it is available
Until the new framework is available you can manually register the newest MySQL driver and use the generic Database Connector node to connect to MySQL 8.
Bye
Tobias

1 Like
3

I also run into a similar problem. I got time zone error as: "Invalid settings: Database connection could not be validated: The server time zone value ‘EDT’ is unrecognized or represents more than one time zone. you must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specific time zone value if you want to utilize time zone support.

I registered the mysql 8 driver as suggested using the database connector and also set the time zone for the mysql server to SET GLOBAL time_zone =system;
what else can I do?

4

We had a similar problem.
both of these worked:
time_zone set to +06:00
system_time_zone set to CDT

or
time_zone set to SYSTEM
system_time_zone set to UTC

5

I’m having a similar issue. I can’t connect to a MYSQL database I keep getting this error: ERROR MySQL Connector 0:1 Execute failed: Could not create connection to database: null. Has anyone else been able to connect to a MySQL database using knime?

Any help would be much appreciated.

Cheers!

1 Like
6

What worked for me was downloading a .jar file MySQL connector for the version of MySQL I’m running and then adding that as a database driver in KNIME under File > Preferences > KNIME > Database.