MySQL Bugs: #51042: REORGANIZE PARTITION can leave table in an inconsitent state in case of crash

Bug #51042	REORGANIZE PARTITION can leave table in an inconsitent state in case of crash
Submitted:	9 Feb 2010 21:33	Modified:	15 Oct 2010 15:58
Reporter:	Mattias Jonsson	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1+	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
When doing some testing with injecting crashes in REORGANIZE PARTITION the the table can be in a inconsistent state. This happens with if a crash is injected from crash_change_partition_4 to _9.

How to repeat:
Run this test with like:
./mtr --mysqld="--skip-stack-trace" --mysqld="--skip-core-file" reorg_crash

mysql-test$ cat t/reorg_crash.test 
--source include/have_partition.inc

let $DATADIR=`SELECT @@datadir`;
CREATE TABLE t1 (a INT) PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10),
 PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1), (2), (3), (4), (11), (12), (13), (14), (21), (22), (23), (24);

--list_files $DATADIR/test
SET SESSION debug="+d,crash_change_partition_4";
--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
wait
EOF 
--error 2013
ALTER TABLE t1 REORGANIZE PARTITION p1 INTO
(PARTITION p1 VALUES LESS THAN (20),
 PARTITION p2 VALUES LESS THAN MAXVALUE);
--list_files $DATADIR/test
--remove_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
restart
EOF
--enable_reconnect
--source include/wait_until_connected_again.inc
--list_files $DATADIR/test
SET SESSION debug="-d,crash_change_partition_4";
SHOW CREATE TABLE t1;
--sorted_result
SELECT * FROM t1;
DROP TABLE t1;

Suggested fix:
Fix the error handling in fast alter partitioning

Thank you for the bug report. Verified just as described:

77-52-24-143:mysql-test openxs$ ./mtr --mysqld="--skip-stack-trace" --mysqld="--skip-core-file" reorg_crash
Logging: ./mtr  --mysqld=--skip-stack-trace --mysqld=--skip-core-file reorg_crash
100210  6:20:18 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/DBu1bBfWGZ/ is case insensitive
100210  6:20:18 [Note] Plugin 'FEDERATED' is disabled.
100210  6:20:18 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.45
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 54554

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
CREATE TABLE t1 (a INT) PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10),
PARTITION p1 VALUES LESS THAN MAXVALUE);
INSERT INTO t1 VALUES (1), (2), (3), (4), (11), (12), (13), (14), (21), (22), (23),
(24);
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1.MYD
t1#P#p1.MYI
t1.frm
t1.par
SET SESSION debug="+d,crash_change_partition_4";
ALTER TABLE t1 REORGANIZE PARTITION p1 INTO
(PARTITION p1 VALUES LESS THAN (20),
PARTITION p2 VALUES LESS THAN MAXVALUE);
ERROR HY000: Lost connection to MySQL server during query
#sql-t1.frm
#sql-t1.par
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1#TMP#.MYD
t1#P#p1#TMP#.MYI
t1#P#p1.MYD
t1#P#p1.MYI
t1#P#p2#TMP#.MYD
t1#P#p2#TMP#.MYI
t1.frm
t1.par
t1#P#p0.MYD
t1#P#p0.MYI
t1#P#p1#TMP#.MYD
t1#P#p1#TMP#.MYI
t1#P#p1.MYD
t1#P#p1.MYI
t1#P#p2#TMP#.MYD
t1#P#p2#TMP#.MYI
t1.frm
t1.par
SET SESSION debug="-d,crash_change_partition_4";
main.reorg_crash                         [ fail ]
        Test ended at 2010-02-10 06:20:26

CURRENT_TEST: main.reorg_crash
mysqltest: At line 28: query 'SHOW CREATE TABLE t1' failed: 1017: Can't find file: 't1' (errno: 2)
...

Related to the fixes in bug#53676 and bug#53770.

fix for 5.5 is included in the proposed patch for bug#53676 / bug#53770 here:
http://lists.mysql.com/commits/110773

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Closing as duplicate of bug#53676.