Bug #57663 Concurrent statement using stored function and DROP DATABASE breaks SBR
Submitted: 22 Oct 2010 16:01 Modified: 20 Dec 2010 3:28
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Locking Severity:S3 (Non-critical)
Version:5.5.7-bzr, 5.5.8 OS:Any
Assigned to: Jon Olav Hauglid CPU Architecture:Any
Tags: regression

[22 Oct 2010 16:01] Dmitry Lenev
Description:
This is a re-incarnation of Bug #30977 "Concurrent statement using stored function and DROP FUNCTION breaks SBR" for DROP DATABASE instead of DROP FUNCTION.

Concurrent execution of DML statements that use stored function and DROP DATABSE statement that drops the same function might result in incorrect binary log in statement (and mixed) mode and therefore break replication.

How to repeat:
# Script for mysqltest tool which demonstrates the problem:
--source include/have_log_bin.inc
--source include/have_binlog_format_statement.inc

# Script for mysqltest which demonstrates the problem
connect (addconn1,localhost,root,,);
connection default;

--disable_parsing
drop table if not exists t1;
drop database if exists mysqltest;
--enable_parsing
reset master;

create table t1 (i int);
create database mysqltest;
# Actually any function which will be executed long enough will do
create function mysqltest.f1 () returns int return sleep(10);
--send insert into t1 values (mysqltest.f1());
connection addconn1;
--sleep 2
drop database mysqltest;
connection default;
--reap
select * from t1;
# Results confirm that INSERT was executed successfully.
# i
# 0

# But order of statements in binary log is incorrect ...
show binlog events;
# Log_name        Pos     Event_type      Server_id       End_log_pos     Info
# master-bin.000001       4       Format_desc     1       107     Server ver: 5.5.8-ga-log, Binlog ver: 4
# master-bin.000001       107     Query   1       193     use `test`; create table t1 (i int)
# master-bin.000001       193     Query   1       286     create database mysqltest
# master-bin.000001       286     Query   1       459     use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `mysqltest`.`f1`() RETURNS int(11)
return sleep(10)
# master-bin.000001       459     Query   1       550     drop database mysqltest
# master-bin.000001       550     Query   1       618     BEGIN
# master-bin.000001       618     Query   1       719     use `test`; insert into t1 values (mysqltest.f1())
# master-bin.000001       719     Query   1       788     COMMIT
[22 Oct 2010 16:15] Valeriy Kravchuk
Verified with mysql-5.5 from bzr on Mac OS X:

macbook-pro:mysql-test openxs$ ./mtr bug57663
Logging: ./mtr  bug57663
101022 19:10:06 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/NzcUMvcyjc/ is case insensitive
101022 19:10:06 [Note] Plugin 'FEDERATED' is disabled.
101022 19:10:06 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.5.7
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 64572

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
main.bug57663                            [ fail ]
        Test ended at 2010-10-22 19:10:26

CURRENT_TEST: main.bug57663
--- /Users/openxs/dbs/5.5/mysql-test/r/bug57663.result	2010-10-22 19:09:59.000000000 +0300
+++ /Users/openxs/dbs/5.5/mysql-test/r/bug57663.reject	2010-10-22 19:10:26.000000000 +0300
@@ -0,0 +1,22 @@
+reset master;
+create table t1 (i int);
+create database mysqltest;
+create function mysqltest.f1 () returns int return sleep(10);
+insert into t1 values (mysqltest.f1());;
+drop database mysqltest;
+Warnings:
+Note	1592	Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave.
+select * from t1;
+i
+0
+show binlog events;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	4	Format_desc	1	107	Server ver: 5.5.7-rc-debug-log, Binlog ver: 4
+master-bin.000001	107	Query	1	193	use `test`; create table t1 (i int)
+master-bin.000001	193	Query	1	286	create database mysqltest
+master-bin.000001	286	Query	1	459	use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `mysqltest`.`f1`() RETURNS int(11)
+return sleep(10)
+master-bin.000001	459	Query	1	550	drop database mysqltest
+master-bin.000001	550	Query	1	618	BEGIN
+master-bin.000001	618	Query	1	719	use `test`; insert into t1 values (mysqltest.f1())
+master-bin.000001	719	Query	1	788	COMMIT
...
[27 Oct 2010 15:12] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122127

3174 Jon Olav Hauglid	2010-10-27
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This is a preliminary version of the patch.
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes an
      exclusive metadata lock on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
      
      Questions to the reviewer:
      - Should we have two iterations in sp_drop_db_routines() so that we
        can try to take all locks before deleting any stored routine?
      - The return value from sp_drop_db_routines() is currently ignored
        by mysql_rm_db(). Should this be addressed in the scope of this patch?
[2 Nov 2010 12:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/122528

3177 Jon Olav Hauglid	2010-11-02
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This is a preliminary version of the patch.
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes
      exclusive metadata locks on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
      
      Questions to the reviewer:
      - The return value from sp_drop_db_routines() is currently ignored
        by mysql_rm_db(). Should this be addressed in the scope of this patch?
[8 Nov 2010 15:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123102

3179 Jon Olav Hauglid	2010-11-08
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This is a preliminary version of the patch.
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes
      exclusive metadata locks on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
[15 Nov 2010 9:51] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123860

3184 Jon Olav Hauglid	2010-11-15
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This pre-requisite patch removes obsolete and dead code used to remove
      raid subdirctories and files during DROP DATABASE code.
      
      Other parts of the raid code have already been removed in WL#5498.
[15 Nov 2010 11:33] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123887

3184 Jon Olav Hauglid	2010-11-15
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This pre-requisite patch removes obsolete and dead code used to remove
      raid subdirectories and files during DROP DATABASE.
      
      Other parts of the raid code have already been removed in WL#5498
      and the support for MyISAM raid tables was removed in 5.0.
[15 Nov 2010 13:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123898

3185 Jon Olav Hauglid	2010-11-15
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This pre-requisite patch refactors the code for dropping tables, used
      by DROP TABLE and DROP DATABASE. The patch moves the code for acquiring
      metadata locks out of mysql_rm_table_part2() and makes it the
      responsibility of the caller. This in preparation of changing the
      DROP DATABASE implementation to acquire all metadata locks before any
      changes are made. mysql_rm_table_part2() is renamed
      mysql_rm_table_no_locks() to reflect the change.
[15 Nov 2010 18:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/123948

3185 Jon Olav Hauglid	2010-11-15
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This pre-requisite patch refactors the code for dropping tables, used
      by DROP TABLE and DROP DATABASE. The patch moves the code for acquiring
      metadata locks out of mysql_rm_table_part2() and makes it the
      responsibility of the caller. This in preparation of changing the
      DROP DATABASE implementation to acquire all metadata locks before any
      changes are made. mysql_rm_table_part2() is renamed
      mysql_rm_table_no_locks() to reflect the change.
[16 Nov 2010 10:01] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124005

3186 Jon Olav Hauglid	2010-11-16
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      This pre-requisite patch refactors the code for dropping tables, used
      by DROP TABLE and DROP DATABASE. The patch moves the code for acquiring
      metadata locks out of mysql_rm_table_part2() and makes it the
      responsibility of the caller. This in preparation of changing the
      DROP DATABASE implementation to acquire all metadata locks before any
      changes are made. mysql_rm_table_part2() is renamed
      mysql_rm_table_no_locks() to reflect the change.
[16 Nov 2010 15:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124054

3187 Jon Olav Hauglid	2010-11-16
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes
      exclusive metadata locks on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
[17 Nov 2010 12:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124153

3187 Jon Olav Hauglid	2010-11-17
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      The problem was that DROP DATABASE ignores any metadata locks on stored
      functions and procedures held by other connections. This makes it
      possible for DROP DATABASE to drop functions/procedures that are in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE can appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes
      exclusive metadata locks on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
[17 Nov 2010 14:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/124161

3187 Jon Olav Hauglid	2010-11-17
      Bug #57663 Concurrent statement using stored function and DROP DATABASE
                 breaks SBR
      
      The problem was that DROP DATABASE ignored any metadata locks on stored
      functions and procedures held by other connections. This made it
      possible for DROP DATABASE to drop functions/procedures that were in use
      by other connections and therefore break statement based replication.
      (DROP DATABASE could appear in the binlog before a statement using a
      dropped function/procedure.)
      
      This problem was an issue left unresolved by the patch for Bug#30977
      where metadata locks for stored functions/procedures were introduced.
      
      This patch fixes the problem by making sure DROP DATABASE takes
      exclusive metadata locks on all stored functions/procedures to be
      dropped.
      
      Test case added to sp-lock.test.
[17 Nov 2010 15:30] Jon Olav Hauglid
Pushed to mysql-5.5-runtime (5.5.8).
This includes two pre-requisite patches.
[5 Dec 2010 12:40] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[14 Dec 2010 17:58] Jon Stephens
User-visible effect is on replication/binary logging, so I'll take this one.
[14 Dec 2010 19:25] Jon Stephens
Documented as follows in the 5.5.8 changelog:

        Concurrent statements using a stored function and a DROP
        DATABASE statement that caused the same stored function to be
        dropped could cause statement-based replication to fail. This
        problem is resolved by making sure that DROP DATABASE takes an
        exclusive metadata lock on all stored functions and stored
        procedures that it causes to be dropped.
      
No 5.6.1 changelog entry needed, since this issue did not occur in any 5.6 release.

Closed.
[16 Dec 2010 21:47] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (version source revid:alexander.nozdrin@oracle.com-20101216181820-7afubgk2fmuv9qsb) (merge vers: 5.6.1) (pib:23)
[16 Dec 2010 22:29] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)