MySQL Bugs: #42190: select table_name from INFORMATION

Bug #42190	select table_name from INFORMATION_SCHEMA.tables return garbage data
Submitted:	19 Jan 2009 6:27	Modified:	30 Jun 2011 15:06
Reporter:	Eran Shwartz	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	information_schema

Description:
this is what I do:
create a table in InnoDB engine. drops it and gets 2 different answers when I run 'select *' and 'select table_name' from INFORMATION_SCHEMA.tables.

actually, it occurs for every column I select explicitly:
'select table_name, table_schema' 'select table_type' etc...

the problem occurs only when I add 'engine=innodb'.

How to repeat:
CREATE SCHEMA test_sch;
GO

create table X (a int, b int) engine=innodb;
GO

drop table x;
GO

select table_name from INFORMATION_SCHEMA.tables where table_name='X';
GO

Results with:
table_name    
------------- 
 X          

select * from INFORMATION_SCHEMA.tables where table_name='X';
GO

Results with:
 TABLE_CATALOG     TABLE_SCHEMA     TABLE_NAME     TABLE_TYPE     ENGINE     VERSION     ROW_FORMAT     TABLE_ROWS     AVG_ROW_LENGTH     DATA_LENGTH     MAX_DATA_LENGTH     INDEX_LENGTH     DATA_FREE     AUTO_INCREMENT     CREATE_TIME     UPDATE_TIME     CHECK_TIME     TABLE_COLLATION     CHECKSUM     CREATE_OPTIONS     TABLE_COMMENT    
 ----------------  ---------------  -------------  -------------  ---------  ----------  -------------  -------------  -----------------  --------------  ------------------  ---------------  ------------  -----------------  --------------  --------------  -------------  ------------------  -----------  -----------------  ---------------- 

 0 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]

the garbage data returns even after I drop the schema where the table was created in:

DROP SCHEMA IF EXISTS sch_test;
GO
CREATE SCHEMA sch_test;
GO
...

I can not repeat the behavious described. Look:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.30-community-log MySQL Community Server (GPL)

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

mysql> CREATE SCHEMA sch_test;
Query OK, 1 row affected (0.05 sec)

mysql> use sch_test;
Database changed
mysql> create table X (a int, b int) engine=innodb;
Query OK, 0 rows affected (0.42 sec)

mysql> drop table x;
Query OK, 0 rows affected (0.09 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='X';
Empty set (0.16 sec)

mysql> select * from INFORMATION_SCHEMA.tables where table_name='X';
Empty set (0.01 sec)

What I am doing wrong? Please, send your my.ini file content also.

my.ini file

Attachment: my.ini (application/octet-stream, text), 8.75 KiB.

I attached my.ini file
I think you did exactly what I said.
this is what I'm getting when I'm doning the same:

c:\mysql -uroot -padmin eran_test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 48
Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql> use eran_test
Database changed
mysql> create table X (a int, b int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table x;
Query OK, 0 rows affected (0.00 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='X';
+------------+
| table_name |
+------------+
| X          |
+------------+
1 row in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.tables where table_name='X';
Empty set (0.00 sec)

Thank you for the feedback. I couldn't repeat with latest source server too, same Valeriy's behavior and you commented the same so you aren't able to repeat the original behavior reported?. Thanks in advance.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.31-nt-debug-log Source distribution

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

mysql 5.1 >CREATE SCHEMA test_sch;
Query OK, 1 row affected (0.01 sec)

mysql 5.1 >use test_sch;
Database changed
mysql 5.1 >create table X (a int, b int) engine=innodb;
Query OK, 0 rows affected (0.15 sec)

mysql 5.1 >drop table x;
Query OK, 0 rows affected (0.03 sec)

mysql 5.1 >select table_name from INFORMATION_SCHEMA.tables where table_name='X';
Empty set (0.01 sec)

mysql 5.1 >

I download the installation file from this location. maybe you should try it.
http://dev.mysql.com/get/Downloads/MySQL-5.1/mysql-essential-5.1.30-win32.msi/from/http://...

please note that I can repeat the problem. Actually it happens all the time:
see that "select table_name" returned X and "select *" returned nothing:

///////////////////////////////////////////////////////////////
mysql> select table_name from INFORMATION_SCHEMA.tables where
table_name='X';

+------------+
| table_name |
+------------+
| X          |
+------------+
1 row in set (0.00 sec)

mysql> select * from INFORMATION_SCHEMA.tables where table_name='X';
Empty set (0.00 sec)
///////////////////////////////////////////////////////////////

can you tell me when sql 6.0 is going to be GA?

Thank you for the feedback.

Please also provide full error log file.

error log from: "C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data\"

Attachment: SHWARTZE01.err (application/octet-stream, text), 1.08 KiB.

Hello, I could duplicate it, too. In my test, it occurs
- only when I execute the same SELECT statements before dropping the table.
- even with MyISAM. 

---
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.30-community MySQL Community Server (GPL)

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

mysql> use test_sch;
Database changed
mysql> create table X (a int, b int) engine=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='X';
+------------+
| table_name |
+------------+
| X          |
+------------+
1 row in set (0.00 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='x';
+------------+
| table_name |
+------------+
| x          |
+------------+
1 row in set (0.00 sec)

mysql> drop table x;
Query OK, 0 rows affected (0.00 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='X';
+------------+
| table_name |
+------------+
| X          |
+------------+
1 row in set (0.00 sec)

mysql> select table_name from INFORMATION_SCHEMA.tables where table_name='x';
Empty set (0.00 sec)

Yoshiaki Tajika, thank you for the feedback.

Verified as described in your comment.

Bug #45576 can relate to the current.

I can not reproduce this bug anymore with version 5.1.56.