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