MySQL Bugs: #58935: ALTER DATABASE can be used in prepared statements

Bug #58935	ALTER DATABASE can be used in prepared statements
Submitted:	15 Dec 2010 4:36	Modified:	16 Dec 2010 5:34
Reporter:	Filipus Klutiero	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
According to http://dev.mysql.com/doc/refman/5.1/en/sql-syntax-prepared-statements.html :

The following SQL statements can be used in prepared statements: ALTER TABLE, CALL, COMMIT, CREATE INDEX, CREATE TABLE, DELETE, DO, DROP INDEX, DROP TABLE, INSERT, RENAME TABLE, REPLACE, SELECT, SET, UPDATE, and most SHOW statements.

As of MySQL 5.1.10, the following additional statements are supported:

ANALYZE TABLE
OPTIMIZE TABLE
REPAIR TABLE

As of MySQL 5.1.12, the following additional statements are supported:

CACHE INDEX
CHANGE MASTER
CHECKSUM {TABLE | TABLES}
{CREATE | DROP} DATABASE
{CREATE | RENAME | DROP} USER
FLUSH {TABLE | TABLES | TABLES WITH READ LOCK | HOSTS | PRIVILEGES
  | LOGS | STATUS | MASTER | SLAVE | DES_KEY_FILE | USER_RESOURCES}
GRANT
REVOKE
KILL
LOAD INDEX INTO CACHE
RESET {MASTER | SLAVE | QUERY CACHE}
SHOW BINLOG EVENTS
SHOW CREATE {PROCEDURE | FUNCTION | EVENT | TABLE | VIEW}
SHOW {AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS}
SHOW {MASTER | BINARY} LOGS
SHOW {MASTER | SLAVE} STATUS
SLAVE {START | STOP}
INSTALL PLUGIN
UNINSTALL PLUGIN

Other statements are not yet supported. 

There is nothing about ALTER DATABASE, which appears to work on my machine, using MySQL 5.1.49.

I don't know when support was introduced, but 5.0.77 doesn't support it ("This command is not supported in the prepared statement protocol yet").

How to repeat:
Dummy content

This is what I see in 5.1.54:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> prepare stmt1 from 'alter database test character set utf8';
ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet

So, please, check with recent released version, 5.1.53, and if ALTER DATABASE still works the way you describe there, please, send complete test case.

I'm sorry Valeriy, a prepared ALTER DATABASE does not work here neither. I was trying through PDO (PHP), which apparently uses ATTR_EMULATE_PREPARES by default, meaning prepared statements are emulated...

Thank you for your quick reply. FWIW, I was using PHP 5.3.3.