MySQL Bugs: #28846: Use of undocumented Prepared Statements crashes server

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

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.

Also hangs on OSX (5.0.42).

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.

Also verified hang in 5.1.17.  Confirmed hang in 5.0.27 while checking for regression.

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.

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.

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.

Pushed into 5.0.46

Pushed into 5.1.21-beta

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.

No additional QA testing is necessary at this point, closing the bug.