MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:November 29 2007 11:42am
Subject:bk commit into 5.1 tree (davi:1.2671) BUG#23713
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of davi. When davi 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-11-29 09:42:26-02:00, davi@stripped +10 -0
  Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
  
  This bug is actually two bugs in one, one of which is CREATE TRIGGER under
  LOCK TABLES and the other is CREATE TRIGGER under LOCK TABLES simultaneous
  to a FLUSH TABLES WITH READ LOCK (global read lock). Both situations could
  lead to a server crash or deadlock.
  
  The first problem arises from the fact that when under LOCK TABLES, if the
  table is in the set of locked tables, the table is already open and it doesn't
  need to be reopened (not a placeholder). Also in this case, if the table is
  not write locked, a exclusive lock can't be acquired because of a possible
  deadlock with another thread also holding a (read) lock on the table. The
  second issue arises from the fact that one should never wait for a global
  read lock if it's holding any locked tables, because the global read lock
  is waiting for these tables and this leads to a circular wait deadlock.
  
  The solution for the first case is to check if the table is write locked
  and upgraded the write lock to a exclusive lock and fail otherwise for non
  write locked tables. Grabbin the exclusive lock in this case also means
  to ensure that the table is opened only by the calling thread. The second
  issue is partly fixed by not waiting for the global read lock if the thread
  is holding any locked tables.
  
  The second issue is only partly addressed in this patch because it turned
  out to be much wider and also affects other DDL statements. Reported as
  Bug#32395

  mysql-test/r/trigger.result@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +35 -0
    Add test case result for Bug#23713

  mysql-test/r/trigger_notembedded.result@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +1 -0
    Add test case result for Bug#23713

  mysql-test/r/trigger_notembedded.result@stripped, 2007-11-29 09:37:30-02:00, davi@stripped +0 -0
    Rename: mysql-test/r/trigger-grant.result -> mysql-test/r/trigger_notembedded.result

  mysql-test/t/trigger.test@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +47 -0
    Add test case for Bug#23713

  mysql-test/t/trigger_notembedded.test@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +34 -0
    Add test case for Bug#23713

  mysql-test/t/trigger_notembedded.test@stripped, 2007-11-29 09:37:41-02:00, davi@stripped +0 -0
    Rename: mysql-test/t/trigger-grant.test -> mysql-test/t/trigger_notembedded.test

  sql/mysql_priv.h@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +4 -2
    Locally export wait_while_table_is_used and name_lock_locked_table
    and add flag to mysql_ha_rm_tables to signal that LOCK_open is locked.

  sql/sql_base.cc@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +40 -2
    Introduce name_lock_locked_table function and match
    close_old_data_files function declaration and definition.

  sql/sql_handler.cc@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +3 -4
    Add flag to mysql_ha_rm_tables to signal that LOCK_open is locked.

  sql/sql_rename.cc@stripped, 2007-11-29 09:42:23-02:00, davi@stripped +1 -1
    Fix mysql_ha_rm_tables caller.

  sql/sql_table.cc@stripped, 2007-11-29 09:42:24-02:00, davi@stripped +7 -5
    Export wait_while_table_is_used and assert that LOCK_open is locked
    and fix mysql_ha_rm_tables caller.

  sql/sql_trigger.cc@stripped, 2007-11-29 09:42:24-02:00, davi@stripped +35 -33
    Upgrade write locked tables to a exclusive lock and fail if
    the table is not write locked. Also, don't wait for the global
    read lock if under LOCK TABLES.

diff -Nrup a/mysql-test/r/trigger-grant.result b/mysql-test/r/trigger-grant.result
--- a/mysql-test/r/trigger-grant.result	2007-06-28 14:34:48 -03:00
+++ /dev/null	Wed Dec 31 16:00:00 196900
@@ -1,450 +0,0 @@
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-DROP DATABASE IF EXISTS mysqltest_db1;
-CREATE DATABASE mysqltest_db1;
-CREATE USER mysqltest_dfn@localhost;
-CREATE USER mysqltest_inv@localhost;
-GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-CREATE TABLE t1(num_value INT);
-CREATE TABLE t2(user_str TEXT);
-
----> connection: default
-GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
-
----> connection: default
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-FOR EACH ROW
-INSERT INTO t2 VALUES(CURRENT_USER());
-ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
-
----> connection: default
-GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-FOR EACH ROW
-INSERT INTO t2 VALUES(CURRENT_USER());
-
----> connection: default
-REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-DROP TRIGGER trg1;
-ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
-
----> connection: wl2818_definer_con
-INSERT INTO t1 VALUES(0);
-ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
-
----> connection: default
-GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-INSERT INTO t1 VALUES(0);
-DROP TRIGGER trg1;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t2;
-
----> connection: default
-REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-FOR EACH ROW
-INSERT INTO t2 VALUES(CURRENT_USER());
-
----> connection: default
-GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
-GRANT ALL PRIVILEGES ON mysqltest_db1.t1
-TO 'mysqltest_inv'@localhost;
-GRANT SELECT ON mysqltest_db1.t2
-TO 'mysqltest_inv'@localhost;
-
----> connection: wl2818_definer_con
-use mysqltest_db1;
-INSERT INTO t1 VALUES(1);
-SELECT * FROM t1;
-num_value
-1
-SELECT * FROM t2;
-user_str
-mysqltest_dfn@localhost
-
----> connection: wl2818_invoker_con
-use mysqltest_db1;
-INSERT INTO t1 VALUES(2);
-SELECT * FROM t1;
-num_value
-1
-2
-SELECT * FROM t2;
-user_str
-mysqltest_dfn@localhost
-mysqltest_dfn@localhost
-
----> connection: default
-use mysqltest_db1;
-REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
-
----> connection: wl2818_invoker_con
-use mysqltest_db1;
-INSERT INTO t1 VALUES(3);
-ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2'
-SELECT * FROM t1;
-num_value
-1
-2
-3
-SELECT * FROM t2;
-user_str
-mysqltest_dfn@localhost
-mysqltest_dfn@localhost
-
----> connection: wl2818_definer_con
-use mysqltest_db1;
-DROP TRIGGER trg1;
-CREATE DEFINER='mysqltest_inv'@'localhost'
-  TRIGGER trg1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @new_sum = 0;
-ERROR 42000: Access denied; you need the SUPER privilege for this operation
-
----> connection: default
-use mysqltest_db1;
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
-
----> connection: wl2818_definer_con
-CREATE DEFINER='mysqltest_inv'@'localhost'
-  TRIGGER trg1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @new_sum = 0;
-CREATE DEFINER='mysqltest_nonexs'@'localhost'
-  TRIGGER trg2 AFTER INSERT ON t1
-FOR EACH ROW
-SET @new_sum = 0;
-Warnings:
-Note	1449	There is no 'mysqltest_nonexs'@'localhost' registered
-INSERT INTO t1 VALUES(6);
-ERROR HY000: There is no 'mysqltest_nonexs'@'localhost' registered
-SHOW TRIGGERS;
-Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
-trg1	INSERT	t1	SET @new_sum = 0	BEFORE	NULL		mysqltest_inv@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
-trg2	INSERT	t1	SET @new_sum = 0	AFTER	NULL		mysqltest_nonexs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
-DROP TRIGGER trg1;
-DROP TRIGGER trg2;
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @a = 1;
-CREATE TRIGGER trg2 AFTER INSERT ON t1
-FOR EACH ROW
-SET @a = 2;
-CREATE TRIGGER trg3 BEFORE UPDATE ON t1
-FOR EACH ROW
-SET @a = 3;
-CREATE TRIGGER trg4 AFTER UPDATE ON t1
-FOR EACH ROW
-SET @a = 4;
-CREATE TRIGGER trg5 BEFORE DELETE ON t1
-FOR EACH ROW
-SET @a = 5;
-
-SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
-trigger_name	definer
-trg1	
-trg2	@
-trg3	@abc@def@@
-trg4	@hostname
-trg5	@abcdef@@@hostname
-Warnings:
-Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
-
-SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
-TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
-NULL	mysqltest_db1	trg1	INSERT	NULL	mysqltest_db1	t1	0	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL			latin1	latin1_swedish_ci	latin1_swedish_ci
-NULL	mysqltest_db1	trg2	INSERT	NULL	mysqltest_db1	t1	0	NULL	SET @a = 2	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@	latin1	latin1_swedish_ci	latin1_swedish_ci
-NULL	mysqltest_db1	trg3	UPDATE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 3	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abc@def@@	latin1	latin1_swedish_ci	latin1_swedish_ci
-NULL	mysqltest_db1	trg4	UPDATE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 4	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
-NULL	mysqltest_db1	trg5	DELETE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 5	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abcdef@@@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
-
----> connection: default
-DROP USER mysqltest_dfn@localhost;
-DROP USER mysqltest_inv@localhost;
-DROP DATABASE mysqltest_db1;
-Warnings:
-Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-DROP DATABASE IF EXISTS mysqltest_db1;
-CREATE DATABASE mysqltest_db1;
-use mysqltest_db1;
-CREATE TABLE t1(col CHAR(20));
-CREATE TABLE t2(col CHAR(20));
-CREATE TABLE t3(col CHAR(20));
-CREATE TABLE t4(col CHAR(20));
-CREATE USER mysqltest_u1@localhost;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
-GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
-SET @mysqltest_var = NULL;
-
----> connection: default
-use mysqltest_db1;
-GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
-SHOW GRANTS FOR mysqltest_u1@localhost;
-Grants for mysqltest_u1@localhost
-GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
-GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
-
----> connection: bug15166_u1_con
-use mysqltest_db1;
-CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
-FOR EACH ROW
-SET @mysqltest_var = 'Hello, world!';
-
----> connection: default
-use mysqltest_db1;
-GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
----> connection: bug15166_u1_con
-use mysqltest_db1;
-CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
-FOR EACH ROW
-SET @mysqltest_var = NEW.col;
-DROP TRIGGER t1_trg_err_1;
-CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
-FOR EACH ROW
-SET @mysqltest_var = OLD.col;
-DROP TRIGGER t1_trg_err_2;
-CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
-FOR EACH ROW
-SET NEW.col = 't2_trg_before_insert';
-CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
-FOR EACH ROW
-SET @mysqltest_var = NEW.col;
-DROP TRIGGER t3_trg_err_1;
-CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
-FOR EACH ROW
-SET @mysqltest_var = OLD.col;
-DROP TRIGGER t3_trg_err_2;
-CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
-FOR EACH ROW
-SET NEW.col = 't4_trg_before_insert';
-
----> connection: default
-use mysqltest_db1;
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
----> connection: bug15166_u1_con
-use mysqltest_db1;
-CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
-FOR EACH ROW
-SET @mysqltest_var = NEW.col;
-CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
-FOR EACH ROW
-SET @mysqltest_var = OLD.col;
-CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
-FOR EACH ROW
-SET NEW.col = 't2_trg_err_1';
-DROP TRIGGER t2_trg_err_1;
-CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
-FOR EACH ROW
-SET NEW.col = CONCAT(OLD.col, '(updated)');
-DROP TRIGGER t2_trg_err_2;
-CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
-FOR EACH ROW
-SET @mysqltest_var = NEW.col;
-CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
-FOR EACH ROW
-SET @mysqltest_var = OLD.col;
-CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
-FOR EACH ROW
-SET NEW.col = 't4_trg_err_1';
-DROP TRIGGER t4_trg_err_1;
-CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
-FOR EACH ROW
-SET NEW.col = CONCAT(OLD.col, '(updated)');
-DROP TRIGGER t4_trg_err_2;
-
----> connection: default
-use mysqltest_db1;
-REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-INSERT INTO t1 VALUES('line1');
-ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1'
-SELECT * FROM t1;
-col
-line1
-SELECT @mysqltest_var;
-@mysqltest_var
-NULL
-INSERT INTO t2 VALUES('line2');
-SELECT * FROM t2;
-col
-t2_trg_before_insert
-INSERT INTO t3 VALUES('t3_line1');
-ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3'
-SELECT * FROM t3;
-col
-t3_line1
-SELECT @mysqltest_var;
-@mysqltest_var
-NULL
-INSERT INTO t4 VALUES('t4_line2');
-SELECT * FROM t4;
-col
-t4_trg_before_insert
-
----> connection: default
-use mysqltest_db1;
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-INSERT INTO t1 VALUES('line3');
-SELECT * FROM t1;
-col
-line1
-line3
-SELECT @mysqltest_var;
-@mysqltest_var
-line3
-INSERT INTO t2 VALUES('line4');
-ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2'
-SELECT * FROM t2;
-col
-t2_trg_before_insert
-INSERT INTO t3 VALUES('t3_line2');
-SELECT * FROM t3;
-col
-t3_line1
-t3_line2
-SELECT @mysqltest_var;
-@mysqltest_var
-t3_line2
-INSERT INTO t4 VALUES('t4_line2');
-ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4'
-SELECT * FROM t4;
-col
-t4_trg_before_insert
-DELETE FROM t1;
-SELECT @mysqltest_var;
-@mysqltest_var
-Hello, world!
-DROP USER mysqltest_u1@localhost;
-DROP DATABASE mysqltest_db1;
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-DROP DATABASE IF EXISTS mysqltest_db1;
-CREATE DATABASE mysqltest_db1;
-USE mysqltest_db1;
-CREATE TABLE t1 (i1 INT);
-CREATE TABLE t2 (i1 INT);
-CREATE USER mysqltest_dfn@localhost;
-CREATE USER mysqltest_inv@localhost;
-GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
-GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
-CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
-CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-CALL p2(NEW.i1);
-INSERT INTO t1 VALUES (7);
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
-INSERT INTO t2 VALUES (11);
-ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-CALL p2(NEW.i1);
-INSERT INTO t1 VALUES (13);
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
-INSERT INTO t2 VALUES (17);
-ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
-REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-CALL p2(NEW.i1);
-INSERT INTO t1 VALUES (19);
-INSERT INTO t2 VALUES (23);
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
-REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-CALL p2(NEW.i1);
-INSERT INTO t1 VALUES (29);
-INSERT INTO t2 VALUES (31);
-REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-DROP PROCEDURE p2;
-DROP PROCEDURE p1;
-GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-CALL p1(NEW.i1);
-INSERT INTO t1 VALUES (41);
-DROP PROCEDURE p1;
-CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
-INSERT INTO t1 VALUES (47);
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
-DROP PROCEDURE p1;
-CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
-INSERT INTO t1 VALUES (53);
-ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
-DROP PROCEDURE p1;
-REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-DROP TRIGGER t1_bi;
-DROP USER mysqltest_inv@localhost;
-DROP USER mysqltest_dfn@localhost;
-DROP TABLE t2;
-DROP TABLE t1;
-DROP DATABASE mysqltest_db1;
-USE test;
-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-11-16 15:05:28 -02:00
+++ b/mysql-test/r/trigger.result	2007-11-29 09:42:23 -02:00
@@ -1981,4 +1981,39 @@ drop table table_25411_b;
 DROP TRIGGER IF EXISTS trg;
 SHOW CREATE TRIGGER trg;
 ERROR HY000: Trigger does not exist
+drop table if exists t1;
+create table t1 (i int, j int);
+create trigger t1_bi before insert on t1 for each row begin end;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR 42000: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
+drop trigger t1_bi;
+drop trigger t1_bi;
+ERROR HY000: Trigger does not exist
+lock tables t1 read;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+drop trigger t1_bi;
+ERROR HY000: Trigger does not exist
+unlock tables;
+create trigger t1_bi before insert on t1 for each row begin end;
+lock tables t1 read;
+create trigger t1_bi before insert on t1 for each row begin end;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+drop trigger t1_bi;
+ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
+unlock tables;
+drop trigger t1_bi;
+lock tables t1 write;
+create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10;
+insert into t1 values (10, 10);
+drop trigger b1_bi;
+insert into t1 values (10, 10);
+select * from t1;
+i	j
+20	10
+10	10
+unlock tables;
+drop table t1;
 End of 5.1 tests.
diff -Nrup a/mysql-test/r/trigger_notembedded.result b/mysql-test/r/trigger_notembedded.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/trigger_notembedded.result	2007-11-29 09:42:23 -02:00
@@ -0,0 +1,451 @@
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TABLE t1(num_value INT);
+CREATE TABLE t2(user_str TEXT);
+
+---> connection: default
+GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+
+---> connection: default
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CURRENT_USER());
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: default
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CURRENT_USER());
+
+---> connection: default
+REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+DROP TRIGGER trg1;
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: wl2818_definer_con
+INSERT INTO t1 VALUES(0);
+ERROR 42000: TRIGGER command denied to user 'mysqltest_dfn'@'localhost' for table 't1'
+
+---> connection: default
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+INSERT INTO t1 VALUES(0);
+DROP TRIGGER trg1;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+
+---> connection: default
+REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+FOR EACH ROW
+INSERT INTO t2 VALUES(CURRENT_USER());
+
+---> connection: default
+GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+GRANT ALL PRIVILEGES ON mysqltest_db1.t1
+TO 'mysqltest_inv'@localhost;
+GRANT SELECT ON mysqltest_db1.t2
+TO 'mysqltest_inv'@localhost;
+
+---> connection: wl2818_definer_con
+use mysqltest_db1;
+INSERT INTO t1 VALUES(1);
+SELECT * FROM t1;
+num_value
+1
+SELECT * FROM t2;
+user_str
+mysqltest_dfn@localhost
+
+---> connection: wl2818_invoker_con
+use mysqltest_db1;
+INSERT INTO t1 VALUES(2);
+SELECT * FROM t1;
+num_value
+1
+2
+SELECT * FROM t2;
+user_str
+mysqltest_dfn@localhost
+mysqltest_dfn@localhost
+
+---> connection: default
+use mysqltest_db1;
+REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
+
+---> connection: wl2818_invoker_con
+use mysqltest_db1;
+INSERT INTO t1 VALUES(3);
+ERROR 42000: INSERT command denied to user 'mysqltest_dfn'@'localhost' for table 't2'
+SELECT * FROM t1;
+num_value
+1
+2
+3
+SELECT * FROM t2;
+user_str
+mysqltest_dfn@localhost
+mysqltest_dfn@localhost
+
+---> connection: wl2818_definer_con
+use mysqltest_db1;
+DROP TRIGGER trg1;
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @new_sum = 0;
+ERROR 42000: Access denied; you need the SUPER privilege for this operation
+
+---> connection: default
+use mysqltest_db1;
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+---> connection: wl2818_definer_con
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @new_sum = 0;
+CREATE DEFINER='mysqltest_nonexs'@'localhost'
+  TRIGGER trg2 AFTER INSERT ON t1
+FOR EACH ROW
+SET @new_sum = 0;
+Warnings:
+Note	1449	There is no 'mysqltest_nonexs'@'localhost' registered
+INSERT INTO t1 VALUES(6);
+ERROR HY000: There is no 'mysqltest_nonexs'@'localhost' registered
+SHOW TRIGGERS;
+Trigger	Event	Table	Statement	Timing	Created	sql_mode	Definer	character_set_client	collation_connection	Database Collation
+trg1	INSERT	t1	SET @new_sum = 0	BEFORE	NULL		mysqltest_inv@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+trg2	INSERT	t1	SET @new_sum = 0	AFTER	NULL		mysqltest_nonexs@localhost	latin1	latin1_swedish_ci	latin1_swedish_ci
+DROP TRIGGER trg1;
+DROP TRIGGER trg2;
+CREATE TRIGGER trg1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @a = 1;
+CREATE TRIGGER trg2 AFTER INSERT ON t1
+FOR EACH ROW
+SET @a = 2;
+CREATE TRIGGER trg3 BEFORE UPDATE ON t1
+FOR EACH ROW
+SET @a = 3;
+CREATE TRIGGER trg4 AFTER UPDATE ON t1
+FOR EACH ROW
+SET @a = 4;
+CREATE TRIGGER trg5 BEFORE DELETE ON t1
+FOR EACH ROW
+SET @a = 5;
+
+SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+trigger_name	definer
+trg1	
+trg2	@
+trg3	@abc@def@@
+trg4	@hostname
+trg5	@abcdef@@@hostname
+Warnings:
+Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
+
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+TRIGGER_CATALOG	TRIGGER_SCHEMA	TRIGGER_NAME	EVENT_MANIPULATION	EVENT_OBJECT_CATALOG	EVENT_OBJECT_SCHEMA	EVENT_OBJECT_TABLE	ACTION_ORDER	ACTION_CONDITION	ACTION_STATEMENT	ACTION_ORIENTATION	ACTION_TIMING	ACTION_REFERENCE_OLD_TABLE	ACTION_REFERENCE_NEW_TABLE	ACTION_REFERENCE_OLD_ROW	ACTION_REFERENCE_NEW_ROW	CREATED	SQL_MODE	DEFINER	CHARACTER_SET_CLIENT	COLLATION_CONNECTION	DATABASE_COLLATION
+NULL	mysqltest_db1	trg1	INSERT	NULL	mysqltest_db1	t1	0	NULL	SET @a = 1	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL			latin1	latin1_swedish_ci	latin1_swedish_ci
+NULL	mysqltest_db1	trg2	INSERT	NULL	mysqltest_db1	t1	0	NULL	SET @a = 2	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@	latin1	latin1_swedish_ci	latin1_swedish_ci
+NULL	mysqltest_db1	trg3	UPDATE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 3	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abc@def@@	latin1	latin1_swedish_ci	latin1_swedish_ci
+NULL	mysqltest_db1	trg4	UPDATE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 4	ROW	AFTER	NULL	NULL	OLD	NEW	NULL		@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
+NULL	mysqltest_db1	trg5	DELETE	NULL	mysqltest_db1	t1	0	NULL	SET @a = 5	ROW	BEFORE	NULL	NULL	OLD	NEW	NULL		@abcdef@@@hostname	latin1	latin1_swedish_ci	latin1_swedish_ci
+
+---> connection: default
+DROP USER mysqltest_dfn@localhost;
+DROP USER mysqltest_inv@localhost;
+DROP DATABASE mysqltest_db1;
+Warnings:
+Warning	1454	No definer attribute for trigger 'mysqltest_db1'.'trg1'. The trigger will be activated under the authorization of the invoker, which may have insufficient privileges. Please recreate the trigger.
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+use mysqltest_db1;
+CREATE TABLE t1(col CHAR(20));
+CREATE TABLE t2(col CHAR(20));
+CREATE TABLE t3(col CHAR(20));
+CREATE TABLE t4(col CHAR(20));
+CREATE USER mysqltest_u1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SET @mysqltest_var = NULL;
+
+---> connection: default
+use mysqltest_db1;
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+Grants for mysqltest_u1@localhost
+GRANT USAGE ON *.* TO 'mysqltest_u1'@'localhost'
+GRANT DELETE, TRIGGER ON `mysqltest_db1`.* TO 'mysqltest_u1'@'localhost'
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
+FOR EACH ROW
+SET @mysqltest_var = 'Hello, world!';
+
+---> connection: default
+use mysqltest_db1;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+DROP TRIGGER t1_trg_err_1;
+CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+DROP TRIGGER t1_trg_err_2;
+CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
+FOR EACH ROW
+SET NEW.col = 't2_trg_before_insert';
+CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+DROP TRIGGER t3_trg_err_1;
+CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+DROP TRIGGER t3_trg_err_2;
+CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
+FOR EACH ROW
+SET NEW.col = 't4_trg_before_insert';
+
+---> connection: default
+use mysqltest_db1;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+---> connection: bug15166_u1_con
+use mysqltest_db1;
+CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
+FOR EACH ROW
+SET NEW.col = 't2_trg_err_1';
+DROP TRIGGER t2_trg_err_1;
+CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
+FOR EACH ROW
+SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t2_trg_err_2;
+CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
+FOR EACH ROW
+SET @mysqltest_var = NEW.col;
+CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
+FOR EACH ROW
+SET @mysqltest_var = OLD.col;
+CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
+FOR EACH ROW
+SET NEW.col = 't4_trg_err_1';
+DROP TRIGGER t4_trg_err_1;
+CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
+FOR EACH ROW
+SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t4_trg_err_2;
+
+---> connection: default
+use mysqltest_db1;
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+INSERT INTO t1 VALUES('line1');
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't1'
+SELECT * FROM t1;
+col
+line1
+SELECT @mysqltest_var;
+@mysqltest_var
+NULL
+INSERT INTO t2 VALUES('line2');
+SELECT * FROM t2;
+col
+t2_trg_before_insert
+INSERT INTO t3 VALUES('t3_line1');
+ERROR 42000: SELECT command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't3'
+SELECT * FROM t3;
+col
+t3_line1
+SELECT @mysqltest_var;
+@mysqltest_var
+NULL
+INSERT INTO t4 VALUES('t4_line2');
+SELECT * FROM t4;
+col
+t4_trg_before_insert
+
+---> connection: default
+use mysqltest_db1;
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+INSERT INTO t1 VALUES('line3');
+SELECT * FROM t1;
+col
+line1
+line3
+SELECT @mysqltest_var;
+@mysqltest_var
+line3
+INSERT INTO t2 VALUES('line4');
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't2'
+SELECT * FROM t2;
+col
+t2_trg_before_insert
+INSERT INTO t3 VALUES('t3_line2');
+SELECT * FROM t3;
+col
+t3_line1
+t3_line2
+SELECT @mysqltest_var;
+@mysqltest_var
+t3_line2
+INSERT INTO t4 VALUES('t4_line2');
+ERROR 42000: UPDATE command denied to user 'mysqltest_u1'@'localhost' for column 'col' in table 't4'
+SELECT * FROM t4;
+col
+t4_trg_before_insert
+DELETE FROM t1;
+SELECT @mysqltest_var;
+@mysqltest_var
+Hello, world!
+DROP USER mysqltest_u1@localhost;
+DROP DATABASE mysqltest_db1;
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+DROP DATABASE IF EXISTS mysqltest_db1;
+CREATE DATABASE mysqltest_db1;
+USE mysqltest_db1;
+CREATE TABLE t1 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (7);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
+INSERT INTO t2 VALUES (11);
+ERROR 42000: SELECT,UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (13);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
+INSERT INTO t2 VALUES (17);
+ERROR 42000: UPDATE command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (19);
+INSERT INTO t2 VALUES (23);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't2'
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+CALL p2(NEW.i1);
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+CALL p1(NEW.i1);
+INSERT INTO t1 VALUES (41);
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+INSERT INTO t1 VALUES (47);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+INSERT INTO t1 VALUES (53);
+ERROR 42000: SELECT command denied to user 'mysqltest_dfn'@'localhost' for column 'i1' in table 't1'
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+DROP TRIGGER t1_bi;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;
+End of 5.0 tests.
+End of 5.1 tests.
diff -Nrup a/mysql-test/t/trigger-grant.test b/mysql-test/t/trigger-grant.test
--- a/mysql-test/t/trigger-grant.test	2007-02-26 08:00:02 -02:00
+++ /dev/null	Wed Dec 31 16:00:00 196900
@@ -1,877 +0,0 @@
-# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not
-# supported in embedded server. So, this test should not be run on embedded
-# server.
-
--- source include/not_embedded.inc
-
-###########################################################################
-#
-# Tests for WL#2818:
-#   - Check that triggers are executed under the authorization of the definer.
-#   - Check DEFINER clause of CREATE TRIGGER statement;
-#     - Check that SUPER privilege required to create a trigger with different
-#       definer.
-#     - Check that if the user specified as DEFINER does not exist, a warning
-#       is emitted.
-#     - Check that the definer of a trigger does not exist, the trigger will
-#       not be activated.
-#   - Check that SHOW TRIGGERS statement provides "Definer" column.
-#   - Check that if trigger contains NEW/OLD variables, the definer must have
-#     SELECT privilege on the subject table (aka BUG#15166/BUG#15196).
-#
-#  Let's also check that user name part of definer can contain '@' symbol (to
-#  check that triggers are not affected by BUG#13310 "incorrect user parsing
-#  by SP").
-#
-###########################################################################
-
-#
-# Prepare environment.
-#
-
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest_db1;
---enable_warnings
-
-CREATE DATABASE mysqltest_db1;
-
-CREATE USER mysqltest_dfn@localhost;
-CREATE USER mysqltest_inv@localhost;
-
-GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-CREATE TABLE t1(num_value INT);
-CREATE TABLE t2(user_str TEXT);
-
---disconnect wl2818_definer_con
-
---connection default
---echo
---echo ---> connection: default
-
-GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
-
-#
-# Check that the user must have TRIGGER privilege to create a trigger.
-#
-
---connection default
---echo
---echo ---> connection: default
-
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
---error ER_TABLEACCESS_DENIED_ERROR
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-  FOR EACH ROW
-    INSERT INTO t2 VALUES(CURRENT_USER());
-
---disconnect wl2818_definer_con
-
-#
-# Check that the user must have TRIGGER privilege to drop a trigger.
-#
-
---connection default
---echo
---echo ---> connection: default
-
-GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-  FOR EACH ROW
-    INSERT INTO t2 VALUES(CURRENT_USER());
-
---disconnect wl2818_definer_con
-
---connection default
---echo
---echo ---> connection: default
-
-REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
---error ER_TABLEACCESS_DENIED_ERROR
-DROP TRIGGER trg1;
-
---disconnect wl2818_definer_con
-
-#
-# Check that the definer must have TRIGGER privilege to activate a trigger.
-#
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(0);
-
---disconnect wl2818_definer_con
-
---connection default
---echo
---echo ---> connection: default
-
-GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-INSERT INTO t1 VALUES(0);
-
-# Cleanup for further tests.
-DROP TRIGGER trg1;
-TRUNCATE TABLE t1;
-TRUNCATE TABLE t2;
-
---disconnect wl2818_definer_con
-
---connection default
---echo
---echo ---> connection: default
-
-REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
-
-#
-# Check that triggers are executed under the authorization of the definer:
-#   - create two tables under "definer";
-#   - grant all privileges on the test db to "definer";
-#   - grant all privileges on the first table to "invoker";
-#   - grant only select privilege on the second table to "invoker";
-#   - create a trigger, which inserts a row into the second table after
-#     inserting into the first table.
-#   - insert a row into the first table under "invoker". A row also should be
-#     inserted into the second table.
-#
-
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-CREATE TRIGGER trg1 AFTER INSERT ON t1
-  FOR EACH ROW
-    INSERT INTO t2 VALUES(CURRENT_USER());
-
---connection default
---echo
---echo ---> connection: default
-
-# Setup definer's privileges.
-
-GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
-GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
-
-# Setup invoker's privileges.
-
-GRANT ALL PRIVILEGES ON mysqltest_db1.t1
-  TO 'mysqltest_inv'@localhost;
-  
-GRANT SELECT ON mysqltest_db1.t2
-  TO 'mysqltest_inv'@localhost;
-
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-use mysqltest_db1;
-
-INSERT INTO t1 VALUES(1);
-
-SELECT * FROM t1;
-SELECT * FROM t2;
-
---connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1)
---connection wl2818_invoker_con
---echo
---echo ---> connection: wl2818_invoker_con
-
-use mysqltest_db1;
-
-INSERT INTO t1 VALUES(2);
-
-SELECT * FROM t1;
-SELECT * FROM t2;
-
-#
-# Check that if definer lost some privilege required to execute (activate) a
-# trigger, the trigger will not be activated:
-#  - create a trigger on insert into the first table, which will insert a row
-#    into the second table;
-#  - revoke INSERT privilege on the second table from the definer;
-#  - insert a row into the first table;
-#  - check that an error has been risen;
-#  - check that no row has been inserted into the second table;
-#
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
-
---connection wl2818_invoker_con
---echo
---echo ---> connection: wl2818_invoker_con
-
-use mysqltest_db1;
-
---error ER_TABLEACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES(3);
-
-SELECT * FROM t1;
-SELECT * FROM t2;
-
-#
-# Check DEFINER clause of CREATE TRIGGER statement.
-#
-#   - Check that SUPER privilege required to create a trigger with different
-#     definer:
-#     - try to create a trigger with DEFINER="definer@localhost" under
-#       "invoker";
-#     - analyze error code;
-#   - Check that if the user specified as DEFINER does not exist, a warning is
-#     emitted:
-#     - create a trigger with DEFINER="non_existent_user@localhost" from
-#       "definer";
-#     - check that a warning emitted;
-#   - Check that the definer of a trigger does not exist, the trigger will not
-#     be activated:
-#     - activate just created trigger;
-#     - check error code;
-#
-
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-use mysqltest_db1;
-
-DROP TRIGGER trg1;
-
-# Check that SUPER is required to specify different DEFINER.
-
---error ER_SPECIFIC_ACCESS_DENIED_ERROR
-CREATE DEFINER='mysqltest_inv'@'localhost'
-  TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = 0;
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
-
---disconnect wl2818_definer_con
---connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
---connection wl2818_definer_con
---echo
---echo ---> connection: wl2818_definer_con
-
-CREATE DEFINER='mysqltest_inv'@'localhost'
-  TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = 0;
-
-# Create with non-existent user.
-
-CREATE DEFINER='mysqltest_nonexs'@'localhost'
-  TRIGGER trg2 AFTER INSERT ON t1
-  FOR EACH ROW
-    SET @new_sum = 0;
-
-# Check that trg2 will not be activated.
-
---error ER_NO_SUCH_USER
-INSERT INTO t1 VALUES(6);
-
-#
-# Check that SHOW TRIGGERS statement provides "Definer" column.
-#
-
-SHOW TRIGGERS;
-
-#
-# Check that weird definer values do not break functionality. I.e. check the
-# following definer values:
-#   - '';
-#   - '@';
-#   - '@abc@def@@';
-#   - '@hostname';
-#   - '@abc@def@@@hostname';
-#
-
-DROP TRIGGER trg1;
-DROP TRIGGER trg2;
-
-CREATE TRIGGER trg1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @a = 1;
-
-CREATE TRIGGER trg2 AFTER INSERT ON t1
-  FOR EACH ROW
-    SET @a = 2;
-
-CREATE TRIGGER trg3 BEFORE UPDATE ON t1
-  FOR EACH ROW
-    SET @a = 3;
-
-CREATE TRIGGER trg4 AFTER UPDATE ON t1
-  FOR EACH ROW
-    SET @a = 4;
-
-CREATE TRIGGER trg5 BEFORE DELETE ON t1
-  FOR EACH ROW
-    SET @a = 5;
-
-# Replace definers with the "weird" definers
-perl;
-use strict;
-use warnings;
-my $fname= "$ENV{'MYSQLTEST_VARDIR'}/master-data/mysqltest_db1/t1.TRG";
-open(FILE, "<", $fname) or die;
-my @content= grep($_ !~ /^definers=/, <FILE>);
-close FILE;
-open(FILE, ">", $fname) or die;
-# Use binary file mode to avoid CR/LF's being added on windows
-binmode FILE;
-print FILE @content;
-print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n";
-close FILE;
-EOF
-
---echo
-
-SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
-
---echo
-
-SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
-
-#
-# Cleanup
-#
-
---connection default
---echo
---echo ---> connection: default
-
-DROP USER mysqltest_dfn@localhost;
-DROP USER mysqltest_inv@localhost;
-
-DROP DATABASE mysqltest_db1;
-
-
-###########################################################################
-#
-# BUG#15166: Wrong update [was: select/update] permissions required to execute
-# triggers.
-#
-# BUG#15196: Wrong select permission required to execute triggers.
-#
-###########################################################################
-
-#
-# Prepare environment.
-#
-
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest_db1;
---enable_warnings
-
-CREATE DATABASE mysqltest_db1;
-
-use mysqltest_db1;
-
-# Tables for tesing table-level privileges:
-CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
-CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger
-
-# Tables for tesing column-level privileges:
-CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger
-CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger
-
-CREATE USER mysqltest_u1@localhost;
-REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
-GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
-
-SET @mysqltest_var = NULL;
-
---connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1)
-
-# parsing (CREATE TRIGGER) time:
-#   - check that nor SELECT either UPDATE is required to execute triggger w/o
-#     NEW/OLD variables.
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
-SHOW GRANTS FOR mysqltest_u1@localhost;
-
---connection bug15166_u1_con
---echo
---echo ---> connection: bug15166_u1_con
-
-use mysqltest_db1;
-
-CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
-  FOR EACH ROW
-    SET @mysqltest_var = 'Hello, world!';
-
-# parsing (CREATE TRIGGER) time:
-#   - check that UPDATE is not enough to read the value;
-#   - check that UPDATE is required to modify the value;
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-
-GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
---connection bug15166_u1_con
---echo
---echo ---> connection: bug15166_u1_con
-
-use mysqltest_db1;
-
-# - table-level privileges
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
-  FOR EACH ROW
-    SET @mysqltest_var = NEW.col;
-DROP TRIGGER t1_trg_err_1;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
-  FOR EACH ROW
-    SET @mysqltest_var = OLD.col;
-DROP TRIGGER t1_trg_err_2;
-
-CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
-  FOR EACH ROW
-    SET NEW.col = 't2_trg_before_insert';
-
-# - column-level privileges
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
-  FOR EACH ROW
-    SET @mysqltest_var = NEW.col;
-DROP TRIGGER t3_trg_err_1;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
-  FOR EACH ROW
-    SET @mysqltest_var = OLD.col;
-DROP TRIGGER t3_trg_err_2;
-
-CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
-  FOR EACH ROW
-    SET NEW.col = 't4_trg_before_insert';
-
-# parsing (CREATE TRIGGER) time:
-#   - check that SELECT is required to read the value;
-#   - check that SELECT is not enough to modify the value;
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-
-REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
---connection bug15166_u1_con
---echo
---echo ---> connection: bug15166_u1_con
-
-use mysqltest_db1;
-
-# - table-level privileges
-
-CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
- FOR EACH ROW
-  SET @mysqltest_var = NEW.col;
-
-CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
- FOR EACH ROW
-  SET @mysqltest_var = OLD.col;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
- FOR EACH ROW
-  SET NEW.col = 't2_trg_err_1';
-DROP TRIGGER t2_trg_err_1;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
- FOR EACH ROW
-  SET NEW.col = CONCAT(OLD.col, '(updated)');
-DROP TRIGGER t2_trg_err_2;
-
-# - column-level privileges
-
-CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
-  FOR EACH ROW
-    SET @mysqltest_var = NEW.col;
-
-CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
-  FOR EACH ROW
-    SET @mysqltest_var = OLD.col;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
- FOR EACH ROW
-  SET NEW.col = 't4_trg_err_1';
-DROP TRIGGER t4_trg_err_1;
-
-# TODO: check privileges at CREATE TRIGGER time.
-# --error ER_COLUMNACCESS_DENIED_ERROR
-CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
- FOR EACH ROW
-  SET NEW.col = CONCAT(OLD.col, '(updated)');
-DROP TRIGGER t4_trg_err_2;
-
-# execution time:
-#   - check that UPDATE is not enough to read the value;
-#   - check that UPDATE is required to modify the value;
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-
-REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
-# - table-level privileges
-
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES('line1');
-
-SELECT * FROM t1;
-SELECT @mysqltest_var;
-
-INSERT INTO t2 VALUES('line2');
-
-SELECT * FROM t2;
-
-# - column-level privileges
-
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t3 VALUES('t3_line1');
-
-SELECT * FROM t3;
-SELECT @mysqltest_var;
-
-INSERT INTO t4 VALUES('t4_line2');
-
-SELECT * FROM t4;
-
-# execution time:
-#   - check that SELECT is required to read the value;
-#   - check that SELECT is not enough to modify the value;
-
---connection default
---echo
---echo ---> connection: default
-
-use mysqltest_db1;
-
-REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
-REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
-GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
-
-REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
-REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
-GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
-GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
-
-# - table-level privileges
-
-INSERT INTO t1 VALUES('line3');
-
-SELECT * FROM t1;
-SELECT @mysqltest_var;
-
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t2 VALUES('line4');
-
-SELECT * FROM t2;
-
-# - column-level privileges
-
-INSERT INTO t3 VALUES('t3_line2');
-
-SELECT * FROM t3;
-SELECT @mysqltest_var;
-
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t4 VALUES('t4_line2');
-
-SELECT * FROM t4;
-
-# execution time:
-#   - check that nor SELECT either UPDATE is required to execute triggger w/o
-#     NEW/OLD variables.
-
-DELETE FROM t1;
-
-SELECT @mysqltest_var;
-
-#
-# Cleanup.
-#
-
-DROP USER mysqltest_u1@localhost;
-
-DROP DATABASE mysqltest_db1;
-
-
-#
-# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
-# procedures from within triggers
-#
-# We require UPDATE privilege when NEW.x passed as OUT parameter, and
-# SELECT and UPDATE when NEW.x passed as INOUT parameter.
-#
-DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
-DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
-FLUSH PRIVILEGES;
-
---disable_warnings
-DROP DATABASE IF EXISTS mysqltest_db1;
---enable_warnings
-
-CREATE DATABASE mysqltest_db1;
-USE mysqltest_db1;
-
-CREATE TABLE t1 (i1 INT);
-CREATE TABLE t2 (i1 INT);
-
-CREATE USER mysqltest_dfn@localhost;
-CREATE USER mysqltest_inv@localhost;
-
-GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
-GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
-
-connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
-connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
-
-connection definer;
-CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
-CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
-
-# Check that having no privilege won't work.
-connection definer;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-  CALL p2(NEW.i1);
-
-connection invoker;
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES (7);
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t2 VALUES (11);
-
-connection definer;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-
-# Check that having only SELECT privilege is not enough.
-connection default;
-GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
-connection definer;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-  CALL p2(NEW.i1);
-
-connection invoker;
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES (13);
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t2 VALUES (17);
-
-connection default;
-REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-
-connection definer;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-
-# Check that having only UPDATE privilege is enough for OUT parameter,
-# but not for INOUT parameter.
-connection default;
-GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
-connection definer;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-  CALL p2(NEW.i1);
-
-connection invoker;
-INSERT INTO t1 VALUES (19);
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t2 VALUES (23);
-
-connection default;
-REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-
-connection definer;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-
-# Check that having SELECT and UPDATE privileges is enough.
-connection default;
-GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
-connection definer;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  CALL p1(NEW.i1);
-CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
-  CALL p2(NEW.i1);
-
-connection invoker;
-INSERT INTO t1 VALUES (29);
-INSERT INTO t2 VALUES (31);
-
-connection default;
-REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-
-connection definer;
-DROP TRIGGER t2_bi;
-DROP TRIGGER t1_bi;
-
-connection default;
-DROP PROCEDURE p2;
-DROP PROCEDURE p1;
-
-# Check that late procedure redefining won't open a security hole.
-connection default;
-GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
-
-connection definer;
-CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
-CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
-  CALL p1(NEW.i1);
-
-connection invoker;
-INSERT INTO t1 VALUES (41);
-
-connection definer;
-DROP PROCEDURE p1;
-CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
-
-connection invoker;
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES (47);
-
-connection definer;
-DROP PROCEDURE p1;
-CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
-
-connection invoker;
---error ER_COLUMNACCESS_DENIED_ERROR
-INSERT INTO t1 VALUES (53);
-
-connection default;
-DROP PROCEDURE p1;
-REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
-
-connection definer;
-DROP TRIGGER t1_bi;
-
-# Cleanup.
-disconnect definer;
-disconnect invoker;
-connection default;
-DROP USER mysqltest_inv@localhost;
-DROP USER mysqltest_dfn@localhost;
-DROP TABLE t2;
-DROP TABLE t1;
-DROP DATABASE mysqltest_db1;
-USE test;
-
---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-11-16 15:05:28 -02:00
+++ b/mysql-test/t/trigger.test	2007-11-29 09:42:23 -02:00
@@ -2257,4 +2257,51 @@ DROP TRIGGER IF EXISTS trg;
 --error ER_TRG_DOES_NOT_EXIST
 SHOW CREATE TRIGGER trg;
 
+#
+# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
+#
+# Test of trigger creation and removal under LOCK TABLES
+#
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+
+create table t1 (i int, j int);
+
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_NOT_SUPPORTED_YET
+create trigger t1_bi before insert on t1 for each row begin end;
+drop trigger t1_bi;
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger t1_bi;
+
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TRG_DOES_NOT_EXIST
+drop trigger t1_bi;
+unlock tables;
+
+create trigger t1_bi before insert on t1 for each row begin end;
+lock tables t1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t1_bi before insert on t1 for each row begin end;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop trigger t1_bi;
+unlock tables;
+drop trigger t1_bi;
+
+lock tables t1 write;
+create trigger b1_bi before insert on t1 for each row set new.i = new.i + 10;
+insert into t1 values (10, 10);
+drop trigger b1_bi;
+insert into t1 values (10, 10);
+select * from t1;
+unlock tables;
+
+drop table t1;
+
 --echo End of 5.1 tests.
diff -Nrup a/mysql-test/t/trigger_notembedded.test b/mysql-test/t/trigger_notembedded.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/trigger_notembedded.test	2007-11-29 09:42:23 -02:00
@@ -0,0 +1,911 @@
+# Test case(s) in this file contain(s) GRANT/REVOKE statements, which are not
+# supported in embedded server. So, this test should not be run on embedded
+# server.
+
+-- source include/not_embedded.inc
+
+###########################################################################
+#
+# Tests for WL#2818:
+#   - Check that triggers are executed under the authorization of the definer.
+#   - Check DEFINER clause of CREATE TRIGGER statement;
+#     - Check that SUPER privilege required to create a trigger with different
+#       definer.
+#     - Check that if the user specified as DEFINER does not exist, a warning
+#       is emitted.
+#     - Check that the definer of a trigger does not exist, the trigger will
+#       not be activated.
+#   - Check that SHOW TRIGGERS statement provides "Definer" column.
+#   - Check that if trigger contains NEW/OLD variables, the definer must have
+#     SELECT privilege on the subject table (aka BUG#15166/BUG#15196).
+#
+#  Let's also check that user name part of definer can contain '@' symbol (to
+#  check that triggers are not affected by BUG#13310 "incorrect user parsing
+#  by SP").
+#
+###########################################################################
+
+#
+# Prepare environment.
+#
+
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+
+GRANT CREATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE TABLE t1(num_value INT);
+CREATE TABLE t2(user_str TEXT);
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT INSERT, DROP ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT INSERT, DROP ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+
+#
+# Check that the user must have TRIGGER privilege to create a trigger.
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--disconnect wl2818_definer_con
+
+#
+# Check that the user must have TRIGGER privilege to drop a trigger.
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+REVOKE TRIGGER ON mysqltest_db1.t1 FROM mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+DROP TRIGGER trg1;
+
+--disconnect wl2818_definer_con
+
+#
+# Check that the definer must have TRIGGER privilege to activate a trigger.
+#
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES(0);
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+GRANT TRIGGER ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+INSERT INTO t1 VALUES(0);
+
+# Cleanup for further tests.
+DROP TRIGGER trg1;
+TRUNCATE TABLE t1;
+TRUNCATE TABLE t2;
+
+--disconnect wl2818_definer_con
+
+--connection default
+--echo
+--echo ---> connection: default
+
+REVOKE SUPER ON *.* FROM mysqltest_dfn@localhost;
+
+#
+# Check that triggers are executed under the authorization of the definer:
+#   - create two tables under "definer";
+#   - grant all privileges on the test db to "definer";
+#   - grant all privileges on the first table to "invoker";
+#   - grant only select privilege on the second table to "invoker";
+#   - create a trigger, which inserts a row into the second table after
+#     inserting into the first table.
+#   - insert a row into the first table under "invoker". A row also should be
+#     inserted into the second table.
+#
+
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE TRIGGER trg1 AFTER INSERT ON t1
+  FOR EACH ROW
+    INSERT INTO t2 VALUES(CURRENT_USER());
+
+--connection default
+--echo
+--echo ---> connection: default
+
+# Setup definer's privileges.
+
+GRANT ALL PRIVILEGES ON mysqltest_db1.t1 TO mysqltest_dfn@localhost;
+GRANT ALL PRIVILEGES ON mysqltest_db1.t2 TO mysqltest_dfn@localhost;
+
+# Setup invoker's privileges.
+
+GRANT ALL PRIVILEGES ON mysqltest_db1.t1
+  TO 'mysqltest_inv'@localhost;
+  
+GRANT SELECT ON mysqltest_db1.t2
+  TO 'mysqltest_inv'@localhost;
+
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+use mysqltest_db1;
+
+INSERT INTO t1 VALUES(1);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+--connect (wl2818_invoker_con,localhost,mysqltest_inv,,mysqltest_db1)
+--connection wl2818_invoker_con
+--echo
+--echo ---> connection: wl2818_invoker_con
+
+use mysqltest_db1;
+
+INSERT INTO t1 VALUES(2);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+#
+# Check that if definer lost some privilege required to execute (activate) a
+# trigger, the trigger will not be activated:
+#  - create a trigger on insert into the first table, which will insert a row
+#    into the second table;
+#  - revoke INSERT privilege on the second table from the definer;
+#  - insert a row into the first table;
+#  - check that an error has been risen;
+#  - check that no row has been inserted into the second table;
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+REVOKE INSERT ON mysqltest_db1.t2 FROM mysqltest_dfn@localhost;
+
+--connection wl2818_invoker_con
+--echo
+--echo ---> connection: wl2818_invoker_con
+
+use mysqltest_db1;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES(3);
+
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+#
+# Check DEFINER clause of CREATE TRIGGER statement.
+#
+#   - Check that SUPER privilege required to create a trigger with different
+#     definer:
+#     - try to create a trigger with DEFINER="definer@localhost" under
+#       "invoker";
+#     - analyze error code;
+#   - Check that if the user specified as DEFINER does not exist, a warning is
+#     emitted:
+#     - create a trigger with DEFINER="non_existent_user@localhost" from
+#       "definer";
+#     - check that a warning emitted;
+#   - Check that the definer of a trigger does not exist, the trigger will not
+#     be activated:
+#     - activate just created trigger;
+#     - check error code;
+#
+
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+use mysqltest_db1;
+
+DROP TRIGGER trg1;
+
+# Check that SUPER is required to specify different DEFINER.
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @new_sum = 0;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+GRANT SUPER ON *.* TO mysqltest_dfn@localhost;
+
+--disconnect wl2818_definer_con
+--connect (wl2818_definer_con,localhost,mysqltest_dfn,,mysqltest_db1)
+--connection wl2818_definer_con
+--echo
+--echo ---> connection: wl2818_definer_con
+
+CREATE DEFINER='mysqltest_inv'@'localhost'
+  TRIGGER trg1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @new_sum = 0;
+
+# Create with non-existent user.
+
+CREATE DEFINER='mysqltest_nonexs'@'localhost'
+  TRIGGER trg2 AFTER INSERT ON t1
+  FOR EACH ROW
+    SET @new_sum = 0;
+
+# Check that trg2 will not be activated.
+
+--error ER_NO_SUCH_USER
+INSERT INTO t1 VALUES(6);
+
+#
+# Check that SHOW TRIGGERS statement provides "Definer" column.
+#
+
+SHOW TRIGGERS;
+
+#
+# Check that weird definer values do not break functionality. I.e. check the
+# following definer values:
+#   - '';
+#   - '@';
+#   - '@abc@def@@';
+#   - '@hostname';
+#   - '@abc@def@@@hostname';
+#
+
+DROP TRIGGER trg1;
+DROP TRIGGER trg2;
+
+CREATE TRIGGER trg1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @a = 1;
+
+CREATE TRIGGER trg2 AFTER INSERT ON t1
+  FOR EACH ROW
+    SET @a = 2;
+
+CREATE TRIGGER trg3 BEFORE UPDATE ON t1
+  FOR EACH ROW
+    SET @a = 3;
+
+CREATE TRIGGER trg4 AFTER UPDATE ON t1
+  FOR EACH ROW
+    SET @a = 4;
+
+CREATE TRIGGER trg5 BEFORE DELETE ON t1
+  FOR EACH ROW
+    SET @a = 5;
+
+# Replace definers with the "weird" definers
+perl;
+use strict;
+use warnings;
+my $fname= "$ENV{'MYSQLTEST_VARDIR'}/master-data/mysqltest_db1/t1.TRG";
+open(FILE, "<", $fname) or die;
+my @content= grep($_ !~ /^definers=/, <FILE>);
+close FILE;
+open(FILE, ">", $fname) or die;
+# Use binary file mode to avoid CR/LF's being added on windows
+binmode FILE;
+print FILE @content;
+print FILE "definers='' '\@' '\@abc\@def\@\@' '\@hostname' '\@abcdef\@\@\@hostname'\n";
+close FILE;
+EOF
+
+--echo
+
+SELECT trigger_name, definer FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+
+--echo
+
+SELECT * FROM INFORMATION_SCHEMA.TRIGGERS ORDER BY trigger_name;
+
+#
+# Cleanup
+#
+
+--connection default
+--echo
+--echo ---> connection: default
+
+DROP USER mysqltest_dfn@localhost;
+DROP USER mysqltest_inv@localhost;
+
+DROP DATABASE mysqltest_db1;
+
+
+###########################################################################
+#
+# BUG#15166: Wrong update [was: select/update] permissions required to execute
+# triggers.
+#
+# BUG#15196: Wrong select permission required to execute triggers.
+#
+###########################################################################
+
+#
+# Prepare environment.
+#
+
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+
+use mysqltest_db1;
+
+# Tables for tesing table-level privileges:
+CREATE TABLE t1(col CHAR(20)); # table for "read-value" trigger
+CREATE TABLE t2(col CHAR(20)); # table for "write-value" trigger
+
+# Tables for tesing column-level privileges:
+CREATE TABLE t3(col CHAR(20)); # table for "read-value" trigger
+CREATE TABLE t4(col CHAR(20)); # table for "write-value" trigger
+
+CREATE USER mysqltest_u1@localhost;
+REVOKE ALL PRIVILEGES, GRANT OPTION FROM mysqltest_u1@localhost;
+GRANT TRIGGER ON mysqltest_db1.* TO mysqltest_u1@localhost;
+
+SET @mysqltest_var = NULL;
+
+--connect (bug15166_u1_con,localhost,mysqltest_u1,,mysqltest_db1)
+
+# parsing (CREATE TRIGGER) time:
+#   - check that nor SELECT either UPDATE is required to execute triggger w/o
+#     NEW/OLD variables.
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+GRANT DELETE ON mysqltest_db1.* TO mysqltest_u1@localhost;
+SHOW GRANTS FOR mysqltest_u1@localhost;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+CREATE TRIGGER t1_trg_after_delete AFTER DELETE ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = 'Hello, world!';
+
+# parsing (CREATE TRIGGER) time:
+#   - check that UPDATE is not enough to read the value;
+#   - check that UPDATE is required to modify the value;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+# - table-level privileges
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t1_trg_err_1 BEFORE INSERT ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = NEW.col;
+DROP TRIGGER t1_trg_err_1;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t1_trg_err_2 BEFORE DELETE ON t1
+  FOR EACH ROW
+    SET @mysqltest_var = OLD.col;
+DROP TRIGGER t1_trg_err_2;
+
+CREATE TRIGGER t2_trg_before_insert BEFORE INSERT ON t2
+  FOR EACH ROW
+    SET NEW.col = 't2_trg_before_insert';
+
+# - column-level privileges
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t3_trg_err_1 BEFORE INSERT ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = NEW.col;
+DROP TRIGGER t3_trg_err_1;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t3_trg_err_2 BEFORE DELETE ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = OLD.col;
+DROP TRIGGER t3_trg_err_2;
+
+CREATE TRIGGER t4_trg_before_insert BEFORE INSERT ON t4
+  FOR EACH ROW
+    SET NEW.col = 't4_trg_before_insert';
+
+# parsing (CREATE TRIGGER) time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) on mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+--connection bug15166_u1_con
+--echo
+--echo ---> connection: bug15166_u1_con
+
+use mysqltest_db1;
+
+# - table-level privileges
+
+CREATE TRIGGER t1_trg_after_insert AFTER INSERT ON t1
+ FOR EACH ROW
+  SET @mysqltest_var = NEW.col;
+
+CREATE TRIGGER t1_trg_after_update AFTER UPDATE ON t1
+ FOR EACH ROW
+  SET @mysqltest_var = OLD.col;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t2_trg_err_1 BEFORE UPDATE ON t2
+ FOR EACH ROW
+  SET NEW.col = 't2_trg_err_1';
+DROP TRIGGER t2_trg_err_1;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t2_trg_err_2 BEFORE UPDATE ON t2
+ FOR EACH ROW
+  SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t2_trg_err_2;
+
+# - column-level privileges
+
+CREATE TRIGGER t3_trg_after_insert AFTER INSERT ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = NEW.col;
+
+CREATE TRIGGER t3_trg_after_update AFTER UPDATE ON t3
+  FOR EACH ROW
+    SET @mysqltest_var = OLD.col;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t4_trg_err_1 BEFORE UPDATE ON t4
+ FOR EACH ROW
+  SET NEW.col = 't4_trg_err_1';
+DROP TRIGGER t4_trg_err_1;
+
+# TODO: check privileges at CREATE TRIGGER time.
+# --error ER_COLUMNACCESS_DENIED_ERROR
+CREATE TRIGGER t4_trg_err_2 BEFORE UPDATE ON t4
+ FOR EACH ROW
+  SET NEW.col = CONCAT(OLD.col, '(updated)');
+DROP TRIGGER t4_trg_err_2;
+
+# execution time:
+#   - check that UPDATE is not enough to read the value;
+#   - check that UPDATE is required to modify the value;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+REVOKE SELECT ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE SELECT ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT UPDATE ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+REVOKE SELECT(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE SELECT(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT UPDATE(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+# - table-level privileges
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES('line1');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+INSERT INTO t2 VALUES('line2');
+
+SELECT * FROM t2;
+
+# - column-level privileges
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t3 VALUES('t3_line1');
+
+SELECT * FROM t3;
+SELECT @mysqltest_var;
+
+INSERT INTO t4 VALUES('t4_line2');
+
+SELECT * FROM t4;
+
+# execution time:
+#   - check that SELECT is required to read the value;
+#   - check that SELECT is not enough to modify the value;
+
+--connection default
+--echo
+--echo ---> connection: default
+
+use mysqltest_db1;
+
+REVOKE UPDATE ON mysqltest_db1.t1 FROM mysqltest_u1@localhost;
+REVOKE UPDATE ON mysqltest_db1.t2 FROM mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t1 TO mysqltest_u1@localhost;
+GRANT SELECT ON mysqltest_db1.t2 TO mysqltest_u1@localhost;
+
+REVOKE UPDATE(col) ON mysqltest_db1.t3 FROM mysqltest_u1@localhost;
+REVOKE UPDATE(col) ON mysqltest_db1.t4 FROM mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t3 TO mysqltest_u1@localhost;
+GRANT SELECT(col) ON mysqltest_db1.t4 TO mysqltest_u1@localhost;
+
+# - table-level privileges
+
+INSERT INTO t1 VALUES('line3');
+
+SELECT * FROM t1;
+SELECT @mysqltest_var;
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES('line4');
+
+SELECT * FROM t2;
+
+# - column-level privileges
+
+INSERT INTO t3 VALUES('t3_line2');
+
+SELECT * FROM t3;
+SELECT @mysqltest_var;
+
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t4 VALUES('t4_line2');
+
+SELECT * FROM t4;
+
+# execution time:
+#   - check that nor SELECT either UPDATE is required to execute triggger w/o
+#     NEW/OLD variables.
+
+DELETE FROM t1;
+
+SELECT @mysqltest_var;
+
+#
+# Cleanup.
+#
+
+DROP USER mysqltest_u1@localhost;
+
+DROP DATABASE mysqltest_db1;
+
+
+#
+# Test for bug #14635 Accept NEW.x as INOUT parameters to stored
+# procedures from within triggers
+#
+# We require UPDATE privilege when NEW.x passed as OUT parameter, and
+# SELECT and UPDATE when NEW.x passed as INOUT parameter.
+#
+DELETE FROM mysql.user WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.db WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.tables_priv WHERE User LIKE 'mysqltest_%';
+DELETE FROM mysql.columns_priv WHERE User LIKE 'mysqltest_%';
+FLUSH PRIVILEGES;
+
+--disable_warnings
+DROP DATABASE IF EXISTS mysqltest_db1;
+--enable_warnings
+
+CREATE DATABASE mysqltest_db1;
+USE mysqltest_db1;
+
+CREATE TABLE t1 (i1 INT);
+CREATE TABLE t2 (i1 INT);
+
+CREATE USER mysqltest_dfn@localhost;
+CREATE USER mysqltest_inv@localhost;
+
+GRANT EXECUTE, CREATE ROUTINE, TRIGGER ON *.* TO mysqltest_dfn@localhost;
+GRANT INSERT ON mysqltest_db1.* TO mysqltest_inv@localhost;
+
+connect (definer,localhost,mysqltest_dfn,,mysqltest_db1);
+connect (invoker,localhost,mysqltest_inv,,mysqltest_db1);
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 3;
+CREATE PROCEDURE p2(INOUT i INT) DETERMINISTIC NO SQL SET i = i * 5;
+
+# Check that having no privilege won't work.
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (7);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (11);
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only SELECT privilege is not enough.
+connection default;
+GRANT SELECT ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (13);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (17);
+
+connection default;
+REVOKE SELECT ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having only UPDATE privilege is enough for OUT parameter,
+# but not for INOUT parameter.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (19);
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t2 VALUES (23);
+
+connection default;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+# Check that having SELECT and UPDATE privileges is enough.
+connection default;
+GRANT SELECT, UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+CREATE TRIGGER t2_bi BEFORE INSERT ON t2 FOR EACH ROW
+  CALL p2(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (29);
+INSERT INTO t2 VALUES (31);
+
+connection default;
+REVOKE SELECT, UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t2_bi;
+DROP TRIGGER t1_bi;
+
+connection default;
+DROP PROCEDURE p2;
+DROP PROCEDURE p1;
+
+# Check that late procedure redefining won't open a security hole.
+connection default;
+GRANT UPDATE ON mysqltest_db1.* TO mysqltest_dfn@localhost;
+
+connection definer;
+CREATE PROCEDURE p1(OUT i INT) DETERMINISTIC NO SQL SET i = 37;
+CREATE TRIGGER t1_bi BEFORE INSERT ON t1 FOR EACH ROW
+  CALL p1(NEW.i1);
+
+connection invoker;
+INSERT INTO t1 VALUES (41);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(IN i INT) DETERMINISTIC NO SQL SET @v1 = i + 43;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (47);
+
+connection definer;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1(INOUT i INT) DETERMINISTIC NO SQL SET i = i + 51;
+
+connection invoker;
+--error ER_COLUMNACCESS_DENIED_ERROR
+INSERT INTO t1 VALUES (53);
+
+connection default;
+DROP PROCEDURE p1;
+REVOKE UPDATE ON mysqltest_db1.* FROM mysqltest_dfn@localhost;
+
+connection definer;
+DROP TRIGGER t1_bi;
+
+# Cleanup.
+disconnect definer;
+disconnect invoker;
+connection default;
+DROP USER mysqltest_inv@localhost;
+DROP USER mysqltest_dfn@localhost;
+DROP TABLE t2;
+DROP TABLE t1;
+DROP DATABASE mysqltest_db1;
+USE test;
+
+--echo End of 5.0 tests.
+
+#
+# Bug#23713 LOCK TABLES + CREATE TRIGGER + FLUSH TABLES WITH READ LOCK = deadlock
+#
+
+# Test temporarily disable due to Bug#32395
+--disable_parsing
+create table t1 (i int);
+connect (flush,localhost,root,,test,,);
+connection default;
+--echo connection: default
+lock tables t1 write;
+connection flush;
+--echo connection: flush
+--send flush tables with read lock;
+connection default;
+--echo connection: default
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Flushing tables";
+--source include/wait_condition.inc
+create trigger t1_bi before insert on t1 for each row begin end;
+unlock tables;
+connection flush;
+--echo connection: flush
+--reap
+unlock tables;
+connection default;
+select * from t1;
+drop table t1;
+disconnect flush;
+--enable_parsing
+
+--echo End of 5.1 tests.
diff -Nrup a/sql/mysql_priv.h b/sql/mysql_priv.h
--- a/sql/mysql_priv.h	2007-11-28 12:34:09 -02:00
+++ b/sql/mysql_priv.h	2007-11-29 09:42:23 -02:00
@@ -978,7 +978,8 @@ bool check_dup(const char *db, const cha
 bool compare_record(TABLE *table);
 bool append_file_to_dir(THD *thd, const char **filename_ptr, 
                         const char *table_name);
-
+void wait_while_table_is_used(THD *thd, TABLE *table,
+                              enum ha_extra_function function);
 bool table_cache_init(void);
 void table_cache_free(void);
 bool table_def_init(void);
@@ -1141,6 +1142,7 @@ TABLE *open_ltable(THD *thd, TABLE_LIST 
                    uint lock_flags);
 TABLE *open_table(THD *thd, TABLE_LIST *table_list, MEM_ROOT* mem,
 		  bool *refresh, uint flags);
+bool name_lock_locked_table(THD *thd, TABLE_LIST *tables);
 bool reopen_name_locked_table(THD* thd, TABLE_LIST* table_list, bool link_in);
 TABLE *table_cache_insert_placeholder(THD *thd, const char *key,
                                       uint key_length);
@@ -1292,7 +1294,7 @@ bool mysql_ha_close(THD *thd, TABLE_LIST
 bool mysql_ha_read(THD *, TABLE_LIST *,enum enum_ha_read_modes,char *,
                    List<Item> *,enum ha_rkey_function,Item *,ha_rows,ha_rows);
 void mysql_ha_flush(THD *thd);
-void mysql_ha_rm_tables(THD *thd, TABLE_LIST *tables);
+void mysql_ha_rm_tables(THD *thd, TABLE_LIST *tables, bool is_locked);
 void mysql_ha_cleanup(THD *thd);
 
 /* sql_base.cc */
diff -Nrup a/sql/sql_base.cc b/sql/sql_base.cc
--- a/sql/sql_base.cc	2007-11-28 12:34:10 -02:00
+++ b/sql/sql_base.cc	2007-11-29 09:42:23 -02:00
@@ -2199,6 +2199,41 @@ void wait_for_condition(THD *thd, pthrea
 }
 
 
+/**
+  Exclusively name-lock a table that is already write-locked by the
+  current thread.
+
+  @param thd current thread context
+  @param tables able list containing one table to open.
+
+  @return FALSE on success, TRUE otherwise.
+*/
+
+bool name_lock_locked_table(THD *thd, TABLE_LIST *tables)
+{
+  DBUG_ENTER("name_lock_locked_table");
+
+  /* Under LOCK TABLES we must only accept write locked tables. */
+  tables->table= find_locked_table(thd, tables->db, tables->table_name);
+
+  if (!tables->table)
+    my_error(ER_TABLE_NOT_LOCKED, MYF(0), tables->alias);
+  else if (tables->table->reginfo.lock_type < TL_WRITE_LOW_PRIORITY)
+    my_error(ER_TABLE_NOT_LOCKED_FOR_WRITE, MYF(0), tables->alias);
+  else
+  {
+    /*
+      Ensures that table is opened only by this thread and that no
+      other statement will open this table.
+    */
+    wait_while_table_is_used(thd, tables->table, HA_EXTRA_FORCE_REOPEN);
+    DBUG_RETURN(FALSE);
+  }
+
+  DBUG_RETURN(TRUE);
+}
+
+
 /*
   Open table which is already name-locked by this thread.
 
@@ -3118,6 +3153,9 @@ bool reopen_table(TABLE *table)
           then there is only one table open and locked. This means that
           the function probably has to be adjusted before it can be used
           anywhere outside ALTER TABLE.
+
+    @note Must not use TABLE_SHARE::table_name/db of the table being closed,
+          the strings are used in a loop even after the share may be freed.
 */
 
 void close_data_files_and_morph_locks(THD *thd, const char *db,
@@ -3387,8 +3425,8 @@ bool reopen_tables(THD *thd,bool get_loc
     @param send_refresh  Should we awake waiters even if we didn't close any tables?
 */
 
-void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
-			  bool send_refresh)
+static void close_old_data_files(THD *thd, TABLE *table, bool morph_locks,
+                                 bool send_refresh)
 {
   bool found= send_refresh;
   DBUG_ENTER("close_old_data_files");
diff -Nrup a/sql/sql_handler.cc b/sql/sql_handler.cc
--- a/sql/sql_handler.cc	2007-11-20 15:17:52 -02:00
+++ b/sql/sql_handler.cc	2007-11-29 09:42:23 -02:00
@@ -714,17 +714,16 @@ static TABLE_LIST *mysql_ha_find(THD *th
 
   @param thd Thread identifier.
   @param tables The list of tables to remove.
+  @param is_locked If LOCK_open is locked.
 
   @note Broadcasts refresh if it closed a table with old version.
 */
 
-void mysql_ha_rm_tables(THD *thd, TABLE_LIST *tables)
+void mysql_ha_rm_tables(THD *thd, TABLE_LIST *tables, bool is_locked)
 {
   TABLE_LIST *hash_tables, *next;
   DBUG_ENTER("mysql_ha_rm_tables");
 
-  safe_mutex_assert_not_owner(&LOCK_open);
-
   DBUG_ASSERT(tables);
 
   hash_tables= mysql_ha_find(thd, tables);
@@ -733,7 +732,7 @@ void mysql_ha_rm_tables(THD *thd, TABLE_
   {
     next= hash_tables->next_local;
     if (hash_tables->table)
-      mysql_ha_close_table(thd, hash_tables, FALSE);
+      mysql_ha_close_table(thd, hash_tables, is_locked);
     hash_delete(&thd->handler_tables_hash, (uchar*) hash_tables);
     hash_tables= next;
   }
diff -Nrup a/sql/sql_rename.cc b/sql/sql_rename.cc
--- a/sql/sql_rename.cc	2007-11-20 15:17:52 -02:00
+++ b/sql/sql_rename.cc	2007-11-29 09:42:23 -02:00
@@ -51,7 +51,7 @@ bool mysql_rename_tables(THD *thd, TABLE
     DBUG_RETURN(1);
   }
 
-  mysql_ha_rm_tables(thd, table_list);
+  mysql_ha_rm_tables(thd, table_list, FALSE);
 
   if (wait_if_global_read_lock(thd,0,1))
     DBUG_RETURN(1);
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc	2007-11-28 13:48:58 -02:00
+++ b/sql/sql_table.cc	2007-11-29 09:42:24 -02:00
@@ -1521,7 +1521,7 @@ int mysql_rm_table_part2(THD *thd, TABLE
       built_query.append("DROP TABLE ");
   }
 
-  mysql_ha_rm_tables(thd, tables);
+  mysql_ha_rm_tables(thd, tables, FALSE);
 
   pthread_mutex_lock(&LOCK_open);
 
@@ -3705,14 +3705,16 @@ mysql_rename_table(handlerton *base, con
     Win32 clients must also have a WRITE LOCK on the table !
 */
 
-static void wait_while_table_is_used(THD *thd,TABLE *table,
-				     enum ha_extra_function function)
+void wait_while_table_is_used(THD *thd, TABLE *table,
+                              enum ha_extra_function function)
 {
   DBUG_ENTER("wait_while_table_is_used");
   DBUG_PRINT("enter", ("table: '%s'  share: 0x%lx  db_stat: %u  version: %lu",
                        table->s->table_name.str, (ulong) table->s,
                        table->db_stat, table->s->version));
 
+  safe_mutex_assert_owner(&LOCK_open);
+
   VOID(table->file->extra(function));
   /* Mark all tables that are in use as 'old' */
   mysql_lock_abort(thd, table, TRUE);	/* end threads waiting on lock */
@@ -4031,7 +4033,7 @@ static bool mysql_admin_table(THD* thd, 
                             Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF))
     DBUG_RETURN(TRUE);
 
-  mysql_ha_rm_tables(thd, tables);
+  mysql_ha_rm_tables(thd, tables, FALSE);
 
   for (table= tables; table; table= table->next_local)
   {
@@ -5795,7 +5797,7 @@ bool mysql_alter_table(THD *thd,char *ne
   build_table_filename(reg_path, sizeof(reg_path), db, table_name, reg_ext, 0);
   build_table_filename(path, sizeof(path), db, table_name, "", 0);
 
-  mysql_ha_rm_tables(thd, table_list);
+  mysql_ha_rm_tables(thd, table_list, FALSE);
 
   /* DISCARD/IMPORT TABLESPACE is always alone in an ALTER TABLE */
   if (alter_info->tablespace_op != NO_TABLESPACE_OP)
diff -Nrup a/sql/sql_trigger.cc b/sql/sql_trigger.cc
--- a/sql/sql_trigger.cc	2007-11-18 17:28:35 -02:00
+++ b/sql/sql_trigger.cc	2007-11-29 09:42:24 -02:00
@@ -323,6 +323,7 @@ bool mysql_create_or_drop_trigger(THD *t
   TABLE *table;
   bool result= TRUE;
   String stmt_query;
+  bool need_start_waiting= FALSE;
 
   DBUG_ENTER("mysql_create_or_drop_trigger");
 
@@ -374,10 +375,12 @@ bool mysql_create_or_drop_trigger(THD *t
   /*
     We don't want perform our operations while global read lock is held
     so we have to wait until its end and then prevent it from occurring
-    again until we are done. (Acquiring LOCK_open is not enough because
-    global read lock is held without holding LOCK_open).
+    again until we are done, unless we are under lock tables. (Acquiring
+    LOCK_open is not enough because global read lock is held without holding
+    LOCK_open).
   */
-  if (wait_if_global_read_lock(thd, 0, 1))
+  if (!thd->locked_tables &&
+      !(need_start_waiting= !wait_if_global_read_lock(thd, 0, 1)))
     DBUG_RETURN(TRUE);
 
   VOID(pthread_mutex_lock(&LOCK_open));
@@ -433,35 +436,25 @@ bool mysql_create_or_drop_trigger(THD *t
     goto end;
   }
 
-  if (lock_table_names(thd, tables))
-    goto end;
+  /* We also don't allow creation of triggers on views. */
+  tables->required_type= FRMTYPE_TABLE;
 
-  /*
-    If the table is under LOCK TABLES, lock_table_names() does not set
-    tables->table. Find the table in open_tables.
-  */
-  if (!tables->table && thd->locked_tables)
-  {
-    for (table= thd->open_tables;
-         table && (strcmp(table->s->table_name.str, tables->table_name) ||
-                   strcmp(table->s->db.str, tables->db));
-         table= table->next) {}
-    tables->table= table;
-  }
-  if (!tables->table)
+  /* Keep consistent with respect to other DDL statements */
+  mysql_ha_rm_tables(thd, tables, TRUE);
+
+  if (thd->locked_tables)
   {
-    /* purecov: begin inspected */
-    my_error(ER_TABLE_NOT_LOCKED, MYF(0), tables->alias);
-    goto end;
-    /* purecov: end */
+    /* Table must be write locked */
+    if (name_lock_locked_table(thd, tables))
+      goto end;
   }
-
-  /* No need to reopen the table if it is locked with LOCK TABLES. */
-  if (!thd->locked_tables || (tables->table->in_use != thd))
+  else
   {
-    /* We also don't allow creation of triggers on views. */
-    tables->required_type= FRMTYPE_TABLE;
+    /* Grab the name lock and insert the placeholder*/
+    if (lock_table_names(thd, tables))
+      goto end;
 
+    /* Convert the placeholder to a real table */
     if (reopen_name_locked_table(thd, tables, TRUE))
     {
       unlock_table_name(thd, tables);
@@ -489,13 +482,20 @@ bool mysql_create_or_drop_trigger(THD *t
   /* Under LOCK TABLES we must reopen the table to activate the trigger. */
   if (!result && thd->locked_tables)
   {
-    /*
-      Must not use table->s->db.str or table->s->table_name.str here.
-      The strings are used in a loop even after the share may be freed.
-    */
+    /* Make table suitable for reopening */
     close_data_files_and_morph_locks(thd, tables->db, tables->table_name);
     thd->in_lock_tables= 1;
-    result= reopen_tables(thd, 1, 0);
+    if (reopen_tables(thd, 1, 1))
+    {
+      /* To be safe remove this table from the set of LOCKED TABLES */
+      unlink_open_table(thd, tables->table, FALSE);
+
+      /*
+        Ignore reopen_tables errors for now. It's better not leave master/slave
+        in a inconsistent state.
+      */
+      thd->clear_error();
+    }
     thd->in_lock_tables= 0;
   }
 
@@ -507,7 +507,9 @@ end:
   }
 
   VOID(pthread_mutex_unlock(&LOCK_open));
-  start_waiting_global_read_lock(thd);
+
+  if (need_start_waiting)
+    start_waiting_global_read_lock(thd);
 
   if (!result)
     send_ok(thd);
Thread
bk commit into 5.1 tree (davi:1.2671) BUG#23713Davi Arnaut29 Nov