Bug #35352 If InnoDB crashes with UNDO slots full error the error persists on restart

• Submitted: 17 Mar 2008 22:40
• Modified: 13 Aug 2008 17:45
• Reporter: Sunny Bains
• Status: Closed
• Category: Server: InnoDB
• Severity: S1 (Critical)
• Version: 5.0 5.1 6.0
• OS: Any
• Assigned to: Timothy Smith
• Target Version: 5.0+
• Triage: D3 (Medium)

[17 Mar 2008 22:40] Sunny Bains

Description:
If the server crashes with an out of UNDO slots error and the used slots are not used
during rollback they are put on the UNDO slots cached list. There are two types of UNDO
cache lists. INSERT and UPDATE UNDO cache lists. If all the UNDO slots end up in one of
the lists then all transactions that try and request for the other type of UNDO slot will
fail with an out of slot error internally but "table is full" error externally (on the
client side).

How to repeat:
Related to Bug#26590

<?
        $dbs=array();
        $i=1024;
        while($i--) {
                $db=$dbs[$i]=mysql_connect("localhost","root","",1);
                mysql_select_db("test",$db);
                mysql_query("BEGIN",$db) or die(mysql_error());
                mysql_query("INSERT INTO yt VALUES ($i)",$db) or die(mysql_error());
        }
        sleep(600);
?>

Suggested fix:
If there are empty slots in any cached list we steal from there to fulfill the request.

[18 Mar 2008 5:11] Sunny Bains

There is a workaround however:

For the case where all the 1024 slots are reserved by cached INSERT UNDO. In this case
INSERTs will work but UPDATEs will not.
 
CREATE TABLE test.t(c int) ENGINE=InnoDB;
INSERT INTO t VALUES(0),(0),(0), (0), (0);
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
INSERT INTO test.t SELECT * FROM test.t;
...
DROP TABLE test.t;

For the case where all the 1024 slots are reserved by cached UPDATE UNDO. In this case
UPDATEs will work but INSERTs will not. This is a little trickier as data must already
exist that can be DELETEd and/or UPDATEd.

[19 Mar 2008 14:24] Heikki Tuuri

Emailed a new simpler patch suggestion.

[19 Mar 2008 15:24] Heikki Tuuri

Sunny has a simple patch now.

[31 Mar 2008 16:53] Bugs System

Pushed into 5.1.24-rc

[2 Apr 2008 20:55] Paul DuBois

Noted in 5.1.24 changelog.

If the server crashes with an InnoDB error due to unavailability of
undo slots and the used slots are not used during rollback, the error 
could persist when the server is restarted.

Resetting report to Patch queued waiting for push into 6.0.x.

[3 Apr 2008 15:01] Bugs System

Pushed into 6.0.5-alpha

[14 Apr 2008 15:56] Paul DuBois

Noted in 6.0.5 changelog.

Also, revised the changelog to say this:

If the server crashed with an InnoDB error due to unavailability of
undo slots, errors could persist during rollback when the server was
restarted: There are two UNDO slot caches (for INSERT and UPDATE). If
all slots end up in one of the slot cashes, a request for a slot from
the other slot cache would fail. This can happen if the request is
for an UPDATE slot and all slots are in the INSERT slot cache, or
vice versa.

[24 Jun 2008 23:48] Calvin Sun

Merged into 6.0.6-alpha, according to Tim. Please put it back to queued waiting for
pushing into 5.0 (r2475).

[26 Jun 2008 1:26] Paul DuBois

Moved 6.0.5 changelog entry to 6.0.6

Setting report to Patch Queued pending push into 5.0.x.

[13 Aug 2008 10:29] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080731214757-65lrunl1vmnnlues)
(version source revid:[e-mail]-20080813063149-5o63l7vlvbm6nuhm) (pib:3)

[13 Aug 2008 10:35] Bugs System

Pushed into 5.0.68  (revid:[e-mail]-20080731214757-65lrunl1vmnnlues) (version
source revid:[e-mail]-20080813061111-bt9vnarlk41r3yif) (pib:3)

[13 Aug 2008 10:38] Bugs System

Pushed into 5.1.28  (revid:[e-mail]-20080731214757-65lrunl1vmnnlues) (version
source revid:[e-mail]-20080813061206-ew8rffhoqwi9wm5p) (pib:3)

[13 Aug 2008 17:45] Paul DuBois

Noted in 5.0.68 changelog.

[28 Aug 2008 22:15] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080822132131-uveo6wiuecy6m2b8) (version
source revid:[e-mail]-20080822132131-uveo6wiuecy6m2b8) (pib:3)

[10 Sep 2008 10:38] Timothy Smith

James, this is fixed in 5.1.24.  The 5.1.28 message is a null-merge of the change that
went into 5.0.68.  This is always confusing. :-/

[14 Sep 2008 1:56] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20080731214757-65lrunl1vmnnlues)
(version source revid:[e-mail]-20080718081554-1cq4mw3bk21fzol6) (pib:3)