MySQL Bugs: #14476: `information_schema`.`TABLES`.`TABLE

Bug #14476	`information_schema`.`TABLES`.`TABLE_TYPE` with empty value
Submitted:	29 Oct 2005 21:56	Modified:	2 Dec 2005 20:38
Reporter:	Bugs System	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.15/BK source 5.0.16	OS:	Windows (Windows XP/Linux)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
Please run the given sequence. 

You will get an empty value for `information_schema`.`TABLES`.`TABLE_TYPE` - even since this field is marked as NOT NULL;

... IMHO the empty string an invalid value for this field. If I'm right this is a way to get an inconsitent database state. :-(

How to repeat:
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE VIEW `test_view` AS 
  SELECT * FROM `test_table`;

DROP VIEW `test_view`;

SELECT * FROM `information_schema`.`TABLES`;

I was able for to repeat this issue, however when I did a complete fresh
install I was unable for to repeat. So is necessary to find the condition
which provokes this behavior.

Sorry, I told a wrong procedure. I ment a bug if the table has been removed.

Please use this procedure:
CREATE TABLE `test_table` (
  `id` int(11) NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

CREATE VIEW `test_view` AS 
  SELECT * FROM `test_table`;

DROP TABLE `test_table`;

SELECT * FROM `information_schema`.`TABLES`;

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

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

mysql> SELECT TABLE_TYPE  FROM `information_schema`.`TABLES`;
+-------------+
| TABLE_TYPE  |
+-------------+
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| VIEW        |
| BASE TABLE  |
| BASE TABLE  |
+-------------+
38 rows in set (0.14 sec)

mysql> DROP TABLE `test_table`;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT TABLE_TYPE  FROM `information_schema`.`TABLES`;
+-------------+
| TABLE_TYPE  |
+-------------+
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| SYSTEM VIEW |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| BASE TABLE  |
| VIEW        |
| BASE TABLE  |
| BASE TABLE  |
|             |
+-------------+
39 rows in set (0.08 sec)

mysql> 

miguel@hegel:~/dbs/5.0> bin/mysqladmin shutdown -uroot
miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

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

mysql> SELECT TABLE_TYPE  FROM `information_schema`.`TABLES` WHERE TABLE_TYPE='';
+------------+
| TABLE_TYPE |
+------------+
|            |
+------------+
1 row in set (0.19 sec)

mysql>

The problem comes from the fact that open_normal_and_derived_tables() fails and the result code is checked in get_schema_table_records(). In case of error the latter bails out. The problem is not only a VIEW problem, the same behaviour can be observed with a .frm file without corresponding MYD(for MyISAM table) or not in InnoDB DDIC.
TABLE_TYPE is not NULL but empty string.

The developers are not interessed about our bug messages.

Fixed in 5.0.17

Noted in 5.0.17 changelog.