MySQL Bugs: #47697: Inconsistencies in the query outputs on INFORMATION

Bug #47697	Inconsistencies in the query outputs on INFORMATION_SCHEMA.TABLES.
Submitted:	29 Sep 2009 6:19	Modified:	29 Sep 2009 6:56
Reporter:	Ritheesh Vedire	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1.39	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
There are inconsistencies in query outputs   on INFORMATION_SCHEMA.TABLES  when the *.frm files are invalid.
The files may be invalid due to few reasons like : 
 1) *.frm file is corrupt.
 2) when the storage engine of *.frm is not existent in the present server
   ( A scenario :    In Machine 1, *. frm file was created .
                        Storage engine = 'Paradox'
                     
                     In Machine 2, where 'Paradox' is non existent, and server tries to use that file )  

How to repeat:
 create database testDB;
 use testDB;
 create table test1(Id int ) ;
   #now copy  a .frm  file with non existent storage engine : 
       cp  mysql-test /std_data/bug30938.frm to testDB-datadirectory.
   # create a nt_vd_tb.frm file with invalid information and move it 
      to testDB-datadirectory.

 show tables ;

 output: bug30938
         nt_vd_tb
         test1

1) select * from information_schema.tables where table_schema = 'testDB' and table_type = 'base table';

   output: (I'm displaying first five columns)
  
        def testDB bug30938 BASE TABLE NULL
        def testDB nt_vd_tb BASE TABLE NULL
        def testDB test1    BASE TABLE MyISAM

2) select table_name from information_schema.tables where table_schema = 'testDB' and table_type = 'base table';

   output :  test1

     ----> How's this happening ? when in the first query it clearly displayed 
   bug30938 and nt_vd_tb as BASE TABLEs
 
3)   select * from information_schema.tables where table_schema = 'testDB' and engine is NULL;

   output: (I'm displaying the first five columns)
         
          def testDB bug30938 BASE TABLE NULL
          def testDB nt_vd_tb BASE TABLE NULL

4)   select table_name information_schema.tables where table_schema = 'testDB' and engine is NULL;

   output:  Empty set (0.00 sec)

     ---> even in this case, when the engines are listed as NULL in the previous command , that is not happening now.

 

NOTE : There is an ERROR message in the mysqld window if an invalid nt_vd_tb.frm file is in the database testDB.

now remove the nt_vd_tb.frm from testDB- and try the tests again for the bug30958.frm and test1.frm files .  Output is  still inconsistent.

Verified with 5.1.39 on Windows:

mysql> select * from information_schema.tables where table_schema='test' and eng
ine is NULL\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: t1
     TABLE_TYPE: BASE TABLE
         ENGINE: NULL
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: Unknown table engine 'MYYYYY'
*************************** 2. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: test
     TABLE_NAME: t3
     TABLE_TYPE: BASE TABLE
         ENGINE: NULL
        VERSION: NULL
     ROW_FORMAT: NULL
     TABLE_ROWS: NULL
 AVG_ROW_LENGTH: NULL
    DATA_LENGTH: NULL
MAX_DATA_LENGTH: NULL
   INDEX_LENGTH: NULL
      DATA_FREE: NULL
 AUTO_INCREMENT: NULL
    CREATE_TIME: NULL
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: NULL
       CHECKSUM: NULL
 CREATE_OPTIONS: NULL
  TABLE_COMMENT: Can't find file: 't3' (errno: 2)
2 rows in set (0.00 sec)

mysql> select table_name from information_schema.tables where table_schema='test
' and engine is NULL;
Empty set (0.02 sec)

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.39-community |
+------------------+
1 row in set (0.03 sec)

So, we have clear inconsistency.

In the test database above t2 is MyISAM table. t1 was MyISAM with the same structure as t2, but with MyISAM replaced by MYYYY in the .frm file. For t3 the t2.frm file was copied with the name t3.frm, but no t3.MYD or t3.MYI files are present:

mysql> show create table t1\G
ERROR 1286 (42000): Unknown table engine 'MYYYYY'
mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `c1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> show create table t3\G
ERROR 1017 (HY000): Can't find file: 't3' (errno: 2)

Out of the outputs generated by SELECT * and SELECT <table_name> which of them is   the correct one?

I am hitting the same bug on 5.5.46 :
 * SELECT * FROM information_schema.tables WHERE table_type='VIEW' AND table_comment LIKE '%invalid%';
  => Takes 3 minutes and returns 5 rows containing invalid VIEWs on different databases

 * SELECT table_schema, table_name FROM information_schema.tables WHERE table_type='VIEW' AND table_comment LIKE '%invalid%';
  => Takes 40 seconds and returns 0 rows and 28 warnings but "SHOW WARNINGS" only returns 2 rows of invalid views on the database in USE for this query