MySQL Bugs: #50968: Insertion id is Long instead of BigInteger for BIGINT unsigned ID column

Bug #50968	Insertion id is Long instead of BigInteger for BIGINT unsigned ID column
Submitted:	6 Feb 2010 23:09	Modified:	9 Feb 2010 9:50
Reporter:	shaoxian yang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.10	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	mysql jdbc BIGINT problem

Description:
Hi,

I am using MySQL server 5.1.36 and JDBC driver (mysql connector/J) 5.1.10.  The primary key of the table is BIGINT unsigned type.  According to documentation, if it is unsigned, java.math.BigInteger shall be used.

I use a prepared statement for insertion with option to return the ID as the resultset of the prepared statement.  

            conn = getConnection();
            pst = conn.prepareStatement("insert into placement_event(type, detailed_type, tracking_id, version) values (?, ?, unhex(?), ?)", Statement.RETURN_GENERATED_KEYS);

            pst.setString(1, "PlacementStatus");
            pst.setString(2, "startPlacement");
            pst.setString(3, "6E11E13012FF11DFB6E4028037EC0200");
            pst.setInt(4, 1);

            pst.executeUpdate();
            rs = pst.getGeneratedKeys();

            while (rs.next()) {

                System.out.println("bigint column uses java type: " + rs.getObject(1).getClass().getName());
                java.math.BigInteger eventId = (java.math.BigInteger)rs.getObject(1);

            }

I get the following exception for the code above, showing that returned resultset object is java.lang.Long, instead of java.math.BigInteger.  This does not match what documentation mentions.

java.lang.ClassCastException: java.lang.Long cannot be cast to java.math.BigInteger

I also tried without using GENERATED_KEYS mode, instead just query DB "select last_insert_id" after the original insertion.   I get the same classcast exception.

However, after the insertion, when I query DB with "select id from placement_event" using jdbc, in that case, it is fine retrieving java.math.BigInteger as id.  

Therefore, I believe the implementation is mostly correctly, but broken with returning id as java.lang.BigInteger at the end of insertion.  Maybe that has something to do with mysql auto_increment?

How to repeat:
Download the attachment.

mysql script will create the test table with ID as "unsigned" BIGINT type

The java program has two methods.  One to insert a record and returning an id at the end, which is broken with ClassCastException.  The other will just query the DB assuming you already put a record there, which is fine.  This is just provided as a contrast.

Test files to verify the bug reporting

Attachment: bigint_bug_test.rar (application/octet-stream, text), 2.11 KiB.

This is a server/protocol bug. MySQL doesn't return any sign information as part of the return value for an INSERT containing an auto increment column, therefore the driver can't imply the type ahead of time.

If, however, the server does return an unsigned long value that is larger than Long.MAX_VALUE (and thus the sign goes negative), the driver detects this condition and returns BigInteger as the type with the correct unsigned value.

Mark, Thanks for the reply.  That makes sense, as long as it will return biginteger when value exceed the long limit. 

I will modify my application logic since my entity objecty field type if BigInteger:  explicitly check the returned type from result.  If it is long, convert to BigInteger.  If it is BigInteger, return as it is.

May I check if this still remain a bug but belonging to Server bug, or there is nothing to be fixed even at MYSQL server side?

Thanks.  If it is not bug, please close it, or instruct me to do so.

Shaoxian Yang

Shaoxian, there is already a bug report for your problem; Bug#20964.