MySQL Bugs: #36572: During Restore, select operation from another connection hangs.

Bug #36572	During Restore, select operation from another connection hangs.
Submitted:	7 May 2008 16:39	Modified:	21 Aug 2008 13:13
Reporter:	Hema Sridharan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
1) I create Database of bigger size, so that Restore will take more time to complete.
2)I backup the database , drop it and then perform Restore.
3) While Restore is going on ,from other connection I tried to check the data contents using select * from <table_name>, the command hangs!
4)Aborted the RESTORE and performed the SELECT operation, Still
hangs.
There is no workaround for this problem other than restarting the server.

How to repeat:
connection 1:
I am restoring db2 from /tmp/b2

mysql> restore from '/tmp/b2';

Restore goes on and on!

connection 2:
While Restore is going on in connection 1, perform some operations like
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.01 sec)
This shows that database db2 is restored.

mysql> show tables from db2;
+---------------+
| Tables_in_db2 |
+---------------+
| t1            |
| t2            |
+---------------+
2 rows in set (0.01 sec)

mysql> describe t1;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| aid     | int(11)      | NO   | PRI | 0       |       |
| bid     | int(11)      | YES  |     | NULL    |       |
| balance | decimal(8,2) | YES  |     | NULL    |       |
| filler  | char(80)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

mysql> describe t2;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| letter | char(10) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)

Also objects from db2 is restored!

Now try to perform the SELECT operations and the Restore hangs!
mysql> select * from t1;

In this case RESTORE hangs!

Thank you for the bug report.

Not only the SELECT operation hangs, but also CREATE/DROP database/tables hangs . This happens even if we abort the Restore operation.

I retried with a 800MB database:

connection 1: 
restore from '/home/.../big.bak' //remote location, takes a long time

connection 2:
show tables;             // works fine, answers immediately
describe table bigtable; // works fine, answers immediately
select count(*) from bigtable; // hangs until restore completes, then answer immediately

Hence, I cannot reproduce the hang for the restore command. Further, I think this is the expected behavior unless we do not want to show the metadata to other transactions.

DDL commands are also blocked for the duration of restore, as described above. However, the command completes 4-5 seconds after restore completes/is aborted. Hence, I do not observe any long-term hang for these commands either. 

Since DDL operations are blocked during restore, I think this behavior is expected as well.

I verified this defect again and noticed that select statement(or other DDL like create, drop) completes after few mins of Restore completion/abortion. Also I understand that DDL statements will be blocked during Restore operation, just have to make sure that this is "documented" properly.

Status for backup branch: Cannot repeat.

All DDL statements are blocked for the duration of the restore command, but complete immediately after restore completes. 

All selects etc executed on the database being restored are blocked for the duration of the restore command, but complete immediately after restore completes.

Not sure what fixed this problem, but it could be bug#33414 which modifies the ddl blocker code.