Bug #41529 Safe handling of InnoDB running out of undo log slots
Submitted: 17 Dec 2008 3:20 Modified: 12 Mar 2009 22:00
Reporter: James Day Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0,5.1,6.0 OS:Any
Assigned to: Calvin Sun CPU Architecture:Any

[17 Dec 2008 3:20] James Day
Description:
Bug #18828 added to 5.1 an improved error message and safe rejection of request of a transaction to exceed the 1024 undo slots that are available. Apparently not done at that time was similar work in 6.0 to make it safe to attempt to exceed the limit without crashing the server.

If it is not safe in 6.0 to attempt to go over 1024 undo slots please implement a good error message and safe rejection of the attempt so that users can allow more than 1024 connections provided the excess haven't modified data.

Alternatively there was talk of increasing the number of slots possible in 6.0 and I assume that will also have a safe cap. If that's being done instead, please say so.

The related bugs on handling lots of open transactions are:

Bug #35352 (Closed): If InnoDB crashes with UNDO slots full error the error persists on restart 
Bug #18828 (Closed): If InnoDB runs out of undo slots, it returns misleading 'table is full' 
Bug #26590 (Patch pending): MySQL does not allow more than 1023 open transactions

How to repeat:
.

Suggested fix:
Reject attempts to exceed the limit or increase the limit and reject attempts to exceed the new limit.
[17 Dec 2008 11:33] Sveta Smirnova
Actually bug #18828 is not fixed in current 5.1 tree: it returns "Table is full" error to application, although server does not crash and starts up cleanly.
[17 Dec 2008 17:29] Mikhail Izioumtchenko
assigning to Calvin since it's appear to be a complaint about 6.0.
I don't quite understand, however, why can't all issues related 
to the 1023 txn problem can be handled under bug#26590
[17 Dec 2008 18:32] Calvin Sun
Sveta,

The changes to the InnoDB was pushed into 5.1.21. But it has not been turned on due to MySQL forgot to add the appropriate error code and message. See the code:

	case DB_TOO_MANY_CONCURRENT_TRXS:
		/* Once MySQL add the appropriate code to errmsg.txt then
		we can get rid of this #ifdef. NOTE: The code checked by
		the #ifdef is the suggested name for the error condition
		and the actual error code name could very well be different.
		This will require some monitoring, ie. the status
		of this request on our part.*/
#ifdef ER_TOO_MANY_CONCURRENT_TRXS
		return(ER_TOO_MANY_CONCURRENT_TRXS);
#else
		return(HA_ERR_RECORD_FILE_FULL);

Thanks,
Calvin
[17 Dec 2008 22:21] Timothy Smith
Calvin,

Thanks for the pointer -- I've re-opened Bug #18828 to track adding the correct error message to MySQL's errmsg.txt.

Regarding the present bug, I believe the background on this is that there have been reports of crashes when going beyond the 1024 limit (for example, see the description of Bug #35352, "If the server crashes with an out of UNDO slots error...").  James was concerned that something got fixed in 5.1 which is not fixed in 6.0.  However, I can't find any concrete evidence of that.  If crashes related to this limit are seen, I'll ask that they be written in detail to this bug.

For now, the summary is:

For 5.0:  can bug #18828 be fixed in 5.0, too?  IF the patch for 18828 could prevent some crashes (in addition to providing a better error message), then it should be backported to 5.0 if not too risky.

For 5.1 & 6.0:  complete fix for bug #18828 (already re-opened that bug)

For 6.0 only:  see bug #26590 for further enhancements

Regards,

Timothy
[11 Mar 2009 22:21] Timothy Smith
Has been pushed into 5.0, fix will be in 5.0.79.
[12 Mar 2009 3:36] James Day
Note for Docs, might document as "eliminated the chance of assertion failures when hitting the InnoDB maximum open transaction limit and provided support for an improved error message that will be introduced later". Also the other text for this that is in bug #18828.
[12 Mar 2009 22:00] Paul DuBois
Noted in 5.0.79 changelog.