MySQL Bugs: #32220: INSERT DELAYED not working on a table with Triggers

Bug #32220	INSERT DELAYED not working on a table with Triggers
Submitted:	9 Nov 2007 10:50	Modified:	14 Nov 2007 12:07
Reporter:	Stefan Haubold	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Linux (Debian 4.0, DotDeb Packages)
Assigned to:	Sveta Smirnova	CPU Architecture:	Any
Tags:	insert delayed, trigger

Description:
I've run into an issue with Triggers on a MyISAM Table and inserting into that Table with INSERT DELAYED. When i execute an INSERT DELAYED on a MyIsam table with triggers, the query is executed and the data is inserted but the query isn't executed as delayed. If the trigger is dropped, the query is executed as delayed query again. Even if only an update-trigger is active on the table the INSERT isn't executed as DELAYED. 

I checked the documentation and the forum, i don't think this is working as intended if so, the documentation needs an update. 

We run into this after upgrading from 5.0.37 to 5.0.45. With 5.0.37 we had a different issue with the same environment, the insert were run delayed but the trigger wasn't executed. On top of that MySQL crashed every 30 minutes, i found something in the bug reports about Insert Delayed, Triggers + MyIsam and so i upgraded. After the upgrade everything looked fine but since we monitoring the "Delayed_writes" via Munin i noticed that they were "0" all the time. After a few minutes testing and searching i realised that the triggers were the problem as mentioned above.
I also checked if only the counters aren't working. But if you lock the Tables and then execute an Insert Delayed, the Inserts waits for the lock to release. With a real Insert Delayed the Delayed-Thread should queue the insert and the Querie should return imediatly.

How to repeat:
Here is the example Setup:

CREATE TABLE `testDelayed` (
            `text` varchar(50) NOT NULL                          
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Executing this Query:

INSERT DELAYED INTO testDelayed (text) VALUE ('blah');

This query works as indented, since delayed_writes is increased by 1 and the Delayed Insert Threads is running.

SHOW STATUS LIKE '%delay%' 

Delayed_errors    0
Delayed_insert_threads    1
Delayed_writes    1
Not_flushed_delayed_rows    0

(Assuming you make this on a new started Mysql Server and having run no other DELAYED queries before)

If you put a Trigger on that table, for example:

CREATE TRIGGER testDelayedBeforeInsert BEFORE INSERT on testDelayed
FOR EACH ROW BEGIN
   SET NEW.text = CONCAT(NEW.text,'-Hallo');
END;

And rerun the Insert from above:

INSERT DELAYED INTO testDelayed (text) VALUE ('blah');

They INSERT is exectued, the trigger runs. (text = 'blah-Hallo')

But this query wasn't executed as a DELAYED Insert.

SHOW STATUS LIKE '%delay%' 

Delayed_errors    0
Delayed_insert_threads    1
Delayed_writes    1
Not_flushed_delayed_rows    0

Suggested fix:
No suggestion.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

For details see comment "[23 Aug 2006 19:16] Tomash Brechko" to bug #21483

Thank you. 
I checked the Bug Report you referencing to, but didn't see the comment mentionening the patch, sorry for that.

But since you also referencing to the normal documentation for mysql 5.0x, there isn't anything mentioned about it. There should be an update to http://dev.mysql.com/doc/refman/5.0/en/insert-delayed.html

Stefan,

thank you for the feedback. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.