MySQL Bugs: #36533: Backup and replication don't work together

Bug #36533	Backup and replication don't work together
Submitted:	6 May 2008 13:13	Modified:	1 Dec 2008 16:14
Reporter:	Lars Thalmann	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.6	OS:	Any
Assigned to:	Chuck Bell	CPU Architecture:	Any

Description:
If replication is enabled while doing backup, there can be problems.

How to repeat:
Code inspection.

Suggested fix:
WL#4209

This is an example of what can happen.
Running this script, the structure is correctly replicated, but the data is restored only in the master. 

####
--source include/master-slave.inc
--source include/not_embedded.inc

connection master;

--disable_warnings
drop schema if exists mydb1;
create schema mydb1;
--enable_warnings

create table mydb1.t1( id int not null) engine = myisam;

insert into mydb1.t1 values (100000);

backup schema mydb1 to 'mydb1.bkp';
drop schema mydb1;
restore from 'mydb1.bkp';

--echo # records on master
select count(*) from mydb1.t1;

--echo # records on slave
connection slave;
select count(*) from mydb1.t1;

connection master;
drop schema mydb;

####

In addition to that, the slave crashes on shutdown.
(tree: mysql-6.0 ChangeSet@1.2646, 2008-05-05 16:12:14+03:00)

The slave error log reports:

080506 21:11:19 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
080506 21:11:19 [Note] Slave I/O thread killed while reading event 
080506 21:11:19 [Note] Slave I/O thread exiting, read up to log 'master-bin.000001', position 1113
080506 21:11:19 - mysqld got signal 11 ;

This bug should be fixed by WL#4209.

This bug is fixed by the WL#4209 patch:

http://lists.mysql.com/commits/57328

The correct behaviour is for the slave to stop with an error via an incident report from the master when a restore is run on a master. See WL#4209 for more details.

Note that I have created a sample test to test this behaviour (see attached).

TEST                           RESULT         TIME (ms)
-------------------------------------------------------
rpl.rpl_bug_36533 'row'        [ pass ]            795
rpl.rpl_bug_36533 'stmt'       [ pass ]            859
rpl.rpl_bug_36533 'mix'        [ pass ]            756

New test file to demonstrate proper behavior.

Attachment: rpl_bug_36533.test (application/octet-stream, text), 1.04 KiB.

Bug fixed by WL#4209 patch.

Pushed to mysql-6.0 Friday Nov 14, v6.0.9

Documentation Information:

The original problem was that if a restore was run on the master, only the metadata (CREATE statements) were replicated -- not the data. There was also the possibility that when the master issued updates to the restore database(s), the slave could crash with a packet error message.

The bug fix (and the worklog) change the code to issue an incident report when restore is run on the master forcing the slave to stop and allowing the DBA to take the appropriate action to resynchronize the restored database(s).

Noted in 6.0.9 changelog.