MySQL Bugs: #30513: Table doesn't exist for DESCRIBE, exists for CREATE

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:	None
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

Description:
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.

Sound similar to http://bugs.mysql.com/bug.php?id=30205

Similar, but not exactly.  In my case:

Returns "Does not exist":
SELECT
INSERT
DROP TABLE
UPDATE
SHOW TABLES
ndb_desc

Returns "Exists":
CREATE TABLE
ndb_show_tables

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.

Hi Chris,

can you add the full ndb_show_tables output to this issue?

output of ndb_show_tables

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

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".

So, how do I convince Bugzilla that I gave the requested feedback a month ago?

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'
   =====================
Or 
   =====================
   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!

I've resolved my issue  - I had an errant .frm file in the CIEG data directory.

Table which exists AND does NOT exist

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

I experience the same problem with:
. Windosw XP
. MySql server 5.0.27-community-log, client 5.0.22 (phpMyAdmin 2.9.1.1)

I have 2 (empty) tables which:
. exist when SHOW TABLES or CREATE TABLE
. 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

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.

Closing as not a bug, according to the last comment.

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.

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:

CREATE TABLE TestData (
    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
) ENGINE=InnoDB DEFAULT CHARACTER SET 'utf8';

It is correct that the table has no indexes. I tried `-quoting the table name and changing engines and character sets to no avail.

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.

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:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME=TestData

Is this still a problem in latest version?

Yes, this still happens in 5.1.28-rc on Mac OS X 10.5.5 (Darwin 9.5)

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.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;

-- THIS BROKED!
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

-- THIS DID NOT BROKED!
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.

Please verify this on a proper cluster release,
http://dev.mysql.com/downloads/cluster/

Please check with latest released version. Thanks.

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".