Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-07-10 01:50:20+04:00, kostja@bodhi.(none) +20 -0
A fix and a test case for Bug#26141 "mixing table types in trigger
causes full table lock on innodb table".
Also fixes Bug #28502 Triggers that update another innodb table
will block on X lock unnecessarily (duplciate).
Both bug synopsises are misleading, InnoDB table in both cases is
not X locked. The statements, however, cannot proceed concurrently,
just as described in the bug reprots.
If a user had an InnoDB table, and two triggers, AFTER UPDATE and
AFTER INSERT, competing for the same resource (e.g. a MyISAM table),
then INSERTS/UPDATES of the InnoDB table were not concurrent any more.
The problem had other side-effects (see respective bug reports).
This behavior was a consequence of a shortcoming of the pre-locking
algorithm, which would not distinguish between different DML operations
(e.g. INSERT and DELETE) and pre-lock all the tables
that are used by any trigger defined on the subject table.
The idea of the fix is to extend the pre-locking algorithm to keep track,
for each table, what DML operation it is used for and not
load triggers that are known to never be fired.
mysql-test/r/trigger-trans.result@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +59 -0
Update results (Bug#26141)
mysql-test/r/trigger.result@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +271 -0
Update results (Bug#28502)
mysql-test/t/trigger-trans.test@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +81 -1
Add a test case for Bug#26141 "mixing table types in trigger causes
full table lock on innodb table"
mysql-test/t/trigger.test@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +232 -0
Add a teste case for Bug#28502 Triggers that update another innodb
table will block echo on X lock unnecessarily. Add more test
coverage for triggers.
sql/item.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +0 -8
enum trg_event_type is needed in table.h
sql/sp.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +38 -16
Take into account table_list->trg_event_map when determining
what tables to pre-lock.
sql/sp_head.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +19 -6
Generate sroutines hash key in init_spname.
Maintain and merge trg_event_map when compiling the pre-locking
list.
sql/sp_head.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +1 -0
Add sroutines_key member, used to insert the sphead for a
trigger into the cache of routines used by the statements.
Previously we would include table name instead, since
all triggers were always loaded for a table.
sql/sql_bitmap.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +130 -49
Update the template and provide meta-programming framework
to allow Bitmap<3> and, generally, bitmaps for arbitrary number
of bits.
sql/sql_class.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +0 -1
Move enum_duplicates to table.h, it's used in
TABLE_LIST::set_trg_event_type.
sql/sql_lex.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +22 -0
Introduce a new lex step:
sql/sql_lex.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +2 -0
Add declaration for set_trg_event_type_for_tables.
sql/sql_parse.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +3 -2
Call set_trg_event_type_for_tables after mysql_parse.
sql/sql_prepare.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +1 -0
Call set_trg_event_type_for_tables after mysql_parse.
sql/sql_trigger.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +16 -12
Call set_trg_event_type_for_tables after mysql_parse.
sql/sql_trigger.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +0 -8
Removean obsolete member.
sql/sql_view.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +1 -0
Call set_trg_event_type_for_tables after mysql_parse.
sql/sql_yacc.yy@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +2 -2
Move assignment of m_type before init_spname, it's now used there.
sql/table.cc@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +122 -0
Implement TABLE_LIST::set_trg_event_map
sql/table.h@stripped, 2007-07-10 01:50:17+04:00, kostja@bodhi.(none) +23 -0
Add missing declarations.
diff -Nrup a/mysql-test/r/trigger-trans.result b/mysql-test/r/trigger-trans.result
--- a/mysql-test/r/trigger-trans.result 2006-05-12 20:58:50 +04:00
+++ b/mysql-test/r/trigger-trans.result 2007-07-10 01:50:17 +04:00
@@ -82,3 +82,62 @@ ALICE 33 1 0
THE CROWN 43 1 0
THE PIE 53 1 1
drop table t1;
+
+Bug#26141 mixing table types in trigger causes full
+table lock on innodb table
+
+Ensure we do not open and lock tables for the triggers we do not
+fire.
+
+drop table if exists t1, t2, t3;
+drop trigger if exists trg_bug26141_au;
+drop trigger if exists trg_bug26141_ai;
+create table t1 (c int primary key) engine=innodb;
+create table t2 (c int) engine=myisam;
+create table t3 (c int) engine=myisam;
+insert into t1 (c) values (1);
+create trigger trg_bug26141_ai after insert on t1
+for each row
+begin
+insert into t2 (c) values (1);
+# We need the 'sync' lock to synchronously wait in connection 2 till
+# the moment when the trigger acquired all the locks.
+select release_lock("lock_bug26141_sync") into @a;
+# 1000 is time in seconds of lock wait timeout -- this is a way
+# to cause a manageable sleep up to 1000 seconds
+select get_lock("lock_bug26141_wait", 1000) into @a;
+end|
+create trigger trg_bug26141_au after update on t1
+for each row
+begin
+insert into t3 (c) values (1);
+end|
+select get_lock("lock_bug26141_wait", 0);
+get_lock("lock_bug26141_wait", 0)
+1
+select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
+get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0)
+1
+insert into t1 (c) values (2);
+select get_lock("lock_bug26141_sync", 1000);
+get_lock("lock_bug26141_sync", 1000)
+1
+update t1 set c=3 where c=1;
+select release_lock("lock_bug26141_sync");
+release_lock("lock_bug26141_sync")
+1
+select release_lock("lock_bug26141_wait");
+release_lock("lock_bug26141_wait")
+1
+select * from t1;
+c
+2
+3
+select * from t2;
+c
+1
+select * from t3;
+c
+1
+drop table t1, t2, t3;
+End of 5.0 tests
diff -Nrup a/mysql-test/r/trigger.result b/mysql-test/r/trigger.result
--- a/mysql-test/r/trigger.result 2007-07-05 11:34:02 +04:00
+++ b/mysql-test/r/trigger.result 2007-07-10 01:50:17 +04:00
@@ -1476,4 +1476,275 @@ DROP TRIGGER t1_test;
DROP TABLE t1,t2;
SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
+
+Bug#28502 Triggers that update another innodb table will block
+on X lock unnecessarily
+
+Ensure we do not open and lock tables for triggers we do not fire.
+
+drop table if exists t1, t2;
+drop trigger if exists trg_bug28502_au;
+create table t1 (id int, count int);
+create table t2 (id int);
+create trigger trg_bug28502_au before update on t2
+for each row
+begin
+if (new.id is not null) then
+update t1 set count= count + 1 where id = old.id;
+end if;
+end|
+insert into t1 (id, count) values (1, 0);
+lock table t1 write;
+insert into t2 set id=1;
+unlock tables;
+update t2 set id=1 where id=1;
+select * from t1;
+id count
+1 1
+select * from t2;
+id
+1
+drop table t1, t2;
+
+Additionally, provide test coverage for triggers and
+all MySQL data changing commands.
+
+drop table if exists t1, t2, t1_op_log;
+drop trigger if exists trg_bug28502_bi;
+drop trigger if exists trg_bug28502_ai;
+drop trigger if exists trg_bug28502_bu;
+drop trigger if exists trg_bug28502_au;
+drop trigger if exists trg_bug28502_bd;
+drop trigger if exists trg_bug28502_ad;
+create table t1 (id int primary key auto_increment, operation varchar(255));
+create table t2 (id int primary key);
+create table t1_op_log(operation varchar(255));
+create trigger trg_bug28502_bi before insert on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("Before INSERT, new=", new.operation));
+end|
+create trigger trg_bug28502_ai after insert on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("After INSERT, new=", new.operation));
+end|
+create trigger trg_bug28502_bu before update on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("Before UPDATE, new=", new.operation,
+", old=", old.operation));
+end|
+create trigger trg_bug28502_au after update on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("After UPDATE, new=", new.operation,
+", old=", old.operation));
+end|
+create trigger trg_bug28502_bd before delete on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("Before DELETE, old=", old.operation));
+end|
+create trigger trg_bug28502_ad after delete on t1
+for each row
+begin
+insert into t1_op_log (operation)
+values (concat("After DELETE, old=", old.operation));
+end|
+insert into t1 (operation) values ("INSERT");
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 INSERT
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT
+After INSERT, new=INSERT
+truncate t1_op_log;
+update t1 set operation="UPDATE" where id=@id;
+select * from t1;
+id operation
+1 UPDATE
+select * from t1_op_log;
+operation
+Before UPDATE, new=UPDATE, old=INSERT
+After UPDATE, new=UPDATE, old=INSERT
+truncate t1_op_log;
+delete from t1 where id=@id;
+select * from t1;
+id operation
+select * from t1_op_log;
+operation
+Before DELETE, old=UPDATE
+After DELETE, old=UPDATE
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After INSERT, new=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1_op_log;
+insert into t1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+select * from t1;
+id operation
+0 INSERT ON DUPLICATE KEY UPDATE, updating the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ON DUPLICATE KEY UPDATE, the key value is the same
+Before UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+After UPDATE, new=INSERT ON DUPLICATE KEY UPDATE, updating the duplicate, old=INSERT ON DUPLICATE KEY UPDATE, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into t1 values (NULL, "REPLACE, inserting a new key");
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 REPLACE, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, inserting a new key
+After INSERT, new=REPLACE, inserting a new key
+truncate t1_op_log;
+replace into t1 values (@id, "REPLACE, deleting the duplicate");
+select * from t1;
+id operation
+1 REPLACE, deleting the duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE, deleting the duplicate
+Before DELETE, old=REPLACE, inserting a new key
+After DELETE, old=REPLACE, inserting a new key
+After INSERT, new=REPLACE, deleting the duplicate
+truncate t1;
+truncate t1_op_log;
+create table if not exists t1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+Warnings:
+Note 1050 Table 't1' already exists
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 CREATE TABLE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... SELECT, inserting a new key
+truncate t1_op_log;
+create table if not exists t1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+Warnings:
+Note 1050 Table 't1' already exists
+select * from t1;
+id operation
+1 CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+select * from t1_op_log;
+operation
+Before INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+Before DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After DELETE, old=CREATE TABLE ... SELECT, inserting a new key
+After INSERT, new=CREATE TABLE ... REPLACE SELECT, deleting a duplicate key
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 INSERT ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT, inserting a new key
+After INSERT, new=INSERT ... SELECT, inserting a new key
+truncate t1_op_log;
+insert into t1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+select * from t1;
+id operation
+0 INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate
+Before UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+After UPDATE, new=INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate, old=INSERT ... SELECT, inserting a new key
+truncate t1;
+truncate t1_op_log;
+replace into t1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+set @id=last_insert_id();
+select * from t1;
+id operation
+1 REPLACE ... SELECT, inserting a new key
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, inserting a new key
+truncate t1_op_log;
+replace into t1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+select * from t1;
+id operation
+1 REPLACE ... SELECT, deleting a duplicate
+select * from t1_op_log;
+operation
+Before INSERT, new=REPLACE ... SELECT, deleting a duplicate
+Before DELETE, old=REPLACE ... SELECT, inserting a new key
+After DELETE, old=REPLACE ... SELECT, inserting a new key
+After INSERT, new=REPLACE ... SELECT, deleting a duplicate
+truncate t1;
+truncate t1_op_log;
+insert into t1 (id, operation) values (1, "insert");
+insert into t2 (id) values (1);
+delete t1.*, t2.* from t1, t2 where t1.id=1;
+select * from t1;
+id operation
+select * from t2;
+id
+select * from t1_op_log;
+operation
+Before INSERT, new=insert
+After INSERT, new=insert
+Before DELETE, old=insert
+After DELETE, old=insert
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+insert into t1 (id, operation) values (1, "insert");
+insert into t2 (id) values (1);
+update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;
+update t1, t2
+set t2.id=3, operation="multi-UPDATE, no real update, but the trigger is fired" where t1.id=2;
+select * from t1;
+id operation
+2 multi-UPDATE, no real update, but the trigger is fired
+select * from t2;
+id
+3
+select * from t1_op_log;
+operation
+Before INSERT, new=insert
+After INSERT, new=insert
+Before UPDATE, new=multi-UPDATE, old=insert
+After UPDATE, new=multi-UPDATE, old=insert
+Before UPDATE, new=multi-UPDATE, no real update, but the trigger is fired, old=multi-UPDATE
+After UPDATE, new=multi-UPDATE, no real update, but the trigger is fired, old=multi-UPDATE
+drop table t1, t2, t1_op_log;
End of 5.0 tests
diff -Nrup a/mysql-test/t/trigger-trans.test b/mysql-test/t/trigger-trans.test
--- a/mysql-test/t/trigger-trans.test 2006-03-24 14:58:13 +03:00
+++ b/mysql-test/t/trigger-trans.test 2007-07-10 01:50:17 +04:00
@@ -49,4 +49,84 @@ insert into t1 values ('The Pie', 50, 1,
select * from t1;
drop table t1;
-# End of 5.0 tests
+--echo
+--echo Bug#26141 mixing table types in trigger causes full
+--echo table lock on innodb table
+--echo
+--echo Ensure we do not open and lock tables for the triggers we do not
+--echo fire.
+--echo
+--disable_warnings
+drop table if exists t1, t2, t3;
+drop trigger if exists trg_bug26141_au;
+drop trigger if exists trg_bug26141_ai;
+--enable_warnings
+# Note, for InnoDB to allow concurrent UPDATE and INSERT the
+# table must have a unique key.
+create table t1 (c int primary key) engine=innodb;
+create table t2 (c int) engine=myisam;
+create table t3 (c int) engine=myisam;
+insert into t1 (c) values (1);
+delimiter |;
+
+create trigger trg_bug26141_ai after insert on t1
+for each row
+begin
+ insert into t2 (c) values (1);
+# We need the 'sync' lock to synchronously wait in connection 2 till
+# the moment when the trigger acquired all the locks.
+ select release_lock("lock_bug26141_sync") into @a;
+# 1000 is time in seconds of lock wait timeout -- this is a way
+# to cause a manageable sleep up to 1000 seconds
+ select get_lock("lock_bug26141_wait", 1000) into @a;
+end|
+
+create trigger trg_bug26141_au after update on t1
+for each row
+begin
+ insert into t3 (c) values (1);
+end|
+delimiter ;|
+
+# Establish an alternative connection.
+--connect (connection_aux,localhost,root,,test,,)
+--connect (connection_update,localhost,root,,test,,)
+
+connection connection_aux;
+# Lock the wait lock, it must not be locked, so specify zero timeout.
+select get_lock("lock_bug26141_wait", 0);
+
+#
+connection default;
+#
+# Run the trigger synchronously
+#
+select get_lock("lock_bug26141_sync", /* must not be priorly locked */ 0);
+# Will acquire the table level locks, perform the insert into t2,
+# release the sync lock and block on the wait lock.
+send insert into t1 (c) values (2);
+
+connection connection_update;
+# Wait for the trigger to acquire its locks and unlock the sync lock.
+select get_lock("lock_bug26141_sync", 1000);
+#
+# This must continue: after the fix for the bug, we do not
+# open tables for t2, and with c=4 innobase allows the update
+# to run concurrently with insert.
+update t1 set c=3 where c=1;
+select release_lock("lock_bug26141_sync");
+connection connection_aux;
+select release_lock("lock_bug26141_wait");
+connection default;
+reap;
+select * from t1;
+select * from t2;
+select * from t3;
+
+# Drops the trigger as well.
+drop table t1, t2, t3;
+disconnect connection_update;
+disconnect connection_aux;
+
+
+--echo End of 5.0 tests
diff -Nrup a/mysql-test/t/trigger.test b/mysql-test/t/trigger.test
--- a/mysql-test/t/trigger.test 2007-07-05 11:34:02 +04:00
+++ b/mysql-test/t/trigger.test 2007-07-10 01:50:17 +04:00
@@ -1828,5 +1828,237 @@ DROP TRIGGER t1_test;
DROP TABLE t1,t2;
SET SESSION LOW_PRIORITY_UPDATES=DEFAULT;
SET GLOBAL LOW_PRIORITY_UPDATES=DEFAULT;
+--echo
+--echo Bug#28502 Triggers that update another innodb table will block
+--echo on X lock unnecessarily
+--echo
+--echo Ensure we do not open and lock tables for triggers we do not fire.
+--echo
+--disable_warnings
+drop table if exists t1, t2;
+drop trigger if exists trg_bug28502_au;
+--enable_warnings
+create table t1 (id int, count int);
+create table t2 (id int);
+delimiter |;
+
+create trigger trg_bug28502_au before update on t2
+for each row
+begin
+ if (new.id is not null) then
+ update t1 set count= count + 1 where id = old.id;
+ end if;
+end|
+
+delimiter ;|
+insert into t1 (id, count) values (1, 0);
+
+lock table t1 write;
+
+--connect (connection_insert, localhost, root, , test, , )
+connection connection_insert;
+# Is expected to pass.
+insert into t2 set id=1;
+connection default;
+unlock tables;
+update t2 set id=1 where id=1;
+select * from t1;
+select * from t2;
+# Will drop the trigger
+drop table t1, t2;
+disconnect connection_insert;
+--echo
+--echo Additionally, provide test coverage for triggers and
+--echo all MySQL data changing commands.
+--echo
+--disable_warnings
+drop table if exists t1, t2, t1_op_log;
+drop trigger if exists trg_bug28502_bi;
+drop trigger if exists trg_bug28502_ai;
+drop trigger if exists trg_bug28502_bu;
+drop trigger if exists trg_bug28502_au;
+drop trigger if exists trg_bug28502_bd;
+drop trigger if exists trg_bug28502_ad;
+--enable_warnings
+create table t1 (id int primary key auto_increment, operation varchar(255));
+create table t2 (id int primary key);
+create table t1_op_log(operation varchar(255));
+delimiter |;
+create trigger trg_bug28502_bi before insert on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("Before INSERT, new=", new.operation));
+end|
+create trigger trg_bug28502_ai after insert on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("After INSERT, new=", new.operation));
+end|
+create trigger trg_bug28502_bu before update on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("Before UPDATE, new=", new.operation,
+ ", old=", old.operation));
+end|
+create trigger trg_bug28502_au after update on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("After UPDATE, new=", new.operation,
+ ", old=", old.operation));
+end|
+create trigger trg_bug28502_bd before delete on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("Before DELETE, old=", old.operation));
+end|
+create trigger trg_bug28502_ad after delete on t1
+for each row
+begin
+ insert into t1_op_log (operation)
+ values (concat("After DELETE, old=", old.operation));
+end|
+delimiter ;|
+
+insert into t1 (operation) values ("INSERT");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+update t1 set operation="UPDATE" where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+delete from t1 where id=@id;
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values
+(NULL, "INSERT ON DUPLICATE KEY UPDATE, inserting a new key")
+on duplicate key update id=NULL, operation="Should never happen";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values
+(@id, "INSERT ON DUPLICATE KEY UPDATE, the key value is the same")
+on duplicate key update id=NULL,
+operation="INSERT ON DUPLICATE KEY UPDATE, updating the duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into t1 values (NULL, "REPLACE, inserting a new key");
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into t1 values (@id, "REPLACE, deleting the duplicate");
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+create table if not exists t1
+select NULL, "CREATE TABLE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+create table if not exists t1 replace
+select @id, "CREATE TABLE ... REPLACE SELECT, deleting a duplicate key";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation)
+select NULL, "INSERT ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+insert into t1 (id, operation)
+select @id,
+"INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate"
+on duplicate key update id=NULL,
+operation="INSERT ... SELECT ... ON DUPLICATE KEY UPDATE, updating a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+replace into t1 (id, operation)
+select NULL, "REPLACE ... SELECT, inserting a new key";
+
+set @id=last_insert_id();
+
+select * from t1;
+select * from t1_op_log;
+truncate t1_op_log;
+
+replace into t1 (id, operation)
+select @id, "REPLACE ... SELECT, deleting a duplicate";
+
+select * from t1;
+select * from t1_op_log;
+truncate t1;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values (1, "insert");
+insert into t2 (id) values (1);
+
+delete t1.*, t2.* from t1, t2 where t1.id=1;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+truncate t1;
+truncate t2;
+truncate t1_op_log;
+
+insert into t1 (id, operation) values (1, "insert");
+insert into t2 (id) values (1);
+update t1, t2 set t1.id=2, operation="multi-UPDATE" where t1.id=1;
+update t1, t2
+set t2.id=3, operation="multi-UPDATE, no real update, but the trigger is fired" where t1.id=2;
+
+select * from t1;
+select * from t2;
+select * from t1_op_log;
+
+drop table t1, t2, t1_op_log;
+
+#
+# TODO: test LOAD DATA INFILE
--echo End of 5.0 tests
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h 2007-07-06 16:18:44 +04:00
+++ b/sql/item.h 2007-07-10 01:50:17 +04:00
@@ -2309,14 +2309,6 @@ enum trg_action_time_type
TRG_ACTION_BEFORE= 0, TRG_ACTION_AFTER= 1, TRG_ACTION_MAX
};
-/*
- Event on which trigger is invoked.
-*/
-enum trg_event_type
-{
- TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2, TRG_EVENT_MAX
-};
-
class Table_triggers_list;
/*
diff -Nrup a/sql/sp.cc b/sql/sp.cc
--- a/sql/sp.cc 2007-07-05 02:20:30 +04:00
+++ b/sql/sp.cc 2007-07-10 01:50:17 +04:00
@@ -440,6 +440,19 @@ db_load_routine(THD *thd, int type, sp_n
lex_start(thd);
thd->spcont= NULL;
ret= MYSQLparse(thd);
+
+ if (ret == 0)
+ {
+ /*
+ Not strictly necessary to invoke this method here, since we know
+ that we've parsed CREATE PROCEDURE/FUNCTION and not an
+ UPDATE/DELETE/INSERT/REPLACE/LOAD/CREATE TABLE, but we try to
+ maintain the invariant that this method is called for each
+ distinct statement, in case its logic is extended with other
+ types of analyses in future.
+ */
+ newlex.set_trg_event_type_for_tables();
+ }
}
if (ret || thd->is_fatal_error || newlex.sphead == NULL)
@@ -1742,31 +1755,40 @@ sp_cache_routines_and_add_tables_for_tri
TABLE_LIST *table)
{
int ret= 0;
- Table_triggers_list *triggers= table->table->triggers;
- if (add_used_routine(lex, thd->stmt_arena, &triggers->sroutines_key,
- table->belong_to_view))
+
+ Sroutine_hash_entry **last_cached_routine_ptr=
+ (Sroutine_hash_entry **)lex->sroutines_list.next;
+
+ if (static_cast<int>(table->lock_type) >=
+ static_cast<int>(TL_WRITE_ALLOW_WRITE))
{
- Sroutine_hash_entry **last_cached_routine_ptr=
- (Sroutine_hash_entry **)lex->sroutines_list.next;
+ DBUG_ASSERT(table->is_trg_event_map_initialized);
+
for (int i= 0; i < (int)TRG_EVENT_MAX; i++)
{
- for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
+ if (table->trg_event_map.is_set(static_cast<uint>(i)))
{
- if (triggers->bodies[i][j])
+ for (int j= 0; j < (int)TRG_ACTION_MAX; j++)
{
- (void)triggers->bodies[i][j]->
- add_used_tables_to_table_list(thd, &lex->query_tables_last,
- table->belong_to_view);
- sp_update_stmt_used_routines(thd, lex,
- &triggers->bodies[i][j]->m_sroutines,
- table->belong_to_view);
+ /* We can have only one trigger per action type currently */
+ sp_head *trigger= table->table->triggers->bodies[i][j];
+ if (trigger &&
+ add_used_routine(lex, thd->stmt_arena, &trigger->m_sroutines_key,
+ table->belong_to_view))
+ {
+ trigger->add_used_tables_to_table_list(thd, &lex->query_tables_last,
+ table->belong_to_view);
+ sp_update_stmt_used_routines(thd, lex,
+ &trigger->m_sroutines,
+ table->belong_to_view);
+ }
}
}
}
- ret= sp_cache_routines_and_add_tables_aux(thd, lex,
- *last_cached_routine_ptr,
- FALSE, NULL);
}
+ ret= sp_cache_routines_and_add_tables_aux(thd, lex,
+ *last_cached_routine_ptr,
+ FALSE, NULL);
return ret;
}
diff -Nrup a/sql/sp_head.cc b/sql/sp_head.cc
--- a/sql/sp_head.cc 2007-06-19 01:54:33 +04:00
+++ b/sql/sp_head.cc 2007-07-10 01:50:17 +04:00
@@ -480,9 +480,9 @@ sp_head::init(LEX *lex)
my_init_dynamic_array(&m_instr, sizeof(sp_instr *), 16, 8);
m_param_begin= m_param_end= m_body_begin= 0;
m_qname.str= m_db.str= m_name.str= m_params.str=
- m_body.str= m_defstr.str= 0;
+ m_body.str= m_defstr.str= m_sroutines_key.str= 0;
m_qname.length= m_db.length= m_name.length= m_params.length=
- m_body.length= m_defstr.length= 0;
+ m_body.length= m_defstr.length= m_sroutines_key.length= 0;
m_return_field_def.charset= NULL;
DBUG_VOID_RETURN;
}
@@ -509,9 +509,14 @@ sp_head::init_sp_name(THD *thd, sp_name
if (spname->m_qname.length == 0)
spname->init_qname(thd);
- m_qname.length= spname->m_qname.length;
- m_qname.str= strmake_root(thd->mem_root, spname->m_qname.str,
- m_qname.length);
+ m_sroutines_key.length= spname->m_sroutines_key.length;
+ m_sroutines_key.str= memdup_root(thd->mem_root,
+ spname->m_sroutines_key.str,
+ spname->m_sroutines_key.length + 1);
+ m_sroutines_key.str[0]= static_cast<char>(m_type);
+
+ m_qname.length= m_sroutines_key.length - 1;
+ m_qname.str= m_sroutines_key.str + 1;
DBUG_VOID_RETURN;
}
@@ -1796,8 +1801,11 @@ sp_head::restore_lex(THD *thd)
{
DBUG_ENTER("sp_head::restore_lex");
LEX *sublex= thd->lex;
- LEX *oldlex= (LEX *)m_lex.pop();
+ LEX *oldlex;
+
+ sublex->set_trg_event_type_for_tables();
+ oldlex= (LEX *)m_lex.pop();
if (! oldlex)
return; // Nothing to restore
@@ -3429,6 +3437,7 @@ typedef struct st_sp_table
thr_lock_type lock_type; /* lock type used for prelocking */
uint lock_count;
uint query_lock_count;
+ Bitmap<3> trg_event_map;
} SP_TABLE;
byte *
@@ -3515,6 +3524,7 @@ sp_head::merge_table_list(THD *thd, TABL
tab->query_lock_count++;
if (tab->query_lock_count > tab->lock_count)
tab->lock_count++;
+ tab->trg_event_map.merge(table->trg_event_map);
}
else
{
@@ -3536,6 +3546,7 @@ sp_head::merge_table_list(THD *thd, TABL
tab->db_length= table->db_length;
tab->lock_type= table->lock_type;
tab->lock_count= tab->query_lock_count= 1;
+ tab->trg_event_map= table->trg_event_map;
my_hash_insert(&m_sptabs, (byte *)tab);
}
}
@@ -3613,6 +3624,8 @@ sp_head::add_used_tables_to_table_list(T
table->cacheable_table= 1;
table->prelocking_placeholder= 1;
table->belong_to_view= belong_to_view;
+ table->trg_event_map= stab->trg_event_map;
+ table->is_trg_event_map_initialized= TRUE;
/* Everyting else should be zeroed */
diff -Nrup a/sql/sp_head.h b/sql/sp_head.h
--- a/sql/sp_head.h 2007-05-07 12:23:09 +04:00
+++ b/sql/sp_head.h 2007-07-10 01:50:17 +04:00
@@ -130,6 +130,7 @@ public:
st_sp_chistics *m_chistics;
ulong m_sql_mode; // For SHOW CREATE and execution
LEX_STRING m_qname; // db.name
+ LEX_STRING m_sroutines_key; // [type]db.name\0
LEX_STRING m_db;
LEX_STRING m_name;
LEX_STRING m_params;
diff -Nrup a/sql/sql_bitmap.h b/sql/sql_bitmap.h
--- a/sql/sql_bitmap.h 2006-12-23 22:04:26 +03:00
+++ b/sql/sql_bitmap.h 2007-07-10 01:50:17 +04:00
@@ -21,21 +21,20 @@
#include <my_bitmap.h>
-template <uint default_width> class Bitmap
+template <uint byte_width> class Bitmap_my_bitmap
{
MY_BITMAP map;
- uchar buffer[(default_width+7)/8];
+ uchar buffer[byte_width];
public:
- Bitmap() { init(); }
- Bitmap(const Bitmap& from) { *this=from; }
- explicit Bitmap(uint prefix_to_set) { init(prefix_to_set); }
- void init() { bitmap_init(&map, buffer, default_width, 0); }
+ Bitmap_my_bitmap() { init(); }
+ Bitmap_my_bitmap(const Bitmap_my_bitmap& from) { *this=from; }
+ explicit Bitmap_my_bitmap(uint prefix_to_set) { init(prefix_to_set); }
+ void init() { bitmap_init(&map, buffer, byte_width*8, 0); }
void init(uint prefix_to_set) { init(); set_prefix(prefix_to_set); }
- uint length() const { return default_width; }
- Bitmap& operator=(const Bitmap& map2)
+ Bitmap_my_bitmap& operator=(const Bitmap_my_bitmap& rhs)
{
init();
- memcpy(buffer, map2.buffer, sizeof(buffer));
+ memcpy(buffer, rhs.buffer, sizeof(buffer));
return *this;
}
void set_bit(uint n) { bitmap_set_bit(&map, n); }
@@ -43,7 +42,10 @@ public:
void set_prefix(uint n) { bitmap_set_prefix(&map, n); }
void set_all() { bitmap_set_all(&map); }
void clear_all() { bitmap_clear_all(&map); }
- void intersect(Bitmap& map2) { bitmap_intersect(&map, &map2.map); }
+ void intersect(const Bitmap_my_bitmap &rhs)
+ {
+ bitmap_intersect(&map, &rhs.map);
+ }
void intersect(ulonglong map2buff)
{
MY_BITMAP map2;
@@ -58,14 +60,23 @@ public:
bitmap_set_above(&map, sizeof(ulonglong),
test(map2buff & (LL(1) << (sizeof(ulonglong) * 8 - 1))));
}
- void subtract(Bitmap& map2) { bitmap_subtract(&map, &map2.map); }
- void merge(Bitmap& map2) { bitmap_union(&map, &map2.map); }
+ void subtract(const Bitmap_my_bitmap &rhs)
+ {
+ bitmap_subtract(&map, &rhs.map);
+ }
+ void merge(const Bitmap_my_bitmap &rhs) { bitmap_union(&map, &rhs.map); }
my_bool is_set(uint n) const { return bitmap_is_set(&map, n); }
my_bool is_prefix(uint n) const { return bitmap_is_prefix(&map, n); }
my_bool is_clear_all() const { return bitmap_is_clear_all(&map); }
my_bool is_set_all() const { return bitmap_is_set_all(&map); }
- my_bool is_subset(const Bitmap& map2) const { return bitmap_is_subset(&map, &map2.map); }
- my_bool operator==(const Bitmap& map2) const { return bitmap_cmp(&map, &map2.map); }
+ my_bool is_subset(const Bitmap_my_bitmap &rhs) const
+ {
+ return bitmap_is_subset(&map, &rhs.map);
+ }
+ my_bool operator==(const Bitmap_my_bitmap &rhs) const
+ {
+ return bitmap_cmp(&map, &rhs.map);
+ }
char *print(char *buf) const
{
char *s=buf;
@@ -92,47 +103,117 @@ public:
}
};
-template <> class Bitmap<64>
+
+/**
+ A bitmap that is based on a built-in integral type
+*/
+
+template <typename T> class Bitmap_integral
{
- ulonglong map;
+ T map;
+ inline static T exp2(uint n) { return static_cast<T>(1) << n; }
public:
- Bitmap<64>() { }
-#if defined(__NETWARE__) || defined(__MWERKS__)
- /*
- Metwork compiler gives error on Bitmap<64>
- Changed to Bitmap, since in this case also it will proper construct
- this class
- */
- explicit Bitmap(uint prefix_to_set) { set_prefix(prefix_to_set); }
-#else
- explicit Bitmap<64>(uint prefix_to_set) { set_prefix(prefix_to_set); }
-#endif
- void init() { }
+ Bitmap_integral() { clear_all(); }
+ explicit Bitmap_integral(uint prefix_to_set) { set_prefix(prefix_to_set); }
+ void init() { clear_all(); }
void init(uint prefix_to_set) { set_prefix(prefix_to_set); }
- uint length() const { return 64; }
- void set_bit(uint n) { map|= ((ulonglong)1) << n; }
- void clear_bit(uint n) { map&= ~(((ulonglong)1) << n); }
- void set_prefix(uint n)
+ void set_bit(uint bit_no) { map|= exp2(bit_no); }
+ void clear_bit(uint bit_no) { map&= ~exp2(bit_no); }
+ void set_prefix(uint bit_count)
{
- if (n >= length())
+ if (bit_count >= sizeof(T)*8)
set_all();
else
- map= (((ulonglong)1) << n)-1;
+ map= exp2(bit_count) - 1;
}
- void set_all() { map=~(ulonglong)0; }
- void clear_all() { map=(ulonglong)0; }
- void intersect(Bitmap<64>& map2) { map&= map2.map; }
- void intersect(ulonglong map2) { map&= map2; }
- void intersect_extended(ulonglong map2) { map&= map2; }
- void subtract(Bitmap<64>& map2) { map&= ~map2.map; }
- void merge(Bitmap<64>& map2) { map|= map2.map; }
- my_bool is_set(uint n) const { return test(map & (((ulonglong)1) << n)); }
- my_bool is_prefix(uint n) const { return map == (((ulonglong)1) << n)-1; }
- my_bool is_clear_all() const { return map == (ulonglong)0; }
- my_bool is_set_all() const { return map == ~(ulonglong)0; }
- my_bool is_subset(const Bitmap<64>& map2) const { return !(map & ~map2.map); }
- my_bool operator==(const Bitmap<64>& map2) const { return map == map2.map; }
- char *print(char *buf) const { longlong2str(map,buf,16); return buf; }
- ulonglong to_ulonglong() const { return map; }
+ void set_all() { map= ~static_cast<T>(0); }
+ void clear_all() { map= static_cast<T>(0); }
+ void intersect(const Bitmap_integral<T> &rhs) { map&= rhs.map; }
+ void intersect(T rhs) { map&= rhs; }
+ void intersect_extended(ulonglong rhs) { map&= static_cast<T>(rhs); }
+ void subtract(const Bitmap_integral<T> &rhs) { map&= ~rhs.map; }
+ void merge(const Bitmap_integral<T> &rhs) { map|= rhs.map; }
+ my_bool is_set(uint bit_no) const { return test(map & exp2(bit_no)); }
+ my_bool is_prefix(uint bit_count) const { return map == exp2(bit_count) - 1; }
+ my_bool is_clear_all() const { return map == static_cast<T>(0); }
+ my_bool is_set_all() const { return map == ~static_cast<T>(0); }
+ my_bool is_subset(const Bitmap_integral<T> &rhs) const
+ {
+ return !(map & ~rhs.map);
+ }
+ my_bool operator==(const Bitmap_integral<T> &rhs) const
+ {
+ return map == rhs.map;
+ }
+ char *print(char *buf) const
+ {
+ longlong2str(static_cast<longlong>(map), buf, 16);
+ return buf;
+ }
+ ulonglong to_ulonglong() const { return static_cast<ulonglong>(map); }
+};
+
+
+/**
+ Select the right bitmap implementation for a given number of bits:
+ 0 - error, 1-8 - uint8, 9-16 - uint16, 17-32 - uint32, 33-64 - uint64,
+ 65+ - based on an array of unsigned chars
+*/
+
+
+/** Default to 65+ case. */
+
+template <uint B> struct Bitmap_select_type
+{ typedef Bitmap_my_bitmap<B> Implementation; };
+
+/** To produce a compile-time error in case of a programming typo. */
+
+template <> struct Bitmap_select_type<0> {};
+
+template <> struct Bitmap_select_type<1>
+{ typedef Bitmap_integral<uint8> Implementation; };
+
+template <> struct Bitmap_select_type<2>
+{ typedef Bitmap_integral<uint16> Implementation; };
+
+template <> struct Bitmap_select_type<4>
+{ typedef Bitmap_integral<uint32> Implementation; };
+
+template <> struct Bitmap_select_type<8>
+{ typedef Bitmap_integral<ulonglong> Implementation; };
+
+/**
+ The purpose of this template is to evaluate at compile time
+ the number of bytes necessary to store the given number of bits
+ and then select the best implementation for the given number of bytes.
+*/
+
+template <uint B> struct Bitmap_select
+{
+ /** Calculate the number of bytes */
+ enum { W= (B+7)/8 };
+ /** Select an aligned width for the given number of bytes */
+ enum { A= W > 8 ? W :
+ (W > 4 ? 8 :
+ (W > 2 ? 4 :
+ (W > 1 ? 2 :
+ (W > 0 ? 1 : 0))))
+ };
+
+ /* Select the right implementation for the aligned number of bytes */
+ typedef typename Bitmap_select_type<A>::Implementation Implementation;
+};
+
+
+template <uint B>
+class Bitmap: public Bitmap_select<B>::Implementation
+{
+public:
+ typedef typename Bitmap_select<B>::Implementation Implementation;
+
+ Bitmap() {}
+ explicit Bitmap(uint prefix_to_set) :Implementation(prefix_to_set) {}
+ uint length() { return B; }
+ /* The remaining methods are inherited */
};
diff -Nrup a/sql/sql_class.h b/sql/sql_class.h
--- a/sql/sql_class.h 2007-07-02 02:01:02 +04:00
+++ b/sql/sql_class.h 2007-07-10 01:50:17 +04:00
@@ -32,7 +32,6 @@ class Lex_input_stream;
enum enum_enable_or_disable { LEAVE_AS_IS, ENABLE, DISABLE };
enum enum_ha_read_modes { RFIRST, RNEXT, RPREV, RLAST, RKEY, RNEXT_SAME };
-enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_UPDATE };
enum enum_log_type { LOG_CLOSED, LOG_TO_BE_OPENED, LOG_NORMAL, LOG_NEW, LOG_BIN};
enum enum_delay_key_write { DELAY_KEY_WRITE_NONE, DELAY_KEY_WRITE_ON,
DELAY_KEY_WRITE_ALL };
diff -Nrup a/sql/sql_lex.cc b/sql/sql_lex.cc
--- a/sql/sql_lex.cc 2007-07-06 16:22:08 +04:00
+++ b/sql/sql_lex.cc 2007-07-10 01:50:17 +04:00
@@ -2034,6 +2034,28 @@ void st_select_lex_unit::set_limit(SELEC
}
+/**
+ Update the semantical tree to reflect INSERT .. ON DUPLICATE KEY UPDATE
+ clause, LOAD DATA ... REPLACE and other syntaxes that change
+ semantics of INSERT or UPDATE operations.
+*/
+
+void st_lex::set_trg_event_type_for_tables()
+{
+ /*
+ Do not iterate over sub-selects, only the tables in the outermost
+ SELECT_LEX can be modified, if any.
+ */
+ TABLE_LIST *tables= select_lex.get_table_list();
+
+ while (tables)
+ {
+ tables->set_trg_event_type(this);
+ tables= tables->next_local;
+ }
+}
+
+
/*
Unlink the first table from the global table list and the first table from
outer select (lex->select_lex) local list
diff -Nrup a/sql/sql_lex.h b/sql/sql_lex.h
--- a/sql/sql_lex.h 2007-07-06 16:22:08 +04:00
+++ b/sql/sql_lex.h 2007-07-10 01:50:17 +04:00
@@ -1188,6 +1188,8 @@ typedef struct st_lex : public Query_tab
un->uncacheable|= cause;
}
}
+ void set_trg_event_type_for_tables();
+
TABLE_LIST *unlink_first_table(bool *link_to_local);
void link_first_table_back(TABLE_LIST *first, bool link_to_local);
void first_lists_tables_same();
diff -Nrup a/sql/sql_parse.cc b/sql/sql_parse.cc
--- a/sql/sql_parse.cc 2007-07-05 11:34:03 +04:00
+++ b/sql/sql_parse.cc 2007-07-10 01:50:17 +04:00
@@ -6080,8 +6080,9 @@ void mysql_parse(THD *thd, const char *i
(thd->query_length= (ulong)(lip.found_semicolon - thd->query)))
thd->query_length--;
/* Actually execute the query */
- mysql_execute_command(thd);
- query_cache_end_of_result(thd);
+ lex->set_trg_event_type_for_tables();
+ mysql_execute_command(thd);
+ query_cache_end_of_result(thd);
}
}
}
diff -Nrup a/sql/sql_prepare.cc b/sql/sql_prepare.cc
--- a/sql/sql_prepare.cc 2007-06-22 17:40:33 +04:00
+++ b/sql/sql_prepare.cc 2007-07-10 01:50:17 +04:00
@@ -2826,6 +2826,7 @@ bool Prepared_statement::prepare(const c
lex_start(thd);
lex->safe_to_cache_query= FALSE;
int err= MYSQLparse((void *)thd);
+ lex->set_trg_event_type_for_tables();
error= err || thd->is_fatal_error ||
thd->net.report_error || init_param_array(this);
diff -Nrup a/sql/sql_trigger.cc b/sql/sql_trigger.cc
--- a/sql/sql_trigger.cc 2007-06-19 01:54:33 +04:00
+++ b/sql/sql_trigger.cc 2007-07-10 01:50:17 +04:00
@@ -943,17 +943,6 @@ bool Table_triggers_list::check_n_load(T
table->triggers= triggers;
/*
- Construct key that will represent triggers for this table in the set
- of routines used by statement.
- */
- triggers->sroutines_key.length= 1+strlen(db)+1+strlen(table_name)+1;
- if (!(triggers->sroutines_key.str=
- alloc_root(&table->mem_root, triggers->sroutines_key.length)))
- DBUG_RETURN(1);
- triggers->sroutines_key.str[0]= TYPE_ENUM_TRIGGER;
- strxmov(triggers->sroutines_key.str+1, db, ".", table_name, NullS);
-
- /*
TODO: This could be avoided if there is no triggers
for UPDATE and DELETE.
*/
@@ -991,6 +980,15 @@ bool Table_triggers_list::check_n_load(T
DBUG_ASSERT(lex.sphead == 0);
goto err_with_lex_cleanup;
}
+ /*
+ Not strictly necessary to invoke this method here, since we know
+ that we've parsed CREATE TRIGGER and not an
+ UPDATE/DELETE/INSERT/REPLACE/LOAD/CREATE TABLE, but we try to
+ maintain the invariant that this method is called for each
+ distinct statement, in case its logic is extended with other
+ types of analyses in future.
+ */
+ lex.set_trg_event_type_for_tables();
lex.sphead->set_info(0, 0, &lex.sp_chistics, (ulong) *trg_sql_mode);
@@ -1550,6 +1548,12 @@ bool Table_triggers_list::process_trigge
new_field= record1_field;
old_field= trigger_table->field;
}
+ /*
+ This trigger must have been processed by the pre-locking
+ algorithm.
+ */
+ DBUG_ASSERT(trigger_table->pos_in_table_list->trg_event_map.is_set(
+ static_cast<uint>(event)));
thd->reset_sub_statement_state(&statement_state, SUB_STMT_TRIGGER);
err_status= sp_trigger->execute_trigger
@@ -1568,7 +1572,7 @@ bool Table_triggers_list::process_trigge
SYNOPSIS
mark_fields_used()
thd Current thread context
- event Type of event triggers for which we are going to inspect
+ event Type of event triggers for which we are going to ins
DESCRIPTION
This method marks fields of subject table which are read/set in its
diff -Nrup a/sql/sql_trigger.h b/sql/sql_trigger.h
--- a/sql/sql_trigger.h 2007-04-05 10:29:09 +04:00
+++ b/sql/sql_trigger.h 2007-07-10 01:50:17 +04:00
@@ -56,14 +56,6 @@ class Table_triggers_list: public Sql_al
updating trigger definitions during RENAME TABLE.
*/
List<LEX_STRING> on_table_names_list;
- /*
- Key representing triggers for this table in set of all stored
- routines used by statement.
- TODO: We won't need this member once triggers namespace will be
- database-wide instead of table-wide because then we will be able
- to use key based on sp_name as for other stored routines.
- */
- LEX_STRING sroutines_key;
/*
Grant information for each trigger (pair: subject table, trigger definer).
diff -Nrup a/sql/sql_view.cc b/sql/sql_view.cc
--- a/sql/sql_view.cc 2007-06-22 17:40:33 +04:00
+++ b/sql/sql_view.cc 2007-07-10 01:50:17 +04:00
@@ -1038,6 +1038,7 @@ bool mysql_make_view(THD *thd, File_pars
CHARSET_INFO *save_cs= thd->variables.character_set_client;
thd->variables.character_set_client= system_charset_info;
res= MYSQLparse((void *)thd);
+ lex->set_trg_event_type_for_tables();
if ((old_lex->sql_command == SQLCOM_SHOW_FIELDS) ||
(old_lex->sql_command == SQLCOM_SHOW_CREATE))
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy 2007-07-05 11:34:03 +04:00
+++ b/sql/sql_yacc.yy 2007-07-10 01:50:17 +04:00
@@ -9644,13 +9644,13 @@ trigger_tail:
MYSQL_YYABORT;
sp->reset_thd_mem_root(thd);
sp->init(lex);
+ sp->m_type= TYPE_ENUM_TRIGGER;
sp->init_sp_name(thd, $3);
lex->stmt_definition_begin= $2;
lex->ident.str= $7;
lex->ident.length= $10 - $7;
- sp->m_type= TYPE_ENUM_TRIGGER;
lex->sphead= sp;
lex->spname= $3;
/*
@@ -9728,9 +9728,9 @@ sp_tail:
sp= new sp_head();
sp->reset_thd_mem_root(YYTHD);
sp->init(lex);
+ sp->m_type= TYPE_ENUM_PROCEDURE;
sp->init_sp_name(YYTHD, $3);
- sp->m_type= TYPE_ENUM_PROCEDURE;
lex->sphead= sp;
/*
* We have to turn of CLIENT_MULTI_QUERIES while parsing a
diff -Nrup a/sql/table.cc b/sql/table.cc
--- a/sql/table.cc 2007-07-06 16:18:45 +04:00
+++ b/sql/table.cc 2007-07-10 01:50:17 +04:00
@@ -1776,6 +1776,128 @@ void st_table::reset_item_list(List<Item
}
}
+
+/**
+ Set the initial purpose of this TABLE_LIST object in the list of
+ used tables. We need to track this information on table-by-
+ table basis, since when this table becomes an element of the
+ pre-locked list, it's impossible to identify which SQL
+ sub-statement it has been originally used in.
+
+ E.g.:
+
+ User request: SELECT * FROM t1 WHERE f1();
+ FUNCTION f1(): DELETE FROM t2; RETURN 1;
+ BEFORE DELETE trigger on t2: INSERT INTO t3 VALUES (old.a);
+
+ For this user request, the pre-locked list will contain t1, t2, t3
+ table elements, each needed for different DML.
+
+ This method is called immediately after parsing for tables
+ of the table list of the top-level select lex.
+
+ The trigger event map is update to reflect INSERT, UPDATE, DELETE,
+ REPLACE, LOAD DATA, CREATE TABLE .. SELECT, CREATE TABLE ..
+ REPLACE SELECT and additionally ON DUPLICATE KEY UPDATE clause.
+*/
+
+void
+TABLE_LIST::set_trg_event_type(const st_lex *lex)
+{
+ enum trg_event_type trg_event;
+
+ /*
+ We keep track of this member because some auxiliary operations
+ (e.g. GRANT processing) create TABLE_LIST instances outside
+ the parser. Additionally, some commands (e.g. OPTIMIZE) change
+ the lock type for a table only after parsing is done. Luckily,
+ these do not fire triggers and do not need to pre-load them.
+
+ TODO: this usage pattern creates unnecessary module dependencies
+ and should be rewritten to go through the parser.
+ Table list instances created outside the parser in most cases
+ refer to mysql.* system tables. It is not allowed to have
+ a trigger on a system table, but keeping track of
+ initialization provides extra safety in case this limitation
+ is circumvented.
+ */
+ is_trg_event_map_initialized= TRUE;
+ /*
+ This is a fast check to filter out statements that do
+ not change data, or tables on the right side, in case of
+ INSERT .. SELECT, CREATE TABLE .. SELECT and so on.
+ Here we also filter out OPTIMIZE statement, for which
+ lock_type is TL_UNLOCK after parsing.
+ */
+ if (static_cast<int>(lock_type) < static_cast<int>(TL_WRITE_ALLOW_WRITE))
+ return;
+
+ switch (lex->sql_command) {
+ /*
+ Basic INSERT. If there is an additional ON DUPLIATE KEY UPDATE
+ clause, it will be handled later in this method.
+ */
+ case SQLCOM_INSERT: /* fall through */
+ case SQLCOM_INSERT_SELECT:
+ /*
+ LOAD DATA ... INFILE is expected to fire BEFORE/AFTER INSERT
+ triggers.
+ If the statement also has REPLACE clause, it will be
+ handled later in this method.
+ */
+ case SQLCOM_LOAD: /* fall through */
+ /*
+ REPLACE is semantically equivalent to INSERT. In case
+ of a primary or unique key conflict, it deletes the old
+ record and inserts a new one. So we also may need to
+ fire ON DELETE triggers. This functionality is handled
+ later in this method.
+ */
+ case SQLCOM_REPLACE: /* fall through */
+ case SQLCOM_REPLACE_SELECT:
+ /*
+ CREATE TABLE ... SELECT defaults to INSERT if the table already
+ exists.
+ In case of CREATE TABLE ... REPLACE SELECT,
+ REPLACE option is handled later in this method.
+ */
+ case SQLCOM_CREATE_TABLE:
+ trg_event= TRG_EVENT_INSERT;
+ break;
+ /* Basic update and multi-update */
+ case SQLCOM_UPDATE: /* fall through */
+ case SQLCOM_UPDATE_MULTI:
+ trg_event= TRG_EVENT_UPDATE;
+ break;
+ /* Basic delete and multi-delete */
+ case SQLCOM_DELETE: /* fall through */
+ case SQLCOM_DELETE_MULTI:
+ trg_event= TRG_EVENT_DELETE;
+ break;
+ default:
+ /*
+ OK to return, since value of 'duplicates' is irrelevant
+ for non-updating commands.
+ */
+ return;
+ }
+ trg_event_map.set_bit(static_cast<uint>(trg_event));
+
+ switch (lex->duplicates) {
+ case DUP_UPDATE:
+ trg_event= TRG_EVENT_UPDATE;
+ break;
+ case DUP_REPLACE:
+ trg_event= TRG_EVENT_DELETE;
+ break;
+ case DUP_ERROR:
+ default:
+ return;
+ }
+ trg_event_map.set_bit(static_cast<uint>(trg_event));
+}
+
+
/*
calculate md5 of query
diff -Nrup a/sql/table.h b/sql/table.h
--- a/sql/table.h 2007-07-06 16:18:45 +04:00
+++ b/sql/table.h 2007-07-10 01:50:17 +04:00
@@ -59,6 +59,16 @@ enum tmp_table_type {NO_TMP_TABLE=0,
NON_TRANSACTIONAL_TMP_TABLE=1, TRANSACTIONAL_TMP_TABLE=2,
SYSTEM_TMP_TABLE=3};
+enum enum_duplicates { DUP_ERROR, DUP_REPLACE, DUP_UPDATE };
+/*
+ Event on which trigger is invoked.
+*/
+enum trg_event_type
+{
+ TRG_EVENT_INSERT= 0 , TRG_EVENT_UPDATE= 1, TRG_EVENT_DELETE= 2, TRG_EVENT_MAX
+};
+
+
enum frm_type_enum
{
FRMTYPE_ERROR= 0,
@@ -701,6 +711,18 @@ struct TABLE_LIST
... SELECT implementation).
*/
bool create;
+ /**
+ Indicates what triggers we need to pre-load for this TABLE_LIST
+ when opening an associated TABLE. This is filled after
+ the parsed tree is created.
+ */
+ Bitmap<TRG_EVENT_MAX> trg_event_map;
+ /*
+ To ensure an invariant on trg_event_map when in
+ debug builds.
+ */
+ bool is_trg_event_map_initialized;
+
enum enum_schema_table_state schema_table_state;
void calc_md5(char *buffer);
@@ -752,6 +774,7 @@ struct TABLE_LIST
void reinit_before_use(THD *thd);
Item_subselect *containing_subselect();
+ void set_trg_event_type(const st_lex *lex);
private:
bool prep_check_option(THD *thd, uint8 check_opt_type);
bool prep_where(THD *thd, Item **conds, bool no_where_clause);
| Thread |
|---|
| • bk commit into 5.0 tree (kostja:1.2517) BUG#26141 | konstantin | 9 Jul |
