Bug #61956 | Undo Slots not available after being maxed out from UPDATE statements | ||
---|---|---|---|
Submitted: | 22 Jul 2011 16:05 | Modified: | 16 May 2013 4:48 |
Reporter: | Chris Calender | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: InnoDB storage engine | Severity: | S2 (Serious) |
Version: | 5.0, 5.1, 5.1.57 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | too many active concurrent transactions, undo slot, UPDATE |
[22 Jul 2011 16:05]
Chris Calender
[22 Jul 2011 17:10]
Chris Calender
Here are the basic scripts I used .. For INSERT into `t`: <?php $dbs=array(); for ($i = 1; $i <= 1024; $i++) { $db=$dbs[$i]=mysql_connect("localhost:3307","root","xxxx",1); echo "$ dbs[i] = $ dbs[" . $i . "]<br>"; mysql_select_db("test",$db); mysql_query("BEGIN",$db) or die(mysql_error()); mysql_query("INSERT INTO t VALUES ($i)",$db) or die(mysql_error()); } ?> For UPDATE of `t1`: <?php $dbs=array(); for ($i = 1; $i <= 513; $i++) { $db=$dbs[$i]=mysql_connect("localhost:3307","root","xxxx",1); echo "$ dbs[i] = $ dbs[" . $i . "]<br>"; mysql_select_db("test",$db); mysql_query("BEGIN",$db) or die(mysql_error()); mysql_query("UPDATE t1 SET id=$i+1024 WHERE id=$i",$db) or die(mysql_error()); } ?>
[6 Aug 2011 12:33]
Sveta Smirnova
Thank you for the report. Verified as described. This is fixed in trunk. Latest version I could repeat it is 5.1.59
[19 Nov 2012 13:24]
Rikhi Singh
I know this is quite an old thread, but can somebody please confirm, in what release this is fixed? If there's a hotfix for this, please point as well. Many thanks
[23 Nov 2012 13:48]
James Day
Once caused, the problem persists on disk and it takes a mysqldump and reload to get rid of it. Do that, then upgrade to at least 5.1.62 to get a fix for a related bug: "With 1024 concurrent InnoDB transactions running concurrently and the innodb_file_per_table setting enabled, a CREATE TABLE operation for an InnoDB table could fail. The .ibd file from the failed CREATE TABLE was left behind, preventing the table from being created later, after the load had dropped." Or mysqldump and reload, then upgrade to any production release of 5.5 and get the much bigger concurrent transaction limit and other benefits of 5.5. James Day, MySQL Senior Principal Support Engineer, Oracle
[16 May 2013 4:48]
Erlend Dahl
[13 May 2013 2:14] Sunny Bains This issue is fixed in 5.5+. There is now support for 128 * 1024 RW concurrent transactions.