Description:
When using updatable ResultSets, Connector/J creates and issues a SELECT statement to refresh a row after completing an UPDATE. The statement does not account for the data type of the primary key:
13 Query /* mysql-connector-java-5.1.14-SNAPSHOT ( Revision: todd.farmer@sun.com-20101005223618-1am3srykr2tx5p1n ) */SHOW VARIABLES ...
13 Query /* mysql-connector-java-5.1.14-SNAPSHOT ( Revision: todd.farmer@sun.com-20101005223618-1am3srykr2tx5p1n ) */SELECT @@session.auto_increment_increment
13 Query SHOW COLLATION
13 Query SET NAMES latin1
13 Query SET character_set_results = NULL
13 Query SET autocommit=1
13 Query SET sql_mode='STRICT_TRANS_TABLES'
13 Query SELECT * FROM t1 WHERE a = 2
13 Query SHOW KEYS FROM `t1` FROM `test`
13 Query UPDATE `test`.`t1` SET `test`.`t1`.`a`='2',`test`.`t1`.`b`=3 WHERE `test`.`t1`.`a`<=>2
13 Query SELECT `test`.`t1`.`a`,`test`.`t1`.`b` FROM `test`.`t1` WHERE `test`.`t1`.`a`<=>'2'
How to repeat:
public static void main(String[] args) {
try {
Class.forName("com.mysql.jdbc.Driver");
String user = "root";
String pass = "";
String url = "jdbc:mysql://localhost:3311/test";
Connection conn = DriverManager.getConnection(url, user, pass);
String sql = "SELECT * FROM t1 WHERE a = ?";
PreparedStatement ps = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
ps.setInt(1, 2);
ResultSet rs = ps.executeQuery();
while(rs.next()){
rs.updateInt(2, rs.getInt(2) - 1);
rs.updateRow();
}
rs.close();
} catch (Exception e) {
e.printStackTrace();
}
}
Check general query log.
Suggested fix:
Issue correct parameter setXXX() method depending on data type of column.
© 2024, Oracle Corporation and/or its affiliates
Description: When using updatable ResultSets, Connector/J creates and issues a SELECT statement to refresh a row after completing an UPDATE. The statement does not account for the data type of the primary key: 13 Query /* mysql-connector-java-5.1.14-SNAPSHOT ( Revision: todd.farmer@sun.com-20101005223618-1am3srykr2tx5p1n ) */SHOW VARIABLES ... 13 Query /* mysql-connector-java-5.1.14-SNAPSHOT ( Revision: todd.farmer@sun.com-20101005223618-1am3srykr2tx5p1n ) */SELECT @@session.auto_increment_increment 13 Query SHOW COLLATION 13 Query SET NAMES latin1 13 Query SET character_set_results = NULL 13 Query SET autocommit=1 13 Query SET sql_mode='STRICT_TRANS_TABLES' 13 Query SELECT * FROM t1 WHERE a = 2 13 Query SHOW KEYS FROM `t1` FROM `test` 13 Query UPDATE `test`.`t1` SET `test`.`t1`.`a`='2',`test`.`t1`.`b`=3 WHERE `test`.`t1`.`a`<=>2 13 Query SELECT `test`.`t1`.`a`,`test`.`t1`.`b` FROM `test`.`t1` WHERE `test`.`t1`.`a`<=>'2' How to repeat: public static void main(String[] args) { try { Class.forName("com.mysql.jdbc.Driver"); String user = "root"; String pass = ""; String url = "jdbc:mysql://localhost:3311/test"; Connection conn = DriverManager.getConnection(url, user, pass); String sql = "SELECT * FROM t1 WHERE a = ?"; PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); ps.setInt(1, 2); ResultSet rs = ps.executeQuery(); while(rs.next()){ rs.updateInt(2, rs.getInt(2) - 1); rs.updateRow(); } rs.close(); } catch (Exception e) { e.printStackTrace(); } } Check general query log. Suggested fix: Issue correct parameter setXXX() method depending on data type of column.