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
Triage: Triaged: D1 (Critical) / R3 (Medium) / E3 (Medium)

[9 Feb 2010 21:33] Mattias Jonsson
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
[10 Feb 2010 4:23] Valerii Kravchuk
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)
...
[9 Jun 2010 23:21] Mattias Jonsson
Related to the fixes in bug#53676 and bug#53770.
[11 Jun 2010 0:29] Mattias Jonsson
fix for 5.5 is included in the proposed patch for bug#53676 / bug#53770 here:
http://lists.mysql.com/commits/110773
[25 Aug 2010 9:22] Bugs System
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)
[30 Aug 2010 8:30] Bugs System
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)
[30 Aug 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[15 Oct 2010 15:58] Mattias Jonsson
Closing as duplicate of bug#53676.