MySQL Bugs: #989: If DROP TABLE while there's an active transaction, wrong binlog order

Bug #989	If DROP TABLE while there's an active transaction, wrong binlog order
Submitted:	4 Aug 2003 3:16	Modified:	14 Apr 18:42
Reporter:	Guilhem Bichot
Status:	Closed
Category:	Server: Locking	Severity:	S1 (Critical)
Version:	4.0 and higher	OS:	Any (all)
Assigned to:	Davi Arnaut	Target Version:	6.0-beta
Tags:	bfsm_2007_06_28, falcon, bfsm_2007_06_21
Triage:	Triaged: D2 (Serious) / R4 (High) / E5 (Major)

Description:
If user1 has an active transaction on a table and then user2 drops this table, then user1
does COMMIT, then in the binlog we have something like:
DROP TABLE t;
BEGIN;
INSERT INTO t ... ;
COMMIT;
which is wrong.

How to repeat:
Heikki:
attached to this bug report is a file innodb_drop.test which reproduces the problem. When
you fix the bug, you'll just need to add this test to the testsuite in 4.0, with a proper
.result file.

a test file for the testsuite, which reproduces the bug

Attachment: innodb_drop.test (application/octet-stream, text), 504 bytes.

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

This will be fixed in the 5.0 tree.

I have updated the 'known bugs' section regarding this.

Priority of this bug record is changed due to reports from some customers.

Several solutions have been discussed.  Monty thinks the best solution is 
to wait for "table definition cache" to be implemented and then 
have support on the MySQL level for this.

At least the problem exists for InnoDB and DROP TABLE, RENAME TABLE, ALTER TABLE RENAME
(any other ALTER TABLE, which scans the table, bumps on other's row locks so waits so has
no problem).
Workarounds *specific of InnoDB*:
1) for all workarounds below, innodb_table_locks should be 1 (the default)
2) for DROP TABLE: the session which wants to do a DROP TABLE should do it like this:
  SET AUTOCOMMIT=0; LOCK TABLES t WRITE; DROP TABLE t;
The LOCK TABLES will wait for all uncommitted updates to be committed or rolled back; the
SET is needed for LOCK TABLES to have this behaviour. The DROP will create name locks
without unlocking the LOCK TABLES, thus continuously ensuring there are no uncommitted
updates to the table until it is physically dropped.
3) for RENAME TABLE: the workaround of 2) will not work because RENAME TABLE refuses to
run when the session has locked tables ("ERROR 1192 (HY000): Can't execute the given
command because you have active locked tables or an active transaction").
4) for ALTER TABLE t RENAME TO u: the workaround of 2) will work; it is however strange
that this ALTER accepts to run when the session has locked tables whereas RENAME TABLE
refuses in the same conditions; if may be that in a future version we realize that the
ALTER must be made to refuse too.

My personal conclusion is that one possible path to solve the problem of DROP TABLE is to,
when executing a DROP TABLE, automatically, silently do the steps of LOCK TABLES WRITE
(which results in InnoDB taking an exclusive table lock).

Note that this is entirely specific of InnoDB, because InnoDB is able to take exclusive
table locks when executing LOCK TABLES.

See WL#3726 Transactional DDL locking for all metadata objects

maybe also related: bug #28829

Bug#28829 was marked a duplicate of this bug.
Please ensure the test case for Bug#28829 is in the test suite when this bug is fixed.

Was not that in 3.23 version when DROP table locked on mysql level the being dropped
table?

At requesting the exclusive write lock the current holders as well as pending request can
be aborted. DROP gains the exclusive lock after all preceding transactions either have
committed or rolled back.
A transaction that follows DROP with a lock request won't get it until DROP finishes off
incl its binlogging.

I wonder, why reverting to the original behaviour could not be a solution?

Bug #24144 strange transactional behaviour: DROP TABLE drops also locks was marked a
duplicate of this bug.

The way to fix this bug is well known add hhe amount of work needed is 2-5 days, depending
on who is fixing it.

The impact of a fix should be quite low (ie, very low change to cause regression)

Reason for the this bug and related bugs is that MySQL doesn't maintain any information
about the tables used in a transactation; MySQL only knows about the tables used by
running statements.

What needs to be done:
- Add a mechanism so that when we are going to alter/drop a table we not only wait for
table uses in running statements but also for tables used by running transactions.

Rough description of how to fix: (Already discussed several times with Dimitry):
- Add counter to table_share (in 5.0 this would be a global hash, assuming someone would
like to backport the fix)
- When we open a transactional table in not autocommit mode, increment the global hash (if
not already done for this transaction) and a local hash
-
- When doing commit/rollback loop over all entries in local hash and decrement global
counter in table share.
- Change remove_table_from_cache() to wait until counter in table_share is 0
- Change open_table() so that we don't wait for a name locked table (table to be deleted
or altered) if we have already used that table in a transaction or any of our used tables
(in local hash) are marked for alter or delete (this avoids deadlocks in the code)
- Don't drop a share if the used counter <> 0

WL#4284 Transactional DDL locking is the task created for this bug fix, not WL#4298 as
indicated earlier.

Additional information:
- The developers of PBXT and Solid have also told us several times that they have serious
issues with this bug. I would expect that most storage engine vendors will have issues
with this bug until it's fixed.

Manyi,

Yes, you are correct about WL #.

Few comments from me. In HL Desc. it should say " ... until the transaction is committed
or rolled back."

Next, Monty's solution is much simpler then the one described in WL4284. Monty's solution
does not defer any metadata locks release. Actually, I think that this solution could be
wrong, as one table should be read or write locked, depending on the statement. This is
not needed in InnoDB, but needed in other transactional engines. 

Some measurement has shown that even some auto-commit DML's can deadlock due to ALTER
TABLE. See bug #37346.

Please, also do note that algorithm in WL4284 is much more complex and less efficient then
the solution suggested by Monty Widenius.

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/50346

2683 Davi Arnaut	2008-07-23
      WL#4284: Transactional DDL locking
      Bug#989: If DROP TABLE while there's an active transaction,
               wrong binlog order
      
      Currently the MySQL server does not keep metadata locks on
      schema objects for the duration of a transaction, thus failing
      to guarantee the integrity of the schema objects being used
      during the transaction and to protect then from concurrent
      DDL operations. This also poses a problem for replication as
      a DDL operation might be replicated even thought there are
      active transactions using the object being modified.
      
      The solution is to defer the release of metadata locks until
      a active transaction is either committed or rolled back. This
      prevents other statements from modifying the table for the
      entire duration of the transaction. This provides commitment
      ordering for guaranteeing serializability across multiple
      transactions.

There is related bug #39675

Bug #37346 innodb does not detect deadlock between update and alter table
was marked a duplicate of this bug.
Davi, please add a test case from Bug#37346 to your changeset.

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/68032

2736 Davi Arnaut	2009-03-02
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      This is a prerequisite patch: 
      
      These changes split lock requests from granted locks and manages
      the memory and lifetime of granted locks within the MDL subsystem.
      Furthermore, ticket locks can now be shared and therefore are used
      to satisfy multiple lock requests, but only shared locks can be
      recursive.
      
      The problem is that the MDL subsystem morphs lock requests into
      granted locks locks but does not manage the memory and lifetime
      of lock requests, and hence, does not manage the memory of
      granted locks either. This can be problematic because it puts the
      burden of tracking references on the users of the subsystem and
      it can't be easily done in transactional contexts where the locks
      have to be kept around for the duration of a transaction.
      
      Another issue is that recursive locks (when the context trying to
      acquire a lock already holds a lock on the same object) requires
      that each time the lock is granted, a unique lock request/granted
      lock structure structure must be kept around until the lock is
      released. This can lead to memory leaks in transactional contexts
      as locks taken during the transaction should only be released at
      the end of the transaction. This also leads to unnecessary wake
      ups (broadcasts) in the MDL subsystem if the context still holds
      a equivalent of the lock being released.
      
      These issues are exacerbated due to the fact that WL#4284 low-level
      design says that the implementation should "2) Store metadata locks
      in transaction memory root, rather than statement memory root" but
      this is not possible because a memory root, as implemented in mysys,
      requires all objects allocated from it to be freed all at once.
      
      This patch combines review input and significant code contributions
      from Konstantin Osipov (kostja) and Dmitri Lenev (dlenev).
     @ mysql-test/r/mdl_sync.result
        Add test case result
     @ mysql-test/t/mdl_sync.test
        Add test case for shared lock upgrade case.
     @ sql/backup/backup_aux.h
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/event_db_repository.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/ha_ndbcluster_binlog.cc
        Use new function names to initialize MDL lock requests.
     @ sql/lock.cc
        Rename MDL functions.
     @ sql/log.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/log_event.cc
        The MDL request now holds the table and database name data (MDL_KEY).
     @ sql/mdl.cc
        Move the MDL key to the MDL_LOCK structure in order to make the
        object suitable for allocation from a fixed-size allocator. This
        allows the simplification of the lists in the MDL_LOCK object,
        which now are just two, one for granted tickets and other for
        waiting (upgraders) tickets.
        
        Recursive requests for a shared lock on the same object can now
        be granted using the same lock ticket. This schema is only used
        for shared locks because that the only case that matters. This
        is used to avoid waste of resources in case a context (connection)
        already holds a shared lock on a object.
     @ sql/mdl.h
        Introduce a medata lock object key which is used  to uniquely
        identify lock objects.
        
        Separate the structure used to represent pending lock requests
        from the structure used to represent granted metadata locks.
        
        Rename functions used to manipulate locks requests in order to
        have a more consistent function naming schema.
     @ sql/si_objects.cc
        Use the MDL API to construct and hold the key.
     @ sql/sp_head.cc
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/sql_acl.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_base.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_class.h
        Last acquired lock before the savepoint was set.
     @ sql/sql_delete.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_handler.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_insert.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_parse.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_plist.h
        Typedef for iterator type.
     @ sql/sql_plugin.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_servers.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_show.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_table.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_trigger.cc
        Save reference to the lock ticket so it can be downgraded later.
     @ sql/sql_udf.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/table.cc
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/table.h
        Separate MDL lock requests from lock tickets (granted locks).
     @ storage/myisammrg/ha_myisammrg.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.

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/68037

2736 Davi Arnaut	2009-03-02
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      This is a prerequisite patch:
      
      These changes are intended to split lock requests from granted
      locks and to allow the memory and lifetime of granted locks to
      be managed within the MDL subsystem. Furthermore, tickets can
      now be shared and therefore are used to satisfy multiple lock
      requests, but only shared locks can be recursive.
      
      The problem is that the MDL subsystem morphs lock requests into
      granted locks locks but does not manage the memory and lifetime
      of lock requests, and hence, does not manage the memory of
      granted locks either. This can be problematic because it puts the
      burden of tracking references on the users of the subsystem and
      it can't be easily done in transactional contexts where the locks
      have to be kept around for the duration of a transaction.
      
      Another issue is that recursive locks (when the context trying to
      acquire a lock already holds a lock on the same object) requires
      that each time the lock is granted, a unique lock request/granted
      lock structure structure must be kept around until the lock is
      released. This can lead to memory leaks in transactional contexts
      as locks taken during the transaction should only be released at
      the end of the transaction. This also leads to unnecessary wake
      ups (broadcasts) in the MDL subsystem if the context still holds
      a equivalent of the lock being released.
      
      These issues are exacerbated due to the fact that WL#4284 low-level
      design says that the implementation should "2) Store metadata locks
      in transaction memory root, rather than statement memory root" but
      this is not possible because a memory root, as implemented in mysys,
      requires all objects allocated from it to be freed all at once.
      
      This patch combines review input and significant code contributions
      from Konstantin Osipov (kostja) and Dmitri Lenev (dlenev).
     @ mysql-test/r/mdl_sync.result
        Add test case result
     @ mysql-test/t/mdl_sync.test
        Add test case for shared lock upgrade case.
     @ sql/backup/backup_aux.h
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/event_db_repository.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/ha_ndbcluster_binlog.cc
        Use new function names to initialize MDL lock requests.
     @ sql/lock.cc
        Rename MDL functions.
     @ sql/log.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/log_event.cc
        The MDL request now holds the table and database name data (MDL_KEY).
     @ sql/mdl.cc
        Move the MDL key to the MDL_LOCK structure in order to make the
        object suitable for allocation from a fixed-size allocator. This
        allows the simplification of the lists in the MDL_LOCK object,
        which now are just two, one for granted tickets and other for
        waiting (upgraders) tickets.
        
        Recursive requests for a shared lock on the same object can now
        be granted using the same lock ticket. This schema is only used
        for shared locks because that the only case that matters. This
        is used to avoid waste of resources in case a context (connection)
        already holds a shared lock on a object.
     @ sql/mdl.h
        Introduce a metadata lock object key which is used  to uniquely
        identify lock objects.
        
        Separate the structure used to represent pending lock requests
        from the structure used to represent granted metadata locks.
        
        Rename functions used to manipulate locks requests in order to
        have a more consistent function naming schema.
     @ sql/si_objects.cc
        Use the MDL API to construct and hold the key.
     @ sql/sp_head.cc
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/sql_acl.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_base.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_class.h
        Last acquired lock before the savepoint was set.
     @ sql/sql_delete.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_handler.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_insert.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_parse.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_plist.h
        Typedef for iterator type.
     @ sql/sql_plugin.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_servers.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/sql_show.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_table.cc
        Various changes to accommodate that lock requests are separated
        from lock tickets (granted locks).
     @ sql/sql_trigger.cc
        Save reference to the lock ticket so it can be downgraded later.
     @ sql/sql_udf.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.
     @ sql/table.cc
        Rename mdl_alloc_lock to mdl_request_alloc.
     @ sql/table.h
        Separate MDL lock requests from lock tickets (granted locks).
     @ storage/myisammrg/ha_myisammrg.cc
        Rename alloc_mdl_locks to alloc_mdl_requests.

Prerequisite patch pushed to 6.0-runtime

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/68333

2741 Davi Arnaut	2009-03-05
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      Currently the MySQL server does not keep metadata locks on
      schema objects for the duration of a transaction, thus failing
      to guarantee the integrity of the schema objects being used
      during the transaction and to protect then from concurrent
      DDL operations. This also poses a problem for replication as
      a DDL operation might be replicated even thought there are
      active transactions using the object being modified.
      
      The solution is to defer the release of metadata locks until
      a active transaction is either committed or rolled back. This
      prevents other statements from modifying the table for the
      entire duration of the transaction. This provides commitment
      ordering for guaranteeing serializability across multiple
      transactions.
     @ mysql-test/extra/binlog_tests/drop_table.test
        Add test case for Bug#989
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/locktrans.inc
        Add test case for WL#4284
     @ mysql-test/include/mix1.inc
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/mix2.inc
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/r/flush_block_commit.result
        Update test case result.
     @ mysql-test/r/flush_block_commit_notembedded.result
        Update test case result.
     @ mysql-test/r/innodb.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/r/locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/r/mix2_myisam.result
        Update test case result.
     @ mysql-test/r/partition_innodb_semi_consistent.result
        Update test case result.
     @ mysql-test/r/read_only_innodb.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_row_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_row_drop_tbl.test
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/t/binlog_stm_drop_tbl.test
        Add test case result for Bug#989
     @ mysql-test/suite/falcon/r/falcon_bugs2.result
        Update test case result.
     @ mysql-test/suite/falcon/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/falcon/t/falcon_bugs2.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/suite/ndb/r/ndb_index_ordered.result
        Update test case result.
     @ mysql-test/suite/ndb/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/ndb/t/ndb_index_ordered.test
        Comment out test made meaningless by transactional metadata locking.
     @ mysql-test/suite/rpl/r/rpl_locktrans_falcon.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/sys_vars/t/autocommit_func.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit_notembedded.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/innodb.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/partition_innodb_semi_consistent.test
        Delete from MyISAM table to avoid taking a exclusive lock.
     @ mysql-test/t/read_only_innodb.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/xa.test
        Fix test case to Reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ sql/mysql_priv.h
        Introduce prototype for function which releases metadata locks.
     @ sql/rpl_rli.cc
        Remove assert made meaningless, metadata locks are released
        at the end of the transaction.
     @ sql/sql_base.cc
        Defer the release of metadata locks when closing tables
        if not required to.
        
        Issue a deadlock error if the locking protocol requires
        that a transaction re-acquire its locks.
        
        Release metadata locks after taking transactional locks.
     @ sql/sql_parse.cc
        Add function to implicitly end a active transaction and that
        closes tables and releases the metadata locks afterwards.
        
        Force release of metadata locks when flushing with autocommit
        off.
     @ sql/sql_table.cc
        Remove unnecessary calls to close_thread_tables.
     @ sql/transaction.cc
        Release locks once the transaction is committed or rolled
        back. Same for savepoints.

Pushed into 6.0.11-alpha (revid:alik@sun.com-20090306190634-6s4zeti557q6stly) (version
source revid:davi.arnaut@sun.com-20090302211826-2y8e362mpmac6j5v) (merge vers:
6.0.11-alpha) (pib:6)

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/68553

2742 Davi Arnaut	2009-03-06
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      Currently the MySQL server does not keep metadata locks on
      schema objects for the duration of a transaction, thus failing
      to guarantee the integrity of the schema objects being used
      during the transaction and to protect then from concurrent
      DDL operations. This also poses a problem for replication as
      a DDL operation might be replicated even thought there are
      active transactions using the object being modified.
      
      The solution is to defer the release of metadata locks until
      a active transaction is either committed or rolled back. This
      prevents other statements from modifying the table for the
      entire duration of the transaction. This provides commitment
      ordering for guaranteeing serializability across multiple
      transactions.
     @ mysql-test/extra/binlog_tests/drop_table.test
        Add test case for Bug#989
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/locktrans.inc
        Add test case for WL#4284
     @ mysql-test/include/mix1.inc
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/mix2.inc
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/r/flush_block_commit.result
        Update test case result.
     @ mysql-test/r/flush_block_commit_notembedded.result
        Update test case result.
     @ mysql-test/r/innodb.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/r/locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/r/mix2_myisam.result
        Update test case result.
     @ mysql-test/r/partition_innodb_semi_consistent.result
        Update test case result.
     @ mysql-test/r/read_only_innodb.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_row_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_row_drop_tbl.test
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/t/binlog_stm_drop_tbl.test
        Add test case result for Bug#989
     @ mysql-test/suite/falcon/r/falcon_bugs2.result
        Update test case result.
     @ mysql-test/suite/falcon/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/falcon/t/falcon_bugs2.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/suite/ndb/r/ndb_index_ordered.result
        Remove result for disabled part of the test case.
     @ mysql-test/suite/ndb/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/ndb/t/ndb_index_ordered.test
        Comment out test made meaningless by transactional metadata locking.
     @ mysql-test/suite/rpl/r/rpl_locktrans_falcon.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/sys_vars/t/autocommit_func.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit_notembedded.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/innodb.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/partition_innodb_semi_consistent.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/read_only_innodb.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/xa.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ sql/log_event.cc
        Release metadata locks after issuing a commit.
     @ sql/mysql_priv.h
        Rename parameter to match the name used in the definition.
     @ sql/rpl_injector.cc
        Release metadata locks on commit and rollback.
     @ sql/rpl_rli.cc
        Remove assert made meaningless, metadata locks are released
        at the end of the transaction.
     @ sql/set_var.cc
        Close tables and release locks if autocommit mode is set.
     @ sql/slave.cc
        Release metadata locks after a rollback.
     @ sql/sql_base.cc
        Defer the release of metadata locks when closing tables
        if not required to.
        
        Issue a deadlock error if the locking protocol requires
        that a transaction re-acquire its locks.
        
        Release metadata locks when closing tables for reopen.
     @ sql/sql_class.cc
        Release metadata locks if the thread is killed.
     @ sql/sql_parse.cc
        Release metadata locks after implicitly committing a active
        transaction, or after explicit commits or rollbacks.
     @ sql/sql_table.cc
        Close table and release metadata locks after a admin operation.
     @ sql/transaction.cc
        Release metadata locks after the implicitly committed due
        to a new transaction being started. Also, release metadata
        locks acquired after a savepoint if the transaction is rolled
        back to the save point.

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/68564

2743 Davi Arnaut	2009-03-06
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      Currently the MySQL server does not keep metadata locks on
      schema objects for the duration of a transaction, thus failing
      to guarantee the integrity of the schema objects being used
      during the transaction and to protect then from concurrent
      DDL operations. This also poses a problem for replication as
      a DDL operation might be replicated even thought there are
      active transactions using the object being modified.
      
      The solution is to defer the release of metadata locks until
      a active transaction is either committed or rolled back. This
      prevents other statements from modifying the table for the
      entire duration of the transaction. This provides commitment
      ordering for guaranteeing serializability across multiple
      transactions.
      
      - Incompatible change:
      
      If MySQL's metadata locking system encounters a lock conflict,
      the usual schema is to use the try and back-off technique to
      avoid deadlocks -- this schema consists in releasing all locks
      and trying to acquire them all in one go.
      
      But in a transactional context this algorithm can't be utilized
      as its not possible to release locks acquired during the course
      of the transaction without breaking the transaction commitments.
      
      To avoid deadlocks in this case, the ER_LOCK_DEADLOCK will be
      returned if a lock conflict is encountered during a transaction.
      
      Let's consider an example:
      
      A transaction has two statements that modify table t1, then table
      t2, and then commits. The first statement of the transaction will
      acquire a shared metadata lock on table t1, and it will be kept
      utill COMMIT to ensure serializability.
      
      At the moment when the second statement attempts to acquire a
      shared metadata lock on t2, a concurrent ALTER or DROP statement
      might have locked t2 exclusively. The prescription of the current
      locking protocol is that the acquirer of the shared lock backs off
      -- gives up all his current locks and retries. This implies that
      the entire multi-statement transaction has to be rolled back.
      
      - Incompatible change:
      
      FLUSH commands such as FLUSH PRIVILEGES and FLUSH TABLES WITH READ
      LOCK won't cause locked tables to be implicitly unlocked anymore.
     @ mysql-test/extra/binlog_tests/drop_table.test
        Add test case for Bug#989
     @ mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/locktrans.inc
        Add test case for WL#4284
     @ mysql-test/include/mix1.inc
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/include/mix2.inc
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/r/flush_block_commit.result
        Update test case result.
     @ mysql-test/r/flush_block_commit_notembedded.result
        Update test case result.
     @ mysql-test/r/innodb.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/lock.result
        Add test case result.
     @ mysql-test/r/locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/r/locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/r/mix2_myisam.result
        Update test case result.
     @ mysql-test/r/partition_innodb_semi_consistent.result
        Update test case result.
     @ mysql-test/r/read_only_innodb.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_row_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result
        Add test case result for Bug#989
     @ mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_row_drop_tbl.test
        Add test case for Bug#989
     @ mysql-test/suite/binlog/t/binlog_stm_drop_tbl.test
        Add test case for Bug#989
     @ mysql-test/suite/falcon/r/falcon_bugs2.result
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/suite/falcon/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/falcon/t/falcon_bugs2.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/suite/ndb/r/ndb_index_ordered.result
        Remove result for disabled part of the test case.
     @ mysql-test/suite/ndb/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/ndb/t/ndb_index_ordered.test
        Comment out test made meaningless by transactional metadata locking.
     @ mysql-test/suite/rpl/r/rpl_locktrans_falcon.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_innodb.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/r/rpl_locktrans_myisam.result
        Add test case result for WL#4284
     @ mysql-test/suite/rpl/t/disabled.def
        Disable tests made meaningless by transactional metadata locking.
     @ mysql-test/suite/sys_vars/t/autocommit_func.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/flush_block_commit_notembedded.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/innodb.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/partition_innodb_semi_consistent.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/read_only_innodb.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ mysql-test/t/xa.test
        Fix test case to reflect the fact that transactions now hold
        metadata locks for the duration of a transaction.
     @ sql/log_event.cc
        Release metadata locks after issuing a commit.
     @ sql/mysql_priv.h
        Rename parameter to match the name used in the definition.
     @ sql/rpl_injector.cc
        Release metadata locks on commit and rollback.
     @ sql/rpl_rli.cc
        Remove assert made meaningless, metadata locks are released
        at the end of the transaction.
     @ sql/set_var.cc
        Close tables and release locks if autocommit mode is set.
     @ sql/slave.cc
        Release metadata locks after a rollback.
     @ sql/sql_acl.cc
        Don't implicitly unlock locked tables. Issue a implicit commit
        at the end and unlock tables.
     @ sql/sql_base.cc
        Defer the release of metadata locks when closing tables
        if not required to.
        
        Issue a deadlock error if the locking protocol requires
        that a transaction re-acquire its locks.
        
        Release metadata locks when closing tables for reopen.
     @ sql/sql_class.cc
        Release metadata locks if the thread is killed.
     @ sql/sql_parse.cc
        Release metadata locks after implicitly committing a active
        transaction, or after explicit commits or rollbacks.
     @ sql/sql_servers.cc
        Don't implicitly unlock locked tables. Issue a implicit commit
        at the end and unlock tables.
     @ sql/sql_table.cc
        Close table and release metadata locks after a admin operation.
     @ sql/transaction.cc
        Release metadata locks after the implicitly committed due
        to a new transaction being started. Also, release metadata
        locks acquired after a savepoint if the transaction is rolled
        back to the save point.

Queued to 6.0-runtime

- The most important incompatible change:

It's not possible anymore to drop a table that is being used by pending transaction. This
also means that FLUSH TABLES WITH READ LOCK and SET GLOBAL READ_ONLY=0/1 will wait for
pending (active) transactions (which are holding metadata locks) to complete.

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/70102

2746 Davi Arnaut	2009-03-23
      Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
      WL#4284: Transactional DDL locking
      
      After some discussions with stakeholders it has been decided that
      metadata locks acquired during a PREPARE statement must be released
      once the statement is prepared even if it is prepared within a multi
      statement transaction.
     @ mysql-test/r/ps.result
        Add test case result.
     @ mysql-test/t/ps.test
        Add test case.
     @ sql/sql_prepare.cc
        Release metdata locks acquired while the prepared statement is being
        checked.

Pushed into 6.0.11-alpha (revid:davi.arnaut@sun.com-20090331130058-yonsevjt1833wt5o)
(version source revid:davi.arnaut@sun.com-20090323181710-svbpqqnuyjh1ejsu) (merge vers:
6.0.11-alpha) (pib:6)

Noted in 6.0.11 changelog.

*Incompatible change*
If a data definition language (DDL) statement occurred for a table
that was being used by another session in an active transaction,
statements could be written to the binary log in the wrong order.
For example, this could happen if DROP TABLE occurred for a table
being used in a transaction.  This is now prevented by deferring
release of metadata locks on tables used within a transaction until
the transaction ends.

This bug fix results in some incompatibilities with previous versions:

* A table that is being used by a transaction within one session cannot be
used in DDL statements by other sessions until the transaction ends.

* FLUSH TABLES WITH READ LOCK blocks for active transactions that
hold metadata locks until those transactions end. The same is true
for attempts to set the global value of the read_lock system variable.

Correction: Previous comment should say read_only system variable, not read_lock system
variable.

Bug#15491 has been closed as a duplicate of this one.

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version
source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers:
5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090702084644-k95gd2asolvz2zpu) (version
source revid:luis.soares@sun.com-20090629083542-87rjmhmf34xzmvp3) (merge vers:
5.4.4-alpha) (pib:11)

Also noted in 5.4.4 changelog.

See also BUG#44938.

Can we have a note whether this will/can be backported to 5.0 or 5.1? Just so we know what
to say to customers. Thanks!

No, it won't. Two factors: it's too risky (new implementation of metadata locking) and
because it's a incompatible change (certainly not welcome in middle of a stable release
cycle).

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090723102221-ps4uaphwbxzj8p0q) (version
source revid:jon.hauglid@sun.com-20090706071401-fmmqttomxw0v0gjd) (merge vers:
5.4.4-alpha) (pib:11)

Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version
source revid:iggy@mysql.com-20090731204544-7nio1afvg0dmzs7g) (merge vers: 5.4.4-alpha)
(pib:11)

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

im facing the problem of 3306 bug i have tried from the firewall    it
but still im facing
Is it the problem of Virus or something else

im facing the problem of 3306 bug i have tried from the firewall    it
but still im facing
Is it the problem of Virus or something else