#At file:///export/home/tmp/ss156133/z/b39953-51/ based on revid:azundris@stripped
2828 Staale Smedseng 2009-03-11
Bug#39953 Triggers are not working properly with multi
table updates
The check for conflicting use of tables in SP/triggers
is moved to lock_tables(), to be performed after the
possible downgrade of write locks has been done.
modified:
mysql-test/r/trigger.result
mysql-test/t/trigger.test
sql/sql_base.cc
=== modified file 'mysql-test/r/trigger.result'
--- a/mysql-test/r/trigger.result 2008-05-30 09:12:07 +0000
+++ b/mysql-test/r/trigger.result 2009-03-11 12:12:05 +0000
@@ -2053,4 +2053,21 @@ select @a, @b;
drop trigger trg1;
drop trigger trg2;
drop table t1, t2;
+DROP TABLE IF EXISTS t1;
+DROP TRIGGER IF EXISTS t_insert;
+DROP TABLE IF EXISTS t2;
+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));
+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 |
+INSERT INTO t2 (a,b) VALUES (1,2);
+DROP TRIGGER IF EXISTS t_insert;
+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 |
+INSERT INTO t2 (a,b) VALUES (3,5);
+ERROR HY000: Can't update table 't2' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
+DROP TABLE IF EXISTS t1;
+DROP TRIGGER IF EXISTS t_insert;
+DROP TABLE IF EXISTS t2;
End of 5.1 tests.
=== modified file 'mysql-test/t/trigger.test'
--- a/mysql-test/t/trigger.test 2008-05-30 09:12:07 +0000
+++ b/mysql-test/t/trigger.test 2009-03-11 12:12:05 +0000
@@ -2337,4 +2337,40 @@ drop trigger trg1;
drop trigger trg2;
drop table t1, t2;
+#
+# Bug#39953 Triggers are not working properly with multi table updates
+#
+
+-- 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);
+
+-- disable_warnings
+DROP TRIGGER IF EXISTS t_insert;
+-- enable_warnings
+
+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);
+
+-- disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TRIGGER IF EXISTS t_insert;
+DROP TABLE IF EXISTS t2;
+-- enable_warnings
+
--echo End of 5.1 tests.
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2009-02-26 17:20:50 +0000
+++ b/sql/sql_base.cc 2009-03-11 12:12:05 +0000
@@ -2600,27 +2600,11 @@ TABLE *open_table(THD *thd, TABLE_LIST *
{ // Using table locks
TABLE *best_table= 0;
int best_distance= INT_MIN;
- bool check_if_used= thd->prelocked_mode &&
- ((int) table_list->lock_type >=
- (int) TL_WRITE_ALLOW_WRITE);
for (table=thd->open_tables; table ; table=table->next)
{
if (table->s->table_cache_key.length == key_length &&
!memcmp(table->s->table_cache_key.str, key, key_length))
{
- if (check_if_used && table->query_id &&
- table->query_id != thd->query_id)
- {
- /*
- If we are in stored function or trigger we should ensure that
- we won't change table that is already used by calling statement.
- So if we are opening table for writing, we should check that it
- is not already open by some calling stamement.
- */
- my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
- table->s->table_name.str);
- DBUG_RETURN(0);
- }
/*
When looking for a usable TABLE, ignore MERGE children, as they
belong to their parent and cannot be used explicitly.
@@ -2649,13 +2633,13 @@ TABLE *open_table(THD *thd, TABLE_LIST *
{
best_distance= distance;
best_table= table;
- if (best_distance == 0 && !check_if_used)
+ if (best_distance == 0)
{
/*
- If we have found perfect match and we don't need to check that
- table is not used by one of calling statements (assuming that
- we are inside of function or trigger) we can finish iterating
- through open tables list.
+ We have found perfect match and can finish iterating
+ through open tables list. Check for table use conflict
+ between calling statement and SP/trigger is done in
+ lock_tables().
*/
break;
}
@@ -5374,6 +5358,25 @@ int lock_tables(THD *thd, TABLE_LIST *ta
table && table != first_not_own;
table= table->next_global)
{
+ /*
+ In a stored function or trigger we should ensure that we won't change
+ a table that is already used by the calling statement.
+ */
+ if (table->table && table->lock_type >= TL_WRITE_ALLOW_WRITE)
+ {
+ for (TABLE* opentab=thd->open_tables; opentab; opentab=opentab->next)
+ {
+ if (opentab->query_id &&
+ table->table->s == opentab->s &&
+ table->table->query_id != opentab->query_id)
+ {
+ my_error(ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG, MYF(0),
+ table->table->s->table_name.str);
+ DBUG_RETURN(0);
+ }
+ }
+ }
+
if (!table->placeholder() &&
check_lock_and_start_stmt(thd, table->table, table->lock_type))
{
Attachment: [text/bzr-bundle] bzr/staale.smedseng@sun.com-20090311121205-q47tjtf8ndpqxyjx.bundle
