MySQL Bugs: #49907: ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table

Bug #49907	ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table
Submitted:	24 Dec 2009 2:02	Modified:	16 Nov 2010 13:38
Reporter:	Elena Stepanova	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.5.0-m2, next-4284	OS:	Any
Assigned to:	Mattias Jonsson	CPU Architecture:	Any

Description:
ALTER TABLE ... TRUNCATE PARTITION ... , called concurrently with transactional DML flow on the table, is executed immediately, not waiting for the concurrent transaction to release locks. 

As a result, the ALTER TABLE statement is written into the binary log before the DML statements, which in row-based replication context eventually causes replication failure.

In the provided test case, 
1) we should not be able to switch to connection master after calling alter table, and
2) see slave SQL thread errors.

How to repeat:
perl ./mysql-test-run.pl \
--mysqld=--innodb --mysqld=--binlog_format=row rpl_failure

# t/rpl_failure.test

--source include/master-slave.inc
--source include/have_innodb.inc
--source include/have_binlog_format_row.inc

connection master;
use test;

--disable_warnings
drop table if exists t;
--enable_warnings
create table t ( i int not null auto_increment, primary key (i), f int ) engine = innodb partition by hash(i) partitions 2;
insert into t (f) values (1),(2);
commit;
set autocommit = 0;
delete from t where i = 1;

connection master1;
alter table t truncate partition p1;

connection master;
commit;

connection slave;
sleep 1;
--vertical_results
show slave status;

ALTER TABLE ... TRUNCATE PARTITION is a DML statement as it only deletes rows. Being DML, it takes a shared MDL lock. ALTER TABLE therefore does not have any conflicts with the existing transaction holding a shared MDL lock on the same table. This means that ALTER TABLE should be written to the binary log before DELETE since DELETE commits later.

This bug looks like a replication bug, not a locking bug.

This is not a replication bug.

See a modified version of the test case and the result set.

==== Test case ====

--source include/master-slave.inc
--source include/have_innodb.inc
--source include/have_binlog_format_row.inc

connection master;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
use test;

--disable_warnings
drop table if exists t;
--enable_warnings
create table t ( i int not null auto_increment, primary key (i), f int ) engine = innodb
partition by hash(i) partitions 2;
insert into t (f) values (1),(2),(3),(4),(5),(6);
commit;
set autocommit = 0;
delete from t where i = 1;
select * from t where i = 2;

connection master1;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
alter table t truncate partition p1;

connection master;
echo Check this out;
select * from t;
rollback;
select * from t;

Check this out
select * from t;
i f
3 3
5 5
select * from t;
i f
1 1
3 3
5 5

* The result set should be:
select * from t;
i f
2 2
3 3
4 4
5 5
6 6
select * from t;
i f
1 1
3 3
5 5

Basic isolation levels principles are not being followed and the
problem is not related to partitioning because if we replace "alter
table t truncate partition p1;" by "truncate table t" a similar
error will happen.

Apparently, this is either a MDL or Innodb's problem.

This is related to BUG#42643.

This have some interesting info:

http://blogs.mysql.com/peterg/2008/06/09/truncate-now-and-unto-ages-of-ages/

I have tested in 5.1 up to mysql-next-mr (alik@sun.com-20100127195345-cot7kisr5dgv06ak) and the behavior is consistent with TRUNCATE TABLE (and DROP TABLE ; CREATE TABLE)

But when testing in mysql-next-4284 (kostja@sun.com-20100206103007-2te50xtpwd7o3spx) the DROP TABLE now waits for the ongoing transaction in the other thread, but the TRUNCATE TABLE does not.

Also related to bug#50418

Related to bug#42643.

I have tested with the proposed patch for bug#42643 and it does also fix this bug.

Waiting until that bug is pushed before I can determine to close this as a duplicate or if it needs some extra handling.

Mattias, looks like the fix for Bug#42643 was pushed. Can you confirm that it also fixes this bug?

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

3073 Mattias Jonsson	2010-07-02
      Bug#49907: ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table
      
      Fixing the partitioning specifics after TRUNCATE TABLE in
      bug-42643 was fixed.
      
      Reorganize of code to decrease the size of the giant switch
      in mysql_execute_command, and to prepare for future parser
      reengineering. Moved code into Sql_statement objects.
     @ libmysqld/CMakeLists.txt
        Added new files.
     @ libmysqld/Makefile.am
        Added new files.
     @ mysql-test/r/not_partition.result
        now returning error on partitioning commands
        if partitioning is not enabled.
     @ mysql-test/r/partition_truncate.result
        updated test results
     @ mysql-test/suite/parts/inc/partition_mgm.inc
        Added check that TRUNCATE PARTITION does not delete on failure.
     @ mysql-test/suite/parts/r/partition_debug_sync_innodb.result
        Test case for this bug.
     @ mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        updated results
     @ mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        updated results
     @ mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        updated results
     @ mysql-test/suite/parts/t/partition_debug_sync_innodb.test
        updated results
     @ mysql-test/t/not_partition.test
        Added test for TRUNCATE PARTITION without partitioning.
     @ mysql-test/t/partition_truncate.test
        added test of TRUNCATE PARTITION on
        non partitioned table.
     @ sql/CMakeLists.txt
        Added new files.
     @ sql/Makefile.am
        Added new files.
     @ sql/datadict.cc
        moved out the storage engine check into an own
        function.
     @ sql/datadict.h
        added dd_frm_storage_engine.
     @ sql/sql_alter_table.cc
        moved the code for SQLCOM_ALTER_TABLE in mysql_execute_command
        into its own file, and using the Sql_statement object to
        prepare for future parser reengineering.
     @ sql/sql_alter_table.h
        Created Sql_statement object for ALTER TABLE
     @ sql/sql_lex.cc
        resetting m_stmt
     @ sql/sql_lex.h
        Temporary hack for forward declaration of enum_alter_table_change_level.
     @ sql/sql_parse.cc
        moved out ALTER/ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE
        from the giant switch into their own Sql_statement
        objects
     @ sql/sql_parse.h
        exporting check_merge_table_access
     @ sql/sql_partition_admin.cc
        created Sql_statement for
        ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR/TRUNCATE
        PARTITION. To be able to reuse the TABLE equivalents.
     @ sql/sql_partition_admin.h
        Added Sql_statement of partition admin statements.
     @ sql/sql_table.cc
        Moved table maintenance code into sql_table_maintenance.cc
     @ sql/sql_table.h
        Moved table maintenance code into sql_table_maintenance.h
        exporting functions used by sql_table_maintenance.
     @ sql/sql_table_maintenance.cc
        Moved table maintenance code from sql_table.cc
     @ sql/sql_table_maintenance.h
        Sql_statement objects for ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE.
        also declaring the keycache functions.
     @ sql/sql_truncate.cc
        moved code from SQLCOM_TRUNCATE in mysql_execute_command into
        Truncate_statement::execute.
        Added check for partitioned table on TRUNCATE PARTITION.
        moved locking fix for partitioned table into
        Alter_table_truncate_partition::execute.
     @ sql/sql_truncate.h
        Truncate_statement declaration (sub class of Sql_statement).
     @ sql/sql_yacc.yy
        Using the new Sql_statment objects.

Vasil, yes, the wait for running transactions is fixed by bug#42643, but the clash with INFORMATION_SCHEMA is still not fixed (bug#49938). I'm fixing some partitioning specifics in the scope of this bug.

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

3073 Mattias Jonsson	2010-07-02
      Bug#49907: ALTER TABLE ... TRUNCATE PARTITION does not wait for locks on the table
      
      Fixing the partitioning specifics after TRUNCATE TABLE in
      bug-42643 was fixed.
      
      Reorganize of code to decrease the size of the giant switch
      in mysql_execute_command, and to prepare for future parser
      reengineering. Moved code into Sql_statement objects.
      
      Updated patch according to kostja's comments.
     @ libmysqld/CMakeLists.txt
        Added new files.
     @ libmysqld/Makefile.am
        Added new files.
     @ mysql-test/r/not_partition.result
        now returning error on partitioning commands
        if partitioning is not enabled.
     @ mysql-test/r/partition_disabled.result
        There is no partition handlerton, so it cannot
        find the specified engine in the .frm file.
     @ mysql-test/r/partition_truncate.result
        updated test results
     @ mysql-test/suite/parts/inc/partition_mgm.inc
        Added check that TRUNCATE PARTITION does not delete on failure.
     @ mysql-test/suite/parts/r/partition_debug_sync_innodb.result
        Test case for this bug.
     @ mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        updated results
     @ mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        updated results
     @ mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        updated results
     @ mysql-test/suite/parts/t/partition_debug_sync_innodb.test
        updated results
     @ mysql-test/t/not_partition.test
        Added test for TRUNCATE PARTITION without partitioning.
     @ mysql-test/t/partition_truncate.test
        added test of TRUNCATE PARTITION on
        non partitioned table.
     @ sql/CMakeLists.txt
        Added new files.
     @ sql/Makefile.am
        Added new files.
     @ sql/datadict.cc
        moved out the storage engine check into an own
        function, including assert for lock.
     @ sql/datadict.h
        added dd_frm_storage_engine.
     @ sql/sql_alter_table.cc
        moved the code for SQLCOM_ALTER_TABLE in mysql_execute_command
        into its own file, and using the Sql_statement object to
        prepare for future parser reengineering.
     @ sql/sql_alter_table.h
        Created Sql_statement object for ALTER TABLE
     @ sql/sql_lex.cc
        resetting m_stmt
     @ sql/sql_lex.h
        Temporary hack for forward declaration of enum_alter_table_change_level.
     @ sql/sql_parse.cc
        moved out ALTER/ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE
        from the giant switch into their own Sql_statement
        objects
     @ sql/sql_parse.h
        exporting check_merge_table_access
     @ sql/sql_partition_admin.cc
        created Sql_statement for
        ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR/TRUNCATE
        PARTITION. To be able to reuse the TABLE equivalents.
     @ sql/sql_partition_admin.h
        Added Sql_statement of partition admin statements.
     @ sql/sql_table.cc
        Moved table maintenance code into sql_table_maintenance.cc
     @ sql/sql_table.h
        Moved table maintenance code into sql_table_maintenance.h
        exporting functions used by sql_table_maintenance.
     @ sql/sql_table_maintenance.cc
        Moved table maintenance code from sql_table.cc
     @ sql/sql_table_maintenance.h
        Sql_statement objects for ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE.
        also declaring the keycache functions.
     @ sql/sql_truncate.cc
        moved code from SQLCOM_TRUNCATE in mysql_execute_command into
        Truncate_statement::execute.
        Added check for partitioned table on TRUNCATE PARTITION.
        moved locking fix for partitioned table into
        Alter_table_truncate_partition::execute.
     @ sql/sql_truncate.h
        Truncate_statement declaration (sub class of Sql_statement).
     @ sql/sql_yacc.yy
        Using the new Sql_statment objects.

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

3179 Mattias Jonsson	2010-08-16
      Bug#49907: ALTER TABLE ... TRUNCATE PARTITION does not wait for
                 locks on the table
      
      Fixing the partitioning specifics after TRUNCATE TABLE in
      bug-42643 was fixed.
      
      Reorganize of code to decrease the size of the giant switch
      in mysql_execute_command, and to prepare for future parser
      reengineering. Moved code into Sql_statement objects.
      
      Updated patch according to davi's review comments.
     @ libmysqld/CMakeLists.txt
        Added new files.
     @ libmysqld/Makefile.am
        Added new files.
     @ mysql-test/r/not_partition.result
        now returning error on partitioning commands
        if partitioning is not enabled.
     @ mysql-test/r/partition_disabled.result
        There is no partition handlerton, so it cannot
        find the specified engine in the .frm file.
     @ mysql-test/r/partition_truncate.result
        Updated test results.
     @ mysql-test/suite/parts/inc/partition_mgm.inc
        Added check that TRUNCATE PARTITION does not delete on failure.
     @ mysql-test/suite/parts/r/partition_debug_sync_innodb.result
        updated results.
     @ mysql-test/suite/parts/r/partition_mgm_lc0_archive.result
        updated results.
     @ mysql-test/suite/parts/r/partition_mgm_lc1_archive.result
        updated results.
     @ mysql-test/suite/parts/r/partition_mgm_lc2_archive.result
        updated results.
     @ mysql-test/suite/parts/t/partition_debug_sync_innodb.test
        Test case for this bug.
     @ mysql-test/t/not_partition.test
        Added check for TRUNCATE PARTITION without partitioning.
     @ mysql-test/t/partition_truncate.test
        Added test of TRUNCATE PARTITION on non partitioned table.
     @ sql/CMakeLists.txt
        Added new files.
     @ sql/Makefile.am
        Added new files.
     @ sql/datadict.cc
        Moved out the storage engine check into an own
        function, including assert for lock.
     @ sql/datadict.h
        added dd_frm_storage_engine.
     @ sql/sql_alter_table.cc
        moved the code for SQLCOM_ALTER_TABLE in mysql_execute_command
        into its own file, and using the Sql_statement object to
        prepare for future parser reengineering.
     @ sql/sql_alter_table.h
        Created Sql_statement object for ALTER TABLE.
     @ sql/sql_lex.cc
        resetting m_stmt.
     @ sql/sql_lex.h
        Temporary hack for forward declaration of enum_alter_table_change_level.
     @ sql/sql_parse.cc
        Moved out ALTER/ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE
        from the giant switch into their own Sql_statement
        objects.
     @ sql/sql_parse.h
        Exporting check_merge_table_access.
     @ sql/sql_partition_admin.cc
        created Sql_statement for
        ALTER TABLE t ANALYZE/CHECK/OPTIMIZE/REPAIR/TRUNCATE
        PARTITION. To be able to reuse the TABLE equivalents.
     @ sql/sql_partition_admin.h
        Added Sql_statement of partition admin statements.
     @ sql/sql_table.cc
        Moved table maintenance code into sql_table_maintenance.cc
     @ sql/sql_table.h
        Moved table maintenance code into sql_table_maintenance.h
        exporting functions used by sql_table_maintenance.
     @ sql/sql_table_maintenance.cc
        Moved table maintenance code from sql_table.cc
     @ sql/sql_table_maintenance.h
        Sql_statement objects for ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE.
        Also declaring the keycache functions.
     @ sql/sql_truncate.cc
        Moved code from SQLCOM_TRUNCATE in mysql_execute_command into
        Truncate_statement::execute.
        Added check for partitioned table on TRUNCATE PARTITION.
        Moved locking fix for partitioned table into
        Alter_table_truncate_partition::execute.
     @ sql/sql_truncate.h
        Truncate_statement declaration (sub class of Sql_statement).
     @ sql/sql_yacc.yy
        Using the new Sql_statment objects.

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

3208 Mattias Jonsson	2010-08-16 [merge]
      manual merge of bug#49907 into mysql-trunk-bugteam

patch pushed to mysql-5.5-bugfixing, mysql-trunk-bugfixing and mysql-next-mr-bugfixing (including manual merge changes along the way...)

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

3235 Roy Lyseng	2010-08-24
      Followup to bug#49907: ALTER TABLE ... TRUNCATE PARTITION
      
      The original bug fix added new SQL command objects based on the
      class Sql_statement. However, later the class was renamed to Sql_cmd.
      This fix aligns the new class names with the new name standard
      introduced when renaming the class.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Documented in the 5.5.6 changelog as follows:

      ALTER TABLE ... TRUNCATE PARTITION, when called concurrently with
      transactional DML on the table, was executed immediately and did 
      not wait for the concurrent transaction to release locks. 
      
      As a result, the ALTER TABLE statement was written into the binary 
      log before the DML statement, which led to replication failures 
      when using row-based logging.

Set NM status, waiting for push to trunk.

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Also documented in the 5.6.1 changelog.

Closed.

Already documented in 5.5, no new changelog entry needed.

Set back to Closed state.

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)

No new changelog entries required. Returning to Closed state.