MySQL Bugs: #36585: Trigger hangs, if fired on database being restored.

Bug #36585	Trigger hangs, if fired on database being restored.
Submitted:	8 May 2008 4:05	Modified:	20 Aug 2008 16:24
Reporter:	Hema Sridharan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Backup	Severity:	S3 (Non-critical)
Version:	mysql-6.0-backup	OS:	Linux
Assigned to:	Hema Sridharan	CPU Architecture:	Any

Description:
1) I create 2 databases db1 and db2 and tables in the databases.
2) I create Trigger in db1 in such a way that it will fire in table of other database db2.
3) Here I am creating database of size (33K), so that Restore will not complete immediately and we can utilize this RESTORE time to fire trigger in the table that is being restored.
4) During RESTORE or after aborting RESTORE, if I attempt to fire a trigger on the database that is being restored, the operation hangs.

How to repeat:
CREATE DATABASE db1;
USE db1;
CREATE TABLE t1(id int, letter char(20)); 
CREATE TABLE t2(a char(20));

CREATE DATABASE db2;
USE db2;
CREATE TABLE t3(id int, name char(30));
CREATE TRIGGER trg AFTER INSERT ON t3 FOR EACH ROW  BEGIN 
   INSERT INTO db1.t2 VALUES('*');
 END;

Note:Insert many data entries in table db1.t1. In such case restore will not complete soon and we can fire trigger "trg" in table t2 during restore time.

BACKUP DATABASE db1 to '/tmp/b2';
DROP DATABASE db1;

RESTORE FROM '/tmp/b2';
....

Restore of db1 goes on and on. Now go to another connection and fire trigger by inserting values in table db2.t3, which inturn inserts values in db1.t2

connection 2:
SHOW DATABASES;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| db2                |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

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

mysql>insert into db2.t3 values(101,'aa'),(102,'bb');
....

Trigger hangs!
 
Note: Here if trigger fired on any database other than the one being restored,the hang won't be noticed

I wonder what does that mean that "trigger hangs"? Does it hang for the duration of the RESTORE only, or it hangs forever. In the latter case does the server crash? If not, what is shown in I_S.PROCESSLIST (can be accessed from another connection).

If trigger hangs only for the duration of RESTORE then it is expected behaviour. RESTORE operation write-locks all tables being restored and then any other statement which wants to change data in these tables should hang until locks are removed at the end of RESTORE.

The trigger hanging (i.e the insert/update operations) was
permanent even after completion of Restore operation. Eventually it was
not possible to perform any operations after Restore, as all the
statements used to hang. This was the behavior when I filed the defect.