MDEV-22166 CONVERT PARTITION: move out partition into a table

Syntax for CONVERT keyword

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...] |
    [partition_options]

partition_option: {
    ...
    | CONVERT PARTITION partition_name TO TABLE tbl_name
}

Examples:

    ALTER TABLE t1 CONVERT PARTITION p2 TO TABLE tp2;

New ALTER_PARTITION_CONVERT_OUT command for
fast_alter_partition_table() is done in alter_partition_convert_out()
function which basically does ha_rename_table().

Partition to extract is marked with the same flag as dropped
partition: PART_TO_BE_DROPPED. Note that we cannot have multiple
partitioning commands in one ALTER.

For DDL logging basically the principle is the same as for other
fast_alter_partition_table() commands. The only difference is that it
integrates late Atomic DDL functions and introduces additional phase
of WFRM_BACKUP_ORIGINAL. That is required for binlog consistency
because otherwise we could not revert back after WFRM_INSTALL_SHADOW
is done. And before DDL log is complete if we crash or fail the
altered table will be already new but binlog will miss that ALTER
command. Note that this is different from all other atomic DDL in that
it rolls back until the ddl_log_complete() is done even if everything
was done fully before the crash.

Test cases added to:

  parts.alter_table \
  parts.partition_debug \
  versioning.partition \
  atomic.alter_partition

3 files changed, 438 insertions, 0 deletions

diff --git a/mysql-test/suite/atomic/alter_partition.combinations b/mysql-test/suite/atomic/alter_partition.combinations
new file mode 100644
index 00000000000..898d2f2f332
--- /dev/null
+++ b/mysql-test/suite/atomic/alter_partition.combinations
@@ -0,0 +1,6 @@
+[innodb]
+innodb
+
+[myisam]
+
+[aria]
diff --git a/mysql-test/suite/atomic/alter_partition.result b/mysql-test/suite/atomic/alter_partition.result
new file mode 100644
index 00000000000..32344d7c192
--- /dev/null
+++ b/mysql-test/suite/atomic/alter_partition.result
@@ -0,0 +1,273 @@
+# Crash recovery
+create or replace procedure prepare_table()
+begin
+create or replace table t1 (x int)
+with system versioning
+partition by range(x) (
+partition p0 values less than (10),
+partition p1 values less than (20),
+partition pn values less than maxvalue);
+insert into t1 values (2), (12), (22);
+flush tables;
+end $
+# QUERY: ALTER TABLE t1 CONVERT PARTITION p1 TO TABLE tp1
+# CRASH: ddl_log_create_before_create_frm
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: ddl_log_alter_partition_after_create_frm
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: ddl_log_alter_partition_after_write_frm
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_1
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_2
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_3
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_4
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_5
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_6
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_7
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_8
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#p1.MYD
+t1#P#p1.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `p1` VALUES LESS THAN (20) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+12
+22
+# CRASH: crash_convert_partition_9
+t1#P#p0.MYD
+t1#P#p0.MYI
+t1#P#pn.MYD
+t1#P#pn.MYI
+t1.frm
+t1.par
+tp1.MYD
+tp1.MYI
+tp1.frm
+master-bin.000001	#	Query	#	#	use `test`; ALTER TABLE t1 CONVERT PARTITION p1 TO TABLE tp1
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+ PARTITION BY RANGE (`x`)
+(PARTITION `p0` VALUES LESS THAN (10) ENGINE = DEFAULT_ENGINE,
+ PARTITION `pn` VALUES LESS THAN MAXVALUE ENGINE = DEFAULT_ENGINE)
+x
+2
+22
+Table	Create Table
+tp1	CREATE TABLE `tp1` (
+  `x` int(11) DEFAULT NULL
+) ENGINE=DEFAULT_ENGINE DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING
+x
+12
+Warnings:
+Note	1051	Unknown table 'test.t1'
diff --git a/mysql-test/suite/atomic/alter_partition.test b/mysql-test/suite/atomic/alter_partition.test
new file mode 100644
index 00000000000..9398b886507
--- /dev/null
+++ b/mysql-test/suite/atomic/alter_partition.test
@@ -0,0 +1,159 @@
+--source include/have_partition.inc
+--source include/have_debug.inc
+--source include/have_sequence.inc
+--source include/binlog_combinations.inc
+--source include/have_binlog_format_row_or_statement.inc
+--source include/not_valgrind.inc
+
+let $default_engine=InnoDB;
+let $extra_option=;
+let $save_debug=`select @@debug_dbug`;
+
+if ($MTR_COMBINATION_MYISAM)
+{
+  let $default_engine=MyISAM;
+}
+if ($MTR_COMBINATION_ARIA)
+{
+  let $default_engine=Aria;
+  let $extra_option=transactional=1;
+}
+
+if ($MTR_COMBINATION_STMT)
+{
+  let $binlog_format=include/set_binlog_format_statement.sql;
+}
+if ($MTR_COMBINATION_ROW)
+{
+  let $binlog_format=include/set_binlog_format_row.sql;
+}
+
+--disable_query_log
+--eval set @@default_storage_engine=$default_engine
+--enable_query_log
+
+--echo # Crash recovery
+
+let $MYSQLD_DATADIR= `SELECT @@datadir`;
+
+let $crash_count= 12;
+let $crash_points='ddl_log_create_before_create_frm',
+                  'ddl_log_alter_partition_after_create_frm',
+                  'ddl_log_alter_partition_after_write_frm',
+                  'crash_convert_partition_1',
+                  'crash_convert_partition_2',
+                  'crash_convert_partition_3',
+                  'crash_convert_partition_4',
+                  'crash_convert_partition_5',
+                  'crash_convert_partition_6',
+                  'crash_convert_partition_7',
+                  'crash_convert_partition_8',
+                  'crash_convert_partition_9';
+
+#let $crash_count= 1;
+#let $crash_points= 'crash_convert_partition_8';
+
+let $statement_count= 1;
+let $statements= 'ALTER TABLE t1 CONVERT PARTITION p1 TO TABLE tp1';
+
+#let $statement_count=1;
+#let $statements='CREATE OR REPLACE TABLE t1 SELECT * from const_table';
+
+--delimiter $
+create or replace procedure prepare_table()
+begin
+  create or replace table t1 (x int)
+  with system versioning
+  partition by range(x) (
+    partition p0 values less than (10),
+    partition p1 values less than (20),
+    partition pn values less than maxvalue);
+  insert into t1 values (2), (12), (22);
+  flush tables;
+end $
+--delimiter ;
+
+let $old_debug=`select @@debug_dbug`;
+
+let $keep_include_silent=1;
+let $grep_script=ALTER;
+--disable_query_log
+
+let $r=0;
+while ($r < $statement_count)
+{
+  inc $r;
+  let $statement=`select ELT($r, $statements)`;
+  --echo # QUERY: $statement
+
+  let $c=0;
+  while ($c < $crash_count)
+  {
+    inc $c;
+    let $crash=`select ELT($c, $crash_points)`;
+
+    --eval set @@default_storage_engine=$default_engine
+    call prepare_table;
+    if (!$c)
+    {
+      lock tables t1 write;
+    }
+
+    --source $binlog_format
+
+    RESET MASTER;
+    --exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+    --disable_reconnect
+    --eval set @@debug_dbug="+d,$crash",@debug_crash_counter=1
+    let $errno=0;
+    --error 0,2013
+    --eval $statement;
+    let $error=$errno;
+    --enable_reconnect
+    --source include/wait_until_connected_again.inc
+    --disable_query_log
+    --eval set @@debug_dbug="$old_debug"
+
+    if ($error == 0)
+    {
+      --echo # NO CRASH: $crash
+    }
+    if ($error)
+    {
+      --echo # CRASH: $crash
+    }
+    # Check which tables still exists
+    --replace_result MAI MYI MAD MYD
+    --list_files $MYSQLD_DATADIR/test t*
+    --replace_regex /backup-\d+/backup/
+    --list_files $MYSQLD_DATADIR/test *sql*
+    --remove_files_wildcard $MYSQLD_DATADIR/test *sql-backup-*
+
+    --let $binlog_file=master-bin.000001
+    --source include/show_binlog_events.inc
+    if ($error)
+    {
+      --let $binlog_file=master-bin.000002
+      --source include/show_binlog_events.inc
+    }
+
+    --replace_result $default_engine DEFAULT_ENGINE ' PAGE_CHECKSUM=1' ''
+    show create table t1;
+    select * from t1;
+    --replace_result $default_engine DEFAULT_ENGINE ' PAGE_CHECKSUM=1' ''
+    --error 0, ER_NO_SUCH_TABLE
+    show create table tp1;
+    --error 0, ER_NO_SUCH_TABLE
+    select * from tp1;
+    # Drop the tables. The warnings will show what was dropped
+    --disable_warnings
+    drop table t1;
+    drop table if exists tp1;
+    --enable_warnings
+  }
+}
+drop table if exists t1;
+drop procedure prepare_table;
+--eval set @@debug_dbug="$save_debug"
+
+--enable_query_log