MySQL Bugs: #42643: InnoDB does not support replication of TRUNCATE TABLE

Bug #42643	InnoDB does not support replication of TRUNCATE TABLE
Submitted:	6 Feb 2009 10:28	Modified:	6 Oct 2010 16:48
Reporter:	Mats Kindahl	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	5.1	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any
Tags:	disabled

Description:
TRUNCATE TABLE is a DDL roughly equivalent to the following sequence of statements:

    LOCK TABLE t1 WRITE;
    CREATE TEMPORARY TABLE __tt1 LIKE t1;
    DROP TABLE t1;
    CREATE TABLE t1 LIKE __tt1;
    DROP TABLE __tt1;
    UNLOCK TABLES;

However, InnoDB implements this in a way so that the statement is not supported under transaction isolation level READ COMMITTED and READ UNCOMMITTED, which gives an error when executed under BINLOG_MODE=STATEMENT.

However, according to comment by Heikki in BUG#36763, TRUNCATE TABLE is not safe for replication at all, and should therefore not be allowed to replicate by the InnoDB storage engine.

How to repeat:
CREATE TABLE t1 (a INT) ENGINE=INNODB;
SET BINLOG_FORMAT=STATEMENT;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
TRUNCATE TABLE t1;

Suggested fix:
Block TRUNCATE TABLE until any INSERT threads have finished executing their inserts or, alternatively, abort any threads executing INSERT and then execute the TRUNCATE TABLE.

assigning to Marko who worked on bug#36763. I suspect the proper idea 
would be to wait until this problem resolves itself when we have transactional
DDL. regarding

Suggested fix:
Block TRUNCATE TABLE until any INSERT threads have finished executing their inserts 

### this would violate the spirit of TRUNCATE being a lightweight form of DELETE

alternatively, abort any threads executing INSERT and then execute the TRUNCATE TABLE.

### this is better, could probably be implemented without the 'then' part. Existing DML threads on the table would just get an error like 'sorry, the table is being truncated' and rollback, the replication would sort it out somehow. But this is obviously more work. Could probably be done by synchronizing TRUNCATE and transaction ending statements, feels like transactional DDL again.

see also bug#43832

I have developed a patch that makes DROP TABLE and TRUNCATE TABLE acquire proper table locks. The patch also does away with the background DROP TABLE queue.

There is a problem with the patch: Deadlocks will occur when attempting to TRUNCATE a table while it is in use. The deadlocks are not that fatal, because they will be resolved by innodb_lock_wait_timeout, but nevertheless, the TRUNCATE TABLE will fail if the table is in use. The deadlocks occur because MySQL is not obtaining a MySQL table lock for TRUNCATE TABLE:

deadlocks (limited by innodb_lock_wait_timeout) between MySQL and InnoDB table locks?

thread 1 (TRUNCATE TABLE t):
  srv_suspend_mysql_thread
  row_mysql_handle_errors
  row_mysql_lock_table
  row_truncate_table_for_mysql

thread 2 (UPDATE t):
  wait_for_lock
  thr_lock
  thr_multi_lock
  mysql_lock_tables
  lock_tables
  mysql_update

My colleague Sunny Bains pointed out that even if DROP TABLE is to observe existing lock requests, the background drop queue must remain, because ha_innodb.cc can access tables without obtaining table or record locks. Think of ha_innobase::info() or non-locking SELECTs, for instance.

Bug #37346 is somewhat related.

Can MySQL acquire an exclusive table lock for TRUNCATE TABLE (and DROP TABLE, for that matter) and wait until the queries of all readers have completed? That would make our lives a lot easier.

See also BUG#49907.

Marko,
we're working on a solution for it in scope of Bug#37346.
However, I don't think your suggestion is valid.
An exclusive table lock is not necessary for, say, MyISAM, where truncate
works differently (through replacing the .frm and deleting the data file).
So I believe that InnoDB should be doing it in ::external_lock() call,
it has all the information at hand there.

We can provide a fix in the server with low effort when type-of-operation aware metadata locks are in (The non-workaround fix for Bug#37346).

Marko,
additionally, I don't understand why you suggest that TRUNCATE needs an 
exclusive lock.
Isn't it sufficient to wait for all writer transactions? Why do we need to wait for readers? Is truncate-by-delete done in a manner that will break isolation
of reader transactions as well?

Please with a test case (example).

s/with/provide

I tried to provoke a crash in mysql-test. I could not get it to crash even after disabling the call to row_sel_push_cache_row_for_mysql().

The following test demonstrates two bugs: a breakage of transactions and an occasional race condition (you can improve chances by delaying row_search_for_mysql() or ha_innobase::delete_all_rows() by setting breakpoints).

cd mysql-test
cat > t/innodb_bug42643-master.opt << EOF
--innodb-file-per-table=1
EOF
cat > t/innodb_bug42643.test << EOF
-- source include/have_innodb.inc

connect (con1,localhost,root,,);
connect (con2,localhost,root,,);

connection con1;
create table bug42643(a int)engine=innodb;
insert into bug42643 values (1),(2),(3);
begin;
--send
select count(*) from bug42643 for update;
connection con2;
truncate table bug42643;
disconnect con2;

connection con1;
reap;
select count(*) from bug42643 for update;
commit;
drop table bug42643;
disconnect con1;
EOF

The two SELECT COUNT(*) should return 3 and 3, even without the FOR UPDATE clause, because they are executed in the same transaction. I am seeing 3 and 0, occasionally 0 and 0.

If TRUNCATE TABLE can destroy tables behind the backs of active transactions, it should also abort the affected transactions.

You can see 0 and 0 because the test case has a race itself. The truncate table can be executed before the select -- mysqltest creates a separate thread to run queries when using the send command. Same thing for 3 and 0. 3 and 3 is a problem.

3 and 3 is a "problem" on 5.1. With the new metadata locking, 3 and 3 should be the correct output.

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

3090 Davi Arnaut	2010-02-11
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive lock
      on a table if it resorted to a truncating via a delete of all rows
      in the table. Specifically for InnoDB tables, this could break
      proper isolation as InnoDB ends up aborting some granted locks when
      deleting all rows of a table.
      
      The solution is to take a exclusive metadata lock before TRUNCATE
      TABLE can proceed. This guarantees that no other transaction is
      using the table.
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test cae for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Acquire a exclusive metadata lock before accessing table metadata.
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_table.cc
        Rename function.

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

3090 Davi Arnaut	2010-02-11
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test cae for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Acquire a exclusive metadata lock before accessing table metadata.
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_table.cc
        Rename function.

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

2997 Davi Arnaut	2010-03-16
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/r/innodb_bug38231.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Move away from relying on mysql_delete() to delete all rows of
        a table. This simplifies the body of mysql_delete() a bit.
        
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-
        created.
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_table.cc
        Rename function.

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

2997 Davi Arnaut	2010-03-17
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/r/innodb_bug38231.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Move away from relying on mysql_delete() to delete all rows of
        a table. This simplifies the body of mysql_delete() a bit.
        
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-
        created.
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_table.cc
        Rename function.

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

2999 Davi Arnaut	2010-03-17
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/r/innodb_bug38231.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move away from relying on mysql_delete() to delete all rows of
        a table. Thus, move any bits related to truncate to sql_truncate.cc
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_table.cc
        Rename function.
     @ sql/sql_truncate.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-
        created.

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

2999 Davi Arnaut	2010-03-17
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ libmysqld/Makefile.am
        Add sql_truncate.cc to the build list.
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/r/innodb_bug38231.result
        Update test case result.
     @ mysql-test/r/innodb_mysql.result
        Update test case result.
     @ mysql-test/r/truncate.result
        Update test case result.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/mdl_sync.test
        Ensure that the acquire lock is not given up due to a conflict.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/CMakeLists.txt
        Add sql_truncate.cc to the build list.
     @ sql/mysql_priv.h
        Rename functions.
     @ sql/sql_delete.cc
        Move away from relying on mysql_delete() to delete all rows of
        a table. Thus, move any bits related to truncate to sql_truncate.cc
     @ sql/sql_parse.cc
        Rename function.
     @ sql/sql_show.cc
        Add sync point for testing scenarios where a pending flush
        is ignored.
     @ sql/sql_table.cc
        Rename function.
     @ sql/sql_truncate.cc
        Move truncation of a pre-locked table to its own function -- this
        is only used by repair. Also, move temporary table truncation to
        its own function.
        
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-
        created.

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

3019 Konstantin Osipov	2010-05-20 [merge]
      Bug#42643, with review comments.

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

3022 Davi Arnaut	2010-05-24
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ libmysqld/CMakeLists.txt
        Add new files to the build list.
     @ libmysqld/Makefile.am
        Add new files to the build list.
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/mdl_sync.test
        Ensure that a acquired lock is not given up due to a conflict.
     @ mysql-test/t/partition_innodb_semi_consistent.test
        End transaction as to release metadata locks.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/CMakeLists.txt
        Add new files to the build list.
     @ sql/Makefile.am
        Add new files to the build list.
     @ sql/datadict.cc
        Introduce a new file specific for data dictionary operations.
     @ sql/datadict.h
        Add header file.
     @ sql/sql_base.cc
        Rename data dictionary function.
     @ sql/sql_bitmap.h
        Include dependency.
     @ sql/sql_delete.cc
        Move away from relying on mysql_delete() to delete all rows of
        a table. Thus, move any bits related to truncate to sql_truncate.cc
     @ sql/sql_delete.h
        Remove parameter.
     @ sql/sql_parse.cc
        Add protection against the global read lock -- a intention
        exclusive lock can be acquired in the truncate path.
     @ sql/sql_show.cc
        Add sync point for testing scenarios where a pending flush
        is ignored.
     @ sql/sql_truncate.cc
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-created.
        Rework binlog rules to better reflect the requirements.
     @ sql/table.h
        Move to data dictionary header.

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

3023 Davi Arnaut	2010-05-25
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      The problem was that TRUNCATE TABLE didn't take a exclusive
      lock on a table if it resorted to truncating via delete of
      all rows in the table. Specifically for InnoDB tables, this
      could break proper isolation as InnoDB ends up aborting some
      granted locks when truncating a table.
      
      The solution is to take a exclusive metadata lock before
      TRUNCATE TABLE can proceed. This guarantees that no other
      transaction is using the table.
      
      Incompatible change: Truncate via delete no longer fails
      if sql_safe_updates is activated (this was a undocumented
      side effect).
     @ libmysqld/CMakeLists.txt
        Add new files to the build list.
     @ libmysqld/Makefile.am
        Add new files to the build list.
     @ mysql-test/extra/binlog_tests/binlog_truncate.test
        Add test case for Bug#42643
     @ mysql-test/include/mix1.inc
        Update test case as TRUNCATE TABLE now grabs a exclusive lock.
        Ensure that TRUNCATE waits for granted locks on the table.
     @ mysql-test/suite/binlog/t/binlog_truncate_innodb.test
        As with other data modifying statements, TRUNCATE is still not
        possible in a transaction with isolation level READ COMMITTED
        or READ UNCOMMITED. It would be possible to implement so, but
        it is not worth the effort.
     @ mysql-test/suite/binlog/t/binlog_truncate_myisam.test
        Test under different binlog formats.
     @ mysql-test/suite/binlog/t/disabled.def
        Re-enable test case.
     @ mysql-test/t/innodb_bug38231.test
        Truncate no longer works with row-level locks.
     @ mysql-test/t/mdl_sync.test
        Ensure that a acquired lock is not given up due to a conflict.
     @ mysql-test/t/partition_innodb_semi_consistent.test
        End transaction as to release metadata locks.
     @ mysql-test/t/truncate.test
        A metadata lock is now taken before the object is verified.
     @ sql/CMakeLists.txt
        Add new files to the build list.
     @ sql/Makefile.am
        Add new files to the build list.
     @ sql/datadict.cc
        Introduce a new file specific for data dictionary operations.
     @ sql/datadict.h
        Add header file.
     @ sql/sql_base.cc
        Rename data dictionary function.
     @ sql/sql_bitmap.h
        Include dependency.
     @ sql/sql_delete.cc
        Move away from relying on mysql_delete() to delete all rows of
        a table. Thus, move any bits related to truncate to sql_truncate.cc
     @ sql/sql_delete.h
        Remove parameter.
     @ sql/sql_parse.cc
        Add protection against the global read lock -- a intention
        exclusive lock can be acquired in the truncate path.
     @ sql/sql_show.cc
        Add sync point for testing scenarios where a pending flush
        is ignored.
     @ sql/sql_truncate.cc
        Acquire a shared metadata lock before accessing table metadata.
        Upgrade the lock to a exclusive one if the table can be re-created.
        Rework binlog rules to better reflect the requirements.
     @ sql/sql_yacc.yy
        Set appropriate lock types for table to be truncated.
     @ sql/table.h
        Move to data dictionary header.

Queued to mysql-trunk-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/109427

3026 Davi Arnaut	2010-05-27
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      Post-merge fix: Retrieve handler statistics to workaround quirks
      of the archive storage engine.
     @ mysql-test/t/archive.test
        Add test case.
     @ sql/sql_truncate.cc
        Call info method with has side-effects related to the archive
        storage engine. It will cause rows to become visible, allowing
        a error to be thrown once we attempt to delete the rows.

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

3027 Davi Arnaut	2010-05-27
      Bug#42643: InnoDB does not support replication of TRUNCATE TABLE
      
      Post-merge fix: Pass the right parameter type to open_and_lock_tables.
      Passing FALSE ensures that derived table handling is disabled, truncate
      only operates on base tables.
     @ sql/sql_truncate.cc
        Pass FALSE to disable derived table handling. This was probably
        reminiscent of the use of open_n_lock_single_table, which
        can't be used as in some cases (e.g. merge) more than one
        table will be opened.
     @ sql/sql_truncate.h
        TABLE_LIST is a struct.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Noted in 5.5.5 changelog.

TRUNCATE TABLE did not take an exclusive lock on a table if
truncation was done by deleting all rows in the table. For InnoDB
tables, this could break proper isolation because InnoDB ended up
aborting some granted locks when truncating a table. Now an exclusive
metadata lock is taken before TRUNCATE TABLE can proceed. This
guarantees that no other transaction is using the table.
      
Incompatible change: Truncation using delete no longer fails if
sql_safe_updates is enabled (this was an undocumented side effect).

Re opening since the test that was disabled due to this, binlog_truncate_innodb is not enable on next-mr, http://trollheim.norway.sun.com/archive/2329363.disabled_tests.html.

 Looks like a merge problem. Test was enabled by revision 3023 on mysql-trunk-runtime. Again got disabled by 3030 which merged mysql-trunk-bugfixing to trunk-runtime

Closing bug as per discussion with Kostja. Test changes are needed and another bug will be raised for it

Bug#57921 was reported for the issue with the test case.

The correct bug number must be: BUG#57291.

Is it true that this is *not* fixed in MySQL 5.1? If not, that means that TRUNCATE TABLE on InnoDB tables is unsafe in MySQL 5.1, correct? I am surprised that this is not discussed at http://dev.mysql.com/doc/refman/5.1/en/replication-features-truncate.html or http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html