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:Microsoft Windows (Windows XP/Linux)
Assigned to: Sergey Glukhov

[29 Oct 2005 21:56] Bugs System
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`;
[29 Oct 2005 23:08] Miguel Solorzano
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.
[30 Oct 2005 8:25] MySQL-Front Team
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`;
[1 Nov 2005 13:32] Miguel Solorzano
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>
[1 Nov 2005 16:23] Andrey Hristov
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.
[8 Nov 2005 14:14] MySQL-Front Team
The developers are not interessed about our bug messages.
[1 Dec 2005 6:59] Sergey Glukhov
Fixed in 5.0.17
[2 Dec 2005 20:38] Paul Dubois
Noted in 5.0.17 changelog.