Bug #35352 If InnoDB crashes with UNDO slots full error the error persists on restart
Submitted: 17 Mar 2008 21:40 Modified: 14 Oct 2010 13:17
Reporter: Sunny Bains Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0 5.1 6.0 OS:Any
Assigned to: CPU Architecture:Any

[17 Mar 2008 21: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 4: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 13:24] Heikki Tuuri
Emailed a new simpler patch suggestion.
[19 Mar 2008 14:24] Heikki Tuuri
Sunny has a simple patch now.
[31 Mar 2008 14:53] Bugs System
Pushed into 5.1.24-rc
[2 Apr 2008 18: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 13:01] Bugs System
Pushed into 6.0.5-alpha
[14 Apr 2008 13: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 21: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).
[25 Jun 2008 23: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 8:29] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813063149-5o63l7vlvbm6nuhm) (pib:3)
[13 Aug 2008 8:35] Bugs System
Pushed into 5.0.68  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061111-bt9vnarlk41r3yif) (pib:3)
[13 Aug 2008 8:38] Bugs System
Pushed into 5.1.28  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:timothy.smith@sun.com-20080813061206-ew8rffhoqwi9wm5p) (pib:3)
[13 Aug 2008 15:45] Paul DuBois
Noted in 5.0.68 changelog.
[28 Aug 2008 20:15] Bugs System
Pushed into 6.0.7-alpha  (revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (version source revid:cbell@mysql.com-20080822132131-uveo6wiuecy6m2b8) (pib:3)
[10 Sep 2008 8: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. :-/
[13 Sep 2008 23:56] Bugs System
Pushed into 6.0.7-alpha  (revid:timothy.smith@sun.com-20080731214757-65lrunl1vmnnlues) (version source revid:lars-erik.bjork@sun.com-20080718081554-1cq4mw3bk21fzol6) (pib:3)
[5 May 2010 15:09] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 14:07] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:01] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:30] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:57] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 15:08] Paul DuBois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:04] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:48] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:31] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[19 Aug 2010 10:21] Sunny Bains
There was a debug assertion missed in this fix, see bug# 55699.
[28 Sep 2010 8:45] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[28 Sep 2010 15:38] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[14 Oct 2010 8:29] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:45] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 8:59] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[14 Oct 2010 13:17] Jon Stephens
No new changelog entries required; see previous comment from Paul. Setting back to Closed state.