MySQL Bugs: #18828: If InnoDB runs out of undo slots, it returns misleading 'table is full'

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

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.

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);
    }

Also, the restriction on the number of undo logs should be stated in the manual.

We should add:

HA_ERR_TOO_MANY_CONCURRENT_TRXS

and

ER_TOO_MANY_CONCURRENT_TRXS

Any solution to this? Thanks!

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

Assigning this to Sunny.

http://bugs.mysql.com/bug.php?id=26590 is associated with this bug report.

Queued to 5.1-maint team tree(s)

Pushed into 5.1.21-beta

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.

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.

Included the additional information from James. Thanks.

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

changing the category because to remove #ifdefs in innodb code
we first need ER_TOO_MANY_CONCURRENT_TRXS in MySQL

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.

James,

Thanks for the pointer to the testcase.

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?

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

testcase to verify the fix

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

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.

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

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)

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)

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)

Noted in 5.0.85, 5.1.38, 5.4.4 changelogs.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

The 5.4 fix has been pushed into 5.4.2.

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

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)

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)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

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)

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)

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)