MySQL Bugs: #78329: ResultSet "not updatable" when it probably should be

Bug #78329	ResultSet "not updatable" when it probably should be
Submitted:	4 Sep 2015 18:30	Modified:	5 May 2016 23:46
Reporter:	Christopher Schultz	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	Connector/J 5.1.32, 5.1.36	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When selecting FOR UPDATE from a single table JOINed to another table, I'd like to be able to delete a record from the table mentioned to the left of the JOIN.

An example:

  SELECT test2.id FROM test2
    JOIN test1 ON test2.test1_id=test1.id
    FOR UPDATE

I'd like to be able to delete the record in the "test2" table using ResultSet.deleteRow(), but it fails with this exception:

Exception in thread "main" com.mysql.jdbc.NotUpdatable: Result Set not updatable (references more than one table).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
	at com.mysql.jdbc.UpdatableResultSet.deleteRow(UpdatableResultSet.java:449)
	at CantDelete.main(CantDelete.java:29)

The JDBC spec does not require the SELECT to only select a single table; instead, it lists a few constraints that, if followed, ought to result in an updatable ResultSet. If Connector/J can determine which table is the "primary" table of the SELECT (in this case, "test2"), then the row should be deletable.

If the row is NOT deletable (at least from the ResultSet), will it be a problem to issue a "DELETE FROM test2 WHERE id=?" in the same transaction?

How to repeat:
Simple Java program that reproduces this issue.
Connector/J version: 5.1.32.
MySQL version: 5.5.44 (Debian Linux)

/*

CREATE TABLE test1 (id integer auto_increment primary key)
engine=innodb;

CREATE TABLE test2 (id integer auto_increment primary key,
  test1_id integer not null,
  constraint fk_test foreign key idx__test1_test_id (test1_id)
  references test1(id))
engine=innodb;

*/
import java.sql.*;
public class CantDelete
{
        public static void main(String[] args)
                throws SQLException
        {
                Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/test", "test", "test");
                conn.setAutoCommit(false);

                PreparedStatement ps = conn.prepareStatement("SELECT test2.id FROM test2 JOIN test1 ON test2.test1_id=test1.id FOR UPDATE",
                                ResultSet.TYPE_FORWARD_ONLY,
                                ResultSet.CONCUR_UPDATABLE);
                ResultSet rs = ps.executeQuery();

                rs.next();

                rs.deleteRow(); // Expect to delete row from test2 table

                conn.rollback();
        }
}

Hi Christopher,

Thank you for the report and test case.
I'm not seeing any exception at my end and it is indeed deleting the row from test2(when I change rollback->commit). Could you please confirm this with  Connector/J 5.1.36(no issues with even 5.1.32-35)? Or am I missing anything here?

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: javac -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.32/mysql-connector-java-5.1.32-bin.jar' CantDelete.java
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.32/mysql-connector-java-5.1.32-bin.jar' CantDelete
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9:
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: javac -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar' CantDelete.java
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar' CantDelete
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: javac -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.36/mysql-connector-java-5.1.32-bin.jar' CantDelete.java
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar' CantDelete

-- from general log

150916 10:04:02    15 Connect   root@localhost on test
                   15 Query     /* mysql-connector-java-5.1.36 ( Revision: 4fc1f969f740409a4e03750316df2c0e429f3dc8 ) */SELECT @@session.auto_increment_increment, @@character_set_client, @@character_set_connection, @@character_set_results, @@character_set_server, @@init_connect, @@interactive_timeout, @@license, @@lower_case_table_names, @@max_allowed_packet, @@net_buffer_length, @@net_write_timeout, @@query_cache_size, @@query_cache_type, @@sql_mode, @@system_time_zone, @@time_zone, @@tx_isolation, @@wait_timeout
                   15 Query     SET NAMES latin1
                   15 Query     SET character_set_results = NULL
                   15 Query     SET autocommit=1
                   15 Query     SET sql_mode='STRICT_TRANS_TABLES'
                   15 Query     SET autocommit=0
                   15 Query     SELECT test2.id FROM test2 JOIN test1 ON test2.test1_id=test1.id FOR UPDATE
                   15 Query     SHOW KEYS FROM `test2` FROM `test`
                   15 Query     DELETE FROM `test`.`test2` WHERE `test`.`test2`.`id`<=>1
                   15 Query     rollback
150916 10:04:26    13 Quit

Thanks,
Umesh

I'll try again with Connector/J 5.1.36 against MySQL server 5.5.44.

Hmm. I don't seem to be able to repeat my own test-case. :(

I'll try to re-create the test case from the original, more complicated case where we saw this in the wild.

Okay, I see the oversight in the test case (which was slightly too simple). The SELECT query needs to return data from more than one table. Change the query to this and you'll see it fail:

    SELECT test2.id, test1.id
    FROM test2
    JOIN test1 ON test2.test1_id=test1.id
    FOR UPDATE

Thank you for the feedback.

-- I'm able to repeat it now with 5.1.36

[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: javac -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar' CantDelete.java
[umshastr@hod03]/export/umesh/server/binaries/mysql-advanced-5.7.9: java -classpath '.:/home/umshastr/bugs/mysql-connector-java-5.1.36/mysql-connector-java-5.1.36-bin.jar' CantDelete
Exception in thread "main" com.mysql.jdbc.NotUpdatable: Result Set not updatable (references more than one table).This result set must come from a statement that was created with a result set type of ResultSet.CONCUR_UPDATABLE, the query must select only one table, can not use functions and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details.
        at com.mysql.jdbc.UpdatableResultSet.deleteRow(UpdatableResultSet.java:427)
        at CantDelete.main(CantDelete.java:17)

Hi Christopher

There is nothing wrong here.

In such case there are three possible outcomes:

1. Using query "SELECT test2.id FROM test2 JOIN test1 ON test2.test1_id=test1.id FOR UPDATE", rs.deleteRow() deletes a row from table test2.

2. Using query "SELECT test1.id FROM test2 JOIN test1 ON test2.test1_id=test1.id FOR UPDATE", rs.deleteRow() deletes a row from table test1.

3. Using query "SELECT test1.id, test2.id FROM test2 JOIN test1 ON test2.test1_id=test1.id FOR UPDATE", rs.deleteRow() throws an exception because the result set rows contains data from more than one table.

Please provide more info if there is something else not covered here. I'll keep this bug open for the time being.

Thanks,

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".

The exception you are getting because of the permissions. Please check your permission like this:
System.out.println(rs.getConcurrency());
If it gives 1007. i.e. ResultSet.CONCUR_READ_ONLY so cannot do this. To update the row the value of rs.getConcurrency() must be 1008 i.e. ResultSet.CONCUR_UPDATABLE.
Verify this once. Hope this helps!

Thank you.