Bug #43227 Ambiguous Error Message - Create Procedure over information_schema
Submitted: 26 Feb 2009 13:54 Modified: 26 Feb 2009 14:27
Reporter: Santo Leto Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.1.31 OS:Microsoft Windows (XP SP3)
Assigned to: CPU Architecture:Any
Tags: messages, usability

[26 Feb 2009 13:54] Santo Leto
Description:
When trying to create a stored procedure on information_schema, an ambiguous error message is returned by the server.

How to repeat:
Test Script:

SELECT VERSION(), CURRENT_USER(), DATABASE();

DELIMITER $$
CREATE 
	DEFINER = 'root'@'localhost' 
	PROCEDURE `information_schema`.`test` ()
	NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER 
	BEGIN
		#do nothing
	END$$

DELIMITER ;

Script Output:

mysql>  SELECT VERSION(), CURRENT_USER(), DATABASE();
+----------------------+----------------+--------------------+
| VERSION()            | CURRENT_USER() | DATABASE()         |
+----------------------+----------------+--------------------+
| 5.1.31-community-log | root@localhost | information_schema |
+----------------------+----------------+--------------------+
1 row in set (0.00 sec)

mysql>
mysql>  DELIMITER $$
mysql>  CREATE
    ->          DEFINER = 'root'@'localhost'
    ->          PROCEDURE `information_schema`.`test` ()
    ->          NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER
    ->          BEGIN
    ->                  #do nothing
    ->          END$$
ERROR 1049 (42000): Unknown database 'information_schema'
mysql>
mysql>  DELIMITER ;
mysql>

Suggested fix:
For better usability, the error message would not be "Unknown database..." but "Can't create..." or "Access denied...". Database information_schema is known, only it is not possible to create objects in it.
[26 Feb 2009 14:27] Miguel Solorzano
Thank you for the bug report. Duplicate of bug: http://bugs.mysql.com/bug.php?id=24062
[21 Dec 2009 8:08] Roel Van de Paar
Note that this may show in other (odd) ways:

mysql> CREATE PROCEDURE setuptables(IN numrows INT)
    -> BEGIN
    ->  DECLARE a INT;
    ->  SET a = 1;
    ->  DROP TABLE IF EXISTS a;
    ->  DROP TABLE IF EXISTS b;
    ->  CREATE TABLE `a` (`id` int,`p` varchar(20) KEY) ENGINE=MyISAM;
    ->  CREATE TABLE `b` (         `p` varchar(20) KEY) ENGINE=MyISAM;
    ->  WHILE (a < numrows) DO
    ->   INSERT INTO a VALUES (a,a);
    ->   INSERT INTO b VALUES (a);
    ->   SET a=a+1;
    ->  END WHILE;
    -> END;
    -> //
ERROR 1109 (42S02): Unknown table 'a' in information_schema