MySQL Bugs: #15346: SyncProviderException with CachedRowSet when updating TINYINT

Bug #15346	SyncProviderException with CachedRowSet when updating TINYINT
Submitted:	30 Nov 2005 11:16	Modified:	20 Dec 2005 18:39
Reporter:	Jamie Garton	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.0.15-nt	OS:	Windows (Windows XP Pro SP2)
Assigned to:		CPU Architecture:	Any

Description:
I'm getting the folllowing error when trying to update either a TINYINT or SMALLINT 
column yet a VARCHAR column works fine.

Error message from test program output.
item_id name phases feed_from
1 test string 3 1
2 Level 6A DB 3 1
*******************
*******************
item_id name phases feed_from
1 test string 0 1
2 Level 6A DB 3 1
*******************
javax.sql.rowset.spi.SyncProviderException: 2 conflicts while synchronizing
at com.sun.rowset.internal.CachedRowSetWriter.writeData(Unknown Source)
at com.sun.rowset.CachedRowSetImpl.acceptChanges(Unknown Source)
at com.rjg.test.TestCachedRowSet.<init>(TestCachedRowSet.java:53)
at com.rjg.test.TestCachedRowSet.main(TestCachedRowSet.java:105)

How to repeat:
He is test code that I have written to recreate the error.

import java.sql.SQLException;
import java.sql.ResultSetMetaData;
import javax.sql.rowset.spi.SyncProviderException;

import javax.sql.rowset.CachedRowSet;
import com.sun.rowset.CachedRowSetImpl;

public class TestCachedRowSet {

private final String DB_URL =
"jdbc:mysql://localhost:3306/test?dumpQueriesOnException=true";
private final String USERNAME = "username";
private final String PASSWORD = "password";

private CachedRowSet rowSet = null;
private ResultSetMetaData metaData = null;

//==============================================================================

public TestCachedRowSet( ) {

try {

Class.forName( "com.mysql.jdbc.Driver" ); // load database driver class
rowSet = new CachedRowSetImpl();
rowSet.setUrl(DB_URL); // set database URL
rowSet.setUsername(USERNAME); // set username
rowSet.setPassword(PASSWORD); // set password
rowSet.setCommand("select * from item"); // set query
rowSet.execute();
metaData = rowSet.getMetaData();
showTable();

for ( int i = 0; i < 10; i++) {

System.err.println("*******************");
rowSet.first();

/* if this line is used the updates work fine */
// rowSet.updateString(2, ""+i);

/* this line will cause an error */
rowSet.updateInt(3, Integer.parseInt(""+(i)));

/* this line will cause an error */
// rowSet.updateInt(4, Integer.parseInt(""+i));

rowSet.updateRow();
rowSet.acceptChanges();
System.err.println("*******************");
showTable();

}// for.

rowSet.close();

} catch ( SQLException sqlException ) {
sqlException.printStackTrace();
System.exit( 1 );
} catch ( ClassNotFoundException classNotFound ) {
classNotFound.printStackTrace();
System.exit( 1 );
}// catch.

}// Constructor.

//==============================================================================

private void showTable( ) {

try {

int numberOfColumns = metaData.getColumnCount();
rowSet.beforeFirst();

// display rowset header
for ( int i = 1; i <= numberOfColumns; i++ ) {
System.err.print(metaData.getColumnName(i) + "\t");
}
System.err.println();

// display each row
while ( rowSet.next() ) {
for ( int i = 1; i <= numberOfColumns; i++ ) {
System.err.print(rowSet.getObject(i)+ "\t");
}// for.
System.err.println();
} // end while

} catch ( SQLException sqlException ) {
sqlException.printStackTrace();
System.exit( 1 );
}// catch.

}// showTable.

//==============================================================================

public static void main(String[] args) {

new TestCachedRowSet();

}// main.

//==============================================================================

}// TestCachedRowSet.

You can use this sql to create the item table.

USE test;

CREATE TABLE item (
item_id SMALLINT AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
phases TINYINT NOT NULL,
feed_from SMALLINT,
PRIMARY KEY (item_id)
);

insert into item (name, phases, feed_from)
values
('test string', 3, 1),
('Level 6A DB', 3, 1);

Suggested fix:
None

Thanks for the bug report. I was able to reproduce the bug.

I'm asking Sun whether they can help out with this, as they don't ship source for their CachedRowset implementation, and it seems to be related to their implementation.

It seems that a workaround is to _not_ use a TINYINT field. If the TINYINT field is changed to an INT, the testcase works. If you debug the SQL that Sun's CachedRowset provider creates, the row exists as it expects, but the way it compares it doesn't work, which causes the synchronization failure.

We'll see if we get any input from Sun before debugging this further, as unfortunately, it's a black box once you're inside their implementation.

I've spoken with the lead for Sun's RowSET RI, and this is indeed a bug with their implementation and how it handles TINYINT values (it maps them to a Byte instance internally, and then eventually compares them with .getObject() on the same column, which returns an Integer as required by the JDBC API, and then fails).

I'll update this with a bug number from Sun when I get one.

Currently, the workaround is to use any integer type that is larger than TINYINT.

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/298