#At file:///Users/staale/z/b39953/ based on revid:aelkin@stripped
2804 Staale Smedseng 2009-02-24
Bug#39953 Triggers are not working properly with multi table updates
open_table() is modified to take a flag instructing it not to check
whether a table for update is used by the calling statement in a
SF/trigger context.
mysql_multi_update_prepare() is modified to do this check after the
tables are opened and locks are (possibly) downgraded.
modified:
.cvsignore
mysql-test/r/trigger.result
mysql-test/t/trigger.test
sql/mysql_priv.h
sql/sql_base.cc
sql/sql_update.cc
=== modified file '.cvsignore'
--- a/.cvsignore 2000-07-31 19:29:14 +0000
+++ b/.cvsignore 2009-02-24 15:43:33 +0000
@@ -1,37 +1,40 @@
-.snprj
-COPYING
-COPYING.LIB
-INSTALL-SOURCE
-INSTALL-SOURCE-GENERIC
-Logs
-MIRRORS
-Makefile
-Makefile.in
-NEW-RPMS
+skr
PUBLIC
-Projects
-TODO
+MIRRORS
+internal-docs
WIN-LICENSE
-aclocal.m4
-binary
+TODO
+Makefile
+.bzr
+conftest.s2
compile
-confdefs.h
-config.cache
+COPYING
+Logs
+.bzrignore
+INSTALL-SOURCE
config.h
-config.log
+mysql-copyright-120700-194832
config.status
-configure
-configure.in-removed
-conftest.c
+mysql-copyright-120700-221248
+NEW-RPMS
+config.log
+Projects
+libtool
conftest.s1
-conftest.s2
conftest.subs
-internal-docs
-libtool
+config.cache
+stamp-h
+Makefile.in
linked_client_sources
linked_server_sources
-mysql-copyright-120700-194832
-mysql-copyright-120700-221248
-skr
-stamp-h
+INSTALL-SOURCE-GENERIC
tmp
+COPYING.LIB
+configure.in-removed
+configure
+.snprj
+confdefs.h
+binary
+aclocal.m4
+.bzr-mysql
+conftest.c
=== 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-02-24 15:43:33 +0000
@@ -2053,4 +2053,15 @@ select @a, @b;
drop trigger trg1;
drop trigger trg2;
drop table t1, t2;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TRIGGER IF EXISTS t_insert;
+CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));
+INSERT INTO t1 (a) VALUES (2);
+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 TABLE IF EXISTS t1;
+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-02-24 15:43:33 +0000
@@ -2337,4 +2337,26 @@ 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 TABLE IF EXISTS t2;
+DROP TRIGGER IF EXISTS t_insert;
+--enable_warnings
+
+CREATE TABLE t1 (a int, date_insert timestamp, PRIMARY KEY (a));
+INSERT INTO t1 (a) VALUES (2);
+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 TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+
--echo End of 5.1 tests.
=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h 2009-02-14 10:40:22 +0000
+++ b/sql/mysql_priv.h 2009-02-24 15:43:33 +0000
@@ -2065,6 +2065,7 @@ MYSQL_LOCK *mysql_lock_tables(THD *thd,
#define MYSQL_OPEN_TEMPORARY_ONLY 0x0008
#define MYSQL_LOCK_IGNORE_GLOBAL_READ_ONLY 0x0010
#define MYSQL_LOCK_PERF_SCHEMA 0x0020
+#define MYSQL_SKIP_UPDATE_USED_TABLE_CHECK 0x0040
void mysql_unlock_tables(THD *thd, MYSQL_LOCK *sql_lock);
void mysql_unlock_read_tables(THD *thd, MYSQL_LOCK *sql_lock);
=== modified file 'sql/sql_base.cc'
--- a/sql/sql_base.cc 2009-02-06 16:06:41 +0000
+++ b/sql/sql_base.cc 2009-02-24 15:43:33 +0000
@@ -2500,6 +2500,9 @@ bool check_if_table_exists(THD *thd, TAB
No version number checking is done.
MYSQL_OPEN_TEMPORARY_ONLY - Open only temporary
table not the base table or view.
+ MYSQL_SKIP_CANT_UPDATE_CHECK - Don't check for
+ table already in use in calling statement (when
+ in a SF/trigger context).
IMPLEMENTATION
Uses a cache of open tables to find a table not in use.
@@ -2600,6 +2603,7 @@ TABLE *open_table(THD *thd, TABLE_LIST *
TABLE *best_table= 0;
int best_distance= INT_MIN;
bool check_if_used= thd->prelocked_mode &&
+ !(flags & MYSQL_SKIP_UPDATE_USED_TABLE_CHECK) &&
((int) table_list->lock_type >=
(int) TL_WRITE_ALLOW_WRITE);
for (table=thd->open_tables; table ; table=table->next)
=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc 2009-02-05 09:49:32 +0000
+++ b/sql/sql_update.cc 2009-02-24 15:43:33 +0000
@@ -972,9 +972,12 @@ int mysql_multi_update_prepare(THD *thd)
reopen_tables:
- /* open tables and create derived ones, but do not lock and fill them */
+ /* open tables and create derived ones, do not lock and fill them and skip
+ the check for update table in use by calling statement (in SF/trigger
+ context), it will be checked below */
if (((original_multiupdate || need_reopen) &&
- open_tables(thd, &table_list, &table_count, 0)) ||
+ open_tables(thd, &table_list, &table_count,
+ MYSQL_SKIP_UPDATE_USED_TABLE_CHECK)) ||
mysql_handle_derived(lex, &mysql_derived_prepare))
DBUG_RETURN(TRUE);
/*
@@ -1031,6 +1034,19 @@ reopen_tables:
DBUG_RETURN(TRUE);
}
+ if (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);
+ }
+
table->mark_columns_needed_for_update();
DBUG_PRINT("info",("setting table `%s` for update", tl->alias));
/*
Attachment: [text/bzr-bundle] bzr/staale.smedseng@sun.com-20090224154333-kf0kecc0hj8nr3l3.bundle
