Bug #16458 Simple SELECT FOR UPDATE causes "Result Set not updatable" error.
Submitted: 12 Jan 2006 16:12 Modified: 7 Jul 2006 0:25
Reporter: Christopher Schultz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1 OS:Linux (Linux 2.6.14 / JRE 1.4.2_09-b05)
Assigned to: Georgi Kodinov CPU Architecture:Any

[12 Jan 2006 16:12] Christopher Schultz
Description:
I have a table with quite a few FKs in it, but only a single PK.

I am issuing the following query:

SELECT DISTINCT id,status_id,order_num FROM patient_questionnaire WHERE patient_id=? FOR UPDATE

The PK of the table is "id". I'm using DISTINCT because this query is a greatly simplified version of a longer query (including JOINs) that I really want to run. I have boiled-down the problem into something must easier to deal with. If DISTINCT is removed from the query, it /does not/ fail. Unfortunately, DISTINCT is necessary for the larger query.

At any rate, I believe that this query should work with or without DISTINCT specified.

The exact error I get is (not suprisingly):
"com.mysql.jdbc.NotUpdatable: Result Set not updatable.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, and must select all primary keys from that table. See the JDBC 2.1 API Specification, section 5.6 for more details."

As I mentioned, the one-and-only PK of the table is "id", there is only one table in the query, and the code to create the query is:

	    ps = conn.prepareStatement("SELECT DISTINCT id,status_id,order_num FROM patient_questionnaire WHERE patient_id=? FOR UPDATE",
				       ResultSet.TYPE_FORWARD_ONLY,
				       ResultSet.CONCUR_UPDATABLE);

I have this code repeated in many other places with other tables (including the DISTINCT) with no problem at all.

In the interest of full disclosure, here is all the info about my environment:

Linux 2.6.14
Java 1.4.2_09-b05
MySQL 4.1.14 (tables are using InnoDB engine)
Connector/J 3.0.16 (also tried 3.0.17 and 3.1.12)

Note that queries much the same as this one are working in other parts of the code.

How to repeat:
I tried to get this working minimally in a test database with bogus data, but I couldn't get it to fail with bogus data. So, I created a minimal set of tables in my test database and then copied over a chunk of data that *does* make it fail.

I will include the failure-inducing data first, and then the data that does not cause a failure.

DDL for the tables (note that one of the FKs is nullable, and is often NULL).

CREATE TABLE a (id INTEGER NOT NULL, primary key (id)) Type=InnoDB;
CREATE TABLE b (id INTEGER NOT NULL, primary key (id)) Type=InnoDB;
CREATE TABLE c (id INTEGER NOT NULL, primary key (id)) Type=InnoDB;

CREATE TABLE `problem_table` (
  `id` int(11) NOT NULL auto_increment,
  `a_id` int(11) NOT NULL default '0',
  `b_id` int(11) NOT NULL default '0',
  `c_id` int(11) default NULL,

  `order_num` int(2) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `idx_problem_table__b_id` (`b_id`),
  KEY `idx_problem_table__a_id` (`a_id`),
  KEY `idx_problem_table__c_id` (`c_id`),
  CONSTRAINT `fk_problem_table__c` FOREIGN KEY (`c_id`) REFERENCES `c` (`id`),
  CONSTRAINT `fk_problem_table__a` FOREIGN KEY (`a_id`) REFERENCES `a` (`id`),
  CONSTRAINT `fk_problem_table__b` FOREIGN KEY (`b_id`) REFERENCES `b` (`id`)
)
ENGINE=InnoDB
;

===================
Data that will cause the error
===================

INSERT INTO `a` VALUES (1),(4),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24),(25),(26),(27),(28),(29),(30),(31),(32),(33),(34),(35),(36),(37),(38),(39),(40),(41),(42),(43),(45),(46),(47),(48),(49),(50);

INSERT INTO `b` VALUES (1),(2),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20);

INSERT INTO `c` VALUES (1),(2),(3),(13),(15),(16),(22),(30),(31),(32),(33),(34),(35),(36),(37),(148),(159),(167),(174),(176),(177),(178),(179),(180),(187),(188),(189),(190),(191),(192),(193),(194),(195),(196),(197),(198),(199),(200),(201),(202),(203),(204),(205),(206),(207),(208);

INSERT INTO `problem_table` VALUES (1,1,1,NULL,1),(2,1,4,NULL,1),(3,1,5,NULL,1),(4,1,8,NULL,1),(5,23,1,NULL,1),(6,23,4,NULL,1),(7,24,1,NULL,1),(8,24,2,NULL,1),(9,24,4,NULL,1),(10,25,1,NULL,1),(11,25,2,NULL,1),(12,25,4,NULL,1),(13,27,1,NULL,1),(14,28,1,NULL,1),(15,29,1,NULL,1),(16,15,2,NULL,1),(17,15,5,NULL,1),(18,15,8,NULL,1),(19,30,1,NULL,1),(20,31,1,NULL,1),(21,31,4,NULL,1),(22,32,2,NULL,1),(23,32,4,NULL,1),(24,32,6,NULL,1),(25,32,8,NULL,1),(26,32,10,NULL,1),(27,32,11,NULL,1),(28,32,13,NULL,1),(29,32,16,NULL,1),(30,32,17,NULL,1),(31,32,18,NULL,1),(32,32,19,NULL,1),(33,32,20,NULL,1),(34,33,15,NULL,1),(35,33,15,NULL,1),(36,32,20,206,1),(96,32,9,NULL,1),(100,47,6,NULL,1),(101,47,10,NULL,1),(102,47,5,NULL,1),(105,47,19,NULL,1);

======================
Data that will not cause the error
======================
INSERT INTO a VALUES (1);
INSERT INTO a VALUES (2);
INSERT INTO a VALUES (3);
INSERT INTO b VALUES (1);
INSERT INTO b VALUES (2);
INSERT INTO b VALUES (3);
INSERT INTO b VALUES (4);
INSERT INTO b VALUES (5);
INSERT INTO b_status VALUES (1);
INSERT INTO c VALUES (1);

INSERT INTO problem_table VALUES (1, 1, 1, NULL, 1);
INSERT INTO problem_table VALUES (2, 1, 2, NULL, 1);
INSERT INTO problem_table VALUES (3, 1, 3, NULL, 1);
INSERT INTO problem_table VALUES (4, 1, 4, NULL, 1);
INSERT INTO problem_table VALUES (5, 1, 5, NULL, 1);

INSERT INTO problem_table VALUES (6, 2, 1, NULL, 1);
INSERT INTO problem_table VALUES (7, 2, 2, NULL, 1);
INSERT INTO problem_table VALUES (8, 2, 3, NULL, 1);
INSERT INTO problem_table VALUES (9, 2, 4, NULL, 1);
INSERT INTO problem_table VALUES (10, 2, 5, NULL, 1);

INSERT INTO problem_table VALUES (11, 3, 1, NULL, 1);
INSERT INTO problem_table VALUES (12, 3, 2, NULL, 1);
INSERT INTO problem_table VALUES (13, 3, 3, NULL, 1);
INSERT INTO problem_table VALUES (14, 3, 4, NULL, 1);
INSERT INTO problem_table VALUES (15, 3, 5, NULL, 1);

======================
Code that demonstrates the error
======================

import java.sql.*;

public class TableTest
{
    public static void main(String[] args)
	throws Exception
    {
	Class.forName("com.mysql.jdbc.Driver").newInstance();

	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;

	try
	{
	    conn = DriverManager.getConnection("jdbc:mysql://localhost/test");

	    ps = conn.prepareStatement("SELECT DISTINCT id,order_num FROM problem_table WHERE a_id=? FOR UPDATE",
				       ResultSet.TYPE_FORWARD_ONLY,
				       ResultSet.CONCUR_UPDATABLE);

	    ps.setInt(1, 32);

	    rs = ps.executeQuery();

	    while(rs.next())
	    {
		rs.updateInt(3, 51);

		rs.updateRow();
	    }

	    System.out.println("Success!");
	}
	finally
	{
	    if(null != rs)
		try { rs.close(); } catch (SQLException sqle)
		{ System.err.println("Cannot close ResultSet"); }

	    if(null != ps)
		try { ps.close(); } catch (SQLException sqle)
		{ System.err.println("Cannot close PreparedStatement"); }

	    if(null != conn)
		try { conn.close(); } catch (SQLException sqle)
		{ System.err.println("Cannot close Connection"); }
	}
    }
}

I added a toString to the UpdatableResultSet class in the 3.0.16 version of Connector/J and ran my query, printing-out the result set's fields. This is the information that I got:

fields[0]=diagnosis . problem_table(problem_table) . id(id) (pk? false)
fields[1]=diagnosis . problem_table(problem_table) . order_num(order_num) (pk? false)

This seems to indicate that the "id" column is no longer being considered the PK. If I remove the DISTINCT, the query executes as expected (and the PK is properly identified).

Suggested fix:
None. Any ideas?
[12 Jan 2006 16:58] Mark Matthews
Christopher,

What is the output of EXPLAIN for the query and data in question? My guess is the "DISTINCT" causes the optimizer to use a temporary table to resolve a portion of the query, and it's changing some type information that causes the JDBC driver to not "see" the primary key.

You could also use the JDBC driver with "strictUpdates=false" as a JDBC connection property and the driver won't check for primary keys.

However, we _are_ interested in knowing why this is happening, not that the workaround is effective ;)
[12 Jan 2006 17:16] Christopher Schultz
Thanks for the quick response.

Here are the results from EXPLAIN:

mysql> explain SELECT DISTINCT id,order_num FROM problem_table WHERE a_id=32 FOR UPDATE;
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+------------------------------+
| id | select_type | table         | type | possible_keys           | key                     | key_len | ref   | rows | Extra                        |
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+------------------------------+
|  1 | SIMPLE      | problem_table | ref  | idx_problem_table__a_id | idx_problem_table__a_id |       4 | const |   14 | Using where; Using temporary |
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+------------------------------+
1 row in set (0.02 sec)

I basically know nothing about EXPLAIN information, so any explanation (ha ha) of this would be helpful, too.

Would the "strictUpdates=false" workaround simply turn off checking for the updatability on the driver side? I'm assuming that the database would continue to enforce ill-advised updates (i.e. those that do not include a PK). I'd prefer not to open myself up to some potential data corruption in the future because I (or some other engineer) writes a bad query.

Is it possible to enable that setting for a single query, or must be be a connection-wide setting?
[12 Jan 2006 18:03] Christopher Schultz
Oh, I ought to display the results of EXPLAIN when DISTINCT is not present:

mysql> explain SELECT id,order_num FROM problem_table WHERE a_id=32 FOR UPDATE;
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------+
| id | select_type | table         | type | possible_keys           | key                     | key_len | ref   | rows | Extra |
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | problem_table | ref  | idx_problem_table__a_id | idx_problem_table__a_id |       4 | const |   14 |       |
+----+-------------+---------------+------+-------------------------+-------------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
[15 Jan 2006 21:47] Vasily Kishkin
Thanks for the bug report. I was able to reproduce it. My test case is attached.
[15 Jan 2006 21:48] Vasily Kishkin
Test case

Attachment: Bug16458.java (text/java), 2.12 KiB.

[16 Jan 2006 15:58] Mark Matthews
I'm re-categorizing this as a server bug, as running your testcase shows that when the query is resolved as a temporary table, the server doesn't return the "isPrimaryKey" flag for the primary key field, only the fact that the field is not nullable (i.e. NOT_NULL_FLAG, the "1" value in field-level metadata).
[16 Jan 2006 15:59] Mark Matthews
The server doesn't enforce the "ill-advised" updates, that's why the check is in the driver in the first place. (The server does not yet have updatable cursors).

There isn't currently a way to set this check on a case-by-case basis in the JDBC driver. We could certainly look into that as a feature request.
[16 Jan 2006 16:12] Christopher Schultz
Mark, you may wish to change the version number to 4.1.14 since you've changed the product from Connector/J to MySQL Server.

Let me know if there's anything I can do to help.
[26 Jun 2006 11:04] Bugs System
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/8240
[26 Jun 2006 16:31] Bugs System
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/8255
[27 Jun 2006 10:48] Bugs System
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/8310
[27 Jun 2006 14:35] Bugs System
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/8322
[5 Jul 2006 18:10] Evgeny Potemkin
When a GROUP BY/DISTINCT clause contains all key parts of a unique
index, then it is guaranteed that the fields of the clause will be
unique, therefore we can optimize away GROUP BY/DISTINCT altogether.
This optimization has two effects:
* there is no need to create a temporary table to compute the
   GROUP/DISTINCT operation (or the temporary table will be smaller if only GROUP 
   is removed and DISTINCT stays or if DISTINCT is removed and GROUP BY stays)
* this causes the statement in effect to become updatable in Connector/Java
because the result set columns will be direct reference to the primary key of 
the table (instead to the temporary table that it currently references). 

Fixed in 4.1.21, 5.0.24, 5.1.12
[7 Jul 2006 0:25] Paul DuBois
Noted in 4.1.21, 5.0.24, 5.1.12 changelogs.

For SELECT ... FOR UPDATE statements that used DISTINCT or GROUP BY
over all key parts of a unique index (or primary key), the optimizer
unnecessarily created a temporary table, thus losing the linkage to
the underlying unique index values. This caused a Result set not
updatable error. (The temporary table is unnecessary because under 
these circumstances the distinct or grouped columns must also be
unique.)
[13 Jul 2006 3:35] Paul DuBois
5.0.x fix went to 5.0.25 instead.