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: Dmitry Lenev CPU Architecture:Any

[29 Dec 2005 16:32] Dean Ellis
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
[28 Feb 2006 9:46] Nizamettin OZPOLAT
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
[12 Mar 2006 16:51] Cristiano Contin
I've the same problem on macosx 10.4 and mysql 5.0.18/19
using innodb engine the problem disappears...
[16 Mar 2006 21:03] Cristiano Contin
after more try, on windows and on linux fedora1 it quietly fails (it inserted the first row only, but doesn't return any error...)
[7 Apr 2006 15:34] Konstantin Osipov
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