MySQL Bugs: #41529: Safe handling of InnoDB running out of undo log slots

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

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.

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.

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

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

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

Has been pushed into 5.0, fix will be in 5.0.79.

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.

Noted in 5.0.79 changelog.