MySQL Bugs: #37747: Backup: restore fails if another connection tries to restore

Bug #37747	Backup: restore fails if another connection tries to restore
Submitted:	30 Jun 2008 18:01	Modified:	2 Oct 2008 7:36
Reporter:	Peter Gulutzan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	6.0.6-alpha-debug	OS:	Linux (SUSE 10 \| 32-bit)
Assigned to:	Øystein Grøvlen	CPU Architecture:	Any

Description:
I'm using mysql-6.0-backup, pulled today.

I create a table with many rows.
I backup.
I start to restore.
On another connection, I also try to restore.
Both restores fail.

How to repeat:
Start two connections. Call them T1 and T2.

Most of the instructions here are just a way of making a big table
so that RESTORE will take a long time. The contents don't matter.

On T1, say:
delimiter ;
drop table if exists t;
set @@autocommit=0;
create table t (s0 int, s1 double precision, s2 datetime);
insert into t values (0,1e-300, '1970-01-01 00:00:00');
create index i0 on t (s0);
create index i1 on t (s1);
create index i2 on t (s2);
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
insert into t select s1+1,s2+1e-300,timestampadd(second,1,s2) from t;
backup database test to '1';
restore from '1';

On T2 -- while T1 is still running -- say:
restore from '1';

Result:
T2 says
"ERROR 1626 (HY000): Can't execute this command because another BACKUP/RESTORE operation is in progress"
Okay.

T1 says:
"ERROR 1650 (HY000): Error when reading data from backup stream"
Not okay.

Thank you for the report.

Verified as described using mysql-6.0-backup tree. With mysql-6.0 tree backup crashes server.

I am not able to reproduce this.  (I also added a few more insert statements to make restore take longer.  That way I was able perform several attempts at starting another restore while the restore was running.)

This problem was probably fixed by the fix for bug#36795.

I am still able to reproduce this bug. Please use bigger database perform backup/restore operation from 2 different connections.

1) Start Backup operation from con1 first and then immediately from con2. The backup gets completed from con1 and from con2, following error message is noticed,

mysql>  backup database y to 'y1.bak';
ERROR 1627 (HY000): Can't execute this command because another BACKUP/RESTORE operation is in progress

2) Start Restore from con1 and immediately from con2. The following is noticed,

From connection con2;
mysql> restore from 'y.bak';
ERROR 1627 (HY000): Can't execute this command because another BACKUP/RESTORE operation is in progress

From connection con1;
mysql> restore from 'y.bak';
ERROR 1699 (HY000): Error when reading summary section of backup image

Therefore, I mark this bug status to verified.

I am not able to reproduce this with a 880 MB database.
To try to reproduce this, I need info about:
 - Database schema
 - Size of database
 - The timing of commands issues on the two connections

All,

me and Oystein can't reproduce it. Hema, Peter and Sven can reproduce.

I think this is related to OS/installation.

I need exactly to know how you installed the server and which system you used.

I used Ubuntu and FreeBSD 7.0, both 64bit and both installed by using compile-amd64-max-no-ndb

I meant compile-amd64-debug-max-no-ndb

I had an email discussion with Hema on this last week (Sorry for not updating the report before now).  She does not claim to be able to reproduce the original problem, but she referred to the "ERROR 1627 (HY000): Can't execute this command because another BACKUP/RESTORE operation is in progress" messages.  That you cannot run backup/restores in parallel is by design, and Hema opened BUG#39614 as a feature request.

Much has changed since Peter filed this report.  At that time, the protection against concurrent operations had several issues that has now been fixed.  However, my concern was Hema's report of "ERROR 1699 (HY000): Error when reading summary section of backup image".  Hema told me last week that she does no longer see that.

I have not seen any report from Sven.  Where can I find that?

Anyhow, since the original problem no longer appears maybe we should just close this report?

I can not repeat this too.

Hema, if you are still able to repeat please provide information requested.