| Bug #43196 | Statement.getGeneratedKeys() use long but unsigned bigint(20) use BigInteger | ||
|---|---|---|---|
| Submitted: | 25 Feb 2009 16:12 | Modified: | 18 Dec 2009 16:31 |
| Reporter: | cole dda | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.1.31 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | getgeneratedkeys, last_insert_id() | ||
[25 Feb 2009 17:50]
Tonci Grgin
Test case for our test framework.
Attachment: TestBug43196.java (text/x-java), 2.70 KiB.
[25 Feb 2009 17:52]
Tonci Grgin
Thank you Cole for your report. Verified as described using attached test case on bzr source repo: .Loading JDBC driver 'com.mysql.jdbc.Driver' Done. Done. Connected to 5.1.31-log java.vm.version : 1.5.0_17-b04 java.vm.vendor : Sun Microsystems Inc. java.runtime.version : 1.5.0_17-b04 os : Windows Server 2008, 6.0, x86 sun.management.compiler : HotSpot Client Compiler ------------------------------------------------- F Time: 120,614 There was 1 failure: 1) testBug43196(testsuite.simple.TestBug43196)junit.framework.AssertionFailedError: No rows returned at testsuite.simple.TestBug43196.testBug43196(TestBug43196.java:55) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at testsuite.simple.TestBug43196.main(TestBug43196.java:72) FAILURES!!! Tests run: 1, Failures: 1, Errors: 0 mysql> select * from bug43196; +----------------------+---+ | id | a | +----------------------+---+ | 1 | 1 | | 18446744073709551200 | 1 | +----------------------+---+ 2 rows in set (0.00 sec)
[28 May 2009 19:38]
Mark Matthews
This is now fixed, but it's not optimal, because the server doesn't give clients enough information to be consistent. Unfortunately, because the server doesn't tell clients what TYPE the auto increment value is, the driver can't consistently return BigIntegers for the result set returned from getGeneratedKeys(), it will only return them if the value is > Long.MAX_VALUE. If your application needs this consistency, it will need to check the class of the return value from .getObject() on the ResultSet returned by Statement.getGeneratedKeys() and if it's not a BigInteger, create one based on the java.lang.Long that is returned.
[18 Dec 2009 16:31]
Tony Bedford
An entry has been added to the 5.2.8 changelog: The method Statement.getGeneratedKeys() did not return values for UNSIGNED BIGINTS with values greater than Long.MAX_VALUE. Unfortunately, because the server does not tell clients what TYPE the auto increment value is, the driver cannot consistently return BigIntegers for the result set returned from getGeneratedKeys(), it will only return them if the value is greater than Long.MAX_VALUE. If your application needs this consistency, it will need to check the class of the return value from .getObject() on the ResultSet returned by Statement.getGeneratedKeys() and if it is not a BigInteger, create one based on the java.lang.Long that is returned.
Description: about get auto_increment key: CREATE TABLE test1 ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Statement state = conn.createStatement(); state.executeUpdate("insert into test1(a) values(1)", Statement.RETURN_GENERATED_KEYS); ResultSet rs = state.getGeneratedKeys(); if(rs.next()){ long id = rs.getObject(1);//use long } rs.close(); rs = state.executeQuery("select id from test1"); if(rs.next()){ BigInteger id = rs.getObject(1);//use BigInteger } rs.close(); //insert a id > Long.MAX_VALUE(9223372036854775807) state.executeUpdate("insert into test1(id,a) values(18446744073709551615,1)", Statement.RETURN_GENERATED_KEYS); //insert successfully rs = state.getGeneratedKeys();//or rs = state.executeQuery("select last_insert_id()"); if(rs.next()){//false //can't return id }else{ //goto to here } How to repeat: CREATE TABLE test1 ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `a` bigint(20) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Statement state = conn.createStatement(); state.executeUpdate("insert into test1(a) values(1)", Statement.RETURN_GENERATED_KEYS); ResultSet rs = state.getGeneratedKeys(); if(rs.next()){ long id = rs.getObject(1);//use long } rs.close(); rs = state.executeQuery("select id from test1"); if(rs.next()){ BigInteger id = rs.getObject(1);//use BigInteger } rs.close(); //insert a id > Long.MAX_VALUE(9223372036854775807) state.executeUpdate("insert into test1(id,a) values(18446744073709551615,1)", Statement.RETURN_GENERATED_KEYS); //insert successfully rs = state.getGeneratedKeys();//or rs = state.executeQuery("select last_insert_id()"); if(rs.next()){//false //can't return id }else{ //goto to here } Suggested fix: getGeneratedKeys() and "select last_insert_id()") use BigInteger for unsigned BIGINT(20)