Description:
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.
Description: 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.