| 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 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
Description: If I run the follwing SQL on either 5.0.42 (or the latest snapshot) or 5.1.19-beta, I repeatably get "ERROR 1030 (HY000): Got error 134 from storage engine". DROP TABLE IF EXISTS delete_self_join_bug_test; CREATE TABLE delete_self_join_bug_test ( id int NOT NULL, ref int NOT NULL, INDEX (id) ); CREATE TEMPORARY TABLE loader LIKE delete_self_join_bug_test; INSERT INTO loader (id, ref) VALUES (1,3), (2,1), (3,2), (4,5), (4,4); INSERT INTO delete_self_join_bug_test SELECT * FROM loader; 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; Note: * the error doesn't occur if you INSERT the rows directly rather than copying them via the "loader" table; * the error also doesn't occur if the index is removed; * the order of the rows appears to be important; * this occurs only with MyISAM tables, not with InnoDB. How to repeat: Run the above SQL on one of the affected versions of the MySQL server.