Bug #34759 Server crash when backing-up database with trigger

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.

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>

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.

This Bug depends on Bug#35249

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';