Bug #66659 mysql 5.6.6m9 fails on OPTION SQL_SELECT_LIMIT=DEFAULT
Submitted: 3 Sep 2012 3:37 Modified: 5 Sep 2012 16:49
Reporter: A Ramos Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.6.6 OS:Any (XP and Linux)
Assigned to: CPU Architecture:Any

[3 Sep 2012 3:37] A Ramos
Description:
Major Open Source java application (Pentaho Data Integration v4.3.0) which works fine in mysql 5.0-5.5, fails in 5.6.6m9 with this exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have a
n error in your SQL syntax; check the manual that corresponds to your MySQL serv
er version for the right syntax to use near 'OPTION SQL_SELECT_LIMIT=DEFAULT' at
 line 1
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

        at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Sou
rce)
        at java.lang.reflect.Constructor.newInstance(Unknown Source)
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
        at com.mysql.jdbc.Util.getInstance(Util.java:381)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2548)
        at com.mysql.jdbc.ConnectionImpl.unsetMaxRows(ConnectionImpl.java:5391)
        at com.mysql.jdbc.StatementImpl.realClose(StatementImpl.java:2304)
        at com.mysql.jdbc.PreparedStatement.realClose(PreparedStatement.java:268
8)
        at com.mysql.jdbc.ConnectionImpl.closeAllOpenStatements(ConnectionImpl.j
ava:1489)
        at com.mysql.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:4289)
        at com.mysql.jdbc.ConnectionImpl.close(ConnectionImpl.java:1461)
        at org.pentaho.di.core.database.Database.closeConnectionOnly(Database.ja
va:592)

How to repeat:
Use the Table Input or Table Output with JNDI connection in PDI/Kettle.

Suggested fix:
Enable backward-compatible syntax for OPTION SQL_SELECT_LIMIT=DEFAULT
[3 Sep 2012 16:57] Sveta Smirnova
Thank you for the report.

But option SQL_SELECT_LIMIT still supported: http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sql_select_limi...

Please enable general query log, run your application again and send us full query which it sends.
[5 Sep 2012 11:35] Elena Stepanova
It's not the option that was deprecated (and removed in 5.6), but the syntax 'SET OPTION ...'.
[5 Sep 2012 16:49] Sveta Smirnova
Elena,

thank you for the explanation.

SET OPTION was deprecated years ago: http://dev.mysql.com/doc/refman/5.0/en/set-statement.html, so developers of this application had plenty of time to fix it. This is not MySQL bug.

As workaround you can use MySQL Proxy or c/J plugin with proxy functionality.
[3 Dec 2012 14:23] Leonid Bushuev
I don't use this option explicitly, it is added by the jdbc driver.
Is there a workaround for this bug/feature?
[15 Mar 2013 17:23] Raja Kumar
You could use mysql-connector-java-5.1.23-bin.jar file in place of the old connector in case of Java i.e use the respective mysql server version supported connector
[2 Nov 2013 12:27] Vivek Srivastava
To remove this error in pentaho for MySQL 5.6,use jar file 'mysql-connector-java-5.1.26'.First copy it in 'lib' folder and then run pentaho.