MySQL Bugs: #15383: updating an updatable ResultSet with a BigInteger on an UNSIGNED BIGINT fails

Bug #15383	updating an updatable ResultSet with a BigInteger on an UNSIGNED BIGINT fails
Submitted:	1 Dec 2005 14:15	Modified:	10 Mar 2006 20:25
Reporter:	Aurélien Gathelot	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.11	OS:	Windows (Windows XP SP2)
Assigned to:		CPU Architecture:	Any

Description:
Updating a row on an updatable ResultSet with a BigInteger object on an UNSIGNED INT fails. A MysqlDataTruncation is throwned when updateRow() is executed.

As the BigInteger class is not in the if/else if instance of liste in the PreparedStatement.setObject() method, it is serialized. A (very) quick debug session showed a queer byte array length of 203 bytes when updating that field.

How to repeat:
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.util.BaseBugReport;

public class BugReport1 extends BaseBugReport {
	Connection con;
	Statement stm;
	
	public BugReport1() {
		super();
		// TODO Auto-generated constructor stub
	}

	public void setUp() throws Exception {
		con = DriverManager.getConnection(getUrl());
		stm = con.createStatement();
		// table creation
		stm.executeUpdate("CREATE TABLE bug1 (id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,value BIGINT UNSIGNED NULL DEFAULT 0,PRIMARY KEY(id))ENGINE=InnoDB;");
		// insert 1 line
		stm.executeUpdate("INSERT INTO bug1(value) VALUES(1)");
	}

	public void tearDown() throws Exception {
		con = DriverManager.getConnection(getUrl());
		stm = con.createStatement();
		stm.executeUpdate("DROP TABLE bug1");
		stm.close();
	}

	public void runTest() throws Exception {
		con = DriverManager.getConnection(getUrl());
		stm = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = null;
		String value = "1";
		
		// testing reading and updating from ResultSet
		try {
			rs = stm.executeQuery("SELECT * from bug1");
			if (rs.next()) {
				// updating a BigInteger as a String works
				rs.updateString("value", new BigInteger("2").toString());
				rs.updateRow();
				// if correctly updated, now value="2"
				value = rs.getString("value");
			}
			rs.close();
		} catch (SQLException e) {}
		assertTrue("String value 2 wasn't updated correctly to an UNSIGNED BIGINT", (value.equals("2")));
		
		// testing reading and updating from ResultSet
		try {
			rs = stm.executeQuery("SELECT * from bug1");
			if (rs.next()) {
				// updating a BigInteger fails
				rs.updateObject("value", new BigInteger("3"));
				rs.updateRow();
				// if correctly updated, now value="3"
				value = rs.getString("value");
			}
			rs.close();
		} catch (SQLException e) {}
		assertTrue("BigInteger value 3 wasn't updated correctly to an UNSIGNED BIGINT", (value.equals("3")));
	}

	public static void main(String[] args) throws Exception {
		new BugReport1().run();
	}

}

Suggested fix:
A simple workaround : insert the value as a String (as shown in the BugReport class).

As I didn't studied how the connector is written, I can't suggest anything for the fix.

Test class to reproduce the submitted bug

Attachment: BugReport1.java (application/octet-stream, text), 2.22 KiB.

Thanks for the bug report. I was able to reproduce the bug. I've got the follow exception:

Exception in thread "main" java.lang.Exception: Assertion failed: BigInteger value 3 wasn't updated correctly to an UNSIGNED BIGINT

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/265

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/266

Fix will be available in 5.0.1 and 3.1.13. You can try a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j if you want to test the fix before it's officially released. Thanks for the bug report.