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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.