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.

1 files changed, 21 insertions, 0 deletions

diff --git a/mysql-test/t/partition_innodb.test b/mysql-test/t/partition_innodb.test
index 3e9ac2ce2b5..ddc2812f617 100644
--- a/mysql-test/t/partition_innodb.test
+++ b/mysql-test/t/partition_innodb.test
@@ -569,3 +569,24 @@ SET SESSION sql_mode = 'NO_ZERO_DATE';
 OPTIMIZE TABLE t1;
 SET SESSION sql_mode = @old_mode;
 DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#57985 "ONLINE/FAST ALTER PARTITION can fail and leave the
+--echo #            table unusable".
+--echo #
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+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);
+--echo # The below ALTER should fail. It should leave the
+--echo # table in its original, non-corrupted, usable state.
+--error ER_UNIQUE_KEY_NEED_ALL_FIELDS_IN_PF
+ALTER TABLE t1 ADD UNIQUE KEY (b);
+--echo # The below statements should succeed, as ALTER should
+--echo # have left table intact.
+SHOW CREATE TABLE t1;
+SELECT * FROM t1;
+DROP TABLE t1;