| Bug #40081 | UPDATE....ORDER BY cannot find rows in cluster | ||
|---|---|---|---|
| Submitted: | 16 Oct 2008 15:40 | Modified: | 20 Jan 2009 18:53 |
| Reporter: | Andrew Hutchings | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Cluster: Cluster (NDB) storage engine | Severity: | S2 (Serious) |
| Version: | 6.3.17 | OS: | Linux |
| Assigned to: | Martin Skold | CPU Architecture: | Any |
| Tags: | MCP | ||
[16 Oct 2008 15:54]
Andrew Hutchings
With 24 rows in the table, half with the text 'hello' the other with the text 'world':
mysql> show create table testing\G
*************************** 1. row ***************************
Table: testing
Create Table: CREATE TABLE `testing` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`mytext` char(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> update testing set mytext = 'newtext' where mytext = 'world' order by mytext;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 12 Changed: 2 Warnings: 0
mysql> select * from testing; +----+---------+
| id | mytext |
+----+---------+
| 19 | hello |
| 12 | world |
| 5 | hello |
| 7 | hello |
| 3 | hello |
| 9 | hello |
| 21 | hello |
| 22 | world |
| 10 | world |
| 14 | world |
| 11 | hello |
| 18 | world |
| 20 | world |
| 1 | hello |
| 15 | hello |
| 13 | hello |
| 6 | world |
| 23 | hello |
| 2 | world |
| 17 | hello |
| 16 | world |
| 4 | world |
| 24 | world |
| 8 | newtext |
+----+---------+
24 rows in set (0.00 sec)
mysql> update testing set mytext = 'world' where mytext = 'newtext' order by mytext;
ERROR 1032 (HY000): Can't find record in 'testing'
[16 Oct 2008 16:41]
Andrew Hutchings
If using the PK as the ORDER BY field this does work: mysql> update testing2 set mytext = 'newtext' where mytext = 'world' order by id; Query OK, 2 rows affected (0.01 sec) Rows matched: 2 Changed: 2 Warnings: 0 But fails for regular indexed fields.
[28 Nov 2008 13:23]
Tomas Ulin
so the workaround for this is to make sure the primary key is listed in the order by e.g. update t1 set mytext = 'newtext' where mytext = 'world' order by mytext,id; instead of update t1 set mytext = 'newtext' where mytext = 'world' order by mytext;
[28 Nov 2008 16:09]
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/60201 2777 Martin Skold 2008-11-28 Bug#40081 UPDATE....ORDER BY cannot find rows in cluster: Prepare handler for position call so it can include primary key in read-set if needed
[2 Dec 2008 10:30]
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/60361 2777 Martin Skold 2008-12-02 Bug#40081 UPDATE....ORDER BY cannot find rows in cluster: Prepare handler for position call so it can include primary key in read-set if needed
[2 Dec 2008 15:07]
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/60400 2778 Martin Skold 2008-12-02 Bug#40081 UPDATE....ORDER BY cannot find rows in cluster: Prepare handler for position call so it can include primary key in read-set if needed
[2 Dec 2008 15:09]
Bugs System
Pushed into 5.1.30-ndb-6.3.20 (revid:martin.skold@mysql.com-20081202150757-6f9t7kaerwsulydn) (version source revid:martin.skold@mysql.com-20081202150757-6f9t7kaerwsulydn) (pib:5)
[3 Dec 2008 20:02]
Bugs System
Pushed into 5.1.30-ndb-6.4.0 (revid:martin.skold@mysql.com-20081202150757-6f9t7kaerwsulydn) (version source revid:jonas@mysql.com-20081203195133-nime1xk10b5xwf18) (pib:5)
[5 Dec 2008 10:59]
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/60715 2782 Martin Skold 2008-12-05 Bug#40081 UPDATE....ORDER BY cannot find rows in cluster: Added test case
[5 Dec 2008 11:00]
Bugs System
Pushed into 5.1.30-ndb-6.3.20 (revid:martin.skold@mysql.com-20081205105932-9bnrm2ew1dchbk4a) (version source revid:martin.skold@mysql.com-20081205105932-9bnrm2ew1dchbk4a) (pib:5)
[5 Dec 2008 11:49]
Bugs System
Pushed into 5.1.30-ndb-6.4.0 (revid:martin.skold@mysql.com-20081205105932-9bnrm2ew1dchbk4a) (version source revid:tomas.ulin@sun.com-20081205114608-29u591nwpz936k7t) (pib:5)
[20 Jan 2009 18:53]
Jon Stephens
Documented bugfix in the NDB-6.3.20 changelog as follows:
Statements of the form UPDATE ... ORDER BY ... LIMIT run against
NDBCLUSTER tables failed to update all matching rows, or failed
with the error Can't find record in 'table_name'.
Description: When doing an UPDATE...ORDER BY...LIMIT query on a cluster table it will either only update a few of the matched rows or error with: ERROR 1032 (HY000): Can't find record in 'testing' How to repeat: mysql> show create table testing2\G *************************** 1. row *************************** Table: testing2 Create Table: CREATE TABLE `testing2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `mytext` char(30) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from testing2; +----+--------+ | id | mytext | +----+--------+ | 1 | hello | | 2 | world | | 3 | hello | | 4 | world | +----+--------+ 4 rows in set (0.00 sec) mysql> update testing2 set mytext = 'newtext' where mytext = 'world' order by mytext; ERROR 1032 (HY000): Can't find record in 'testing2' Without ORDER BY: mysql> update testing2 set mytext = 'newtext' where mytext = 'world'; Query OK, 2 rows affected (0.02 sec) Rows matched: 2 Changed: 2 Warnings: 0