a new MySQL connector seems be released in not so distant future with version 6, currently there are (the last?) milestones: https://dev.mysql.com/doc/relnotes/connector-j/6.0/en/news-6-0-2.html
If I use this connector is does work with the
(generic) "Database Connector" and
"Database Reader" without a connector
But if I use the "MySQL Database Connector" I get the error
ERROR MySQL Connector 0:foo Execute failed: Could not create connection to database: null
If I use version 5 of the connector all the three possibilities do work.
can you please send the part of the KNIME log file that contains the error. It should state which driver is used and hopefully also contains more information regarding the problem. To view the log file open KNIME and go to View->Open KNIME log
I did not know that there is more log data besides the "console". The stripped output is (I guess the problem is "Driver class name: com.mysql.jdbc.Driver major version: 6 minor version: 0 jdbc compliant: false"):
2017-04-28 13:24:39,875 : DEBUG : KNIME-Worker-3 : NodeContext : Database Reader : 0:638 : Workflow user found: XXXX
2017-04-28 13:24:39,875 : DEBUG : KNIME-Worker-3 : DatabaseDriverLoader : Database Reader : 0:638 : Database driver retrieved from user defined drivers: com.mysql.jdbc.DriverDriver info: Driver class name: com.mysql.jdbc.Driver major version: 6 minor version: 0 jdbc compliant: false
2017-04-28 13:24:39,875 : DEBUG : KNIME-Worker-3 : PriorityDriverFactory : Database Reader : 0:638 : DB driver: com.mysql.jdbc.Driver found in driver factory: org.knime.core.node.port.database.RegisteredDriversConnectionFactory. Driver info: Driver class name: org.knime.core.node.port.database.DatabaseWrappedDriver major version: 6 minor version: 0 jdbc compliant: false
2017-04-28 13:24:39,875 : DEBUG : pool-4-thread-1 : RegisteredDriversConnectionFactory : Database Reader : 0:638 : Opening database connection to "jdbc:mysql://XXXXX"...
2017-04-28 13:24:40,109 : DEBUG : KNIME-Worker-3 : Database Reader : Database Reader : 0:638 : reset
2017-04-28 13:24:40,109 : ERROR : KNIME-Worker-3 : Database Reader : Database Reader : 0:638 : Execute failed: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
2017-04-28 13:24:40,109 : DEBUG : KNIME-Worker-3 : Database Reader : Database Reader : 0:638 : Execute failed: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
java.sql.SQLException: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
at org.knime.core.node.port.database.reader.DBReaderImpl.initConnection(DBReaderImpl.java:169)
at org.knime.core.node.port.database.reader.DBReaderImpl.createTable(DBReaderImpl.java:245)
at org.knime.core.node.port.database.reader.DBReader.createTable(DBReader.java:121)
at org.knime.base.node.io.database.DBReaderNodeModel.getResultTable(DBReaderNodeModel.java:150)
at org.knime.base.node.io.database.DBReaderNodeModel.execute(DBReaderNodeModel.java:127)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:566)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1128)
at org.knime.core.node.Node.execute(Node.java:915)
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: java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:676)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:663)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:653)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:638)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:606)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1857)
at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:1673)
at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:656)
at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:349)
at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:221)
at org.knime.core.node.port.database.DatabaseWrappedDriver.connect(DatabaseWrappedDriver.java:81)
at org.knime.core.node.port.database.connection.CachedConnectionFactory.createConnection(CachedConnectionFactory.java:302)
at org.knime.core.node.port.database.connection.CachedConnectionFactory$1.call(CachedConnectionFactory.java:225)
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:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
at java.util.Properties$LineReader.readLine(Properties.java:434)
at java.util.Properties.load0(Properties.java:353)
at java.util.Properties.load(Properties.java:341)
at com.mysql.cj.jdbc.util.TimeUtil.loadTimeZoneMappings(TimeUtil.java:163)
at com.mysql.cj.jdbc.util.TimeUtil.getCanonicalTimezone(TimeUtil.java:109)
at com.mysql.cj.mysqla.MysqlaSession.configureTimezone(MysqlaSession.java:308)
at com.mysql.cj.jdbc.ConnectionImpl.initializePropsFromServer(ConnectionImpl.java:2474)
at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:1817)
... 14 more
2017-04-28 13:24:40,125 : DEBUG : KNIME-Worker-3 : WorkflowManager : Database Reader : 0:638 : Database Reader 0:638 doBeforePostExecution
2017-04-28 13:24:40,125 : DEBUG : KNIME-Worker-3 : NodeContainer : Database Reader : 0:638 : Database Reader 0:638 has new state: POSTEXECUTE
2017-04-28 13:24:40,125 : DEBUG : KNIME-Worker-3 : WorkflowManager : Database Reader : 0:638 : Database Reader 0:638 doAfterExecute - failure
2017-04-28 13:24:40,125 : DEBUG : KNIME-Worker-3 : Database Reader : Database Reader : 0:638 : reset
2017-04-28 13:24:40,125 : DEBUG : KNIME-Worker-3 : Database Reader : Database Reader : 0:638 : clean output ports.
even if it is not JDBC compliant int might work. I guess the problem more related to some missing timezone property according to the stack trace:
Caused by: java.lang.NullPointerException
at java.util.Properties$LineReader.readLine(Properties.java:434)
at java.util.Properties.load0(Properties.java:353)
at java.util.Properties.load(Properties.java:341)
at com.mysql.cj.jdbc.util.TimeUtil.loadTimeZoneMappings(TimeUtil.java:163)
Maybe this is a bug in the driver which requires you to set a timezone in the connection parameters?
I now get another error (maybe since I found out there there was some mysql-connector of an old and removed (!) database connector plugin in the plugin directory that was silently used by KNIME) using the mysql connector 6.0.6:
ERROR Database Connector 0:532 Execute failed: Could not create connection to database: The server time zone value 'Mitteleurop�ische Sommerzeit' 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 specifc time zone value if you want to utilize time zone support.
But I alway get this error no matter what I select in the database connector "Timezone correction" setting:
"no correction (use UTC)"
"Use local timezone"
"Use selected timezone" -> "Europe/Berlin" and many others