MySQLSyntaxErrorException in overlord node

Hi,

I am getting MySQLSyntaxErrorException from overlord task queue.

ERROR [2015-03-23T20:09:50,055] io.druid.indexing.overlord.TaskQueue: Failed to persist status for task: {class=io.druid.indexing.overlord.TaskQueue, exceptionType=class org.skife.jdbi.v2.exceptions.CallbackFailedException, exceptionMessage=org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:“SELECT status_payload FROM druid_tasks WHERE id = :id”, located:“SELECT status_payload FROM druid_tasks WHERE id = :id”, rewritten:“SELECT status_payload FROM druid_tasks WHERE id = ?”, arguments:{ positional:{}, named:{id:‘index_realtime_datasource_2015-03-23T19:46:00.000Z_0_0_ejkfimcp’}, finder:}], task=index_realtime_datasource_2015-03-23T19:46:00.000Z_0_0_ejkfimcp, statusCode=SUCCESS}
com.mysql.jdbc.exceptions.jdbc4.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=DEFAULT’ at line 1
at sun.reflect.GeneratedConstructorAccessor84.newInstance(Unknown Source) ~[?:?]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.8.0_31]
at java.lang.reflect.Constructor.newInstance(Constructor.java:408) ~[?:1.8.0_31]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2618) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.ConnectionImpl.unsetMaxRows(ConnectionImpl.java:5424) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2489) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:3091) ~[mysql-connector-java-5.1.18.jar:?]
at com.mysql.jdbc.PreparedStatement.close(PreparedStatement.java:1156) ~[mysql-connector-java-5.1.18.jar:?]
at org.apache.commons.dbcp2.DelegatingStatement.close(DelegatingStatement.java:156) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.apache.commons.dbcp2.DelegatingStatement.close(DelegatingStatement.java:156) ~[commons-dbcp2-2.0.1.jar:2.0.1]
at org.skife.jdbi.v2.DefaultStatementBuilder.close(DefaultStatementBuilder.java:63) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Cleanables$StatementBuilderCleanable.cleanup(Cleanables.java:84) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.BaseStatement$StatementCleaningCustomizer.cleanup(BaseStatement.java:111) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.BaseStatement.cleanup(BaseStatement.java:87) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.fold(Query.java:190) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.first(Query.java:267) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.first(Query.java:259) ~[jdbi-2.32.jar:?]
at io.druid.metadata.SQLMetadataStorageActionHandler$4.withHandle(SQLMetadataStorageActionHandler.java:193) ~[druid-server-0.7.0.jar:0.7.0]
at io.druid.metadata.SQLMetadataStorageActionHandler$4.withHandle(SQLMetadataStorageActionHandler.java:189) ~[druid-server-0.7.0.jar:0.7.0]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]
… 21 more
Wrapped by: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:“SELECT status_payload FROM druid_tasks WHERE id = :id”, located:“SELECT status_payload FROM druid_tasks WHERE id = :id”, rewritten:“SELECT status_payload FROM druid_tasks WHERE id = ?”, arguments:{ positional:{}, named:{id:‘index_realtime_datasource_2015-03-23T19:46:00.000Z_0_0_ejkfimcp’}, finder:}]
at org.skife.jdbi.v2.BaseStatement.cleanup(BaseStatement.java:90) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.fold(Query.java:190) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.first(Query.java:267) ~[jdbi-2.32.jar:?]
at org.skife.jdbi.v2.Query.first(Query.java:259) ~[jdbi-2.32.jar:?]
at io.druid.metadata.SQLMetadataStorageActionHandler$4.withHandle(SQLMetadataStorageActionHandler.java:193) ~[druid-server-0.7.0.jar:0.7.0]
at io.druid.metadata.SQLMetadataStorageActionHandler$4.withHandle(SQLMetadataStorageActionHandler.java:189) ~[druid-server-0.7.0.jar:0.7.0]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:259) ~[jdbi-2.32.jar:?]
… 21 more
Wrapped by: org.skife.jdbi.v2.exceptions.CallbackFailedException: org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: Could not clean up [statement:“SELECT status_payload FROM druid_tasks WHERE id = :id”, located:“SELECT status_payload FROM druid_tasks WHERE id = :id”, rewritten:“SELECT status_payload FROM druid_tasks WHERE id = ?”, arguments:{ positional:{}, named:{id:‘index_realtime_datasource_2015-03-23T19:46:00.000Z_0_0_ejkfimcp’}, finder:}]
at org.skife.jdbi.v2.DBI.withHandle(DBI.java:262) ~[jdbi-2.32.jar:?]
at io.druid.metadata.SQLMetadataStorageActionHandler$12.call(SQLMetadataStorageActionHandler.java:483) ~[druid-server-0.7.0.jar:0.7.0]
at com.metamx.common.RetryUtils.retry(RetryUtils.java:38) ~[java-util-0.26.14.jar:?]
at io.druid.metadata.SQLMetadataStorageActionHandler.retryingHandle(SQLMetadataStorageActionHandler.java:496) ~[druid-server-0.7.0.jar:0.7.0]
at io.druid.metadata.SQLMetadataStorageActionHandler.getStatus(SQLMetadataStorageActionHandler.java:187) ~[druid-server-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.MetadataTaskStorage.getStatus(MetadataTaskStorage.java:176) ~[druid-indexing-service-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.TaskQueue.notifyStatus(TaskQueue.java:389) [druid-indexing-service-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.TaskQueue.access$500(TaskQueue.java:65) [druid-indexing-service-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.TaskQueue$4.handleStatus(TaskQueue.java:457) [druid-indexing-service-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.TaskQueue$4.onSuccess(TaskQueue.java:433) [druid-indexing-service-0.7.0.jar:0.7.0]
at io.druid.indexing.overlord.TaskQueue$4.onSuccess(TaskQueue.java:428) [druid-indexing-service-0.7.0.jar:0.7.0]

Any idea why this might be happening?

I’m using Druid 0.7.0.

What version of MySQL are you using?

Wondering if you are hitting this:

http://bugs.mysql.com/bug.php?id=66659

Perhaps it is time to update some of our dependencies.

Hmm, I am using mysql-server-5.6.

As per that thread, it’s not MySQL bug because SET OPTION was deprecated and eventually removed long time ago.

I will try to use latest mysql-connector-java jar and let you know if it fixes the problem.

Updating mysq-connector.jar to 5.1.34 [1] seems to have fixed the issue.

https://search.maven.org/#artifactdetails|mysql|mysql-connector-java|5.1.34|jar

Thanks for checking

PR in for the fix.