No tables listed MySQL 6.3

General Discussion
1

When I open the DB Query Browser from the tools menu no tables are listed from any MySQL server when using the latest version of MySQL.

I already had to install the latest JDBC driver and it is storing data corrected to MySQL, but for some reason I cannot browse that data from within Ignition.

The following error message is displayed whenever I try to browse the database from within Ignition. MS SQL databases work with no issues.

com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Connection Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME' at line 1
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:341)
	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:315)
	at com.inductiveautomation.ignition.designer.gateway.PriviledgedDesignerTask$GatewayMessageTask.execute(PriviledgedDesignerTask.java:439)
	at com.inductiveautomation.ignition.designer.gateway.PriviledgedDesignerTask$TaskThread.run(PriviledgedDesignerTask.java:328)
	at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME' at line 1
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:118)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1019)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.executeMetadataQuery(DatabaseMetaDataUsingInfoSchema.java:62)
	at com.mysql.cj.jdbc.DatabaseMetaDataUsingInfoSchema.getTables(DatabaseMetaDataUsingInfoSchema.java:835)
	at com.inductiveautomation.ignition.gateway.db.DatasourceMetaProviderImpl.getTables(DatasourceMetaProviderImpl.java:176)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.runListTables(Gateway.java:1484)
	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:434)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:790)
	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:85)
	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:837)
	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:583)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:226)
	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1160)
	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185)
	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1092)
	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
	at org.eclipse.jetty.server.Server.handle(Server.java:518)
	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:308)
	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:244)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:186)
	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
	at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:246)
	at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:156)
	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
	at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
	at java.lang.Thread.run(null)

Ignition v7.9.8 (b2018060714)
Java: Oracle Corporation 1.8.0_172
2

Try turning on statement logging so you can see the entire query responsible. Based on the column names, it looks like a standard query to information_schema, which is standardized across all SQL vendors.

3

Full MySQL 8 compatibility is coming in 7.9.9 - this looks related to one of the changes I had to make to the schema settings, in an area that you can’t modify through updating the JDBC driver. Could you try making a connection to your DB with the beta and make sure it’s working?

4

Updating to the Beta solved the issue, thanks!

1 Like