MySQL Bugs: #57232: Possible corruption during UNDO phase at startup after crash recovery

Bug #57232	Possible corruption during UNDO phase at startup after crash recovery
Submitted:	4 Oct 2010 22:44	Modified:	30 Nov 2010 23:38
Reporter:	Sunny Bains	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	All	OS:	Any
Assigned to:	Sunny Bains	CPU Architecture:	Any

Description:
1. Tx1 - Resurrected transaction has an implicit lock on a record on table T - rollback of Tx1 not started yet

2. Tx2 - User transaction converts the implicit lock to an explicit record  lock. dict_table_t::n_mysql_handles_opened == 1

3. Tx2 - is rolled back for some reason - timeout or deadlock. dict_table_t::n_mysql_handles_opened == 0

4. Tx3 - Drops T, because there are no (MySQL) open handles on T 

5. There is a now a dangling reference to an instance of index/table T in a record lock

How to repeat:
Difficult to repeat. The assertion that is triggered is not in the existing code it has been added as part of another feature.

Suggested fix:
Add a record lock reference count in dict_table_t. Check this counter before dropping the table. The table should only be dropped when this reference count is zero.

This could have an impact on how we fix bug# 56655.  This bug will only
affect users if they connect to the server and start DML/DDL before the UNDO
phase of recovered transactions is completed..

A simpler fix: While trx_rollback_or_clean_recovered() is running, defer all DROP TABLE and TRUNCATE TABLE to the background queue. Disadvantage: If the server was taken offline because of running out of disk space during a bulk operation, the DBA would be unable to reclaim disk space until after the bulk operation has been rolled back.

The record-lock reference counting idea looks good, provided that it will not cause contention. One idea is to protect the reference count by atomic memory access primitives instead of a mutex or rw-lock.

I don't think TRUNCATE is affected by this because we reuse the
dict_table_t* instance but drop and create a new tablespace.

How can the rec lock count cause contention?  It will simply
be protected by the lock/kernel mutex. I see no reason to
use atomics. We increment when we add a record lock to the
hash table and decrement when we remove from the hash table.
We already have the lock/kernel mutex for both operations.

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/121290

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/121291

Sorry, my two commits are not fixing this bug, but fixing bugs in the patch that Sunny committed earlier. Sunny's patch was primarily addressing Bug #20877.

Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:alexander.nozdrin@oracle.com-20101113152540-gxro4g0v29l27f5x) (pib:21)

Pushed into mysql-5.5 5.5.9 (revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (version source revid:jonathan.perkin@oracle.com-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)

What is the text of the assertion error message? Something that we can see and use to recognise this bug as the fixed cause.

James, it is this code in 5.6 - row_drop_table_for_mysql().

       if (table->n_ref_count == 0) {
                lock_remove_all_on_table(table, TRUE);
                ut_a(table->n_rec_locks == 0);