Bug #33301 PREPARE crashes the server with large statements
Submitted: 17 Dec 2007 22:55 Modified: 27 Feb 2008 23:30
Reporter: Roland Bouman Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:5.1.22, 5.1.23 OS:Any (XP Home, linux)
Assigned to: CPU Architecture:Any

[17 Dec 2007 22:55] Roland Bouman
Description:
On windows the server is easily crashed by using the PREPARE syntax on a very large statement. In the error log there is a message that says MySQL ran out of memory

On Linux, the same sequence of statements does not crash the server but slows down the entire computer to the point where it becomes unusable for a long period of time. 

The issue only occurs when there is not enough RAM available to handle the PREPARE.

How to repeat:
set max_allowed_packet := 1073741824;
set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');
set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));
prepare s from @s;

Suggested fix:
Ideally, MySQL should not execute statements that exceed pre-configured resource quota, and fail such requests with a proper error. However, in case it is not doable to detect such conditions beforehand and MySQL has to execute the statement, MySQL should certainly not crash because it ran out of memory.
[17 Dec 2007 23:01] Kolbe Kegel
This also causes the system to become unresponsive using 5.0.52 on Linux.
[17 Dec 2007 23:32] MySQL Verification Team
Thank you for the bug report. I wasn't able to repeat with latest Windows
server from the source tree running on laptop with 2GB RAM, could you
please specify your hardware?. Thanks in advance.

c:\dbs>5.1\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.23-rc-nt Source distribution

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

mysql> set max_allowed_packet := 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');
Query OK, 0 rows affected (0.00 sec)

mysql> set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));
Query OK, 0 rows affected (0.28 sec)

mysql> prepare s from @s;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist
mysql>
[17 Dec 2007 23:47] Roland Bouman
Win Xp home, 1G physical RAM, but ~700 MB used already (before running the query)
[18 Dec 2007 19:36] MySQL Verification Team
roland, you testcase misses the creation of table t1 :P
[18 Dec 2007 19:42] MySQL Verification Team
My result.
drop table if exists t1;
create table t1(a datetime) engine=myisam;
set max_allowed_packet := 1073741824;
set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');
set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));
prepare s from @s;
#<crash>

mysqld.exe!MYSQLparse
mysqld.exe!parse_sql
mysqld.exe!Prepared_statement::prepare
mysqld.exe!mysql_sql_stmt_prepare
mysqld.exe!mysql_execute_command
mysqld.exe!mysql_parse
mysqld.exe!dispatch_command
mysqld.exe!do_command
mysqld.exe!handle_one_connection
mysqld.exe!pthread_start
mysqld.exe!_threadstart

071218 21:38:13  InnoDB: Started; log sequence number 0 46409
071218 21:38:13 [Note] mysqld: ready for connections.
Version: '5.1.22-rc-community'  socket: ''  port: 3306  MySQL Community Server (GPL)
071218 21:39:37 [ERROR] mysqld: Out of memory (Needed 2751268 bytes)
[18 Dec 2007 19:46] Davi Arnaut
Might be a duplicate of Bug#31153.
[20 Dec 2007 12:51] MySQL Verification Team
Thank you for the bug report.
[27 Feb 2008 14:47] Konstantin Osipov
May be fixed by now, should be re-verified.
[27 Feb 2008 14:55] Roland Bouman
This is the create table statement that needs to be run before running the script:

create table t1 ( i int);

Anyway - this still crashes 5.1.23 at least on windows.
[27 Feb 2008 23:30] MySQL Verification Team
Not repeatable anymore with latest source server:

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\dbs>5.1\bin\mysqladmin.exe -uroot shutdown

c:\dbs>5.1\bin\mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.24-rc-nt-log Source distribution

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

mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> create table t1(a datetime) engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> set max_allowed_packet := 1073741824;
Query OK, 0 rows affected (0.00 sec)

mysql> set @s=concat('insert into t1 values(@d:=10000101+@i:=1)');
Query OK, 0 rows affected (0.03 sec)

mysql> set @s=concat(@s,repeat(',(date_add(@d,interval @i:=@i+1 day))',999999));
Query OK, 0 rows affected (0.34 sec)

mysql> prepare s from @s;
Query OK, 0 rows affected (27 min 31.72 sec)
Statement prepared

mysql>