Bug #57985 ONLINE/FAST ALTER PARTITION can fail and leave the table unusable
Submitted: 4 Nov 2010 16:25 Modified: 14 Dec 2010 20:12
Reporter: Mattias Jonsson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S1 (Critical)
Version:5.1.54-bzr, 5.5+ OS:Any
Assigned to: Dmitry Lenev CPU Architecture:Any

[4 Nov 2010 16:25] Mattias Jonsson
Description:
A partitioned table have some checks during the open table phase, which will fail if the definition is not correct.

The code path for online/fast index add/drop does not handle this which leaves the table in an unusable state (not possible to open the table).

Found this when looking into bug#57778.

How to repeat:
mysql-test-run test:
--source include/have_partition.inc
--source include/have_innodb.incCREATE TABLE t1
(a bigint not null, 
 b int not null,
 PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY KEY(a) PARTITIONS 2;
INSERT INTO t1 values (0,1), (1,2);
SHOW CREATE TABLE t1;
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
ALTER TABLE t1 ADD UNIQUE KEY (b);
--echo # The above failed, but since it was an online operation, the FRM was
--echo # already updated, leaving the .frm file in a state where
--echo # the partitioning engine disallows it from being opened.
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;

Suggested fix:
Add error handling in mysql_alter_table to revert the .frm and index operations on failures.
[4 Nov 2010 17:38] MySQL Verification Team
Thank you for the bug report.
[18 Nov 2010 8:33] Dmitry Lenev
Hello I have found that this bug is also repeatable for 5.1.54-bzr version of server. You just have to use innodb_plugin instead of built-in innodb to trigger it.

--source include/have_innodb_plugin.inc
--source include/have_partition.inc
CREATE TABLE t1
(a bigint not null,
 b int not null,
 PRIMARY KEY (a))
ENGINE = InnoDB
PARTITION BY KEY(a) PARTITIONS 2;
INSERT INTO t1 values (0,1), (1,2);
SHOW CREATE TABLE t1;
--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
ALTER TABLE t1 ADD UNIQUE KEY (b);
--echo # The above failed, but since it was an online operation, the FRM was
--echo # already updated, leaving the .frm file in a state where
--echo # the partitioning engine disallows it from being opened.
SHOW CREATE TABLE t1;
SELECT * FROM t1;
DROP TABLE t1;

Please re-triage.
[19 Nov 2010 1:00] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

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

3186 Dmitry Lenev	2010-11-18
      Fix for bug #57985 "ONLINE/FAST ALTER PARTITION can fail and 
      leave the table unusable".
       
      Failing ALTER statement on partitioned table could have left
      this table in an unusable state. This has happened in cases
      when ALTER was executed using "fast" algorithm, which doesn't 
      involve copying of data between old and new versions of table, 
      and the resulting new table was incompatible with partitioning
      function in some way.
       
      The problem stems from the fact that discrepancies between new 
      table definition and partitioning function are discovered only 
      table is opened. In case of "fast" algorithm this has happened 
      too late during ALTER's execution, at the moment when all
      changes were already done and couldn't have been reverted.
       
      In the cases when "slow" algoritm, which copies data, is used 
      such discrepancies are detected at the moment new table
      definition is opened implicitly when new version of table is
      created in storage engine. As result ALTER is aborted before 
      any changes to table were done.
       
      This fix tries to address this issue by ensuring that "fast"
      algorithm behaves similarly to "slow" algoritm and checks
      compatibility between new definition and partitioning function 
      by trying to open new definition after .FRM file for it has 
      been created.
       
      Long term we probably should implement some way to check
      compatibility between partitioning function and new table
      definition which won't involve opening it, as this should
      allow much cleaner fix for this problem.
     @ mysql-test/r/partition_innodb.result
        Added test for bug #57985 "ONLINE/FAST ALTER PARTITION can
        fail and leave the table unusable".
     @ mysql-test/t/partition_innodb.test
        Added test for bug #57985 "ONLINE/FAST ALTER PARTITION can
        fail and leave the table unusable".
     @ sql/sql_table.cc
        Ensure that in cases when .FRM for partitioned table is
        created without creating table in storage engine (e.g.
        during "fast" ALTER TABLE) we still open table definition. 
        This allows to check that definition of created table/.FRM 
        is compatible with its partitioning function.
[19 Nov 2010 7:27] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

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

3193 Dmitry Lenev	2010-11-19
      Fix for bug #57985 "ONLINE/FAST ALTER PARTITION can fail and 
      leave the table unusable".
       
      Failing ALTER statement on partitioned table could have left
      this table in an unusable state. This has happened in cases
      when ALTER was executed using "fast" algorithm, which doesn't 
      involve copying of data between old and new versions of table, 
      and the resulting new table was incompatible with partitioning
      function in some way.
       
      The problem stems from the fact that discrepancies between new 
      table definition and partitioning function are discovered only 
      when the table is opened. In case of "fast" algorithm this has
      happened too late during ALTER's execution, at the moment when
      all changes were already done and couldn't have been reverted.
       
      In the cases when "slow" algorithm, which copies data, is used 
      such discrepancies are detected at the moment new table
      definition is opened implicitly when new version of table is
      created in storage engine. As result ALTER is aborted before 
      any changes to table were done.
       
      This fix tries to address this issue by ensuring that "fast"
      algorithm behaves similarly to "slow" algorithm and checks
      compatibility between new definition and partitioning function 
      by trying to open new definition after .FRM file for it has 
      been created.
       
      Long term we probably should implement some way to check
      compatibility between partitioning function and new table
      definition which won't involve opening it, as this should
      allow much cleaner fix for this problem.
     @ mysql-test/r/partition_innodb.result
        Added test for bug #57985 "ONLINE/FAST ALTER PARTITION can
        fail and leave the table unusable".
     @ mysql-test/t/partition_innodb.test
        Added test for bug #57985 "ONLINE/FAST ALTER PARTITION can
        fail and leave the table unusable".
     @ sql/sql_table.cc
        Ensure that in cases when .FRM for partitioned table is
        created without creating table in storage engine (e.g.
        during "fast" ALTER TABLE) we still open table definition. 
        This allows to check that definition of created table/.FRM 
        is compatible with its partitioning function.
[5 Dec 2010 12:41] Bugs System
Pushed into mysql-trunk 5.6.1 (revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (version source revid:alexander.nozdrin@oracle.com-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)
[14 Dec 2010 20:12] Paul Dubois
Noted in 5.5.8 changelog.

"Fast" ALTER TABLE operations (that do not involve a table copy) on a
partitioned table could leave the table in an unusable state.
[16 Dec 2010 22:30] Bugs System
Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)