1include/master-slave.inc 2[connection master] 3CREATE TEMPORARY TABLE t1 (a INT); 4CREATE TABLE t2 (a INT, b INT) ENGINE= MyISAM; 5INSERT INTO t1 VALUES (1); 6CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; 7Warnings: 8Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 9INSERT INTO t1 VALUES (1); 10ALTER EVENT e1 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; 11INSERT INTO t1 VALUES (1); 12DROP EVENT IF EXISTS e1; 13INSERT INTO t1 VALUES (1); 14CREATE PROCEDURE p1() SELECT 1; 15INSERT INTO t1 VALUES (1); 16ALTER PROCEDURE p1 SQL SECURITY INVOKER; 17INSERT INTO t1 VALUES (1); 18CREATE FUNCTION f1() RETURNS INT RETURN 123; 19INSERT INTO t1 VALUES (1); 20ALTER FUNCTION f1 SQL SECURITY INVOKER; 21INSERT INTO t1 VALUES (1); 22CREATE DATABASE mysqltest1; 23INSERT INTO t1 VALUES (1); 24DROP DATABASE mysqltest1; 25INSERT INTO t1 VALUES (1); 26CREATE USER test_1@localhost; 27INSERT INTO t1 VALUES (1); 28GRANT SELECT ON t2 TO test_1@localhost; 29INSERT INTO t1 VALUES (1); 30GRANT ALL ON f1 TO test_1@localhost; 31INSERT INTO t1 VALUES (1); 32GRANT ALL ON p1 TO test_1@localhost; 33INSERT INTO t1 VALUES (1); 34GRANT USAGE ON *.* TO test_1@localhost; 35INSERT INTO t1 VALUES (1); 36REVOKE ALL PRIVILEGES ON f1 FROM test_1@localhost; 37INSERT INTO t1 VALUES (1); 38REVOKE ALL PRIVILEGES ON p1 FROM test_1@localhost; 39INSERT INTO t1 VALUES (1); 40REVOKE ALL PRIVILEGES ON t2 FROM test_1@localhost; 41INSERT INTO t1 VALUES (1); 42REVOKE USAGE ON *.* FROM test_1@localhost; 43INSERT INTO t1 VALUES (1); 44RENAME USER test_1@localhost TO test_2@localhost; 45INSERT INTO t1 VALUES (1); 46DROP USER test_2@localhost; 47INSERT INTO t1 VALUES (1); 48CREATE PROCEDURE p2() 49BEGIN 50# CREATE USER when a temporary table is open. 51CREATE TEMPORARY TABLE t3 (a INT); 52CREATE USER test_2@localhost; 53INSERT INTO t1 VALUES (1); 54# GRANT select on table to user when a temporary table is open. 55GRANT SELECT ON t2 TO test_2@localhost; 56INSERT INTO t1 VALUES (1); 57# GRANT all on function to user when a temporary table is open. 58GRANT ALL ON f1 TO test_2@localhost; 59INSERT INTO t1 VALUES (1); 60# GRANT all on procedure to user when a temporary table is open. 61GRANT ALL ON p1 TO test_2@localhost; 62INSERT INTO t1 VALUES (1); 63# GRANT usage on *.* to user when a temporary table is open. 64GRANT USAGE ON *.* TO test_2@localhost; 65INSERT INTO t1 VALUES (1); 66# REVOKE ALL PRIVILEGES on function to user when a temporary table is open. 67REVOKE ALL PRIVILEGES ON f1 FROM test_2@localhost; 68INSERT INTO t1 VALUES (1); 69# REVOKE ALL PRIVILEGES on procedure to user when a temporary table is open. 70REVOKE ALL PRIVILEGES ON p1 FROM test_2@localhost; 71INSERT INTO t1 VALUES (1); 72# REVOKE ALL PRIVILEGES on table to user when a temporary table is open. 73REVOKE ALL PRIVILEGES ON t2 FROM test_2@localhost; 74INSERT INTO t1 VALUES (1); 75# REVOKE usage on *.* from user when a temporary table is open. 76REVOKE USAGE ON *.* FROM test_2@localhost; 77INSERT INTO t1 VALUES (1); 78# RENAME USER when a temporary table is open. 79RENAME USER test_2@localhost TO test_3@localhost; 80INSERT INTO t1 VALUES (1); 81# DROP USER when a temporary table is open. 82DROP USER test_3@localhost; 83INSERT INTO t1 VALUES (1); 84DROP TEMPORARY TABLE t3; 85END | 86DROP PROCEDURE p1; 87INSERT INTO t1 VALUES (1); 88DROP PROCEDURE p2; 89INSERT INTO t1 VALUES (1); 90DROP FUNCTION f1; 91INSERT INTO t1 VALUES (1); 92DROP TABLE t2; 93INSERT INTO t1 VALUES (1); 94DROP TEMPORARY TABLE t1; 95connection slave; 96connection master; 97CREATE TABLE t1 (a CHAR(30)); 98CREATE TEMPORARY TABLE t2 (b CHAR(60)); 99LOCK TABLE t1 WRITE; 100CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13; 101ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 102INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked"); 103UNLOCK TABLE; 104CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13; 105LOCK TABLE t1 WRITE; 106ALTER FUNCTION f2 SQL SECURITY INVOKER; 107ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 108INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked"); 109LOCK TABLE t1 WRITE; 110DROP FUNCTION f2; 111ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 112INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked"); 113CREATE PROCEDURE p1() SELECT 1; 114ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 115INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked"); 116UNLOCK TABLE; 117CREATE PROCEDURE p2() SELECT 1; 118LOCK TABLE t1 WRITE; 119ALTER PROCEDURE p2 SQL SECURITY INVOKER; 120ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 121INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked"); 122DROP PROCEDURE p2; 123ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 124INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked"); 125CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; 126ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 127INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked"); 128UNLOCK TABLE; 129CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; 130Warnings: 131Warning 1105 Event scheduler is switched off, use SET GLOBAL event_scheduler=ON to enable it. 132LOCK TABLE t1 WRITE; 133ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; 134ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 135INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked"); 136DROP EVENT e2; 137ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 138INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked"); 139CREATE DATABASE mysqltest1; 140ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 141INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked"); 142UNLOCK TABLE; 143CREATE DATABASE mysqltest2; 144LOCK TABLE t1 WRITE; 145DROP DATABASE mysqltest2; 146ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction 147INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked"); 148UNLOCK TABLE; 149DROP DATABASE mysqltest2; 150LOCK TABLE t1 WRITE; 151CREATE USER test_1@localhost; 152ERROR HY000: Table 'db' was not locked with LOCK TABLES 153INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked"); 154UNLOCK TABLE; 155CREATE USER test_2@localhost; 156LOCK TABLE t1 WRITE; 157GRANT SELECT ON t1 TO test_2@localhost; 158ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 159INSERT INTO t2 VALUES ("GRANT select on table to user with table locked"); 160GRANT ALL ON f2 TO test_2@localhost; 161ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 162INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked"); 163GRANT ALL ON p2 TO test_2@localhost; 164ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 165INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked"); 166GRANT USAGE ON *.* TO test_2@localhost; 167ERROR HY000: Table 'db' was not locked with LOCK TABLES 168INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked"); 169REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost; 170ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 171INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked"); 172REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost; 173ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 174INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked"); 175REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost; 176ERROR HY000: Table 'tables_priv' was not locked with LOCK TABLES 177INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked"); 178REVOKE USAGE ON *.* FROM test_2@localhost; 179ERROR HY000: Table 'db' was not locked with LOCK TABLES 180INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked"); 181RENAME USER test_2@localhost TO test_3@localhost; 182ERROR HY000: Table 'db' was not locked with LOCK TABLES 183INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked"); 184UNLOCK TABLE; 185RENAME USER test_2@localhost TO test_3@localhost; 186LOCK TABLE t1 WRITE; 187DROP USER test_3@localhost; 188ERROR HY000: Table 'db' was not locked with LOCK TABLES 189INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked"); 190UNLOCK TABLE; 191CREATE DATABASE db; 192CREATE TABLE db.t1 LIKE t2; 193CREATE TABLE t3 LIKE t2; 194DROP TABLE t3; 195DROP DATABASE db; 196DROP USER test_3@localhost; 197DROP FUNCTION f2; 198DROP PROCEDURE p2; 199DROP EVENT e2; 200DROP TABLE t1, t2; 201include/rpl_end.inc 202