| Bug #34759 | Server crash when backing-up database with trigger | ||
|---|---|---|---|
| Submitted: | 22 Feb 2008 12:45 | Modified: | 21 May 2008 20:10 |
| Reporter: | Rafal Somla | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: Backup | Severity: | S2 (Serious) |
| Version: | 6.0.5 | OS: | Any |
| Assigned to: | Jørgen Løland | CPU Architecture: | Any |
[28 Feb 2008 17:40]
MySQL Verification Team
Thank you for the bug report.
CREATE TABLE t1(a int);
CREATE TABLE t2(a int);
delimiter ||
CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
BEGIN
DELETE FROM t2 WHERE a > 1;
END||
delimiter ;
INSERT INTO t2 VALUES (2);
INSERT INTO t1 VALUES (1);
BACKUP DATABASE test TO 'test.bak';
[miguel@mira dbs]$ 6.0b/bin/mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.5-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> use test
Database changed
mysql> CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t2(a int);
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> delimiter ||
mysql>
mysql> CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW
-> BEGIN
-> DELETE FROM t2 WHERE a > 1;
-> END||
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
mysql>
mysql> INSERT INTO t2 VALUES (2);
Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO t1 VALUES (1);
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> BACKUP DATABASE test TO 'test.bak';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[18 Mar 2008 15:08]
Hema Sridharan
Even I could notice the similar behavior in my system. But to be more precise, there is a mysql server crash when backing up database with after insert trigger event and for default blocking drivers and not for CS drivers.
[27 Mar 2008 22:31]
Hema Sridharan
This Bug depends on Bug#35249
[21 May 2008 20:09]
Øystein Grøvlen
I am not able to reproduce this. The following script succeeds for me: CREATE TABLE t1(a int); CREATE TABLE t2(a int); delimiter ||; CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW BEGIN DELETE FROM t2 WHERE a > 1; END; || delimiter ;|| INSERT INTO t2 VALUES (2); INSERT INTO t1 VALUES (1); BACKUP DATABASE test TO 'test.bak';
Description: Server crashes when backing up a database with a trigger, if that trigger fired before. Before crash server reports: > 080222 15:33:24 - mysqld got signal 6; > 080222 15:33:24 [Note] Backup: Backing up selected databases > 080222 15:33:24 [ERROR] Backup: Error when polling Default backup driver for its image data > mysqld: sql_base.cc:1418: bool close_thread_table(THD*, TABLE**): Assertion `!table->file || table->file->inited == handler::NONE' failed. > 080222 15:33:24 - mysqld got signal 6; Problem happens when the table locking thread closes tables. Here is the call stack: > #6 0x401ce05f in __assert_fail () from /lib/tls/libc.so.6 > #7 0x08336c72 in close_thread_table (thd=0x8e3fcf0, table_ptr=0x8e3fd38) at sql_base.cc:1418 > #8 0x083372fc in close_open_tables (thd=0x8e3fcf0) at sql_base.cc:1245 > #9 0x0833775b in close_thread_tables (thd=0x8e3fcf0) at sql_base.cc:1394 > #10 0x088d8935 in backup_thread_for_locking (arg=0x8d872b8) at be_thread.cc:207 Here is the place in sql_base.cc where assertion is violated: > 1415 TABLE *table= *table_ptr; > 1416 DBUG_ENTER("close_thread_table"); > 1417 DBUG_ASSERT(table->key_read == 0); > 1418 DBUG_ASSERT(!table->file || table->file->inited == handler::NONE); Pointer 'table' points at TABLE structure for table t2 (see "How to repeat" below). Looks like the trigger which has fired before and operated on t2, left the table in a state which the locking thread can't handle correctly. How to repeat: Execute the following test case: > CREATE TABLE t1(a int); > CREATE TABLE t2(a int); > > delimiter ||; > > CREATE TRIGGER tr1 AFTER INSERT ON t1 FOR EACH ROW > BEGIN > DELETE FROM t2 WHERE a > 1; > END; > || > > delimiter ;|| > > INSERT INTO t2 VALUES (2); > INSERT INTO t1 VALUES (1); > > BACKUP DATABASE test TO 'test.bak'; On my machine it crashes producing the above stack. If I remove trigger definition it works OK. If instead of (2) I insert (1) into t2, so that the trigger doesn't change t2 even when fired, then everything works OK.