Bug #53062 Cannot drop and recreate table after querying INFORMATION_SCHEMA.columns
Submitted: 22 Apr 2010 13:36 Modified: 19 Dec 2010 21:13
Reporter: Steve Gansemer Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.45 OS:Mac OS X
Assigned to: CPU Architecture:Any

[22 Apr 2010 13:36] Steve Gansemer
Description:
I have a SQL script that runs during an install.  Its purpose is to drop the tables and recreate them. However, I found that if the INFORMATION_SCHEMA.columns table is queried (using a table name), the drop of the table specified works, but the subsequent create table will fail with an "already exists" message.  I end up having to do a FLUSH TABLES, to get around the issue.

How to repeat:
CREATE TABLE some_table;

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME = 'some_table' AND COLUMN_NAME = 'mycolumn';

DROP TABLE some_table;

CREATE_TABLE some_table:
(Will give an "already exists" error.
[22 Apr 2010 16:11] Valeriy Kravchuk
Sorry, but I can NOT repeat this:

77-52-4-109:5.1 openxs$ 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 12
Server version: 5.1.47-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table some_table(mycolumn int);
Query OK, 0 rows affected (0.07 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS
    -> WHERE TABLE_SCHEMA='myschema' AND TABLE_NAME = 'some_table' AND COLUMN_NAME =
    -> 'mycolumn';
Empty set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 'some_table' AND COLUMN_NAME = 'mycolumn';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES                      | COLUMN_COMMENT |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
| NULL          | test         | some_table | mycolumn    |                1 | NULL           | YES         | int       |                     NULL |                   NULL |                10 |             0 | NULL               | NULL           | int(11)     |            |       | select,insert,update,references |                |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+
1 row in set (0.00 sec)

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

mysql> create table some_table(mycolumn int);
Query OK, 0 rows affected (0.39 sec)

So, please, copy-paste all the results of complete test case, as I did.
[22 May 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[5 Nov 2010 15:53] Steve Gansemer
Some additional information.  It appears it only occurs if you use uppercase tables names.

CREATE TABLE SOMETABLE (
 SOMECOLUMN BIGINT NOT NULL
 )

select * from information_schema.columns where table_schema = schema() AND TABLE_NAME = 'SOMETABLE' 
	AND COLUMN_NAME = 'SOMECOLUMN'

DROP TABLE SOMETABLE

If you use sometable, then it will work, but SOMETABLE results in the error.
[19 Nov 2010 21:13] Sveta Smirnova
Thank you for the feedback.

I also can repeat described behavior. Please try with current version 5.1.52 and if problem still exists indicate accurate version of MAC OSX you use and provide your configuration file.
[20 Dec 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".