MySQL Bugs: #49542: Select from Information_schema.tables fails when a database does not exist

Bug #49542	Select from Information_schema.tables fails when a database does not exist
Submitted:	8 Dec 2009 21:05	Modified:	14 Oct 2010 13:55
Reporter:	Marc ALFF	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S1 (Critical)
Version:	5.6	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
In 5.6, the following query:

set @have_broken_tables=
  (select count(table_name) from
    information_schema.tables where table_schema='broken');

should return 0 when the database 'broken' does not exist.

Instead, it returns:

failed: 1049: Unknown database 'broken'

This is a show stopper for the fix for Bug#45194,
because the fix need something like:

set @have_broken_tables=
  (select count(table_name) from information_schema.tables
  where engine != 'PERFORMANCE_SCHEMA'
  and table_schema='performance_schema')

How to repeat:
See description

Thank you for the bug report. Verified as described.

miguel@quetzal2:~$ dbs/5.6/bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.0-beta-debug Source distribution

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

mysql> set @have_broken_tables=
    ->   (select count(table_name) from
    ->     information_schema.tables where table_schema='broken');
ERROR 1049 (42000): Unknown database 'broken'
mysql>

This problem exists in both 5.6 and 5.1.  It needs to be triaged again.

I isolated this to one of those nasty special case things in the information_schema code.

This works;

select count(table_name) from information_schema.tables where table_schema='broken';

But this does not;

set @have_broken_tables= (select count(table_name) from information_schema.tables where table_schema='broken');

In both cases, find_files() is called to locate the 'broken' directory on disk.  It is called from a function in sql_show.c named make_table_name_list() which is only called while selecting data from an information_schema table.  Both times, the directory 'broken' is not found and an error is reported.
But in make_table_name_list(), the error is cleared for the first case because the statement is a SQLCOM_SELECT.  The second is a SQLCOM_SET_OPTION so the error is returned.

There is a comment in the make_table_name_list() that says;
    /*
      Downgrade errors about problems with database directory to
      warnings if this is not a 'SHOW' command.  Another thread
      may have dropped database, and we may still have a name
      for that directory.
    */
But the code in all MySQL branches only downgrades for SQL_SELECT.   

I propose to make the code clear the missing directory error for all commands except SHOW commands, just as the comment suggests.

I just pushed the following patch to mysql-5.1-bugteam which fixes the problem. Will see if it has any error on pushbuild.

plain text  - view push

=== modified file 'sql/sql_show.cc'
--- sql/sql_show.cc 2010-07-21 15:05:57 +0000
+++ sql/sql_show.cc 2010-07-22 16:17:26 +0000
@@ -2997,7 +2997,7 @@ make_table_name_list(THD *thd, List<LEX_
  if (res == FIND_FILES_DIR)
  {
-   if (lex->sql_command != SQLCOM_SELECT)
+   if (sql_command_flags[lex->sql_command] & CF_STATUS_COMMAND)
      return 1; 
    thd->clear_error();
    return 2;

triage: SR56RC  (I2 as needed for 5.6 testing. Not clear why needed for 5.1. If needed will need to be retriaged - changing tag from SRMILESTONE to SR56RC

Has been merged to mysql-trunk-merge

Noted in 5.1.50 changelog.

Searches in INFORMATION_SCHEMA tables for rows matching a nonexistent
database produced an error instead of an empty query result.

Setting report to Need Merge pending further pushes.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)

Noted in 5.5.6 changelog.
Bug does not appear in any released 5.6.x version.

Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

Already documented in the 5.1.50 changelog; no additional changelog entries required. Set back to Closed state.