MySQL Bugs: #64041: Replication failure on concurrent REMOVE PARTITIONING and OPTIMIZE PARTITION

Bug #64041	Replication failure on concurrent REMOVE PARTITIONING and OPTIMIZE PARTITION
Submitted:	16 Jan 2012 12:13	Modified:	16 Jan 2012 13:43
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.1.60, 5.5.20, 5.6.4	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In the provided test case, REMOVE PARTITIONING is run in parallel with OPTIMIZE PARTITION. On master, OPTIMIZE partition starts first and doesn't cause an error, but in the binary log it is written after REMOVE PARTITIONING, and it makes SQL slave thread abort with ER_PARTITION_MGMT_ON_NONPARTITIONED.

It's only reproducible with InnoDB tables, so it might be related to optimize not being supported and executed as recreate + analyze instead:

CREATE TABLE t (a INT) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 4;
ALTER TABLE t OPTIMIZE PARTITION p1;
ALTER TABLE t REMOVE PARTITIONING;
Table   Op      Msg_type        Msg_text
test.t  optimize        note    Table does not support optimize, doing recreate + analyze instead
test.t  optimize        status  OK

SHOW BINLOG EVENTS IN 'master-bin.000001';
Log_name        Pos     Event_type      Server_id       End_log_pos     Info
master-bin.000001       4       Format_desc     1       106     Server ver: 5.1.60-log, Binlog ver: 4
master-bin.000001       106     Query   1       242     use `test`; CREATE TABLE t (a INT) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 4
master-bin.000001       242     Query   1       338     use `test`; ALTER TABLE t REMOVE PARTITIONING
master-bin.000001       338     Query   1       436     use `test`; ALTER TABLE t OPTIMIZE PARTITION p1

Last_SQL_Error  Error 'Partition management on a not partitioned table is not possible' on query. Default database: 'test'. Query: 'ALTER TABLE t OPTIMIZE PARTITION p1'

How to repeat:
--source include/have_innodb.inc
--source include/master-slave.inc
CREATE TABLE t (a INT) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 4;
--connection master1
--send
ALTER TABLE t OPTIMIZE PARTITION p1;
--connection master
ALTER TABLE t REMOVE PARTITIONING;
--connection master1
--reap
--sync_slave_with_master

Thank you for the bug report. Verified on Mac OS X:

macbook-pro:mysql-test openxs$ ./mtr bug64041
Logging: ./mtr  bug64041
120116 15:41:00 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/D7vxrmNv7k/ is case insensitive
120116 15:41:00 [Note] Plugin 'FEDERATED' is disabled.
MySQL Version 5.5.20
Checking supported features...
 - skipping ndbcluster
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
 - adding combinations for rpl
vardir: /Users/openxs/dbs/5.5/mysql-test/var
Checking leftover processes...
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.5/mysql-test/var'...
Installing system database...
Using server port 59786

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

TEST                                      RESULT   TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.bug64041 'mix'                       [ fail ]
        Test ended at 2012-01-16 15:41:19

CURRENT_TEST: rpl.bug64041
=== SHOW MASTER STATUS ===
---- 1. ----
File	slave-bin.000001
Position	339
Binlog_Do_DB	
Binlog_Ignore_DB	
==========================

=== SHOW SLAVE STATUS ===
---- 1. ----
Slave_IO_State	Waiting for master to send event
Master_Host	127.0.0.1
Master_User	root
Master_Port	13000
Connect_Retry	1
Master_Log_File	master-bin.000001
Read_Master_Log_Pos	437
Relay_Log_File	slave-relay-bin.000002
Relay_Log_Pos	486
Relay_Master_Log_File	master-bin.000001
Slave_IO_Running	Yes
Slave_SQL_Running	No
Replicate_Do_DB	
Replicate_Ignore_DB	
Replicate_Do_Table	
Replicate_Ignore_Table	
Replicate_Wild_Do_Table	
Replicate_Wild_Ignore_Table	
Last_Errno	1505
Last_Error	Error 'Partition management on a not partitioned table is not possible' on query. Default database: 'test'. Query: 'ALTER TABLE t OPTIMIZE PARTITION p1'
Skip_Counter	0
Exec_Master_Log_Pos	339
Relay_Log_Space	740
Until_Condition	None
Until_Log_File	
Until_Log_Pos	0
Master_SSL_Allowed	No
Master_SSL_CA_File	
Master_SSL_CA_Path	
Master_SSL_Cert	
Master_SSL_Cipher	
Master_SSL_Key	
Seconds_Behind_Master	
Master_SSL_Verify_Server_Cert	No
Last_IO_Errno	0
Last_IO_Error	
Last_SQL_Errno	1505
Last_SQL_Error	Error 'Partition management on a not partitioned table is not possible' on query. Default database: 'test'. Query: 'ALTER TABLE t OPTIMIZE PARTITION p1'
Replicate_Ignore_Server_Ids	
Master_Server_Id	1
=========================

=== SHOW PROCESSLIST ===
---- 1. ----
Id	2
User	root
Host	localhost:59798
db	test
Command	Sleep
Time	1
State	
Info	
---- 2. ----
Id	3
User	root
Host	localhost:59799
db	test
Command	Sleep
Time	3
State	
Info	
---- 3. ----
Id	4
User	system user
Host	
db	
Command	Connect
Time	1
State	Waiting for master to send event
Info	
---- 4. ----
Id	6
User	root
Host	localhost:59805
db	test
Command	Query
Time	0
State	
Info	SHOW PROCESSLIST
---- 5. ----
Id	7
User	root
Host	localhost:59806
db	test
Command	Sleep
Time	1
State	
Info	
========================

analyze: sync_with_master
mysqltest: At line 11: sync_slave_with_master failed: 'select master_pos_wait('master-bin.000001', 437, 300)' returned NULL indicating slave SQL thread failure

The result from queries just before the failure was:
include/master-slave.inc
[connection master]
CREATE TABLE t (a INT) ENGINE = InnoDB PARTITION BY HASH (a) PARTITIONS 4;
ALTER TABLE t OPTIMIZE PARTITION p1;
ALTER TABLE t REMOVE PARTITIONING;
Table	Op	Msg_type	Msg_text
test.t	optimize	note	Table does not support optimize, doing recreate + analyze instead
test.t	optimize	status	OK
...