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

[6 May 2008 13:13] Lars Thalmann
Description:
If replication is enabled while doing backup, there can be problems.

How to repeat:
Code inspection.

Suggested fix:
WL#4209
[7 May 2008 2:26] Giuseppe Maxia
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 ;
[21 May 2008 17:40] Chuck Bell
This bug should be fixed by WL#4209.
[29 Oct 2008 16:33] Chuck Bell
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
[29 Oct 2008 16:37] Chuck Bell
New test file to demonstrate proper behavior.

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

[31 Oct 2008 15:05] Chuck Bell
Bug fixed by WL#4209 patch.
[24 Nov 2008 12:55] Jørgen Løland
Pushed to mysql-6.0 Friday Nov 14, v6.0.9
[27 Nov 2008 17:25] Chuck Bell
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).
[1 Dec 2008 16:14] Paul DuBois
Noted in 6.0.9 changelog.