MySQL Bugs: #18238: When locks exhaust the buffer pool, InnoDB does not roll back the trx

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:	Windows (Windows XP/Suse Linux)
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

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.

InnoDB error messages and back trace

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

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.

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>

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

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

Fixed in InnoDB snapshot368; fixes are in 5.0.20.

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)

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