MySQL Bugs: #12865: Insertion of many rows with duplicates, crashes table with triggers

Bug #12865	Insertion of many rows with duplicates, crashes table with triggers
Submitted:	30 Aug 2005 0:30	Modified:	30 Aug 2005 16:16
Reporter:	Jeff Donner	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.10+ (maybe earlier too)	OS:	SunOS 5.8
Assigned to:		CPU Architecture:	Any

Description:
This isn't very boiled down but, with a uniqueness constraint and these triggers:

  CREATE TRIGGER PxUTrigger AFTER UPDATE ON Px
  FOR EACH ROW UPDATE Px SET LastModUser = CURRENT_USER();

  CREATE TRIGGER PxITrigger AFTER INSERT ON Px
  FOR EACH ROW UPDATE Px SET LastModUser = CURRENT_USER();

inserting ~50 rows with some duplicates, causes: 

  Error, 1194L, "Table 'px' is marked as crashed and should be repaired"

whereas with different triggers:

  CREATE TRIGGER uTrigger BEFORE UPDATE ON px 
  FOR EACH ROW SET NEW.LastModUser = CURRENT_USER();

  CREATE TRIGGER iTrigger BEFORE INSERT ON px 
  FOR EACH ROW SET NEW.LastModUser = CURRENT_USER();

it just warns about the duplicates.

How to repeat:
I have a schema definition and data, but I don't want to release it publicly- if you'll email me (jd@predict.com) I'll send it to you. (A couple simple stabs at boiling it down have failed.)

You can upload the schema definition and data at:

ftp://ftp.mysql.com:/pub/mysql/download

and it will be private only for MySQL's developer.

Please use a zipped file with name bug12865.zip

Thanks in advance.

Miguel, I wasn't able to upload to your FTP server (anonymous didn't have write permissions or something) but this way worked - I hope you notice the file.

Sorry was my mistake. I wrote the wrong directory should
be the /upload directory instead of /download.

I was able to get the attached file. Thanks.

I was unable to reproduce the crash table with current BK source
server:

mysql> source /home/miguel/dbs/5.0/bug12865.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.05 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

ERROR 1442 (HY000): Can't update table 'T' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

mysql> check table T;
+-------+-------+----------+----------+
| Table | Op    | Msg_type | Msg_text |
+-------+-------+----------+----------+
| sd.T  | check | status   | OK       |
+-------+-------+----------+----------+
1 row in set (0.01 sec)

Maybe is related to the bug:

http://bugs.mysql.com/bug.php?id=12644

with your server version.