MySQL5.6 error OPTION SQL_SELECT_LIMIT=1

I use ignition 7.6 beta and mysql 5.6 32 bits on windows 7.

I have the following error :

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ‘OPTION SQL_SELECT_LIMIT=1’ at line 1

[quote]
Time Logger Message
[ERROR] 11:46:13 StoreAndForward.histo_eco2c.TagHistoryDatasourceSink Error initializing data sink.

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 ‘OPTION SQL_SELECT_LIMIT=1’ at line 1
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2934)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3249)
at com.mysql.jdbc.Connection.execSQL(Connection.java:3178)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1408)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper$SRPreparedStatement.executeQuery(SRConnectionWrapper.java:808)
at com.inductiveautomation.ignition.gateway.datasource.SRConnectionWrapper.runScalarPrepQuery(SRConnectionWrapper.java:193)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.getDriverId(TagHistoryDatasourceSink.java:336)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.createPartitionManager(TagHistoryDatasourceSink.java:247)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.initialize(TagHistoryDatasourceSink.java:219)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractSink.tryToInit(AbstractSink.java:112)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.tryToInit(TagHistoryDatasourceSink.java:208)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractSink.isAccepting(AbstractSink.java:129)
at com.inductiveautomation.ignition.gateway.history.sinks.AbstractDatasourceSink.isAccepting(AbstractDatasourceSink.java:82)
at com.inductiveautomation.ignition.gateway.sqltags.history.storage.TagHistoryDatasourceSink.isAccepting(TagHistoryDatasourceSink.java:285)
at com.inductiveautomation.ignition.gateway.history.sinks.AggregateSink.isAccepting(AggregateSink.java:88)
at com.inductiveautomation.ignition.gateway.history.forwarders.ForwarderThread.run(ForwarderThread.java:122)

(I) 11:46:13 StoreAndForward.histo_eco2c.TagHistoryDatasourceSink SQLTags history tables verified successfully.
[ERROR] 11:45:43 StoreAndForward.histo_eco2c.TagHistoryDatasourceSink Error initializing data sink.
[/quote]

I use the last mysql-connector-java-5.1.23-bin.jar jdbc driver.
I’v read “OPTION SQL_SELECT_LIMIT=1” must be replace by “SET SQL_SELECT_LIMIT=1;”

how to solve this pb ???

Hi,

This is a common pb with MySQL 5.6 ; just update the jar jdbc connector :

  • download from this place : mysql.com/downloads/connector/j/
  • and extract the “mysql-connector-java-5.X.XX-bin.jar” from the zip
  • go to the gateway configuration page and edit the driver (database>drivers)
  • et voilà

Hope this help

I’ve update to mysql-connector-java-5.1.24-bin.jar
restarted the gateway
but I’ve always the same error message ?? :scratch:

Yes it’s ok, after reloading the jar and restarting the gateway and cleaning the store and forward cache