MySQL - Table does not exist and can not be created

Question

MySQL 5.5.46 strange error: Table does not exist and can not be created.

Please, can anyone help?

drop table t_example;

Returns: drop table t_example Error Code: 1051. Unknown table 't_example' 0.000 sec

CREATE TABLE  t_example(
  `id_example` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `des_example` VARCHAR(45) NOT NULL,
  `id_example` int unsigned NULL,
  PRIMARY KEY (`id_example`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

Returns: Error Code: 1050. Table 't_example' already exists

select * from t_example;

Returns: Error Code: 1146. Table 't_example' doesn't exist

I'm using:

UBUNTU 14.04 LTS;
mysql Ver 14.14 Distrib 5.5.46, for debian-linux-gnu (x86_64) using readline 6.
I'm using root user.

Tried:

mysql> REPAIR TABLE t_example;
+----------------+--------+----------+--------------------------------------+
| Table          | Op     | Msg_type | Msg_text                             |              
+ ---------------+--------+----------+--------------------------------------+
| mydb.t_example | repair | Error    | Table 'mydb.t_example' doesn't exist |
| mydb.t_example | repair | status   | Operation failed                     |              
+----------------+--------+----------+--------------------------------------+
2 rows in set (0.00 sec)

Tried too: sudo mysqladmin flush-tables ... Also not solved the problem!

REAL EXAMPLE:

mysql> use flexible;
Database changed
mysql> select * from  st_fin_centro_custo;
ERROR 1146 (42S02): Table 'flexible.st_fin_centro_custo' doesn't exist
mysql> CREATE TABLE  st_fin_centro_custo(
    ->   `cod_centro_custo` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `des_centro_custo` VARCHAR(45) NOT NULL,
    ->   PRIMARY KEY (`cod_centro_custo`))
    ->   ENGINE = InnoDB
    -> DEFAULT CHARACTER SET = utf8
    -> COLLATE = utf8_general_ci;
ERROR 1050 (42S01): Table '`flexible`.`st_fin_centro_custo`' already exists
mysql> drop table st_fin_centro_custo;
ERROR 1051 (42S02): Unknown table 'st_fin_centro_custo'
mysql>

Tried get from MySQL information_schema:

    mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_TYPE 
FROM information_schema.tables 
where table_name like 'st_fin_centro_custo';
    Empty set (0.00 sec)

    mysql>

Note: creating the table with another name works fine.

Thanks a lot!

Are there any `USE` statements in there which you didn't mention? — Tim Biegeleisen, May 16 '16 at 13:21
Are you certain you are on the same database the whole time? — Tim Biegeleisen, May 16 '16 at 13:37
can you provide screenshot demonstrates all this query executed in same order you asked in your question? — Alex, May 16 '16 at 13:47
**mydb.t_example** but you said you **use mydatabase;** so what is your db name? — Alex, May 16 '16 at 13:49
Check database folder on you harddisk. On Linux it usually located at `/var/lib/mysql/`. Probably there are some `t_example.*` files exist. — kkomash, May 16 '16 at 13:54
There is no file with the same name of the table on this folder. (has files to all other tables). — Allan Andrade, May 16 '16 at 13:57
Ok, wild guess, if you try to create table with another name, what you will get? — kkomash, May 16 '16 at 14:05
But with the real name: st_fin_centro_custo - not is possible. — Allan Andrade, May 16 '16 at 14:10
One more idea. Issue command `select * from information_schema.tables where table_name="st_fin_centro_custo";`. — kkomash, May 16 '16 at 14:25

score 1 · Answer 1 · answered May 16 '16 at 14:32

1

First select your data base schema by using use command<use schema>. 
Then run DROP TABLE IF EXISTS t_example;
After that try to create your table 

CREATE TABLE  t_example(
  `id_example` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `des_example` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id_example`))
ENGINE = INNODB
DEFAULT CHARACTER SET = UTF8
COLLATE = UTF8_GENERAL_CI;

Also don't use duplicate column name.

Another solution is : 

 Change table name in the create table query, execute it and then rename the table.

Then, you can also drop this table and after it create it without getting error.

answered May 16 '16 at 14:32

Tirtha Maitra

81
1
4

Can you give me the error ? Because I have just executed this and it's working – Tirtha Maitra May 16 '16 at 14:46
Somehow, you lost the my_user.frm and my_user.ibd files. The data dictionary still has an entry for that table. You cannot run DROP TABLE my_user; because mysqld looks for the my_user.frm first. Since this is no my_user.frm, the table cannot be dropped. Although my_user.frm does not exist, you cannot run CREATE TABLE my_user ... because mysqld thinks it is OK to create the table but then defers to the storage engine. InnoDB says "I already have the tablespace_id of my_user registered". – Tirtha Maitra May 16 '16 at 15:05
This sequence of events can be proved if you create the table using MyISAM. mysqld will allow it. Once you switch to InnoDB, it goes right back to the data dictionary, which is faulty on that one entry. I have two suggestions SUGGESTION #1 Don't create the table with that name anymore. Use a different table name This will result in you changing the table name in your application code SUGGESTION #2 mysqldump all the data, triggers and stored procedures backup ibdata1 delete ibdata1 reload the mysqldump – Tirtha Maitra May 16 '16 at 15:06
Detailed steps from StackOverflow post [link] http://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#4056261 – Tirtha Maitra May 16 '16 at 15:07

score 0 · Answer 2 · answered May 16 '16 at 13:24

0

It's possible your table is corrupted.

Try REPAIR TABLE t_example;

answered May 16 '16 at 13:24

nicolesmithdev

1
3

kkomash · Answer 3 · 2016-05-17T10:22:13.650

0

First of all, try to restart mysqld. If it do not help, try to create this table into another database. Then copy newly created *.frm file to the target database folder. Restart mysqld one more time. Now try to get access/drop/create this table in proper DB.

Edit: How about to drop database and recreate if from the scratch? I hope it's not a production system.

edited May 17 '16 at 10:22

answered May 16 '16 at 15:08

kkomash

91
1
1
3

Now the table is listed whith "show tables;", but when do a SELECT, get error: mysql> select * from st_fin_centro_custo; ERROR 1146 (42S02): Table 'flexible.st_fin_centro_custo' doesn't exist – Allan Andrade May 16 '16 at 21:10

MySQL - Table does not exist and can not be created

3 Answers3