MySQL Bugs: #44656: When inserting a new row into a FilteredRowSet in java, MySQL throws exception

Bug #44656	When inserting a new row into a FilteredRowSet in java, MySQL throws exception
Submitted:	5 May 2009 1:32	Modified:	13 Jun 2013 21:08
Reporter:	Daniel White	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.1.6	OS:	Windows (XP Pro)
Assigned to:	Jess Balint	CPU Architecture:	Any

Description:
In the database, test_table has two fields: i1 and i2, both integers, both in the primary key.  It has one record with values (1, 1).

In the Java program, I select the whole table into a FilteredRowSet, insert a new row into the FilteredRowSet with values (1, 2), and try to save the new row to the database by telling the FilteredRowSet to acceptChanges.

Instead of the new record being saved, I get this exception: 

javax.sql.rowset.spi.SyncProviderException: 2conflicts while synchronizing

(By contrast, if I run the same Java code against an Apache Derby database, the new row gets saved to the database with no errors.)

How to repeat:
Definition of table `test_table` in mySQL:

-------------------

CREATE TABLE `test_table` (
  `i1` int(10) unsigned NOT NULL,
  `i2` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`i1`,`i2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-------------------

Java file Main.java:

-------------------

package test;

import com.sun.rowset.FilteredRowSetImpl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.rowset.FilteredRowSet;

public class Main {

    public static void main(String[] args) {
        try {
            String mysqlUrl = "jdbc:mysql://localhost:3306/test";
            String derbyUrl = "jdbc:derby:TestDB1";

            // get connection to database
            Connection connection = DriverManager.getConnection(mysqlUrl);
            connection.setAutoCommit(false);

            // set up the initial records
            Statement statement = connection.createStatement();
            statement.executeUpdate("Delete from test_table");
            statement.executeUpdate("Insert into test_table (i1, i2) values (1, 1)");

            // select records into a filtered row set
            FilteredRowSet results = new FilteredRowSetImpl();
            results.setCommand("select * from test_table");
            results.execute(connection);

            // insert a new row
            results.moveToInsertRow();
            results.updateInt("i1", 1);
            results.updateInt("i2", 2);
            results.insertRow();
            results.moveToCurrentRow();

            // commit changes
            //  This works in derby.
            results.acceptChanges(); 
            //  In mySQL, the previous line throws this exception:
            //  javax.sql.rowset.spi.SyncProviderException: 2conflicts while synchronizing
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Hi Daniel and thanks for your report.

Reading http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/FilteredRowSet.html I came to this conclusion (no matter how Apache works):
  o To perform an insert, the cursor must be moved to a special position called "insert row" which is a property of FilteredRowSet whose Range predicate has to be set for this to work...

So, please add range and apply filter:
Range my_filter = new Range(1,100,1);
frs.setFilter(my_filter);

Then retest and inform me of result.

"Range" is not a standard Java class; it is a user-defined class that implements the Predicate interface.  The documentation for FilteredRowSet links to this page documenting the Predicate interface, which contains the source code for the Range class -- it is just an example Predicate implementation.
http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/Predicate.html

I did test this code using a filter that implements Predicate, and I got the same exception when using MySQL.

The problem is actually not related to the FilteredRowSet class; FilteredRowSet is an extension of CachedRowSet, and CachedRowSet is supposed to provide the ability to update the records locally and then use acceptChanges() to push all updates to the database.  See this documentation for CachedRowSet:
http://java.sun.com/j2se/1.5.0/docs/api/javax/sql/rowset/CachedRowSet.html

When I replace the declaration of the results object with this line, I still get the same exception, which shows that the problem is related to CachedRowSet:
CachedRowSet results = new CachedRowSetImpl();

Again, when I connect to a Derby database and run the same test with a CachedRowSet object, I do not get the exception, which implies that the problem is somehow related to MySQL.

Also, when I switch back to the MySQL connection, comment out the line that inserts the first initial record, and try to update a CachedRowSet that originally had no records in it, the CachedRowSet is able to update the database without throwing an exception:
// statement.executeUpdate("Insert into test_table (i1, i2) values (1, 1)");

So, the same basic code is producing different results merely because the CachedRowSet has different data in it.  That tells me that something is not working correctly.

I should have been more precise with my original bug report and submitted an example that used CachedRowSet instead of FilteredRowSet.

Noted.

Test case for our test framework.

Attachment: TestBug44656.java (text/x-java), 2.76 KiB.

Verified as described. Attached test case gives following output:
.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
-------------------------------------------------
E
Time: 0,323
There was 1 error:
1) testBug44656(testsuite.simple.TestBug44656)javax.sql.rowset.spi.SyncProviderException: Invalid operation while on insert row
	at com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:848)
	at testsuite.simple.TestBug44656.testBug44656(TestBug44656.java:69)
	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.TestBug44656.main(TestBug44656.java:88)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

I tried varying CachedRowSet, RowSet but using .acceptChanges(); leads to exception...

General query log shows MySQL server did nothing regarding insert of (1,2) pair.

Test case for our test framework, working in JDK 1.5.

Attachment: TestBug44656.java (text/x-java), 2.77 KiB.

Daniel, I have attached new test case as first one was wrong. This test case works on JDK 1.5 but fails on 1.6. See if you can use it as workaround.

Much simpler test case *not* using reflection works on JDK 1.5 (and fails on 1.6) too:
    // select records into a filtered row set
    CachedRowSet results = new CachedRowSetImpl();
    results.setCommand("select * from bug44656");
    results.execute(conn);
     
    // insert a new row
    results.moveToInsertRow();
    results.updateInt("i1", 1);
    results.updateInt("i2", 2);
    results.insertRow();
    results.moveToCurrentRow();
     
    // commit changes
    results.acceptChanges();

I can download JDK/JRE 5.0 Update 17 from http://java.sun.com/products/archive/, but Sun doesn't recommend it; it says:

Sun is providing the products available below as a courtesy to developers for problem resolution. The products available here have completed the Sun EOL process and are no longer supported under standard support contracts. These products are down-revision products that may have various bugs, Y2000, and possibly security issues associated with them. Sun in no way recommends these products be used in a live, production environment. Any use of product on this page is at the sole discretion of the developer and Sun assumes no responsiblity for any resulting problems. 

Which means that I could certainly test this problem on Java version 5, but I couldn't build an application that uses Java version 5 and release it to production, so that really isn't a workable option.  I'm not sure that it's worth going through the effort to test it.  

I'm not even sure where the source of the bug is -- I'm assuming that it's in the MySQL JDBC connector, but it could be somewhere else.  Do you have reason to believe that the bug is in the Java 6 runtime environment?  If so, then I'll test it against Java 5 and report the problem to Sun.  Let me know.

Daniel, no need. Report is already verified and the fact that test case works on JDK 1.5 is just for our own reference. It will be fixed.

This is a limitation of com.sun.rowset.internal.CachedRowSetWriter which is used by CachedRowSetImpl. This implementation (as of JDK 7) does not support composite primary keys when checking for conflicts. The values of (1,1) and (1,2) are detected as having the same PK value (even though the PK in the DB spans both columns).

The reason this works with Derby is that it doesn't return any PKs unless the table name is given to DatabaseMetaData.getPrimaryKeys() in all caps (e.g. TEST_TABLE). CachedRowSetWriter calls getPrimaryKeys() to check for conflicts and in this case doesn't detect any as the PKs are not returned. If the command is given as "select * from TEST_TABLE", the same conflict will be encountered. (Tested against Derby 10.10.1.1)