Bug #18828 If InnoDB runs out of undo slots, it returns misleading 'table is full'
Submitted: 6 Apr 2006 6:18 Modified: 18 Jun 2010 1:40
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:All OS:Any (All)
Assigned to: Satya B CPU Architecture:Any
Triage: Triaged: D4 (Minor) / R1 (None/Negligible) / E2 (Low)

[6 Apr 2006 6:18] Heikki Tuuri
Description:
InnoDB returns a misleading error message.

This can happen if a user has > 1000 active transactions at the same time. In real-world applications this can happen when there is severe congestion.

InnoDB does print to the .err log a more descriptive error message:

"
060308 17:08:28InnoDB: Warning: cannot find a free slot for an undo log. Do you have too
InnoDB: many active transactions running concurrently?
"

How to repeat:
See above.

Suggested fix:
Return a more descriptive error message.

Also, we could enable InnoDB to allocate more rollback segments if undo slots run out. But also in that case we will need a more descriptive error message.
[6 Apr 2006 6:21] Heikki Tuuri
trx0rec.c:
    if (op_type == TRX_UNDO_INSERT_OP) {

        if (trx->insert_undo == NULL) {

            trx_undo_assign_undo(trx, TRX_UNDO_INSERT);
        }

        undo = trx->insert_undo;
        is_insert = TRUE;
    } else {
        ut_ad(op_type == TRX_UNDO_MODIFY_OP);

        if (trx->update_undo == NULL) {

            trx_undo_assign_undo(trx, TRX_UNDO_UPDATE);

        }

        undo = trx->update_undo;
        is_insert = FALSE;
    }

    if (undo == NULL) {
        /* Did not succeed: out of space */
        mutex_exit(&(trx->undo_mutex));

        return(DB_OUT_OF_FILE_SPACE);
    }
[6 Apr 2006 6:54] Heikki Tuuri
Also, the restriction on the number of undo logs should be stated in the manual.
[3 May 2006 12:11] Heikki Tuuri
We should add:

HA_ERR_TOO_MANY_CONCURRENT_TRXS

and

ER_TOO_MANY_CONCURRENT_TRXS
[11 May 2006 17:49] AJ Prowant
Any solution to this? Thanks!
[11 May 2006 19:04] Heikki Tuuri
AJ,

do you mean how to prevent > 1000 transactions being active at the same time?

You can set max-connections to a lower value in my.cnf, or simply make sure that your transactions do not pile up like this.

Generally, a transaction in an OLTP system should be short-lived, and > 50 active transactions shows a clear performance problem.

On the other hand, in an artificial benchmark we may have many more concurrently active transactions.

Regards,

Heikki
[10 Aug 2006 21:14] Heikki Tuuri
Assigning this to Sunny.
[26 Feb 2007 13:43] Heikki Tuuri
http://bugs.mysql.com/bug.php?id=26590 is associated with this bug report.
[12 Jul 2007 18:20] Timothy Smith
Queued to 5.1-maint team tree(s)
[19 Jul 2007 15:48] Bugs System
Pushed into 5.1.21-beta
[25 Jul 2007 18:47] Paul Dubois
Noted in 5.1.21 changelog.

If InnoDB reached its limit on the number of concurrent transactions
(1023), it wrote a descriptive message to the error log but returned
a misleading error message to the client, or an assertion failure
occurred.

Also updated the InnoDB limitations section to point out the limit
of 1023 concurrent transactions.
[29 Jul 2007 11:40] James Day
Paul, please change the manual text from:

"InnoDB has a limit of 1023 concurrent transactions."

to

"InnoDB has a limit of 1023 concurrent transactions that have created undo records by modifying data."

Possibly also add:

"Workarounds include keeping transactions as small and fast as possible, delaying changes until near the end of the transaction and using stored routines to reduce client-server latency delays. Applications should commit transactions before doing time-consuming client-side operations."

The significance is that all queries that don't modify data also run in transactions, so the limit currently sounds worse than it actually is. About half of the cases I've seen have been fixable by moving redundant work out of the transactions, the rest have been unavoidable very high load.
[30 Jul 2007 19:47] Paul Dubois
Included the additional information from James. Thanks.
[17 Dec 2008 22:10] Timothy Smith
Hi.  Re-opening this bug because it turns out it was never completely fixed.  The actual fix is #ifdef'd out in ha_innodb.cc because it relies on a new error code to be added to errmsg.txt, which has not been done.

See comment on Bug #41529 at [17 Dec 19:32].

This means that the "table is full" error is still returned, instead of a message regarding too many active transactions.

Regards,

Timothy
[17 Dec 2008 22:20] Mikhail Izioumtchenko
changing the category because to remove #ifdefs in innodb code
we first need ER_TOO_MANY_CONCURRENT_TRXS in MySQL
[5 Jun 2009 2:08] James Day
Satya, you may find the script at bug #26590 of use. The test case is to try to go over 1024 connections that have changed data without committing and verify that the error message is appropriate. With the single set of slots used for two lists, one for inserts, another for updates, it may be prudent to have two tests.
[5 Jun 2009 11:51] Satya B
James,

Thanks for the pointer to the testcase.
[25 Jun 2009 14:18] James Day
Satya, has this made it into any release yet? Any idea which release will have it so I can update a customer on when it may be available?
[17 Jul 2009 8:48] Bugs System
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/78919

2787 Satya B	2009-07-17
      Fix for BUG#18828 - If InnoDB runs out of undo slots, 
                          it returns misleading 'table is full'
      
      Innodb returns a misleading error message "table is full" 
      when the number of active concurrent transactions is greater
      than 1024.
      
      Fixed by adding errorcode "ER_TOO_MANY_CONCURRENT_TRXS" to the
      error codes. Innodb should return HA_TOO_MANY_CONCURRENT_TRXS
      to mysql which is then mapped to ER_TOO_MANY_CONCURRENT_TRXS
      
      
      Note: testcase is not written as this was reproducible only by
            changing innodb code.
      modified:
        extra/perror.c
        include/my_base.h
        sql/ha_innodb.cc
        sql/handler.cc
        sql/share/errmsg.txt
[27 Jul 2009 11:50] Satya B
testcase to verify the fix

Attachment: bug18828.test (application/octet-stream, text), 1.11 KiB.

[27 Jul 2009 11:54] Satya B
Note: the above attached testcase should be run only after applying this patch.

=== modified file 'storage/innobase/include/trx0rseg.h'
--- storage/innobase/include/trx0rseg.h	2006-03-10 16:22:21 +0000
+++ storage/innobase/include/trx0rseg.h	2009-07-27 11:52:13 +0000
@@ -110,7 +110,7 @@
 
 
 /* Number of undo log slots in a rollback segment file copy */
-#define TRX_RSEG_N_SLOTS	1024
+#define TRX_RSEG_N_SLOTS	5
 
 /* Maximum number of transactions supported by a single rollback segment */
 #define TRX_RSEG_MAX_N_TRXS	(TRX_RSEG_N_SLOTS / 2)

The changeset simulates the environment for reproducing the error message without actually going over 1024 connections.
[31 Jul 2009 16:47] Bugs System
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/79783
[4 Aug 2009 13:56] Bugs System
Pushed into 5.0.85 (revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (version source revid:davi.arnaut@sun.com-20090804135315-6lfdnk4zjwk7kn7r) (merge vers: 5.0.85) (pib:11)
[4 Aug 2009 19:52] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version source revid:gshchepa@mysql.com-20090731164636-4d3zv78q8ab8fvru) (merge vers: 5.4.4-alpha) (pib:11)
[4 Aug 2009 20:45] Bugs System
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38) (pib:11)
[6 Aug 2009 20:21] Paul Dubois
Noted in 5.0.85, 5.1.38, 5.4.4 changelogs.
[12 Aug 2009 23:03] Paul Dubois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:20] Paul Dubois
Ignore previous comment about 5.4.2.
[1 Oct 2009 5:59] Bugs System
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers: 5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 7:25] Bugs System
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay) (version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers: 5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 13:25] Bugs System
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers: 5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 10:50] Bugs System
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6) (version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers: 5.1.39-ndb-6.2.19) (pib:11)
[7 Oct 2009 1:53] Paul Dubois
The 5.4 fix has been pushed into 5.4.2.
[5 May 2010 15:07] 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 1:59] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:05] 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:33] 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 7:01] 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 2:29] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:09] 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:56] 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:37] 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)