mysql wtf with information_schema.tables

Question

-edit2- 3hrs later and still have the same problem. I am using the noinstall archive package. -edit- maybe someone can tell me a better way to check if a table exist?

I have a function in my lib to check if a table exist which i asked how to do in the past.

I deleted my database and created it again. My code didnt create the tables properly. After debugging i decided to write the below.

mysql> SELECT table_schema, table_name FROM information_schema.tables WHERE tabl
e_schema = 'mydb' AND table_name='ApprovePost';
+--------------+-------------+
| table_schema | table_name  |
+--------------+-------------+
| mydb         | ApprovePost |
+--------------+-------------+
1 row in set (0.00 sec)

Weird... mydb was dropped and created again (i wrote drop database mydb; and create database mydb;. It should be gone?). Lets find out what exists

mysql> SELECT table_schema, table_name FROM information_schema.tables WHERE tabl
e_schema = 'mydb';
Empty set (0.00 sec)

WTF

Not only do i not know why the first statement shows tables which is wrecking my code, i dont know why this is not showing any tables (in that database).

note: The databases should all be innodb. Also this is a fresh windows install and i may have configured something wrong.

---

Bonus weirdness.

mysql> drop database mydb;
ERROR 1008 (HY000): Can't drop database 'mydb'; database doesn't exist
mysql> SELECT table_schema, table_name FROM information_schema.tables WHERE tabl
e_schema = 'mydb';
Empty set (0.00 sec)

mysql> SELECT table_schema, table_name FROM information_schema.tables WHERE tabl
e_schema = 'mydb' AND table_name='ApprovePost';
+--------------+-------------+
| table_schema | table_name  |
+--------------+-------------+
| mydb         | ApprovePost |
+--------------+-------------+
1 row in set (0.00 sec)

Answer 1

Hmmm... Are results stable? May be some pending transaction.. What is shown by

show tables like '%'

Also it may be an issue with physical files. Is folder for mydb exists?

Answer 2

Looks like you need to use the FLUSH TABLES command for the INFORMATION_SCHEMA.TABLES to reflect existing tables.

Reference:

• TABLE CACHE