Bug #24164 Create database by stored procedure

• Submitted: 10 Nov 2006 3:25
• Modified: 10 Nov 2006 8:07
• Reporter: Pancoro Pamungkas
• Status: Not a Bug
• Category: MySQL Server: Prepared statements
• Severity: S2 (Serious)
• Version: 5.0.x
• OS: Windows (Windows XP)
• CPU Architecture: Any

[10 Nov 2006 3:25] Pancoro Pamungkas

Description:
There are two (Basically, no different) stored procedures that give two different result:

#1:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`create_db` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_db`(IN db VARCHAR(60))
BEGIN

  SET @db_name = db;
  SET @stmt = CONCAT('create database ',@db_name);
  PREPARE stmt1 FROM @stmt;
  EXECUTE stmt1;

END $$

DELIMITER ;

Result:
Error message: This command is not supported in the prepared statement protocol yet

#2:

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`create_db` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `create_db`(IN db VARCHAR(60))
BEGIN

  create database oh;

END $$

DELIMITER ;

No error message. A new schemata (oh) viewed after window of MySQL Query Browser and MySQL Administrator refreshed.

How to repeat:
Check it up.

Suggested fix:
I haven't any idea about it. But I guess, create schemata isn't allowed. The first error message for first SP should be appeared for second SP.

[10 Nov 2006 8:07] Valeriy Kravchuk

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/sqlps.html) carefully:

"The following SQL statements can be used in prepared statements: CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements. supported. ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE are supported as of MySQL 5.0.23. Other statements are not yet supported."

So, it is not a bug, but documented limitation of prepared statements.