MySQL Bugs: #35249: Mysql server crash for delete operation followed by backup in memory SE

Bug #35249	Mysql server crash for delete operation followed by backup in memory SE
Submitted:	12 Mar 2008 15:16	Modified:	11 Aug 2008 23:38
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:	Chuck Bell	CPU Architecture:	Any

Description:
1) Create database and table with memory storage engine.
2) Insert some rows in table.
3) Delete few rows from the table.
4) Now take backup of the database, mysql server crashes.

Mysql server crash takes place for a delete operation followed by backup.

How to repeat:
Create database try,
mysql> create database try;
Query OK, 1 row affected (0.00 sec)

mysql> use try;
Database changed
mysql> create table me(id int, ccode varchar(20))engine=memory;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into me values(1,'aa'),(2,'bb'),(3,'cc'),(4,'dd');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from me;
+------+-------+
| id   | ccode |
+------+-------+
|    1 | aa    |
|    2 | bb    |
|    3 | cc    |
|    4 | dd    |
+------+-------+
4 rows in set (0.00 sec)

mysql> delete from me where ccode='cc';
Query OK, 1 row affected (0.00 sec)

mysql> backup database try to '/space/hemat/backup_dmp/tryt';
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Thank you for the bug report.

[New Thread 1158457680 (LWP 3844)]
080312 12:48:40 [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.

Program received signal SIGABRT, Aborted.
[Switching to Thread 1158457680 (LWP 3844)]
0x0000003bfca30ec5 in raise () from /lib64/libc.so.6
(gdb)

Stack trace (for the crash)
===========================

ndbdev@ndb16:/data2/hemanew_clone/mysql-6.0-backup/extra> ./resolve_stack_dump -s /tmp/mysqld.sym -n mysqld.stack
0x87a6de safe_print_str + 278
0x87a725 print_stacktrace + 21
0x6dc09b handle_segfault + 595
0x3f4080c430 _end + 1060661000
0x3f3ff2e21d _end + 1051362549
0x3f3ff2fa1e _end + 1051368694
0x3f3ff27ae1 _end + 1051336121
0x72d377 _Z18close_thread_tableP3THDPP8st_table + 171
0x72cf17 _Z30close_cached_connection_tablesP3THDbP19st_mysql_lex_stringb + 1219
0x72d260 _Z19close_thread_tablesP3THD + 726
0xcb5589 backup_thread_for_locking + 991
0x3f4080610a _end + 1060635618
0x3f3ffc68b3 _end + 1051986827

There is a Mysql server crash for delete operation followed by BACKUP DATABASE for Default drivers(that is for Myisam, Memory storage engines)

BUG#36235 is marked as a duplicate of this one.

Here is a smallest test case with which I could repeat this problem:

CREATE TABLE t3(scode int);
INSERT INTO t3 VALUES (1);
DELETE FROM t3 WHERE scode=1;
BACKUP DATABASE test TO 'test.bak';

BACKUP fails only if a row is deleted from t3 before it is invoked.

My stack trace after the crash:

#7  0x0833cbe4 in close_thread_table (thd=0x8ddd7b0, table_ptr=0x8ddd7f8) at sql_base.cc:1422
#8  0x0833d26e in close_open_tables (thd=0x8ddd7b0) at sql_base.cc:1246
#9  0x0833d6e4 in close_thread_tables (thd=0x8ddd7b0) at sql_base.cc:1398
#10 0x088ea442 in backup_thread_for_locking (arg=0x8da0b50) at be_thread.cc:208
#11 0x400270bd in start_thread () from /lib/tls/libpthread.so.0
#12 0x402779ee in clone () from /lib/tls/libc.so.6

I got identical crash when trying to stop server which is running a BACKUP command.

This suggest the following problem. The table locking thread runs function backup_thread_for_locking(). At the end, this function waits on condition and then terminates:

193       thd->enter_cond(&locking_thd->COND_thread_wait,
194                       &locking_thd->THR_LOCK_thread,
195                       "Locking thread: holding table locks");
196       while (!thd->killed && (locking_thd->lock_state != LOCK_SIGNAL))
197         pthread_cond_wait(&locking_thd->COND_thread_wait,
198                           &locking_thd->THR_LOCK_thread);
199       thd->exit_cond("Locking thread: terminating");

When this loop terminates, the next thing which happens is a call to close_thread_tables() where assertion fails.

Now I wonder what happens if the while() loop is interrupted not because condition was signalled, but because the thread has been killed and thd->killed is set. Is it possible that in that case thread is partially destructed and this is why close_thread_tables() can not be called at that moment...

Or... the table locking thread is calling close_thread_tables() while the main backup thread is using them. That could happen during server shutdown which is detected inside table locking thread but not inside the main backup thread (BUG#35079).

The failing assertion is:

#7  0x0833cbf4 in close_thread_table (thd=0x8def048, table_ptr=0x8def090) at sql_base.cc:1422
1422      DBUG_ASSERT(!table->file || table->file->inited == handler::NONE);

and it fails because table->file is not NULL and table->file->inited equals handler::RND. This means that the table is opened now. And yes, this is one of the tables being backed up.

Fixing reviewer assignment.

This problem was not isolated to memory storage engine as suggested. It is part of a larger problem where backup cannot handle deleted rows. 

This is a fault of the default driver. A solution is proposed in BUG#36475 (a duplicate of this bug).

Patch pending.

http://lists.mysql.com/commits/46412

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/46699

ChangeSet@1.2616, 2008-05-14 12:28:33-04:00, cbell@mysql_cab_desk. +3 -0
  BUG#35249 : Mysql server crash for delete operation followed by backup in
memory SE
  
  If a table contains rows marked for deletion but have not been removed,
  backup crashes the server when the deleted row is encountered. 
  
  Cause: Default drivers do not process rows marked as deleted properly. 
  
  Patch allows default and consistent snapshot drivers to skip deleted 
  rows on backup.

Patch queued with minor changes to description and a spacing issue.

http://lists.mysql.com/commits/46699

Patch is in main.

Fix was pushed into 6.0.6.

Noted in 6.0.6 changelog.

BACKUP DATABASE caused a server crash upon encountering a table row
that has been marked for deletion but not removed.

Pushed into 6.0.6-alpha  (revid:sp1r-cbell/Chuck@mysql_cab_desk.-20080514162833-07711) (version source revid:hakan@mysql.com-20080716160034-sdexuyp3qow7zlc6) (pib:3)