CODEWITHSUNDEEP
mysql
Amit P
Amit P

Reputation: 944

MySQL Create Table Error - Table Doesn't Exist

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

Upvotes: 14

Views: 43983

Answers (3)

IbarMario
IbarMario

Reputation: 51

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

Upvotes: 1

Allen
Allen

Reputation: 206

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.

Upvotes: 16

user1864610
user1864610

Reputation:

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

Upvotes: 7

Related Questions