diff options
| -rw-r--r-- | mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result | 97 | ||||
| -rw-r--r-- | mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test | 145 | ||||
| -rw-r--r-- | sql/sp.cc | 30 |
3 files changed, 258 insertions, 14 deletions
diff --git a/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result index 5729faa9659..3136599e5aa 100644 --- a/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result +++ b/mysql-test/suite/rpl/r/rpl_tmp_table_and_DDL.result @@ -94,3 +94,100 @@ INSERT INTO t1 VALUES (1); DROP TABLE t2; INSERT INTO t1 VALUES (1); DROP TEMPORARY TABLE t1; +CREATE TABLE t1 (a CHAR(30)); +CREATE TEMPORARY TABLE t2 (b CHAR(60)); +LOCK TABLE t1 WRITE; +CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked"); +UNLOCK TABLE; +CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13; +LOCK TABLE t1 WRITE; +ALTER FUNCTION f2 SQL SECURITY INVOKER; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked"); +LOCK TABLE t1 WRITE; +DROP FUNCTION f2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked"); +CREATE PROCEDURE p1() SELECT 1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked"); +UNLOCK TABLE; +CREATE PROCEDURE p2() SELECT 1; +LOCK TABLE t1 WRITE; +ALTER PROCEDURE p2 SQL SECURITY INVOKER; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked"); +DROP PROCEDURE p2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked"); +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked"); +UNLOCK TABLE; +CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +LOCK TABLE t1 WRITE; +ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked"); +DROP EVENT e2; +ERROR HY000: Table 'event' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked"); +CREATE DATABASE mysqltest1; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked"); +UNLOCK TABLE; +CREATE DATABASE mysqltest2; +LOCK TABLE t1 WRITE; +DROP DATABASE mysqltest2; +ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction +INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked"); +UNLOCK TABLE; +DROP DATABASE mysqltest2; +LOCK TABLE t1 WRITE; +CREATE USER test_1@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked"); +UNLOCK TABLE; +CREATE USER test_2@localhost; +LOCK TABLE t1 WRITE; +GRANT SELECT ON t1 TO test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("GRANT select on table to user with table locked"); +GRANT ALL ON f2 TO test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked"); +GRANT ALL ON p2 TO test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked"); +GRANT USAGE ON *.* TO test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked"); +REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked"); +REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked"); +REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked"); +REVOKE USAGE ON *.* FROM test_2@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked"); +RENAME USER test_2@localhost TO test_3@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked"); +UNLOCK TABLE; +RENAME USER test_2@localhost TO test_3@localhost; +LOCK TABLE t1 WRITE; +DROP USER test_3@localhost; +ERROR HY000: Table 'user' was not locked with LOCK TABLES +INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked"); +UNLOCK TABLE; +DROP USER test_3@localhost; +DROP FUNCTION f2; +DROP PROCEDURE p2; +DROP EVENT e2; +DROP TABLE t1, t2; diff --git a/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test b/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test index b3efb578b68..b30548d0e45 100644 --- a/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test +++ b/mysql-test/suite/rpl/t/rpl_tmp_table_and_DDL.test @@ -12,3 +12,148 @@ LET $ENGINE_TYPE= MyISAM; source extra/rpl_tests/rpl_tmp_table_and_DDL.test; sync_slave_with_master; +# +# BUG #51839 +# The test makes sure the binlog format is not changed +# after a execution of DDL with a table locked, so that +# the following DML statements will not cause the slave +# to stop. +# + +--disable_abort_on_error + +CREATE TABLE t1 (a CHAR(30)); +CREATE TEMPORARY TABLE t2 (b CHAR(60)); + +# CREATE FUNCTION when a table is locked. +LOCK TABLE t1 WRITE; +CREATE FUNCTION f1 () RETURNS TINYINT RETURN 13; +INSERT INTO t2 VALUES ("CREATE FUNCTION f1 with table locked"); + +UNLOCK TABLE; +CREATE FUNCTION f2 () RETURNS TINYINT RETURN 13; + +# ALTER FUNCTION when a table is locked. +LOCK TABLE t1 WRITE; +ALTER FUNCTION f2 SQL SECURITY INVOKER; +INSERT INTO t2 VALUES ("ALTER FUNCTION f2 with table locked"); + +# DROP FUNCTION when a table is locked. +LOCK TABLE t1 WRITE; +DROP FUNCTION f2; +INSERT INTO t2 VALUES ("DROP FUNCTION f2 with table locked"); + + +# CREATE PROCEDURE when a table is locked. +CREATE PROCEDURE p1() SELECT 1; +INSERT INTO t2 VALUES ("CREATE PROCEDURE p1 with table locked"); + +UNLOCK TABLE; +CREATE PROCEDURE p2() SELECT 1; + +# ALTER PROCEDURE when a table is locked. +LOCK TABLE t1 WRITE; +ALTER PROCEDURE p2 SQL SECURITY INVOKER; +INSERT INTO t2 VALUES ("ALTER PROCEDURE P2 with table locked"); + +# DROP PROCEDURE when a table is locked. +DROP PROCEDURE p2; +INSERT INTO t2 VALUES ("DROP PROCEDURE p2 with table locked"); + + +# CREATE EVENT when a table is locked. +CREATE EVENT e1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; +INSERT INTO t2 VALUES ("CREATE EVENT e1 with table locked"); + +UNLOCK TABLE; +CREATE EVENT e2 ON SCHEDULE EVERY 10 HOUR DO SELECT 1; + +# ALTER EVENT when a table is locked. +LOCK TABLE t1 WRITE; +ALTER EVENT e2 ON SCHEDULE EVERY 20 HOUR DO SELECT 1; +INSERT INTO t2 VALUES ("ALTER EVENT e2 with table locked"); + +# DROP EVENT when a table is locked. +DROP EVENT e2; +INSERT INTO t2 VALUES ("DROP EVENT e2 with table locked"); + + +# CREATE DATABASE when a table is locked. +CREATE DATABASE mysqltest1; +INSERT INTO t2 VALUES ("CREATE DATABASE mysqltest1 with table locked"); + +UNLOCK TABLE; +CREATE DATABASE mysqltest2; + +# DROP DATABASE when a table is locked. +LOCK TABLE t1 WRITE; +DROP DATABASE mysqltest2; +INSERT INTO t2 VALUES ("DROP DATABASE mysqltest2 with table locked"); + +UNLOCK TABLE; +DROP DATABASE mysqltest2; + +# CREATE USER when a table is locked. +LOCK TABLE t1 WRITE; +CREATE USER test_1@localhost; +INSERT INTO t2 VALUES ("CREATE USER test_1@localhost with table locked"); + +UNLOCK TABLE; +CREATE USER test_2@localhost; + +# GRANT select on table to user when a table is locked. +LOCK TABLE t1 WRITE; +GRANT SELECT ON t1 TO test_2@localhost; +INSERT INTO t2 VALUES ("GRANT select on table to user with table locked"); + +# GRANT all on function to user when a table is locked. +GRANT ALL ON f2 TO test_2@localhost; +INSERT INTO t2 VALUES ("GRANT ALL ON f2 TO test_2 with table locked"); + +# GRANT all on procdure to user when a table is locked. +GRANT ALL ON p2 TO test_2@localhost; +INSERT INTO t2 VALUES ("GRANT ALL ON p2 TO test_2 with table locked"); + +# GRANT USAGE ON *.* to user when a table is locked. +GRANT USAGE ON *.* TO test_2@localhost; +INSERT INTO t2 VALUES ("GRANT USAGE ON *.* TO test_2 with table locked"); + + +# REVOKE ALL PRIVILEGES on function to user when a table is locked. +REVOKE ALL PRIVILEGES ON f2 FROM test_2@localhost; +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on function to user with table locked"); + +# REVOKE ALL PRIVILEGES on procedure to user when a table is locked. +REVOKE ALL PRIVILEGES ON p2 FROM test_2@localhost; +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on procedure to user with table locked"); + +# REVOKE ALL PRIVILEGES on table to user when a table is locked. +REVOKE ALL PRIVILEGES ON t1 FROM test_2@localhost; +INSERT INTO t2 VALUES ("REVOKE ALL PRIVILEGES on table to user with table locked"); + +# REVOKE USAGE ON *.* to user when a table is locked. +REVOKE USAGE ON *.* FROM test_2@localhost; +INSERT INTO t2 VALUES ("REVOKE USAGE ON *.* TO test_2 with table locked"); + + +# RENAME USER when a table is locked. +RENAME USER test_2@localhost TO test_3@localhost; +INSERT INTO t2 VALUES ("RENAME USER test_2 TO test_3 with table locked"); + +UNLOCK TABLE; +RENAME USER test_2@localhost TO test_3@localhost; + +# DROP USER when a table is locked. +LOCK TABLE t1 WRITE; +DROP USER test_3@localhost; +INSERT INTO t2 VALUES ("DROP USER test_3@localhost with table locked"); + +UNLOCK TABLE; +DROP USER test_3@localhost; +DROP FUNCTION f2; +DROP PROCEDURE p2; +DROP EVENT e2; +DROP TABLE t1, t2; + +--source include/master-slave-end.inc + diff --git a/sql/sp.cc b/sql/sp.cc index 6aaddb69e05..e7bf15c56d5 100644 --- a/sql/sp.cc +++ b/sql/sp.cc @@ -931,6 +931,11 @@ sp_create_routine(THD *thd, int type, sp_head *sp) DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || type == TYPE_ENUM_FUNCTION); + /* Grab an exclusive MDL lock. */ + if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION, + sp->m_db.str, sp->m_name.str)) + DBUG_RETURN(SP_OPEN_TABLE_FAILED); + /* Reset sql_mode during data dictionary operations. */ thd->variables.sql_mode= 0; @@ -942,11 +947,6 @@ sp_create_routine(THD *thd, int type, sp_head *sp) if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row())) thd->clear_current_stmt_binlog_format_row(); - /* Grab an exclusive MDL lock. */ - if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION, - sp->m_db.str, sp->m_name.str)) - DBUG_RETURN(SP_OPEN_TABLE_FAILED); - saved_count_cuted_fields= thd->count_cuted_fields; thd->count_cuted_fields= CHECK_FIELD_WARN; @@ -1190,6 +1190,14 @@ sp_drop_routine(THD *thd, int type, sp_name *name) DBUG_ASSERT(type == TYPE_ENUM_PROCEDURE || type == TYPE_ENUM_FUNCTION); + /* Grab an exclusive MDL lock. */ + if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION, + name->m_db.str, name->m_name.str)) + DBUG_RETURN(SP_DELETE_ROW_FAILED); + + if (!(table= open_proc_table_for_update(thd))) + DBUG_RETURN(SP_OPEN_TABLE_FAILED); + /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the @@ -1198,13 +1206,6 @@ sp_drop_routine(THD *thd, int type, sp_name *name) if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row())) thd->clear_current_stmt_binlog_format_row(); - /* Grab an exclusive MDL lock. */ - if (lock_routine_name(thd, type == TYPE_ENUM_FUNCTION, - name->m_db.str, name->m_name.str)) - DBUG_RETURN(SP_DELETE_ROW_FAILED); - - if (!(table= open_proc_table_for_update(thd))) - DBUG_RETURN(SP_OPEN_TABLE_FAILED); if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK) { if (table->file->ha_delete_row(table->record[0])) @@ -1276,6 +1277,9 @@ sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics) name->m_db.str, name->m_name.str)) DBUG_RETURN(SP_OPEN_TABLE_FAILED); + if (!(table= open_proc_table_for_update(thd))) + DBUG_RETURN(SP_OPEN_TABLE_FAILED); + /* This statement will be replicated as a statement, even when using row-based replication. The flag will be reset at the end of the @@ -1284,8 +1288,6 @@ sp_update_routine(THD *thd, int type, sp_name *name, st_sp_chistics *chistics) if ((save_binlog_row_based= thd->is_current_stmt_binlog_format_row())) thd->clear_current_stmt_binlog_format_row(); - if (!(table= open_proc_table_for_update(thd))) - DBUG_RETURN(SP_OPEN_TABLE_FAILED); if ((ret= db_find_routine_aux(thd, type, name, table)) == SP_OK) { if (type == TYPE_ENUM_FUNCTION && ! trust_function_creators && |