Bug #11900 Table becomes write locked while trigger calls user defined function
Submitted: 13 Jul 2005 7:28 Modified: 27 Aug 2005 23:49
Reporter: Alan Lumb Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server: User-defined functions ( UDF ) Severity:S3 (Non-critical)
Version:5.0.7-beta-max OS:Linux (Suse linux 9.2)
Assigned to: CPU Architecture:Any

[13 Jul 2005 7:28] Alan Lumb
During a User-Defined function executed by an AFTER trigger (either insert or update), the whole table is write locked.  

This is largely only a problem when this UDF attempts to update the table and appears to wait for the lock to be removed and hangs there,  thus never completing the statement.

I suspect most people would use the BEFORE INSERT function, and write their UDF to change the NEW values prior to the update, however in our case our design does not allow for this (as an external perl script is called by the UDF, which needs to read and write to the table)

The work around has been put in place by detaching the system command from the mysql database (i.e. using an & to allow it to run in the background), however this is not ideal as it does not allow us to track how successful this command is at running.

How to repeat:
We have done it in the following manner.  I have not included the code for the User defined function, but it simply calls a few system commands (in our case to email somebody) and updates the SQL to reset the changed bit to '0'.

use test

create table email_concept(id int not null auto_increment primary key, emailmessage varchar(255), changed enum(‘0’,’1’) default 1);

delimiter //
CREATE TRIGGER insert_email_concept AFTER INSERT ON email_concept FOR EACH ROW BEGIN IF NEW.changed = ‘1’ THEN select callperlroutine("insert_email_concept",NEW.id); END IF;//
delimiter ;

delimiter //

mysql> insert into email_concept set emailmessage='This should trigger an email';

at this stage the udf hangs at the point where it should update the database.

Suggested fix:
remove the locking before executing the trigger statement in AFTER commands (after commands do not allow the change of NEW.columnname fields.
[27 Aug 2005 23:49] MySQL Verification Team
Currently to use a select statement into a trigger for to
call an UDF isn't more allowed:

ERROR 1415 (0A000): Not allowed to return a result set from a trigger