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: | |
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
[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.