Bug #28846 Use of undocumented Prepared Statements crashes server
Submitted: 1 Jun 2007 20:57 Modified: 24 Jun 2009 12:25
Reporter: Todd Farmer (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S1 (Critical)
Version:5.0.42, 5.1.17 OS:Any
Assigned to: Kristofer Pettersson CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[1 Jun 2007 20:57] Todd Farmer
Description:
While testing to identify all statements that can be executed through server-side prepared statements but not documented as such (see http://bugs.mysql.com/bug.php?id=28843 for details), I found that repeatedly preparing and executing prepared statements that contain ALTER VIEW and DROP VIEW commands can cause the server to crash.  This was originally found on 5.0.41 (using Windows) but was subsequently verified on 5.0.42 (Linux):

mysql> DROP VIEW IF EXISTS v;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t (a INT);
Query OK, 0 rows affected (0.09 sec)

mysql>
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> CREATE VIEW v AS SELECT * FROM t;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
ERROR 2013 (HY000): Lost connection to MySQL server during query

On Windows, this causes CPU utilization to go up to 100% and stay pegged for undetermined period of time (> 10 minutes), on Linux it causes server crash.

How to repeat:
DROP VIEW IF EXISTS v;
DROP TABLE IF EXISTS t;
CREATE TABLE t (a INT);

CREATE VIEW v AS SELECT * FROM t;
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;

CREATE VIEW v AS SELECT * FROM t;
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;

CREATE VIEW v AS SELECT * FROM t;
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;

CREATE VIEW v AS SELECT * FROM t;
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;

CREATE VIEW v AS SELECT * FROM t;
PREPARE stmt FROM 'ALTER VIEW v AS SELECT a FROM t';
EXECUTE stmt;
PREPARE stmt FROM 'DROP VIEW v';
EXECUTE stmt;

Note that I am unable to recreate the problem without using both the ALTER VIEW and DROP VIEW statements (e.g., DROP VIEW alone executed 5 times does not cause the crash).

Suggested fix:
Don't crash, or explicitly don't allow ALTER VIEW or DROP VIEW statements in server-side prepared statements.
[1 Jun 2007 21:03] Todd Farmer
Also hangs on OSX (5.0.42).
[1 Jun 2007 21:05] Mark Leith
This hangs for me on OSX:

mysql> EXECUTE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE stmt FROM 'DROP VIEW v';
.....

Processlist whilst hung:

mysql> show full processlist;
+----+------+-----------+------+---------+------+-------+---------------------------------+
| Id | User | Host      | db   | Command | Time | State | Info                            |
+----+------+-----------+------+---------+------+-------+---------------------------------+
|  1 | root | localhost | test | Query   |  122 | NULL  | PREPARE stmt FROM 'DROP VIEW v' | 
|  2 | root | localhost | NULL | Query   |    0 | NULL  | show full processlist           | 
+----+------+-----------+------+---------+------+-------+---------------------------------+
2 rows in set (0.00 sec)

Attaching with gdb it looks like this is the thread that is hanging:

Thread 10 (process 1944 thread 0x2903):
#0  0x00296bc6 in my_hash_insert ()
No symbol table info available.
#1  0x000543fe in Statement_map::insert ()
No symbol table info available.
#2  0x000c1d98 in mysql_sql_stmt_prepare ()
No symbol table info available.
#3  0x00072e15 in mysql_execute_command ()
No symbol table info available.
#4  0x00079560 in mysql_parse ()
No symbol table info available.
#5  0x00079f75 in dispatch_command ()
No symbol table info available.
#6  0x0007ae09 in do_command ()
No symbol table info available.
#7  0x0007b953 in handle_one_connection ()
No symbol table info available.
#8  0x90024147 in _pthread_body ()
No symbol table info available.
[1 Jun 2007 21:38] Todd Farmer
Also verified hang in 5.1.17.  Confirmed hang in 5.0.27 while checking for regression.
[12 Jun 2007 13:17] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28577

ChangeSet@1.2519, 2007-06-12 15:17:50+02:00, thek@adventure.(none) +1 -0
  Bug#28846 Use of undocumented Prepared Statements crashes server
  
  CREATE VIEW and DROP VIEW are currently not supported as prepared statements
  and should be disabled as such as they otherwise could cause server crashes.
[22 Jun 2007 9:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29372

ChangeSet@1.2519, 2007-06-22 11:55:48+02:00, thek@adventure.(none) +11 -0
  Bug#28846 Use of undocumented Prepared Statements crashes server
  
  ALTER VIEW is currently not supported as a prepared statement
  and should be disabled as such as they otherwise could cause server crashes.
  
  ALTER VIEW is currently not supported when called from stored
  procedures or functions for related reasons and should also be disabled.
  
  This patch disables these DDL statements and adjusts the appropriate test
  cases accordingly.
  
  Additional tests has been added to reflect on the fact that we do support
  CREATE/ALTER/DROP TABLE for Prepared Statements (PS), Stored Procedures (SP)
  and PS within SP.
[22 Jun 2007 13:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/29401

ChangeSet@1.2550, 2007-06-22 15:38:23+02:00, thek@adventure.(none) +1 -0
  Bug #28846 Use of undocumented Prepared Statements crashes server
  
  - Manual merge patch.
[2 Jul 2007 15:03] Bugs System
Pushed into 5.0.46
[2 Jul 2007 15:06] Bugs System
Pushed into 5.1.21-beta
[11 Jul 2007 22:51] Paul Dubois
Noted in 5.0.46, 5.1.21 changelogs.

ALTER VIEW is not supported as a prepared statement but was not being
rejected. ALTER VIEW is now prohibited as a prepared statement or
when called within stored routines.

Resetting bug report to QA testing status per Kristofer's comment.
[24 Jun 2009 12:25] Konstantin Osipov
No additional QA testing is necessary at this point, closing the bug.