| Bug #75475 | Updatable ResultSet crashes on updating a table with BIT columns | ||
|---|---|---|---|
| Submitted: | 10 Jan 2015 2:30 | Modified: | 12 Jan 2015 18:34 |
| Reporter: | dachao li | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | Connector / J | Severity: | S3 (Non-critical) |
| Version: | 5.1.34 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[10 Jan 2015 2:34]
dachao li
The bug test case
Attachment: BitColumnBugReport.java (application/octet-stream, text), 1.93 KiB.
[10 Jan 2015 2:40]
dachao li
Updated test case, with assertion and exception catching
Attachment: BitColumnBugReport.java (application/octet-stream, text), 2.25 KiB.
[10 Jan 2015 3:31]
dachao li
Reproduced this bug on both Linux and MacOS. With Java 1.7
[12 Jan 2015 6:25]
MySQL Verification Team
Hello dachao li, Thank you for the report and test case. Thanks, Umesh
[12 Jan 2015 6:26]
MySQL Verification Team
//
[ushastry@ushastry]~/bugs/mysql-5.6.21: java -classpath '.:/home/ushastry/bugs/mysql-connector-java-5.1.34/mysql-connector-java-5.1.34-bin.jar' BitColumnBugReport
Exception in thread "main" 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 ' WHERE `test`.`bug_report`.`id`<=>1' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2325)
at BitColumnBugReport.runTest(BitColumnBugReport.java:60)
at com.mysql.jdbc.util.BaseBugReport.run(BaseBugReport.java:138)
at BitColumnBugReport.main(BitColumnBugReport.java:65)
//
[ushastry@ushastry]~/bugs/mysql-5.6.21: java -classpath '.:/home/ushastry/bugs/mysql-connector-java-5.1.34/mysql-connector-java-5.1.34-bin.jar' BitColumnBugReport
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 ' WHERE `test`.`bug_report`.`id`<=>1' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
at com.mysql.jdbc.Util.getInstance(Util.java:360)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062)
at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2325)
at BitColumnBugReport.runTest(BitColumnBugReport.java:66)
at com.mysql.jdbc.util.BaseBugReport.run(BaseBugReport.java:138)
at BitColumnBugReport.main(BitColumnBugReport.java:78)
Test finished
Description: When using updatable resultset on a table with bit columns, if the bit columns were not explicitly updated with a value using updateBoolean(), then, when updateRow() is called, an exception like this will be thrown: If the bit column has value '0': Exception in thread "main" 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 '' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.Util.getInstance(Util.java:360) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062) at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2325) at BitColumnBugReport.runTest(BitColumnBugReport.java:60) at com.mysql.jdbc.util.BaseBugReport.run(BaseBugReport.java:138) at BitColumnBugReport.main(BitColumnBugReport.java:65) Or, if the bit column has value '1': Exception in thread "main" 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 ' WHERE `test`.`bug_report`.`id`<=>1' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:526) at com.mysql.jdbc.Util.handleNewInstance(Util.java:377) at com.mysql.jdbc.Util.getInstance(Util.java:360) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2530) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1907) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2141) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2062) at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2325) at BitColumnBugReport.runTest(BitColumnBugReport.java:60) at com.mysql.jdbc.util.BaseBugReport.run(BaseBugReport.java:138) at BitColumnBugReport.main(BitColumnBugReport.java:65) I expected the connector would successfully update the row, instead of throwing an exception. How to repeat: 1. Create a table with at least a primary key column, and a column with the type bit(1). 2. Insert a row with any values into the table. 3. In Java code, create a Statement object with the flag ResultSet.CONCUR_UPDATABLE 4. Query the table, get a ResultSet object, navigate to the first(or any) row 5. Update any column except the bit column, with the updateXX() method on the ResultSet object. 6. call updateRow() method on the ResultSet object By inspecting the ResultSet object right before the execution of .updateRow(), the updater has value like this: com.mysql.jdbc.JDBC4PreparedStatement@182bbd37: UPDATE `test`.`bug_report` SET `test`.`bug_report`.`id`=2,`test`.`bug_report`.`deleted`=\u0000 WHERE `test`.`bug_report`.`id`<=>1 or com.mysql.jdbc.JDBC4PreparedStatement@182bbd37: UPDATE `test`.`bug_report` SET `test`.`bug_report`.`id`=2,`test`.`bug_report`.`deleted`=\u0001 WHERE `test`.`bug_report`.`id`<=>1 Where `deleted` is the BIT column. I guess, when it's executing the updating, the query become: UPDATE `test`.`bug_report` SET `test`.`bug_report`.`id`=2,`test`.`bug_report`.`deleted`= The value of the bit column is not correctly escaped or handled. But, for example, if result.updateBoolean("deleted", true); is executed before calling .updateRow(), the `updater` becomes: com.mysql.jdbc.JDBC4PreparedStatement@182bbd37: UPDATE `test`.`bug_report` SET `test`.`bug_report`.`id`=2,`test`.`bug_report`.`deleted`=1 WHERE `test`.`bug_report`.`id`<=>1 where the `deleted` column is assigned a valid value, and no error occurs. Suggested fix: Fix the `updater` variable in the ResultSet object, with a correct/valid value for the column. Or, don't include columns that are not updated in the update SQL.