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
• Status: Duplicate
• Category: MySQL Server: Errors
• Severity: S3 (Non-critical)
• Version: 5.1.31
• OS: Windows (XP SP3)
• 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] MySQL Verification Team

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