MySQL Bugs: #16074: Trigger causes query to fail with error that table is crashed

Bug #16074	Trigger causes query to fail with error that table is crashed
Submitted:	29 Dec 2005 16:32	Modified:	7 Apr 2006 15:34
Reporter:	Dean Ellis	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.19	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
A trigger which performs an INSERT ... SELECT from itself causes a table to be reported as crashed when an INSERT ... SELECT to itself is executed.

How to repeat:
CREATE TABLE t1 ( a INT );
CREATE TABLE t2 LIKE t1;
CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT t1.a FROM t1 LEFT JOIN t2 USING (a) WHERE t2.a IS NULL;
INSERT INTO t1 VALUES (1);
INSERT INTO t1 SELECT a FROM t1;
-- ERROR 1194 (HY000): Table 't1' is marked as crashed and should be repaired

Suggested fix:
n/a

I have olsa alike problem. At the example if you change table type to innodb the problem does not occur. I think this is only myisam type problem. 
This should be take into consideration, this is a very serious bug with triggers.

Version: 5.0.18
OS: Suse linux 9.0

I've the same problem on macosx 10.4 and mysql 5.0.18/19
using innodb engine the problem disappears...

after more try, on windows and on linux fedora1 it quietly fails (it inserted the first row only, but doesn't return any error...)

I can't repeat it in any more. This is a duplicate of Bug#17764.

mysql> drop table t1,t2;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t1 ( a INT );
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t2 LIKE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW INSERT INTO t2 SELECT t1.a
    -> FROM t1 LEFT JOIN t2 USING (a) WHERE t2.a IS NULL;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO t1 SELECT a FROM t1;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1 SELECT a FROM t1;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0