MySQL Create Table Error - Table Doesn't Exist

Question

I am new to MySQL, and I am having a problem where if I try to create a Table in my newly created Database "recommend", I get the following error:

ERROR 1146 (42S02): Table 'recommend.Users' doesn't exist

I checked related posts here and on the internet but nothing helped. If I use the MySQL command line I still get the same error.

SELECT DATABASE() FROM DUAL;

+------------+

| DATABASE() |

+------------+

| recommend  |

+------------+

1 row in set (0.00 sec)

but then when i run this command :

mysql> use recommend
Database changed

mysql> CREATE TABLE Users (UserName VARCHAR(20),password VARCHAR(20),PRIMARY KEY(UserName));

ERROR 1146 (42S02): Table 'recommend.Users' doesn't exist

I also tried using Navicat and still get the same error :(

Allen · Accepted Answer · 2015-07-19 02:45:42Z

16

I had the same problem. I actually read this thread before I got lucky with a simple solution. I attempted to drop my table and it worked, in your case your table is User:

DROP TABLE Users;

The table does not exist, so naturally MySQL complains:

Error Code: 1051. Unknown table 'Users'

But then I ran my CREATE TABLE statement again and it worked. Hopefully others can validate this works every time? A lot easier than going to an error log, especially if you are not the greatest DBA/System Admin/Hacker.

answered Jul 19, 2015 at 2:45

Allen

2062 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Michael Over a year ago

Much easier than mucking around in the file system! Thanks!

sba Over a year ago

Worked for me as well. An elegant way in case you don´t care about a table data backup.

user1623454 Over a year ago

Works also for me, if it doesn't work for you try writing the DROP command before the CREATE command in the same script.

user1864610 · Accepted Answer · 2013-08-03 16:03:20Z

7

This looks like a data dictionary problem. You can get more information by checking the error log. (See the MySQL docs here).

Possibly, you have an orphaned table. If so, the solution is to create a table of the same name in a different database, then copy the .frm file to the current database. Then you can DROP the table, and a subsequent CREATE should then succeed. More details on troubleshooting this sort of problem can be found here

answered Aug 3, 2013 at 16:03

user1864610

2 Comments

Amit P Over a year ago

I just uninstalled mysql server and dropped the old Tables, fixed this issue now.

Amit P Over a year ago

Thanks a tonns for the help :) Cheers :)

Stephen Rauch · Accepted Answer · 2018-06-04 02:34:01Z

1

The Problem was... You have a DB with "InnoDB ENGINE"....... My Solution was... You must have a "MyISAM ENGINE", how to change?... find on your environment may be with

# find / -name my.cnf

on LINUX, simply add the following line to

vim /etc/mysql/my.cnf

Add:

default-storage-engine= MyISAM

Then restart mysql:

service mysql restart

edited Jun 4, 2018 at 2:34

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Jun 4, 2018 at 2:08

IbarMario

512 bronze badges

1 Comment

hannad rehman Over a year ago

this worked. thanks so much. but i didnt change it in my conf. added in query only

Collectives™ on Stack Overflow

MySQL Create Table Error - Table Doesn't Exist

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related