MySQL Bugs: #66659: mysql 5.6.6m9 fails on OPTION SQL_SELECT

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

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

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.

It's not the option that was deprecated (and removed in 5.6), but the syntax 'SET OPTION ...'.

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.

I don't use this option explicitly, it is added by the jdbc driver.
Is there a workaround for this bug/feature?

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

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.