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:40]
Andrew Hutchings
[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'.