MySQL Bugs: #61126: Calling rs.getString after rs.updateString returns the old value from the db

Bug #61126	Calling rs.getString after rs.updateString returns the old value from the db
Submitted:	11 May 2011 2:04	Modified:	11 Jul 2012 12:41
Reporter:	Dmitry Isakbayev	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.13	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	jdbc, oracle compatability

Description:
While testing my application with MySQL I noticed a difference with Oracle's JDBC implementation.  In the following excerpt, I expect the call to getString to return the "new-value"

/* Sometimes the application calls moveToInsertRow() to insert a new row or next() to update an existing row in the database */
...
resultSet.updateString(2,"new-value");
resultSet.getString(2).equals("new-value");
...
/* Sometimes the database does not need to be updated and the changes are canceled */

It works as expected when inserting a new row (calling resultSet.moveToInsertRow() before the two lines above).
However, it does not work when updating a row (calling resultSet.next() before the the two lines above). In this case the call to getString still returns the value in the database.

How to repeat:
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 BugReportVisibilityOfUpdate extends BaseBugReport {
 Connection con;
 Statement stm;

 public BugReportVisibilityOfUpdate() {
  super();
 }

 public void setUp() throws Exception {
  con = DriverManager.getConnection(getUrl());
  stm = con.createStatement();
  stm.executeUpdate("CREATE TABLE bug (id INTEGER UNSIGNED NOT NULL,value VARCHAR(25), PRIMARY KEY(id));");
  stm.executeUpdate("INSERT INTO bug(id,value) VALUES(1,'current-value')");
 }

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

 public void runTest() throws Exception {
  con = DriverManager.getConnection(getUrl());
  stm = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
  ResultSet rs = null;
      /* Test Case 1 - Passed. In the rs.moveToInsertRow test, calling getString after updateString works as expected */
  try {
    rs = stm.executeQuery("SELECT id, value from bug where id = '2'");
    rs.moveToInsertRow();
    assertTrue("Before update getString should return null", rs.getString(2)==null);

    rs.updateString("value", "new-value");
    assertTrue("After update should return 'new-value'", rs.getString(2).equals("new-value"));

    rs.close();
  } catch (SQLException e) {
    System.err.println("SQLException: " + e.getMessage());
  }

  /* Test Case 2 - Failed. In the rs.next test, calling getString after updateString still returns the value from the database
     instead of the value used in the updateString */
  try {
    rs = stm.executeQuery("SELECT id, value from bug where id = '1'");

    rs.next();

    assertTrue("Before update getString should return 'current-value'", rs.getString(2).equals("current-value"));

    rs.updateString("value", "new-value");

    /* The following assert failes.  Oracle returns the "new-value" */
    assertTrue("After update should return 'new-value'", rs.getString(2).equals("new-value"));

    rs.close();
  } catch (SQLException e) {
    System.err.println("SQLException: " + e.getMessage());
  }
 }

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

}

Two test cases for recreating the problem.

Attachment: BugReportVisibilityOfUpdate.java (text/x-java), 2.44 KiB.

Thanks for your report Dmitry. However, I will need two things before start. First I have to check JDBC specs and you should provide your server's SQL mode, status of autocommit variable and a default type of tables created (ie. MyISAM or InnoDB).

Tonci, the application turns autoCommit off.  But I have tried it both ways and results seem to be the same.  I will double check and follow up with answers for the other two questions.

>>What are the status of autocommit variable and type of tables created (ie. MyISAM or InnoDB)?

I have tried changing the test application to use ENGINE MyISAM with autocommit true and false and InnoDB with autocommit true and false.  In all 4 cases the results are the same.

>>What is server's SQL mode?

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   | 
+-------------------+
1 row in set (0.00 sec)

mysql> SELECT @@SESSION.sql_mode;
+--------------------+
| @@SESSION.sql_mode |
+--------------------+
|                    | 
+--------------------+
1 row in set (0.00 sec)

Tonci, thank you for looking into this issue.  
In order to fix this bug or simply to match Oracle's implementation, would it be safe to change the com.mysql.jdbc.UpdatableResultSet.updateXXXX methods to call this.thisRow.setColumnValue when this.onInsertRow equals either true or false?  Right now it is called only when the this.onInsertRow is true.

For background, the reason the driver does this today, is that there is no guarantee that what you write to an updated row will stay that way. If there's a constraint violation, the driver would have to "put back" the old value, and now it is lost. There's also the issues of truncation (which is allowed in certain mysqld configurations), and/or triggers changing the actual stored value. This is why the driver delays changing the row value. 

We probably won't be able to change the default behavior for this case, there may be some argument for making it a "mode", but it's not simple as we'll have to store a copy of the row to "put back" should updateRow() fail.

Mark, thank you for your feedback.  I don't have a detailed knowledge of the JDBC spec.  Does the protocol state what getXXXX should return on update and on insert use cases?

Regards,
Dmitry

AFAIS, specs require you to use rs.insertRow(); or rs.updateRow(); as in examples below:
rs.moveToInsertRow();
// set values for each column
rs.updateString(1, “Huxley, Aldous”);
rs.updateString(2, “Doors of Perception and Heaven and Hell”);
rs.updateLong(3, 60900075);
// insert the row
rs.insertRow();

rs.next();
rs.updateString(“author”, “Zamyatin, Evgenii Ivanovich”);
rs.updateRow();

because updating a row in a ResultSet object is a two-phase process. First, the new value for each column being updated is set, and then the change is applied to the row. The row in the underlying data source is not updated until the second phase is completed.

So much for the specs.

Thank you for the report.

I can not repeat first case with c/J 5.1.20 and second case works for me if follow Tonci's suggestion:

rs.updateString("value", "new-value");
rs.updateRow();

Tonci,

scenario 

rs.updateString("value", "new-value");
rs.insertRow();

fails with error

1) testBug61126(bug61126)com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
	at com.mysql.jdbc.Util.getInstance(Util.java:386)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4096)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4028)
	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2490)
	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2651)
	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2683)
	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2144)
	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2310)
	at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1380)
	at com.mysql.jdbc.UpdatableResultSet.insertRow(UpdatableResultSet.java:921)
	at bug61126.testBug61126(bug61126.java:32)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at bug61126.main(bug61126.java:14)

Is it correct behavior?

test case I used, partially compatible with c/J testsuite

Attachment: bug61126.java (text/x-java), 2.18 KiB.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

After discussion with Tonci found reason for insertRow failure: rs.updateString("value", "new-value"); is not enough for new row, user must specify new values for every column. This is not a bug.