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:
None 
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:11] Dave Pullin
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;
[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.