Bug#53676: Unexpected errors and possible table

           corruption on ADD PARTITION and LOCK TABLE
Bug#53770: Server crash at handler.cc:2076 on
           LOAD DATA after timed out COALESCE PARTITION

5.5 fix for:
Bug#51042: REORGANIZE PARTITION can leave table in an
           inconsistent state in case of crash
Needs to be back-ported to 5.1

5.5 fix for:
Bug#50418: DROP PARTITION does not interact with
           transactions

Main problem was non-persistent operations done
before meta-data lock was taken (53770+53676).
And 53676 needed to keep the table/partitions opened and locked
while copying the data to the new partitions.

Also added thorough tests to spot some additional bugs
in the ddl_log code, which could result in bad state
between the .frm and partitions.

Collapsed patch, includes all fixes required from the reviewers.

mysql-test/r/partition_innodb.result:
  updated result with new test
mysql-test/suite/parts/inc/partition_crash.inc:
  crash test include file
mysql-test/suite/parts/inc/partition_crash_add.inc:
  test all states in fast_alter_partition_table
  ADD PARTITION branch
mysql-test/suite/parts/inc/partition_crash_change.inc:
  test all states in fast_alter_partition_table
  CHANGE PARTITION branch
mysql-test/suite/parts/inc/partition_crash_drop.inc:
  test all states in fast_alter_partition_table
  DROP PARTITION branch
mysql-test/suite/parts/inc/partition_fail.inc:
  recovery test including injecting errors
mysql-test/suite/parts/inc/partition_fail_add.inc:
  test all states in fast_alter_partition_table
  ADD PARTITION branch
mysql-test/suite/parts/inc/partition_fail_change.inc:
  test all states in fast_alter_partition_table
  CHANGE PARTITION branch
mysql-test/suite/parts/inc/partition_fail_drop.inc:
  test all states in fast_alter_partition_table
  DROP PARTITION branch
mysql-test/suite/parts/inc/partition_mgm_crash.inc:
  include file that runs all crash and failure injection tests.
mysql-test/suite/parts/r/partition_debug_innodb.result:
  new test result file
mysql-test/suite/parts/r/partition_debug_myisam.result:
  new test result file
mysql-test/suite/parts/r/partition_special_innodb.result:
  updated result
mysql-test/suite/parts/r/partition_special_myisam.result:
  updated result
mysql-test/suite/parts/t/partition_debug_innodb-master.opt:
  opt file for using with crashing tests of partitioned innodb
mysql-test/suite/parts/t/partition_debug_innodb.test:
  partitioned innodb test that require debug builds
mysql-test/suite/parts/t/partition_debug_myisam-master.opt:
  opt file for using with crashing tests of partitioned myisam
mysql-test/suite/parts/t/partition_debug_myisam.test:
  partitioned myisam test that require debug builds
mysql-test/suite/parts/t/partition_special_innodb-master.opt:
  added innodb-file-per-table to easier verify partition status on disk
mysql-test/suite/parts/t/partition_special_innodb.test:
  added test case
mysql-test/suite/parts/t/partition_special_myisam.test:
  added test case
mysql-test/t/partition_innodb.test:
  added test case
sql/sql_base.cc:
  Moved alter_close_tables to sql_partition.cc
sql/sql_base.h:
  removed some non existing and duplicated functions.
sql/sql_partition.cc:
  fast_alter_partition_table:
  Spletted abort_and_upgrad_lock_and_close_table
  to its parts (wait_while_table_is_used and
  alter_close_tables) and always have
  wait_while_table_is_used before any persistent
  operations (including logs, which will be executed
  on failure) and alter_close_tables after
  create/read/write operations and before
  drop operations.
  
  moved alter_close_tables here from sql_base.cc
  
  Added error injections for better test coverage.
  
  write_log_final_change_partition:
  fixed a log_entry linking bug (delete_frm was not
  linked to change/drop partition)
  and drop partition must be executed before
  change partition (change partition can rename a
  partition to an old name, like REORG p1 INTO (p1,p2).
  
  write_log_add_change_partition:
  need to use drop_frm first, and relinking that entry
  and reusing its execute entry.
sql/sql_table.cc:
  added initialization of next_active_log_entry.
sql/table.h:
  removed a duplicate declaration.

9 files changed, 286 insertions, 0 deletions

diff --git a/mysql-test/suite/parts/inc/partition_crash.inc b/mysql-test/suite/parts/inc/partition_crash.inc
new file mode 100644
index 00000000000..f18b8728784
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_crash.inc
@@ -0,0 +1,28 @@
+# Include file to decrease test code duplication
+
+--eval $create_statement
+--eval $insert_statement
+--echo # State before crash
+--replace_result #p# #P#
+--list_files $DATADIR/test
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM t1;
+--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--disable_reconnect
+# CR_SERVER_LOST
+--error 2013
+--eval $crash_statement
+--echo # State after crash (before recovery)
+--replace_regex /sqlx.*\./sqlx-nnnn_nnnn./ /#p#/#P#/
+--list_files $DATADIR/test
+--exec echo "restart" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+--echo # State after crash recovery
+--replace_result #p# #P#
+--list_files $DATADIR/test
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/parts/inc/partition_crash_add.inc b/mysql-test/suite/parts/inc/partition_crash_add.inc
new file mode 100644
index 00000000000..c7cec9c8791
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_crash_add.inc
@@ -0,0 +1,33 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 ADD PARTITION (LIST/RANGE PARTITIONING).
+--echo # Crash testing ADD PARTITION
+SET SESSION debug="+d,crash_add_partition_1";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_1";
+SET SESSION debug="+d,crash_add_partition_2";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_2";
+SET SESSION debug="+d,crash_add_partition_3";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_3";
+SET SESSION debug="+d,crash_add_partition_4";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_4";
+SET SESSION debug="+d,crash_add_partition_5";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_5";
+SET SESSION debug="+d,crash_add_partition_6";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_6";
+SET SESSION debug="+d,crash_add_partition_7";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_7";
+SET SESSION debug="+d,crash_add_partition_8";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_8";
+SET SESSION debug="+d,crash_add_partition_9";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_9";
+SET SESSION debug="+d,crash_add_partition_10";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_add_partition_10";
diff --git a/mysql-test/suite/parts/inc/partition_crash_change.inc b/mysql-test/suite/parts/inc/partition_crash_change.inc
new file mode 100644
index 00000000000..2a6630b6537
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_crash_change.inc
@@ -0,0 +1,40 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 COALESCE/REBUILD/REORGANIZE PARTITION.
+--echo # Test change partition (REORGANIZE/REBUILD/COALESCE
+--echo # or ADD HASH PARTITION).
+SET SESSION debug="+d,crash_change_partition_1";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_1";
+SET SESSION debug="+d,crash_change_partition_2";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_2";
+SET SESSION debug="+d,crash_change_partition_3";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_3";
+SET SESSION debug="+d,crash_change_partition_4";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_4";
+SET SESSION debug="+d,crash_change_partition_5";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_5";
+SET SESSION debug="+d,crash_change_partition_6";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_6";
+SET SESSION debug="+d,crash_change_partition_7";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_7";
+SET SESSION debug="+d,crash_change_partition_8";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_8";
+SET SESSION debug="+d,crash_change_partition_9";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_9";
+SET SESSION debug="+d,crash_change_partition_10";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_10";
+SET SESSION debug="+d,crash_change_partition_11";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_11";
+SET SESSION debug="+d,crash_change_partition_12";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_change_partition_12";
diff --git a/mysql-test/suite/parts/inc/partition_crash_drop.inc b/mysql-test/suite/parts/inc/partition_crash_drop.inc
new file mode 100644
index 00000000000..0e35e126f4e
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_crash_drop.inc
@@ -0,0 +1,30 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 DROP PARTITION.
+--echo # Test DROP PARTITION
+SET SESSION debug="+d,crash_drop_partition_1";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_1";
+SET SESSION debug="+d,crash_drop_partition_2";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_2";
+SET SESSION debug="+d,crash_drop_partition_3";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_3";
+SET SESSION debug="+d,crash_drop_partition_4";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_4";
+SET SESSION debug="+d,crash_drop_partition_5";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_5";
+SET SESSION debug="+d,crash_drop_partition_6";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_6";
+SET SESSION debug="+d,crash_drop_partition_7";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_7";
+SET SESSION debug="+d,crash_drop_partition_8";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_8";
+SET SESSION debug="+d,crash_drop_partition_9";
+--source suite/parts/inc/partition_crash.inc
+SET SESSION debug="-d,crash_drop_partition_9";
diff --git a/mysql-test/suite/parts/inc/partition_fail.inc b/mysql-test/suite/parts/inc/partition_fail.inc
new file mode 100644
index 00000000000..6942b40042a
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_fail.inc
@@ -0,0 +1,18 @@
+# Include file to decrease test code duplication
+
+--eval $create_statement
+--eval $insert_statement
+--echo # State before failure
+--list_files $DATADIR/test
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM t1;
+--disable_abort_on_error
+--eval $fail_statement
+--enable_abort_on_error
+--echo # State after failure
+--list_files $DATADIR/test
+SHOW CREATE TABLE t1;
+--sorted_result
+SELECT * FROM t1;
+DROP TABLE t1;
diff --git a/mysql-test/suite/parts/inc/partition_fail_add.inc b/mysql-test/suite/parts/inc/partition_fail_add.inc
new file mode 100644
index 00000000000..71d09792fd1
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_fail_add.inc
@@ -0,0 +1,33 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 ADD PARTITION (LIST/RANGE PARTITIONING).
+--echo # Error recovery testing ADD PARTITION
+SET SESSION debug="+d,fail_add_partition_1";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_1";
+SET SESSION debug="+d,fail_add_partition_2";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_2";
+SET SESSION debug="+d,fail_add_partition_3";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_3";
+SET SESSION debug="+d,fail_add_partition_4";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_4";
+SET SESSION debug="+d,fail_add_partition_5";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_5";
+SET SESSION debug="+d,fail_add_partition_6";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_6";
+SET SESSION debug="+d,fail_add_partition_7";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_7";
+SET SESSION debug="+d,fail_add_partition_8";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_8";
+SET SESSION debug="+d,fail_add_partition_9";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_9";
+SET SESSION debug="+d,fail_add_partition_10";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_add_partition_10";
diff --git a/mysql-test/suite/parts/inc/partition_fail_change.inc b/mysql-test/suite/parts/inc/partition_fail_change.inc
new file mode 100644
index 00000000000..390dea34fab
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_fail_change.inc
@@ -0,0 +1,40 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 COALESCE/REBUILD/REORGANIZE PARTITION.
+--echo # Error recovery change partition (REORGANIZE/REBUILD/COALESCE
+--echo # or ADD HASH PARTITION).
+SET SESSION debug="+d,fail_change_partition_1";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_1";
+SET SESSION debug="+d,fail_change_partition_2";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_2";
+SET SESSION debug="+d,fail_change_partition_3";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_3";
+SET SESSION debug="+d,fail_change_partition_4";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_4";
+SET SESSION debug="+d,fail_change_partition_5";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_5";
+SET SESSION debug="+d,fail_change_partition_6";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_6";
+SET SESSION debug="+d,fail_change_partition_7";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_7";
+SET SESSION debug="+d,fail_change_partition_8";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_8";
+SET SESSION debug="+d,fail_change_partition_9";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_9";
+SET SESSION debug="+d,fail_change_partition_10";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_10";
+SET SESSION debug="+d,fail_change_partition_11";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_11";
+SET SESSION debug="+d,fail_change_partition_12";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_change_partition_12";
diff --git a/mysql-test/suite/parts/inc/partition_fail_drop.inc b/mysql-test/suite/parts/inc/partition_fail_drop.inc
new file mode 100644
index 00000000000..54a57ba845f
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_fail_drop.inc
@@ -0,0 +1,30 @@
+# To be used with partition mgm commands like
+# ALTER TABLE t1 DROP PARTITION.
+--echo # Error recovery DROP PARTITION
+SET SESSION debug="+d,fail_drop_partition_1";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_1";
+SET SESSION debug="+d,fail_drop_partition_2";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_2";
+SET SESSION debug="+d,fail_drop_partition_3";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_3";
+SET SESSION debug="+d,fail_drop_partition_4";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_4";
+SET SESSION debug="+d,fail_drop_partition_5";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_5";
+SET SESSION debug="+d,fail_drop_partition_6";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_6";
+SET SESSION debug="+d,fail_drop_partition_7";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_7";
+SET SESSION debug="+d,fail_drop_partition_8";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_8";
+SET SESSION debug="+d,fail_drop_partition_9";
+--source suite/parts/inc/partition_fail.inc
+SET SESSION debug="-d,fail_drop_partition_9";
diff --git a/mysql-test/suite/parts/inc/partition_mgm_crash.inc b/mysql-test/suite/parts/inc/partition_mgm_crash.inc
new file mode 100644
index 00000000000..2148d7ed1b6
--- /dev/null
+++ b/mysql-test/suite/parts/inc/partition_mgm_crash.inc
@@ -0,0 +1,34 @@
+# collection of tests which crashes the server and checks recovery.
+# also using error injection to test recovery of failures.
+# uses $DATADIR and $engine
+
+--echo #
+--echo # Bug#53676: Unexpected errors and possible table corruption on
+--echo # ADD PARTITION and LOCK TABLE
+--echo # Bug#53770: Server crash at handler.cc:2076 on LOAD DATA
+--echo # after timed out COALESCE PARTITION
+--echo # Extended crash recovery testing of fast_alter_partition_table.
+call mtr.add_suppression("Attempting backtrace. You can use the following information to find out");
+
+let $create_statement= CREATE TABLE t1 (a INT, b VARCHAR(64))
+ENGINE = $engine
+PARTITION BY LIST (a)
+(PARTITION p0 VALUES IN (0,1,2,3,4,5,6,7,8,9),
+ PARTITION p10 VALUES IN (11,12,13,14,15,16,17,18,19));
+let $insert_statement= INSERT INTO t1 VALUES (1, "Original from partition p0"), (2, "Original from partition p0"), (3, "Original from partition p0"), (4, "Original from partition p0"), (11, "Original from partition p1"), (12, "Original from partition p1"), (13, "Original from partition p1"), (14, "Original from partition p1");
+
+let $crash_statement= ALTER TABLE t1 ADD PARTITION
+(PARTITION p20 VALUES IN (20,21,22,23,24,25,26,27,28,29));
+--source suite/parts/inc/partition_crash_add.inc
+let $fail_statement= $crash_statement;
+--source suite/parts/inc/partition_fail_add.inc
+let $crash_statement= ALTER TABLE t1 DROP PARTITION p10;
+--source suite/parts/inc/partition_crash_drop.inc
+let $fail_statement= $crash_statement;
+--source suite/parts/inc/partition_fail_drop.inc
+let $crash_statement= ALTER TABLE t1 REORGANIZE PARTITION p10 INTO
+(PARTITION p10 VALUES IN (10,11,12,13,14,15,16,17,18,19),
+ PARTITION p20 VALUES IN (20,21,22,23,24,25,26,27,28,29));
+--source suite/parts/inc/partition_crash_change.inc
+let $fail_statement= $crash_statement;
+--source suite/parts/inc/partition_fail_change.inc