| 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: | |
| 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
[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)
