| Bug #18036 | update of table joined to self reports table as crashed | ||
|---|---|---|---|
| Submitted: | 7 Mar 2006 15:11 | Modified: | 21 Jun 2006 4:43 |
| Reporter: | Dave Pullin (Basic Quality Contributor) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.18/5.1bk/4.1bk | OS: | Windows (Windows 2000, Linux) |
| Assigned to: | Sergey Vojtovich | CPU Architecture: | Any |
[7 Mar 2006 15:39]
MySQL Verification Team
Thank you for the bug report. I was able to repeaT:
miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db27
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE temp ( N int , related_N int , log text );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into temp (N,related_N) values (1,null), (2,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> update temp as t join temp as related
-> on related.N = t.related_N
-> set t.log='changed',related.log='changed'
-> where t.related_N is not null ;
ERROR 1194 (HY000): Table 'related' is marked as crashed and should be repaired
mysql>
miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create db27
miguel@hegel:~/dbs/5.1> bin/mysql -uroot db27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.8-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE temp ( N int , related_N int , log text );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into temp (N,related_N) values (1,null), (2,1);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> update temp as t join temp as related
-> on related.N = t.related_N
-> set t.log='changed',related.log='changed'
-> where t.related_N is not null ;
ERROR 1194 (HY000): Table 'temp' is marked as crashed and should be repaired
mysql>
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db27
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.19-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE temp ( N int , related_N int , log text );
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> insert into temp (N,related_N) values (1,null), (2,1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
mysql> update temp as t join temp as related
-> on related.N = t.related_N
-> set t.log='changed',related.log='changed'
-> where t.related_N is not null ;
ERROR 1030 (HY000): Got error 127 from storage engine
mysql> exit
Bye
miguel@hegel:~/dbs/4.1> bin/perror 127
MySQL error code 127: Record-file is crashed
miguel@hegel:~/dbs/4.1>
[8 Jun 2006 11:18]
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/7386
[19 Jun 2006 9: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/7828
[19 Jun 2006 12:51]
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/7847
[19 Jun 2006 19:02]
Sergey Vojtovich
Pushed into trees currently marked as 4.1.21, 5.0.23, 5.1.12.
[21 Jun 2006 4:43]
Paul DuBois
Noted in 4.1.21, 5.0.23, 5.1.12 changelogs. An update that used a join of a table to itself and modified the table on both sides of the join reported the table as crashed.
[27 Oct 2006 17:21]
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/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.
Description: An update on a join of a table with itself and updates both sides of the join reports 'ERROR 1194 (HY000): Table 'related' is marked as crashed and should be repaired'. This is simplified from a real life problem in which the problem symptom was that the update was only performed on one row (even though many qualified (update count =2 because each joined row is 2 records). As I simplified the table to isolate the problem, I got this spurious 'table crash' message instead of the missing updates. Console Log mysql> drop table if exists temp; Query OK, 0 rows affected (0.00 sec) mysql> CREATE TABLE temp ( N int , related_N int , log text ); Query OK, 0 rows affected (0.00 sec) mysql> insert into temp (N,related_N) values (1,null), (2,1); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> update temp as t join temp as related -> on related.N = t.related_N -> set t.log='changed',related.log='changed' -> where t.related_N is not null ; ERROR 1194 (HY000): Table 'related' is marked as crashed and should be repaired mysql> select * from temp; +------+-----------+---------+ | N | related_N | log | +------+-----------+---------+ | 1 | NULL | NULL | | 2 | 1 | changed | +------+-----------+---------+ 2 rows in set (0.00 sec) How to repeat: drop table if exists temp; CREATE TABLE temp ( N int , related_N int , log text ); insert into temp (N,related_N) values (1,null), (2,1); update temp as t join temp as related on related.N = t.related_N set t.log='changed',related.log='changed' where t.related_N is not null ; select * from temp;