Bug #30513 Table doesn't exist for DESCRIBE, exists for CREATE
Submitted: 20 Aug 2007 16:31 Modified: 26 Feb 2015 10:53
Reporter: Chris St. Pierre Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S3 (Non-critical)
Version:5.1.20-beta OS:Linux
Assigned to: Assigned Account CPU Architecture:Any

[20 Aug 2007 16:31] Chris St. Pierre
I have a table that claims not to exist when I use DESCRIBE, DROP TABLE, etc., but claims to exist when I use CREATE. For example: 

mysql> describe nuke_groups; 
ERROR 1146 (42S02): Table 'nfackler.nuke_groups' doesn't exist 
mysql> drop table if exists nuke_groups; 
Query OK, 0 rows affected, 1 warning (0.00 sec) 

mysql> show warnings; 
| Level | Code | Message | 
| Note | 1051 | Unknown table 'nuke_groups' | 
1 row in set (0.00 sec) 

mysql> CREATE TABLE `nuke_groups` ( `id` int(10) NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `description` text NOT NULL, `points` int(10) NOT NULL default '0', PRIMARY KEY `id` (`id`) ) DEFAULT CHARSET=latin1; 
ERROR 1050 (42S01): Table 'nuke_groups' already exists 

In the logs I'm getting: 

070818 15:43:05 [ERROR] NDB: failed to setup table nfackler.nuke_groups, error: 723, No such table existed 

This error appears about every 1-2 seconds.

No files exist in the datadir for this table.

How to repeat:
I'm not sure.  I was doing a number of batch imports of data into my shiny new cluster, and this table refused to import.  During the course of my attempts to import, I had to tweak several parameters in config.ini, so it may be that this table was unable to import before due to other errors (e.g., out of index space), but those errors are no longer in my terminal scrollback, so I can't comment further.
[20 Aug 2007 18:20] Jonathan Miller
Sound similar to http://bugs.mysql.com/bug.php?id=30205
[20 Aug 2007 19:06] Chris St. Pierre
Similar, but not exactly.  In my case:

Returns "Does not exist":

Returns "Exists":

DROP TABLE IF EXISTS nuke_groups; warns that the table doesn't exist, so IF EXISTS would go in the first group as well.

The table in question _does_ have a text column, though.
[21 Aug 2007 11:17] Hartmut Holzgraefe
Hi Chris,

can you add the full ndb_show_tables output to this issue?
[21 Aug 2007 13:21] Chris St. Pierre
output of ndb_show_tables

Attachment: ndb_show_tables.out (application/octet-stream, text), 173.60 KiB.

[21 Sep 2007 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".
[22 Sep 2007 0:00] Chris St. Pierre
So, how do I convince Bugzilla that I gave the requested feedback a month ago?
[9 Nov 2007 11:11] John Bennett
I'm running on XP (fully patched) with Server version: 5.0.45 and get the same error.  I've a database (containing one table) that I dropped but the process didn't seem to operate and now whilst the table appears to have disappeared the schema hasn't.

So I can't drop the database or the
table because the it is unknown!
   mysql> DROP DATABASE `cieg`;
   ERROR 1051 (42S02): Unknown table 'cieg_data'
   mysql> DROP TABLE IF EXISTS `cieg`.`cieg_data`;
   ERROR 1051 (42S02): Unknown table 'cieg_data'

And selecting from the table gives an 
error that the table doesn't exist
   mysql> SELECT * FROM `cieg`.`cieg_data` c;
   ERROR 1146 (42S02): Table 'cieg.cieg_data' doesn't exist

But I also can't create the table.
   mysql> CREATE TABLE  `cieg`.`cieg_data` (
       ->   `DATETIME_HH` datetime NOT NULL default '0000-00-00 00:00:00',
       ->   `A` double,
       ->   `B` double,
       ->   `C` double,
       ->   PRIMARY KEY  (`DATETIME_HH`)
       -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   ERROR 1050 (42S01): Table 'cieg_data' already exists

It isn't critical just annoying!
[9 Nov 2007 14:18] John Bennett
I've resolved my issue  - I had an errant .frm file in the CIEG data directory.
[6 Dec 2007 2:21] Fred Barboteu
Table which exists AND does NOT exist

Attachment: adh_inno.frm (application/octet-stream, text), 8.57 KiB.

[6 Dec 2007 2:22] Fred Barboteu
I experience the same problem with:
. Windosw XP
. MySql server 5.0.27-community-log, client 5.0.22 (phpMyAdmin

I have 2 (empty) tables which:
. do not exist when SHOW COLUMNS, SHOW KEYS or SELECT

They were imported, among many others, into mysql\data directory, from another computer where they run fine (with same server and client versions, but Vista).

Maybe a significant remark: these 2 tables use InnoDB engine, while the 21 others use MyISAM engine and still run correctly.

Here attached one of these wrong tables: adh_inno.frm
[8 Dec 2007 19:32] Fred Barboteu
I just discovered that my problem was not a bug, but a feature!

It came from the fact that an InnoDB table CANNOT be imported simply as its .frm file, as stated in 12.2.13 InnoDB Tables and Index Structures: http://dev.mysql.com/doc/refman/5.0/en/innodb-table-and-index.html

Sorry for having posted lies.
[18 Jun 2008 19:42] Valeriy Kravchuk
Closing as not a bug, according to the last comment.
[18 Jun 2008 19:47] Chris St. Pierre
The comments added by the most recent poster were not the same bug I initially reported.  Note that he was having issues with InnoDB, not NDB, as I was.  Although we're no longer using NDB (for this very reason), I've seen nothing that suggests that this issue has been fixed.
[23 Jun 2008 18:40] Gwynne Raskind
Having this same issue using MySQL 5.1.24-rc and 5.1.25-rc and an InnoDB table. Only solution I found was to dump and recreate my database, which is a ridiculous inconvenience since I'm having the issue with a test table I need to drop and recreate often. Did NOT have this issue before upgrading from 5.0.51. There is NO stray .frm file in the database directory, and the InnoDB tablespace/table monitors show no errors. No unusual entries appear in the MySQL error log. The table in question has the structure:

    name                VARCHAR(64)         NOT NULL,
    dateFormat          VARCHAR(32)         NOT NULL,
    loginForOne         INT(1) UNSIGNED     NOT NULL,
    loginForTwo         INT(1) UNSIGNED     NOT NULL,
    indexText           MEDIUMTEXT          NOT NULL

It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail.
[24 Jun 2008 6:55] Gwynne Raskind
A couple of notes I forgot to add:

Changing the table's name only resulted in the same thing starting to happen again with the same table. The only special thing about the table is that it's at the end of a batch file.
[30 Jun 2008 12:43] Gwynne Raskind
Additional information:
I've tracked down this bug such that it only happens when the table in question is accessed via INFORMATION_SCHEMA after being created. A mysqladmin flush-tables command eliminates the error without need for any other intervention. I use INFORMATION_SCHEMA this way:

[23 Oct 2008 4:08] Martin Skold
Is this still a problem in latest version?
[23 Oct 2008 4:33] Gwynne Raskind
Yes, this still happens in 5.1.28-rc on Mac OS X 10.5.5 (Darwin 9.5)
[24 Nov 2008 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".
[16 Feb 2011 12:41] Desmond Coertzen
5.1.55 release.

simple script to reproduce ERROR 1146 (42S02) at line 10: Table 'micro.mngd_dedit_order_state' doesn't exist

use micro;

create table mngd_debit_order_state (                                                                                       
mngd_debit_order_state_id int not null,                                                                                     
description varchar(50) not null,                                                                                           
constraint pk_mngd_debit_order_state primary key (mngd_debit_order_state_id),                                               
constraint unq_mdos_desc unique (description)                                                                               
) engine=innodb;

insert into mngd_dedit_order_state (mngd_debit_order_state_id, description) values (1, 'Debit order management inactive');

ERROR 1146 (42S02) at line 10: Table 'micro.mngd_dedit_order_state' doesn't exist

alter table lcn_contract                                                       
add mngd_debit_order_state_id int default 1 not null,                            
add constraint fk_lcn_domstate foreign key (mngd_debit_order_state_id) references mngd_debit_order_state (mngd_debit_order_state_id);

Shorter table and constraint names made the problem disappear.

Better error messages would prevent me from posting under the first (or second) article that was returned by a google search of the error.
[2 Mar 2011 12:24] Martin Skold
Please verify this on a proper cluster release,
[26 Jan 2015 10:53] MySQL Verification Team
Please check with latest released version. Thanks.
[27 Feb 2015 1: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".