From 50ed1ef7520bbff8a88ea33f7838544b1c3ce4e2 Mon Sep 17 00:00:00 2001 From: Staale Smedseng Date: Fri, 27 Mar 2009 12:09:15 +0100 Subject: Bug#39953 Triggers are not working properly with multi table updates Attempt to execute trigger or stored function with multi-UPDATE which used - but didn't update - a table that was also used by the calling statement led to an error. Read-only reference to tables used in the calling statement should be allowed. This problem was caused by the fact that check for conflicting use of tables in SP/triggers was performed in open_tables(), and in case of multi-UPDATE we didn't know exact lock type at this stage. We solve the problem by moving this check to lock_tables(), so it can be performed after exact lock types for tables used by multi-UPDATE are determined. mysql-test/r/trigger.result: Results for the added test case is added. mysql-test/t/trigger.test: A new test case is added, verifying correct table multi-update conflict resolution, both read-only and write. sql/sql_base.cc: The check for conflicting use of tables in SP/triggers is moved to lock_tables(), to be performed after the exact lock types have been determined. Also, an assert is added to open_ltable() to ensure this func is not used in a prelocked context. --- mysql-test/t/trigger.test | 33 +++++++++++++++++++++++++++++++++ 1 file changed, 33 insertions(+) (limited to 'mysql-test/t') diff --git a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test index 2f62ad38621..9a0277a98c2 100644 --- a/mysql-test/t/trigger.test +++ b/mysql-test/t/trigger.test @@ -2217,4 +2217,37 @@ select * from t1; select * from t2; drop table t1; drop temporary table t2; + +--echo #------------------------------------------------------------------------ +--echo # Bug#39953 Triggers are not working properly with multi table updates +--echo #------------------------------------------------------------------------ + +--disable_warnings +DROP TABLE IF EXISTS t1; +DROP TRIGGER IF EXISTS t_insert; +DROP TABLE IF EXISTS t2; +--enable_warnings + +CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a)); +INSERT INTO t1 (a) VALUES (2),(5); +CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW() WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +INSERT INTO t2 (a,b) VALUES (1,2); + +DROP TRIGGER t_insert; + +DELIMITER |; +CREATE TRIGGER t_insert AFTER INSERT ON t2 FOR EACH ROW BEGIN UPDATE t1,t2 SET +date_insert=NOW(),b=b+1 WHERE t1.a=t2.b AND t2.a=NEW.a; END | +DELIMITER ;| +--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG +INSERT INTO t2 (a,b) VALUES (3,5); + +DROP TABLE t1; +DROP TRIGGER t_insert; +DROP TABLE t2; + --echo End of 5.0 tests -- cgit v1.2.1