Bug #18238 When locks exhaust the buffer pool, InnoDB does not roll back the trx
Submitted: 14 Mar 2006 20:40 Modified: 11 Apr 2006 2:56
Reporter: Yuri Koba Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S1 (Critical)
Version:5.0.18-nt via /5.0BK/5.1BK OS:Microsoft Windows (Windows XP/Suse Linux)
Assigned to: Heikki Tuuri

[14 Mar 2006 20:40] Yuri Koba
Description:
The following stored procedure shuts down the server on my computer.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`Proc` $$
CREATE PROCEDURE `Proc`()
BEGIN
  declare v INT;

DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
  `ID` int(10) unsigned NOT NULL auto_increment,
  `N` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
  
  INSERT INTO T1 (N) values (1);

  set v = 1;
  while v < 26 do
     insert into t1 (N) select N from t1;
     set v = v+1;
  end while;

END $$

DELIMITER ;

How to repeat:
Create the stored procedure and execute it. The server crashes.
It takes at least 5 min on my computer to reach the point when it crashes.
I believe it happens somewhere after the 2^24 number of records (which is 16777216) get inserted.
[14 Mar 2006 21:57] Miguel Solorzano
InnoDB error messages and back trace

Attachment: InnoDB-Linux-BT.txt (text/plain), 28.19 KiB.

[14 Mar 2006 22:01] Miguel Solorzano
I was able to repeat on Windows/Linux with InnoDB tables only:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`Proc` $$
Query OK, 0 rows affected, 1 warning (0.27 sec)

mysql> CREATE PROCEDURE `Proc`()
    -> BEGIN
    ->   declare v INT;
    ->
    -> DROP TABLE IF EXISTS `t1`;
    -> CREATE TABLE `t1` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `N` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    ->
    ->   INSERT INTO T1 (N) values (1);
    ->
    ->   set v = 1;
    ->   while v < 26 do
    ->      insert into t1 (N) select N from t1;
    ->      set v = v+1;
    ->   end while;
    ->
    -> END $$
Query OK, 0 rows affected (0.38 sec)

mysql>
mysql> DELIMITER ;
mysql> call Proc();
ERROR 2013 (HY000): Lost connection to MySQL server during query

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`Proc` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `Proc`()
    -> BEGIN
    ->   declare v INT;
    ->
    -> DROP TABLE IF EXISTS `t1`;
    -> CREATE TABLE `t1` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `N` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
    ->
    ->   INSERT INTO t1 (N) values (1);
    ->
    ->   set v = 1;
    ->   while v < 26 do
    ->      insert into t1 (N) select N from t1;
    ->      set v = v+1;
    ->   end while;
    ->
    -> END $$
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> DELIMITER ;
mysql> call Proc();
Query OK, 16777216 rows affected, 1 warning (16 min 10.16 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Note
   Code: 1051
Message: Unknown table 't1'
1 row in set (0.14 sec)

mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 33554432 |
+----------+
1 row in set (0.13 sec)

I attached the file with the error messages and back trace on Linux. I will
test with 5.1.
[14 Mar 2006 23:12] Miguel Solorzano
Thank you for the bug report.
I am not sure if this bug InnoDB's releated or Stored Procedure's.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.8-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> DELIMITER $$
mysql> 
mysql> DROP PROCEDURE IF EXISTS `test`.`Proc` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `Proc`()
    -> BEGIN
    ->   declare v INT;
    -> 
    -> DROP TABLE IF EXISTS `t1`;
    -> CREATE TABLE `t1` (
    ->   `ID` int(10) unsigned NOT NULL auto_increment,
    ->   `N` int(10) unsigned NOT NULL default '0',
    ->   PRIMARY KEY  (`ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    ->   
    ->   INSERT INTO t1 (N) values (1);
    -> 
    ->   set v = 1;
    ->   while v < 26 do
    ->      insert into t1 (N) select N from t1;
    ->      set v = v+1;
    ->   end while;
    -> 
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> call Proc();
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[15 Mar 2006 9:38] Heikki Tuuri
Hi!

You have a buffer pool of 8 MB and you are trying to INSERT and lock 64 million rows! No wonder that you run out of the space in the buffer pool!

InnoDB should roll back the transaction in this case, and not crash the server. I will study why that does not happen.

Thank you,

Heikki
[16 Mar 2006 17:15] Heikki Tuuri
I have now a patch for 5.0 that Marko will review.

I have now made InnoDB to roll back the whole transaction if 75 % of the buffer pool is occupied by lock heaps and adaptive hash indexes. Previously, the limit was 85 %.

A problem is that also a rollback can cause the lock structs to consume more memory in the buffer pool! Looks like that 25 % is enough to accommodate that extra memory used in the rollback. In some cases, InnoDB may still assert, if the rollback for some reason expands the lock table more than in this test.

Regards,

Heikki
[5 Apr 2006 19:17] Elliot Murphy
Fixed in InnoDB snapshot368; fixes are in 5.0.20.
[11 Apr 2006 2:56] Paul Dubois
Noted in 5.0.20 changelog.

If <literal>InnoDB</literal> ran out of buffer space for row
locks and adaptive hashes, the server would crash. Now
<literal>InnoDB</literal> rolls back the transaction. (Bug
#18238)
[2 Feb 2010 9:46] Shane Bester
Just for informational purposes, running the testcase on 5.0.90 shows us this error message:

mysql> call proc();
ERROR 1206 (HY000): The total number of locks exceeds the lock table size