Bug #21310 | Trees in SQL causing a "crashed" table with MyISAM storage engine | ||
---|---|---|---|
Submitted: | 27 Jul 2006 10:14 | Modified: | 7 Feb 2007 12:18 |
Reporter: | Poeydomenge Frederic | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: MyISAM storage engine | Severity: | S2 (Serious) |
Version: | 4.1, 5.0.25-BK, 5.0.22 | OS: | Linux (Debian GNU/Linux) |
Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[27 Jul 2006 10:14]
Poeydomenge Frederic
[27 Jul 2006 10:52]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.25-BK on Linux: openxs@suse:~/dbs/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 8 to server version: 5.0.25 Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> DROP TABLE IF EXISTS `foo`; Query OK, 0 rows affected (0.01 sec) CRmysql> CREATE TABLE `foo` ( -> `id` smallint(5) unsigned NOT NULL default '0', -> `node` tinyint(3) unsigned NOT NULL, -> `parent_node` tinyint(3) unsigned NULL, -> `depth` tinyint(4) unsigned NULL, -> `lineage` varchar(255) NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `foo` VALUES (1, 100,NULL, NULL, NULL); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO `foo` VALUES (2, 101, 100, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (3, 102, 100, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (4, 103, 100, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (5, 104, 101, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (6, 105, 101, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (7, 106, 104, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO `foo` VALUES (8, 107, 102, NULL, NULL); Query OK, 1 row affected (0.00 sec) mysql> UPDATE foo SET depth = 0, lineage = '/' WHERE ISNULL(parent_node); Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE foo SET depth = NULL, lineage = NULL WHERE NOT ISNULL(parent_node ); Query OK, 0 rows affected (0.00 sec) Rows matched: 7 Changed: 0 Warnings: 0 mysql> UPDATE -> foo AS T -> INNER JOIN foo AS P ON (T.parent_node = P.node) -> SET -> T.depth = P.depth + 1, -> T.lineage = LTRIM(CONCAT(P.lineage, T.parent_node, '/')) -> WHERE -> P.depth >= 0 -> AND NOT ISNULL(P.lineage) -> AND ISNULL(T.depth); Query OK, 3 rows affected (0.01 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> UPDATE foo AS T INNER JOIN foo AS P ON (T.parent_node = P.no de) SET T.depth = P.depth + 1, T.lineage = LTRIM(CONCAT(P.lineage, T.pa rent_node, '/')) WHERE P.depth >= 0 AND NOT ISNULL(P.lineage) AND ISNULL(T.depth); Query OK, 3 rows affected (0.00 sec) Rows matched: 3 Changed: 3 Warnings: 0 mysql> UPDATE foo AS T INNER JOIN foo AS P ON (T.parent_node = P.no de) SET T.depth = P.depth + 1, T.lineage = LTRIM(CONCAT(P.lineage, T.pa rent_node, '/')) WHERE P.depth >= 0 AND NOT ISNULL(P.lineage) AND ISNULL(T.depth); ERROR 1194 (HY000): Table 'P' is marked as crashed and should be repaired mysql> select * from foo; +----+------+-------------+-------+---------------+ | id | node | parent_node | depth | lineage | +----+------+-------------+-------+---------------+ | 1 | 100 | NULL | 0 | / | | 2 | 101 | 100 | 1 | /100/ | | 3 | 102 | 100 | 1 | /100/ | | 4 | 103 | 100 | 1 | /100/ | | 5 | 104 | 101 | 2 | /100/101/ | | 6 | 105 | 101 | 2 | /100/101/ | | 7 | 106 | 104 | 3 | /100/101/104/ | | 8 | 107 | 102 | 2 | /100/102/ | +----+------+-------------+-------+---------------+ 8 rows in set (0.00 sec) mysql> analyze table foo; +----------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------+---------+----------+----------+ | test.foo | analyze | status | OK | +----------+---------+----------+----------+ 1 row in set (0.01 sec)
[21 Oct 2006 8:44]
Sergey Vojtovich
This problem was introduced with fix for bug#18036. I was able to create simplier test case: CREATE TABLE t1(a VARCHAR(16)); INSERT INTO t1 VALUES('aaaaaaaa'),(NULL); UPDATE t1 AS ta1, t1 AS ta2 SET ta1.a='aaaaaaaaaaaaaaaa';
[27 Oct 2006 17:35]
Sergey Vojtovich
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/14499 ChangeSet@1.2534, 2006-10-27 22:23:10+05:00, svoj@mysql.com +3 -0 BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine An update that used a join of a table to itself and modified the table on one side of the join reported the table as crashed. This problem was introduced with fix for BUG#18036. Do not rely on (tables_to_update & table->map) while determining whether to use record cache for self-joined table updates. It must be disabled for both: table that is opened for reading and table that is opened for writing.
[23 Nov 2006 15:54]
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/15767 ChangeSet@1.2534, 2006-11-23 19:50:37+04:00, svoj@mysql.com +3 -0 BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine An update that used a join of a table to itself and modified the table on one side of the join reported the table as crashed or updated wrong rows. Fixed by creating temporary table for self-joined multi update statement.
[20 Dec 2006 15:05]
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/17222 ChangeSet@1.2534, 2006-12-20 19:05:35+04:00, svoj@mysql.com +4 -0 BUG#21310 - Trees in SQL causing a "crashed" table with MyISAM storage engine An update that used a join of a table to itself and modified the table on one side of the join reported the table as crashed or updated wrong rows. Fixed by creating temporary table for self-joined multi update statement.
[25 Jan 2007 9:28]
Sergey Vojtovich
Pushed to trees currently marked as 5.0.36 and 5.1.15.
[5 Feb 2007 11:40]
Sergey Vojtovich
Pushed to tree currently marked as 4.1.23.
[7 Feb 2007 12:18]
MC Brown
A note has been added to the 4.1.23, 5.0.36 and 5.1.15 changelogs.