MySQL Bugs: #80532: Encoding of ResultSet.updateRow is broken for non ASCII charcters

Bug #80532	Encoding of ResultSet.updateRow is broken for non ASCII charcters
Submitted:	26 Feb 2016 14:33	Modified:	1 Aug 2018 17:52
Reporter:	Volker Berlin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.38	OS:	Any
Assigned to:	Filipe Silva	CPU Architecture:	Any
Tags:	updateRow

Description:
The encoding of ResultSet.updateRow() for all columns which are not is broken. If it is an primary key it result in follow exception:

Exception in thread "main" java.sql.SQLException: refreshRow() called on row that has been deleted or had primary key changed.
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1329)
	at com.mysql.jdbc.UpdatableResultSet.refreshRow(UpdatableResultSet.java:1250)
	at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2330)

How to repeat:
        Class.forName("com.mysql.jdbc.Driver");
        Properties props = new Properties();
        Connection con = DriverManager.getConnection("jdbc:mysql://data/test?user=root&password=inet", props);

        Statement st = con.createStatement();
        try {
            st.execute("DROP TABLE UmlauteTest");
        } catch (SQLException ex) {
            // ignore
        }
        st.execute("CREATE TABLE UmlauteTest( id varchar(50) NOT NULL, data longtext, PRIMARY KEY (id) )");
        st.execute("INSERT INTO UmlauteTest(id,data) VALUES( 'äöü', 'my data' )");
        st.close();

        st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("Select * From UmlauteTest");
        rs.next();
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("data"));
        rs.updateString("data", "new data");
        rs.updateRow();
        System.out.println(rs.getString("id"));
        System.out.println(rs.getString("data"));

A possible fix is https://github.com/i-net-software/mysql-connector-j/commit/3e942688a14992c11edf4e73ee91b5d...

A fix version can be download from https://bintray.com/i-net-software/OSS/de.inetsoftware.mysql-connector-java/5.1.38.2/view

Hi Volker,

Thank you for this bug report. Verified as described.

This also happens with columns that have no encoding. This program reproduces the issue in a different way:

import java.sql.*;

public class Test80532 {
    public static void main(String[] args) throws Exception {
        Connection conn = DriverManager.getConnection(args[0]); // dburl in args[0]
        Statement st = conn.createStatement();
        st.execute("DROP TABLE IF EXISTS test_80532");
        st.execute("CREATE TABLE test_80532 (id INTEGER, dt DATETIME, PRIMARY KEY(id,dt))");

        st.close();
        st = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = st.executeQuery("SELECT id,dt FROM test_80532 FOR UPDATE");
        rs.next(); // No rows
        rs.moveToInsertRow();
        rs.updateInt("id", 1);
        rs.updateTimestamp("dt", new java.sql.Timestamp(System.currentTimeMillis()));

        rs.insertRow(); // throws error
    }
}

There is no problem if the DATETIME field is removed from the PRIMARY KEY.

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

"ResultSet.updateRow() failed when the character set used by a column in the ResultSet did not match that of the connection's encoding. With this fix, values for the affected columns are first converted to String before the update, so that the character set difference is properly handled."