Bug #44009 Weird error while querying `TABLES` against not existing `TABLE_SCHEMA`
Submitted: 1 Apr 2009 8:27 Modified: 20 May 2009 20:25
Reporter: Serdar S. Kacar Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.33, 5.1, 6.0 bzr OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[1 Apr 2009 8:27] Serdar S. Kacar
Description:
information_schema.`TABLES` has been optimized few months ago. This might be a residue of it.

Basic form that leads to anomaly is :
SELECT 1 FROM information_schema.`TABLES` T
        WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
But the anomaly is not detectable by a simple SELECT.

The following form raises an exception (Error#1049):
SET @a = (SELECT 1 ..);

But the really weird case is observed in the following form inside a stored procedure :
SELECT 1 INTO btmp ..
If there is no exception handler around, it runs as expected.
If there is an exception handler, it catches god-knows-what error !

How to repeat:
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1624
Server version: 5.1.33-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> -- basic behaviours :
mysql>
mysql> SELECT 1 FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
Empty set (0.00 sec)

mysql>
mysql> SET @a = (SELECT 1 FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1);
ERROR 1049 (42000): Unknown database 'nosuchdb'
mysql>
mysql> SELECT 1 INTO @a FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql> -- SET within a stored procedure :
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySet` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `test`.`TestISTablesBySet` ()
    -> BEGIN
    ->   DECLARE btmp BOOL;
    ->
    ->   SET btmp = (SELECT 1 FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1);
    ->
    ->   SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> CALL `test`.`TestISTablesBySet`();
ERROR 1049 (42000): Unknown database 'nosuchdb'
mysql>
mysql>
mysql> -- SELECT .. INTO within a stored procedure :
mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySelectInto1` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `test`.`TestISTablesBySelectInto1` ()
    -> BEGIN
    ->   DECLARE btmp BOOL;
    ->
    ->   SELECT 1 INTO btmp FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
    ->
    ->   SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> CALL `test`.`TestISTablesBySelectInto1`();
+-----------------------------------------+
| CONCAT('btmp is ', IFNULL(btmp,'NULL')) |
+-----------------------------------------+
| btmp is NULL                            |
+-----------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql>
mysql> -- SELECT .. INTO and HANDLER FOR SQLEXCEPTION within a stored procedure:

mysql>
mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `test`.`TestISTablesBySelectInto2` $$
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE `test`.`TestISTablesBySelectInto2` ()
    -> BEGIN
    ->   DECLARE btmp BOOL;
    ->   DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN
    ->     SELECT 'Actually there is a catchable "silent" (?!) exception.'
    ->       AS BEWARE;
    ->   END;
    ->
    ->   SELECT 1 INTO btmp FROM information_schema.`TABLES` T
    ->         WHERE T.`TABLE_SCHEMA` = 'nosuchdb' LIMIT 1;
    ->
    ->   SELECT CONCAT('btmp is ', IFNULL(btmp,'NULL'));
    -> END $$
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql>
mysql> CALL `test`.`TestISTablesBySelectInto2`();
+--------------------------------------------------------+
| BEWARE                                                 |
+--------------------------------------------------------+
| Actually there is a catchable "silent" (?!) exception. |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql>
[1 Apr 2009 8:41] Sveta Smirnova
Thank you for the report.

Verified as described.

Version 5.0 is not affected.
[1 Apr 2009 8:47] Sveta Smirnova
Bug was introduced in version 5.1.21
[20 May 2009 20:25] Serdar S. Kacar
Just a bit of more info :
- The error was actually Error: 1049 SQLSTATE: 42000 (ER_BAD_DB_ERROR)
- Workaround : Use "LIKE" operator instead of "=" while querying an information_schema table against a schema name.
[2 Oct 2009 13:52] Andrei Elkin
Consider relateness of Bug #29790 information schema returns non-atomic content => replication (binlog) fails and a replication test failure 
BUG#45576 rpl.rpl_row_create_table most probably due to  I_S's issue.
[28 Sep 2010 21:58] Marc ALFF
Possibly related bug:

Bug#49542 Select from Information_schema.tables fails when a database does not exist