Bug #28837 | MyISAM storage engine error (134) doing delete with self-join | ||
---|---|---|---|
Submitted: | 1 Jun 2007 13:57 | Modified: | 15 Jan 2008 16:26 |
Reporter: | Zak Kipling | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S3 (Non-critical) |
Version: | 4.1BK/5.0BK/5.1BK | OS: | Any |
Assigned to: | Alexey Kopytov | CPU Architecture: | Any |
Tags: | bfsm_2007_10_18, corruption |
[1 Jun 2007 13:57]
Zak Kipling
[1 Jun 2007 17:39]
MySQL Verification Team
Thank you for the bug report. [miguel@light 5.0]$ bin/mysql -uroot test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.44-debug Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP TABLE IF EXISTS delete_self_join_bug_test; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> CREATE TABLE delete_self_join_bug_test ( -> id int NOT NULL, -> ref int NOT NULL, -> INDEX (id) -> ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> INSERT INTO delete_self_join_bug_test SELECT * FROM loader; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> DELETE FROM a USING delete_self_join_bug_test AS a -> INNER JOIN delete_self_join_bug_test AS b USING (id) -> WHERE a.ref < b.ref; ERROR 1030 (HY000): Got error 134 from storage engine mysql> -----------------without index---------------------- mysql> DROP TABLE IF EXISTS delete_self_join_bug_test; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE delete_self_join_bug_test ( -> id int NOT NULL, -> ref int NOT NULL -> ); Query OK, 0 rows affected (0.01 sec) mysql> CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> INSERT INTO delete_self_join_bug_test SELECT * FROM loader; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> DELETE FROM a USING delete_self_join_bug_test AS a -> INNER JOIN delete_self_join_bug_test AS b USING (id) -> WHERE a.ref < b.ref; Query OK, 1 row affected (0.00 sec) mysql>
[25 Sep 2007 7:46]
Sveta Smirnova
Bug #30290 can be related
[2 Nov 2007 14:50]
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/36993 ChangeSet@1.2552, 2007-11-02 18:51:11+04:00, gshchepa@gleb.loc +3 -0 Fixed bug #28837. The MyISAM storage engine reports error 134 doing DELETE with a self-join. Error 134 (HA_ERR_RECORD_DELETED) is an internal engine code to report deleted records during table scans. The join_read_next_same function has been modified to skip deleted rows silently.
[20 Nov 2007 13:37]
Alexey Kopytov
The same problem applies to MERGE tables. Test case: CREATE TABLE t1 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MyISAM; CREATE TABLE t2 LIKE t1; INSERT INTO t2 (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); INSERT INTO t1 SELECT * FROM t2; INSERT INTO t1 SELECT * FROM t2; CREATE TABLE t3 (id INT NOT NULL, ref INT NOT NULL, INDEX (id)) ENGINE=MERGE UNION(t1); SELECT * FROM t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; SELECT * FROM t3; DELETE FROM a USING t3 AS a INNER JOIN t3 AS b USING (id) WHERE a.ref < b.ref; SELECT * FROM t3;
[20 Nov 2007 13: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/38133 ChangeSet@1.2550, 2007-11-20 16:59:10+03:00, kaa@polly.(none) +6 -0 Fix for bug #28837: MyISAM storage engine error (134) doing delete with self-join When doing DELETE with self-join on a MyISAM or MERGE table, it could happen that a record being retrieved in join_read_next_same() has already been deleted by previous iterations. That caused the engine's index_next_same() method to fail with HA_ERR_RECORD_DELETED error and the whole DELETE query to be aborted with an error. Fixed by suppressing the HA_ERR_RECORD_DELETED error in hy_myisam::index_next_same() and ha_myisammrg::index_next_same(). Since HA_ERR_RECORD_DELETED can only be returned by MyISAM, there is no point in filtering this error in the SQL layer.
[26 Nov 2007 15: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/38531 ChangeSet@1.2550, 2007-11-26 18:58:54+03:00, kaa@polly.(none) +6 -0 Fix for bug #28837: MyISAM storage engine error (134) doing delete with self-join When doing DELETE with self-join on a MyISAM or MERGE table, it could happen that a record being retrieved in join_read_next_same() has already been deleted by previous iterations. That caused the engine's index_next_same() method to fail with HA_ERR_RECORD_DELETED error and the whole DELETE query to be aborted with an error. Fixed by suppressing the HA_ERR_RECORD_DELETED error in hy_myisam::index_next_same() and ha_myisammrg::index_next_same(). Since HA_ERR_RECORD_DELETED can only be returned by MyISAM, there is no point in filtering this error in the SQL layer.
[14 Dec 2007 8:15]
Bugs System
Pushed into 5.0.54
[14 Dec 2007 8:18]
Bugs System
Pushed into 5.1.23-rc
[14 Dec 2007 8:21]
Bugs System
Pushed into 6.0.5-alpha
[15 Jan 2008 16:26]
MC Brown
A note has been added to the 5.0.54, 5.1.23 and 6.0.5 changelogs: When doing a DELETE on table that involved a JOIN with MyISAM or MERGE tables and the JOIN referred to the same table, the operation could fail reporting ERROR 1030 (HY000): Got error 134 from storage engine. This was because scans on the table contents would change because of rows that had already been deleted
[1 Apr 2008 12: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/44728 ChangeSet@1.2623, 2008-04-01 15:36:32+03:00, monty@mysql.com +21 -0 Merge of changes in MyISAM since December 16 -> April 1 Fixes bugs: Bug#28837 MyISAM storage engine error (134) doing delete with self-join Bug#31277 myisamchk --unpack corrupts table Bug#4692 DISABLE/ENABLE KEYS waste a space Bug#31305 myisam tables crash when they are near capacity
[1 Apr 2008 14:56]
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/44737 ChangeSet@1.2623, 2008-04-01 17:57:30+03:00, monty@mysql.com +21 -0 Merge of changes in MyISAM since December 16 -> April 1 Fixes bugs: Bug#28837 MyISAM storage engine error (134) doing delete with self-join Bug#31277 myisamchk --unpack corrupts table Bug#4692 DISABLE/ENABLE KEYS waste a space Bug#31305 myisam tables crash when they are near capacity