MySQL Bugs: #68608: UpdatableResultSet does not properly handle unsigned primary key

Bug #68608	UpdatableResultSet does not properly handle unsigned primary key
Submitted:	7 Mar 2013 22:47	Modified:	21 Dec 2022 17:23
Reporter:	Bas Verhoeven	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.23	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The 'setParamValue' function in UpdatableResultSet does not appear to take the signedness of a column into consideration. 

For example, when the primary key is an unsigned integer column, 'setParamValue' will call 'setInt' instead of 'setLong'. That function then throws an exception because the unsigned value provided to it doesn't fit in an Integer.

How to repeat:
-- Database

CREATE TABLE IF NOT EXISTS `test` (
  `foo` int(10) unsigned NOT NULL,
  `bar` int(11) NOT NULL,
  PRIMARY KEY (`foo`)
) ENGINE=MyISAM;

INSERT INTO `test` (`foo`, `bar`) VALUES (3735928559, 1);

-- Code

Statement stmt = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("SELECT * FROM test");
if (rs.next())
{
    rs.updateInt("bar", 2);
}

-- Exception

java.lang.NumberFormatException: 3735928559
	at com.mysql.jdbc.StringUtils.getInt(StringUtils.java:795)
	at com.mysql.jdbc.StringUtils.getInt(StringUtils.java:803)
	at com.mysql.jdbc.ByteArrayRow.getInt(ByteArrayRow.java:107)
	at com.mysql.jdbc.UpdatableResultSet.setParamValue(UpdatableResultSet.java:515)
	at com.mysql.jdbc.UpdatableResultSet.syncUpdate(UpdatableResultSet.java:1558)
	at com.mysql.jdbc.UpdatableResultSet.updateInt(UpdatableResultSet.java:2156)
	at com.mysql.jdbc.UpdatableResultSet.updateInt(UpdatableResultSet.java:2184)

Suggested fix:
I would expect this to work in somewhat the same way as ResultSet.getObject(), which does properly uses the 'Long' class for unsigned integer columns. That would probably involve adding another argument to 'setParamValue' containing the value of field.isUnsigned().

Hi Bas,

Thanks for your report.
Verified as described.

Posted by developer:
 
Added the following entry to the C/J 8.0.32 changelog: 

"Because the value of a MySQL unsigned INTEGER can be larger than what a Java int can hold, a MySQL unsigned INTEGER must be handled as a Java long. However, the rule was not followed by UpdatableResultSet and a couple of ValueEncoders in Connector/J , causing invalid numeric conversions when the unsigned integers were very large. "