MySQL > Table doesn't exist. But it does (or it should)

Question

I changed the datadir of a MySQL installation and all the bases moved correctly except for one. I can connect and USE the database. SHOW TABLES also returns me all the tables correctly, and the files of each table exists on the MySQL data directory.

However, when I try to SELECT something from the table, I get an error message that the table does not exist. Yet, this does not make sense since I was able to show the same table through SHOW TABLES statement.

My guess is that SHOW TABLES lists file existence but does not check whether a file is corrupted or not. Consequently, I can list those files but not access them.

Nevertheless, it is merely a guess. I have never seen this before. Now, I cannot restart the database for testing, but every other application that uses it is running fine. But that's just a guess, I've never seen this before.

Does anyone know why this is happening?

Example:

mysql> SHOW TABLES;
+-----------------------+
| Tables_in_database    |
+-----------------------+
| TABLE_ONE             |
| TABLE_TWO             |
| TABLE_THREE           |
+-----------------------+
mysql> SELECT * FROM TABLE_ONE;
ERROR 1146 (42S02): Table 'database.TABLE_ONE' doesn't exist

have you restore the database from a backup? or you just copied the db files? do you have root access to the mysql server? — alinoz, Oct 13 '11 at 19:15
I will, but I'll have to restart the server after that, right? Just cant do it now :s — johnsmith, Oct 13 '11 at 19:31
I missed the part that you moved your data folder in my first reading... Now that you have confirmed that problem revolves around InnoDB tables, take a look here: http://bugs.mysql.com/bug.php?id=14582 Creating these tables and then overwriting .frm files should solve your problem. — dev-null-dweller, Oct 13 '11 at 20:54
It is better to export the SQL and import it on the new Database — DavidTaubmann, Sep 24 '15 at 03:02
I don't have a solution, but I do have another mysterious example of how to make the error appear. I have a stand-alone project that reads some WordPress tables and reads and writes some other tables on the same database. It was working fine until I included wp-load.php ( I will need to call some wp methods). With that include I start getting a "Table doesn't exist" on every query of my tables. — BryanT, Nov 17 '16 at 15:30
My problem (including Wordpress wp-load.php) was due to a clash of variables. I had used $table_prefix to make my table names unique. Wordpress used the same variable name, overriding mine! The table, with that wrong name, really did not exist. I hope this helps some one. — BryanT, Nov 17 '16 at 15:56
You should follow: https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html#copy-tables-cold-backup — Chepech, Feb 26 '18 at 05:48
In the future please look at the log files, in order to share a relevant line. Probably in your system you had something like ` [Warning] InnoDB: Load table foo.bar failed, the table has missing foreign key indexes.` as seen in one of the answers. I am writing this message in this old question because no one in the comments has yet said that it is a good practice to look at log files. Thank you! — Valerio Bozz, Aug 29 '22 at 08:23

score 289 · Answer 1 · edited May 23 '16 at 15:44

289

Just in case anyone still cares:

I had the same issue after copying a database directory directly using command

cp -r /path/to/my/database /var/lib/mysql/new_database

If you do this with a database that uses InnoDB tables, you will get this crazy 'table does not exist' error mentioned above.

The issue is that you need the ib* files in the root of the MySQL datadir (e.g. ibdata1, ib_logfile0 and ib_logfile1).

When I copied those it worked for me.

edited May 23 '16 at 15:44

the

21,007
11
68
101

answered Jul 27 '12 at 21:46

Mike Dacre

2,915
1
13
2

28

Saved my life! For anyone else just be sure not to overwrite the existing ib* files if you're trying to copy to a new installation. Backup your existing mysql/ directory, replace with the old one you want to recover, mysqldump everything, then restore the fresh mysql/. Then you can import the mysqldumps properly. – Matthew Nov 15 '12 at 22:13
1

On Mac to replicate my database locally, in addition to copying over the ibdata file (located next to the database dir) I had to `chown _mysql:wheel` the databasename dir, ibdata and all files in the dir (use `chown -R ...`). Similarly, the permissions were incorrect inside the dir so `chmod -R 660 databasename` was needed to get tables to show up in the datatbase. – Dylan Valade Dec 15 '12 at 21:13
4

Thanks Mike. Just to clarify you will need to restart the mysql service to get this working. At least I did, and thank goodness it worked. A lot of data saved there! – Nick Martin Mar 31 '13 at 00:52
Miky, you are great, i formatted my comp and and restored "data" folder...and craaaaaap almost 90% databases had no tables inside...i was doomed...love you dude thanks – www.amitpatil.me Jul 05 '13 at 04:36
i made a fresh installation . but when i am importing my database i am getting this error for my stored procedure – java seeker Mar 11 '14 at 15:26
Rock on - was having this very issue and copied the files. Working now. – dsimer Apr 01 '14 at 12:49
18

NOTE: Do not forget to use `chown`!!! So, all after `cp` use this command -> `chown mysql:mysql /var/lib/mysql/ -R` – Kerem May 11 '14 at 03:14
1

NOTE 2: Do not forget to apply proper permission. In my case `sudo chmod -R 600 /var/lib/mysql ` – Augusto Oct 27 '14 at 15:56
I've just reinstalled XAMPP on Mac, it seems no paths were changed. I still have such an error, I have all the three ib* files in the `/Applications/XAMPP/xamppfiles/var/mysql/` directory, but it doesn't work. – ivkremer Apr 22 '15 at 15:03
Lifesaver! When MySQL died during the Ubuntu upgrade to 15.04 this was the final piece in the recovery puzzle. Thank you. – JohnC May 11 '15 at 09:16
What if the target server already has these files in the /var/lib/mysql folder ? I am worried that i will break something if I replace these. – kentor Aug 12 '15 at 20:34
kentor, found what happens? – Computer's Guy Sep 09 '15 at 07:43
That's why it is better to export the SQL and import it on the new Database – DavidTaubmann Sep 24 '15 at 02:59
Thank you so much. This saved my butt trying to set up replication. – Dessa Simpson Dec 29 '16 at 21:12
Why isn't this answer accepted? Humm, OP's profile shows "Last seen Oct 31 '11 at 15:05" – Majid Fouladpour Jan 13 '17 at 16:32
It works!. Great solution for my envoriment (OsX Sierra and brew setup)! – fractefactos May 01 '17 at 01:37
Those crazy (pity) errors are the "price" to pay for those kind of "free" product :'( – Fabien Feb 02 '18 at 14:53
This also happened to me after restoring a DB by copying over the data folder. Setting that folder to be owned and group-owned by `mysql` allowed my DB client to see all the data again. – Spencer Williams Apr 13 '18 at 18:56
After copying the ib* files, I am getting a checksum error: `InnoDB: Error: checksum mismatch in data file ./ibdata1`. How does one recover from those? – CBenni Jun 04 '18 at 00:04
this saved my life... I was using docker to run mysql, so I copied the data file: /home/Oliver/db_data to another folder , and restart the docker, mapping the data folder to the new folder, everything works... – Han.Oliver Dec 26 '18 at 08:59
Don't make the mistake of renaming the database when you bring the mysql folder into the tmp folder. I did that and I kept on getting errors when restarting the mysql server. – Growling Flea Jan 20 '19 at 23:48
Worked for me, It took 2 days to figure this out :) – Eddie Mar 13 '21 at 18:06
...and what if we lost the ib_log* files and we have ONLY the database folder? asking for a friend... – Strae Sep 11 '21 at 00:49

score 48 · Answer 2 · edited May 23 '16 at 15:45

48

For me on Mac OS (MySQL DMG Installation) a simple restart of the MySQL server solved the problem. I am guessing the hibernation caused it.

edited May 23 '16 at 15:45

the

21,007
11
68
101

answered Feb 12 '14 at 12:38

Martin

621
6
7

Thanks fixed the same issue for me as well. Mine happened after my machine shut down due to a sudden power loss. After the first machine restart/MySQL startup, I got the error. Then, I read this answer. I stopped/started MySQL through System Preferences and it was fixed. – Jeff Evans Jun 11 '14 at 15:19
3

`sudo /usr/local/mysql/support-files/mysql.server restart` – laffuste Mar 25 '15 at 10:33
Likewise. I ran into this after upgrading to macOS Sierra 10.12.6. Not certain there's a causal link, but the timing seems suspicious. – Dave Mulligan Aug 16 '17 at 19:27
Thanks, worked to some extent; i restarted the (5.6, windows) mysql service then ran `check table TABLE_ONE;` I got some errors "partition p2 returned error", "idx_blah_1 is marked as corrupted", and "idx_blah_2 is marked as corrupted". Now I'm back to running `optimize table TABLE_ONE;` and getting error "Table 'database.TABLE_ONE' doesn't exist". – Omar Jan 09 '18 at 18:32
Running MySLQ on Mojave. Restarting through the system preferences panel did not work. I had to restart through command line. – Cortex Jan 21 '19 at 12:48

score 37 · Answer 3 · answered Oct 13 '11 at 19:13

37

I get this issue when the case for the table name I'm using is off. So table is called 'db' but I used 'DB' in select statement. Make sure the case is the same.

answered Oct 13 '11 at 19:13

dkinzer

32,179
12
66
85

20

+1 Field names aren't case sensitive, but table names are. Common mistake, and very annoying. – GolezTrol Oct 13 '11 at 19:15

score 35 · Answer 4 · answered Jun 17 '14 at 12:18

35

This error can also occur when setting lower_case_table_names to 1, and then trying to access tables that were created with the default value for that variable. In that case you can revert it to the previous value and you will be able to read the table.

answered Jun 17 '14 at 12:18

golimar

2,419
1
22
33

7

This bit me. I reverted the value, restarted the database, exported the tables, set the value back to 1, restarted the database, re-imported the tables and everything worked again. – wmarbut Apr 03 '16 at 00:02
2

This was the culprit in my case. – bluelurker Feb 03 '21 at 19:52

score 22 · Answer 5 · answered Jun 29 '16 at 14:34

22

I don't know the reason but in my case I solved just disabling and enabling the foreign keys check

SET FOREIGN_KEY_CHECKS=0;
SET FOREIGN_KEY_CHECKS=1;

answered Jun 29 '16 at 14:34

Bruno Caponi

474
4
6

6

Thanks brother! In my case, I had to disable foreign_key_checks and execute a select query on the disappearing table then the table became normal again. I think there's some foreign key violations in the data rows because I had an interrupted program before this problem happened. – Egist Li Nov 19 '16 at 17:15
Haven't been able to find out why exactly yet, but this also solved my problem – Miroslav Glamuzina Feb 08 '19 at 20:49
3

In my case, it helped to run `SET FOREIGN_KEY_CHECKS=0;`, then execute `SHOW CREATE TABLE …` **in the same console** and then enable back `SET FOREIGN_KEY_CHECKS=1;`. – Alexey Vazhnov Jun 29 '20 at 16:59
This helped us too, some useful info were in logs: `2021-10-01T11:26:26.020904Z 8 [Warning] InnoDB: Load table foo.bar failed, the table has missing foreign key indexes. Turn off 'foreign_key_checks' and try again. 2021-10-01T11:26:26.020927Z 8 [Warning] InnoDB: Cannot open table foo/bar from the internal data dictionary of InnoDB though the .frm file for the table exists. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.` – janedbal Oct 01 '21 at 12:45

score 19 · Answer 6 · edited Jun 21 '18 at 08:26

19

stop mysqld
backup mysql folder: cp -a /var/lib/mysql /var/lib/mysql-backup
copy database folder from old machine to /var/lib/mysql
override ib* (ib_logfile* , ibdata ) from old database
start mysqld
dump dabase
mysqldump >dbase.mysql
stop mysql service
remove /var/lib/mysql
rename /var/lib/mysql-backup to /var/lib/mysql
start mysqld
create the database
mysqldump < dbase.mysql

edited Jun 21 '18 at 08:26

Djizeus

4,161
1
24
42

answered Dec 03 '12 at 00:51

user1772382

536
6
5

In my case, I also had to do: 10.5 delete the directory from under /var/lib/mysql/ – Tony the Tech Sep 19 '14 at 03:42
its not working. :( table 'tablename.wp_posts' doesn't exist – Jahirul Islam Mamun May 21 '20 at 18:00
I backed up my entire `/var/lib/mysql` folder due to some unforseen circumstances and after reinstalling my manjaro box this worked just fine. Awesome my good man! You get one social distancing beer on me. – enchance Jun 29 '20 at 16:36
1

I know this is an old answer, but sharing any explanation about *why* this should work, could be a really useful answer improvement. – Valerio Bozz Aug 29 '22 at 08:25

score 14 · Answer 7 · answered Oct 13 '11 at 19:58

Please run the query:

SELECT 
    i.TABLE_NAME AS table_name, 
    LENGTH(i.TABLE_NAME) AS table_name_length,
    IF(i.TABLE_NAME RLIKE '^[A-Za-z0-9_]+$','YES','NO') AS table_name_is_ascii
FROM
    information_schema.`TABLES` i
WHERE
    i.TABLE_SCHEMA = 'database'

Unfortunately MySQL allows unicode and non-printable characters to be used in table name. If you created your tables by copying create code from some document/website, there is a chance that it has zero-width-space somewhere.

very useful post, thanks! but all tables are ASCII with correct name length — johnsmith, Oct 13 '11 at 20:12

score 13 · Answer 8 · answered Feb 25 '17 at 07:55

13

I had the same problem and I searched for 2-3 days, but the solution for me was really stupid.

Restart the mysql

$ sudo service mysql restart

Now tables become accessible.

answered Feb 25 '17 at 07:55

Siraj Alam

9,217
9
53
65

1

Totally worked for me, although my command was slightly different: $ sudo /usr/local/mysql/support-files/mysql.server restart – KirstieBallance Dec 27 '17 at 18:55
This should be at the top of the list of things to try I guess. Worth a shot and in my case it worked. – Coroos Oct 31 '18 at 08:39

score 12 · Answer 9 · edited Nov 01 '15 at 18:17

12

I have just spend three days on this nightmare. Ideally, you should have a backup that you can restore, then simply drop the damaged table. These sorts of errors can cause your ibdata1 to grow huge (100GB+ in size for modest tables)

If you don't have a recent backup, such as if you relied on mySqlDump, then your backups probably silently broke at some point in the past. You will need to export the databases, which of course you cant do, because you will get lock errors while running mySqlDump.

So, as a workaround, go to /var/log/mysql/database_name/ and remove the table_name.*

Then immediately try to dump the table; doing this should now work. Now restore the database to a new database and rebuild the missing table(s). Then dump the broken database.

In our case we were also constantly getting mysql has gone away messages at random intervals on all databases; once the damaged database were removed everything went back to normal.

edited Nov 01 '15 at 18:17

Krythic

4,184
5
26
67

answered Nov 28 '12 at 12:50

Andy

378
2
8

Thanks Andy, I got a clue for problem I am facing. I moved the ibdata1 from somehwere in C drive to D drive in order to save space crunch over C drive. Thankfully I got the ibdata1 (along with ib_logfile1. and ib_logfile0 file) in my D drive after reading your comments. Now will look from where I moved these file and restore it there. Then hopefully my tables will came back. – AKS Mar 03 '14 at 17:01
How do you "immediately try to dump the table"? I have the same issue, no backups so I'm looking for way to get the table structure at least but if you remove the files from the directory then everything is simply gone? – mmvsbg Dec 03 '16 at 11:17
This did it! Thanks, – jstuardo Aug 30 '17 at 12:10

score 8 · Answer 10 · edited Sep 24 '19 at 16:34

8

Try to run sql query to discard tablespace before copying idb-file:

ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;

Copy idb-file

ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;

Restart MySql

edited Sep 24 '19 at 16:34

reformed

4,505
11
62
88

answered Apr 13 '14 at 20:39

l0pan

476
7
11

You have saved me :) – l00k Nov 14 '16 at 09:32
@I0pan I tried the same steps as mentioned. But after ALTER TABLE mydatabase.mytable IMPORT TABLESPACE; it is showing table doesn't exist. But it does :( – Syed Asad Abbas Zaidi Aug 31 '17 at 07:29

PlanetUnknown · Answer 11 · 2015-11-01T21:47:23.990

7

O.k. this is going to sound pretty absurd, but humor me.
For me the problem got resolved when I changed my statement to this :

SELECT * FROM `table`

I made two changes
1.) Made the table name lower case - I know !!
2.) Used the specific quote symbol = ` : It's the key above your TAB

The solution does sound absurd, but it worked and it's Saturday evening and I've been working since 9 a.m. - So I'll take it :)

Good luck.

edited Nov 01 '15 at 21:47

answered Dec 13 '14 at 22:55

PlanetUnknown

3,956
4
49
67

1

Just FYI - The table is MyISAMand not INNO – PlanetUnknown Dec 13 '14 at 22:56
2

Also the ` is called a backtick – Gary Mar 11 '16 at 12:56
@PlanetUnknown "It's the key above your TAB" - How do you know that's true? I have it at the right side of my "P" key. Not everyone has the same keyboard layout. – Francisco Zarabozo Aug 30 '20 at 20:21

score 6 · Answer 12 · answered Nov 19 '13 at 17:39

6

What worked for me, was just dropping the table, even though it didnt exist. Then I re created the table and repopulated from an sql dump done previously.

There must be some metabase of table names, and it was most likely still existing in there till i dropped it.

answered Nov 19 '13 at 17:39

Zoobra McFly

81
1
4

I had created a procedure, realized it needed to be a view. So I renamed the procedure with some zzz's at the end so I could have it for reference and created a view of the same name. Couldn't get a SELECT to see it, got this error.
Copied the code to a text file, deleted both the view and the procedure. Recreated the view and all was well.
So yeah--seven years later--there's still some sort of ghost/cached name action going on in some edge cases. – Roger Krueger Mar 10 '20 at 19:55

score 5 · Answer 13 · edited May 22 '14 at 05:21

5

Had a similar problem with a ghost table. Thankfully had an SQL dump from before the failure.

In my case, I had to:

Stop mySQL
Move ib* files from /var/mysql off to a backup
Delete /var/mysql/{dbname}
Restart mySQL
Recreate empty database
Restore dump file

NOTE: Requires dump file.

edited May 22 '14 at 05:21

bjb568

11,089
11
50
71

answered Jan 05 '13 at 16:45

Oli Stockman

322
3
10

I guess you mean `/var/lib/mysql` instead of `/var/mysql` – knocte Jun 30 '16 at 04:14
Removing the database directories and restoring from backup was the only thing that helped me. – Jano Apr 27 '17 at 22:37

score 5 · Answer 14 · edited Nov 23 '16 at 07:06

5

I had this problem after upgrading WAMP but having no database backup.

This worked for me:

Stop new WAMP
Copy over database directories you need and ibdata1 file from old WAMP installation
Delete ib_logfile0 and ib_logfile1
Start WAMP

You should now be able to make backups of your databases. However after your server restarts again you will still have problems. So now reinstall WAMP and import your databases.

edited Nov 23 '16 at 07:06

Rohan Khude

4,455
5
49
47

answered Jun 08 '16 at 15:10

ykay says Reinstate Monica

4,634
4
32
42

I wish people would indicate where the files that they reference reside... – MagentoAaron Feb 19 '19 at 13:42
Got here from mysql docker image not having readable tables. Can confirm that stopping the image, deleting these files, and restarting gave access again. – Anthony Harley Jun 03 '19 at 15:05
This isn't necessary the same problem. Deleting ib_logfile0/1 causes [uses to lose data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991). Usually the error log has a descriptive message that should be read before causing a likely data corruption. – danblack Nov 14 '22 at 23:50

score 4 · Answer 15 · answered Sep 05 '12 at 22:21

4

After having to reinstall MySQL I had this same problem, it seems that during the install, some configuration files that store data about the InnoDB log files, these files ib_logfile* (they are log files right?), are overwriten. To solve this problem I just deleted the ib_logfile* files.

answered Sep 05 '12 at 22:21

jonathancardoso

11,737
7
53
72

This isn't necessary the same problem. Deleting ib_logfile0/1 causes [uses to lose data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991). Usually the error log has a descriptive message that should be read before causing a likely data corruption. – danblack Nov 14 '22 at 23:51

score 3 · Answer 16 · edited Jan 18 '18 at 02:05

3

Do mysqldump to database:

mysqldump -u user -ppass dbname > D:\Back-ups\dbname.sql

Restore database

mysql -u user -ppass dbname < D:\Back-ups\dbname.sql

Now all tables in database were restored completely. Try..

SELECT * FROM dbname.tablename;

edited Jan 18 '18 at 02:05

Stephen Rauch

47,830
31
106
135

answered Jan 18 '18 at 01:42

Zaw Htoon

59
1
5

score 2 · Answer 17 · edited May 23 '17 at 10:31

It appears that the issue has to do (at least in mine and a few others) with invalid (corrupt?) innodb log files. Generally speaking, they simply need to be recreated.

Here are solutions, most of which require a restart of mysql.

Recreate your log files (Delete and restart mysql)
Resize your log files (MySql 5.6+ will regenerate the file for you)
If you are doing some type of a data migration, make sure you have correctly migrated the right file and given it permissions as others have already stated
Check permissions of your data and log files, that mysql is owner of both
If all else fails, you will likely have to recreate the database

Importantly "SET GLOBAL innodb_fast_shutdown = 0" is required before a shutdown. — danblack, Nov 14 '22 at 23:57

score 2 · Answer 18 · answered Dec 31 '17 at 06:26

2

In my case, i had defined a trigger on the table and then was trying to insert the row in table. seems like, somehow trigger was erroneous, and hence insert was giving error, table doesn't exist.

answered Dec 31 '17 at 06:26

Yogesh Kumar Gupta

1,009
1
10
10

1

This works for me! Checked each trigger and found one trigger needs to be improved and it worked! – Paresh May 02 '19 at 06:53

score 2 · Answer 19 · answered Feb 18 '18 at 07:02

2

Copy only ibdata1 file from your old data directory. Do not copy ib_logfile1 or ib_logfile0 files. That will cause MySQL to not start anymore.

answered Feb 18 '18 at 07:02

Plabon Dutta

6,819
3
29
33

Just because it starts doesn't mean it won't be horribly corrupted. Lack of ib_logfile0/1 causes [uses to lose data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991). Usually the error log has a descriptive message that should be read before causing a likely data corruption. – danblack Nov 14 '22 at 23:54

score 2 · Answer 20 · answered Apr 10 '18 at 10:19

Came cross same problem today. This is a mysql "Identifier Case Sensitivity" issue.

Please check corresponding data file. It is very likely that file name is in lower case on file system but table name listed in "show tables" command is in upper case. If system variable "lower_case_table_names" is 0, the query will return "table not exist" because name comparisons are case sensitive when "lower_case_table_names" is 0.

score 1 · Answer 21 · edited Nov 23 '16 at 05:57

In my case it was SQLCA.DBParm parameter.

I used

SQLCA.DBParm = "Databse = "sle_database.text""

but it must be

SQLCA.DBParm = "Database='" +sle_database.text+ "'"

Explaination :

You are going to combine three strings :

 1. Database='              -  "Database='"

 2. (name of the database)  - +sle_database.text+

 3. '                       - "'" (means " ' "  without space)

Don't use spaces in quatermarks. Thank to my colleague Jan.

score 1 · Answer 22 · answered Mar 10 '14 at 13:34

1

I installed MariaDB on new computer, stopped Mysql service renamed data folder to data- I solved my problem copying just Mysql\data\table_folders and ibdata1 from crashed HD MySql data Folder to the new installed mysql data folder.

I Skipped ib_logfile0 and ib_logfile1 (otherwise the server did not start service)

Started mysql service.

Then server is running.

answered Mar 10 '14 at 13:34

Tony

16,527
15
80
134

What was the error log message that prevented the server from starting? Where you using the same MariaDB version? Deleting ib_logfile0/1 causes [uses to lose data](https://dba.stackexchange.com/questions/318946/mariadb-doesnt-work-after-system-crash/318991#318991). Usually the error log has a descriptive message that should be read before causing a likely data corruption. – danblack Nov 14 '22 at 23:55

score 1 · Answer 23 · answered Mar 13 '14 at 13:12

1

Same exact problem after TimeMachine backup import. My solution was to stop the MySQL server and fix read-write permissions on the ib* files.

answered Mar 13 '14 at 13:12

user3415481

11
1

score 1 · Answer 24 · answered Aug 13 '14 at 18:57

One other answer I think is worth bringing up here (because I came here with that same problem and this turned out to be the answer for me):

Double check that the table name in your query is spelled exactly the same as it is in the database.

Kind of an obvious, newbie thing, but things like "user" vs "users" can trip people up and I thought it would be a helpful answer to have in the list here. :)

Onur Kucukkece · Answer 25 · 2015-01-31T00:11:58.573

1

In my case, when I was importing the exported sql file, I was getting an error like table doesn't exist for the create table query.

I realized that there was an underscore in my database name and mysql was putting an escape character just before that.

So I removed that underscore in the database name, everything worked out.

Hope it helps someone else too.

edited Jan 31 '15 at 00:11

answered Jan 28 '15 at 08:57

Onur Kucukkece

1,708
1
20
46

score 1 · Answer 26 · answered May 31 '16 at 23:34

Here is another scenario (version upgrade):

I reinstalled my OS (Mac OS El Captain) and installed a new version of mysql (using homebrew). The installed version (5.7) happened to be newer than my previous one. Then I copied over the tables, including the ib* files, and restarted the server. I could see the tables in mysql workbench but when I tried to select anything, I got "Table doesn't exist".

Solution:

stop the mysql server e.g. mysql.server stop or brew services stop mysql
start the server using mysqld_safe --user=mysql --datadir=/usr/local/var/mysql/ (change path as needed)
run mysql_upgrade -u root -p password (in another terminal window)
shut down the running server mysqladmin -u root -p password shutdown
restart the server in normal mode mysql.server start or brew services start mysql

Relevant docs are here.

Tried really a lot but this was the only thing what helped me a lot after I moved to a new server with all my databases. Thanks! (Ubuntu 16.04) — Falk, Jan 03 '18 at 09:26

score 1 · Answer 27 · answered Aug 26 '16 at 12:55

My table had somehow been renamed to ' Customers' i.e. with a leading space

This meant

a) queries broke

b) the table didn't appear where expected in the alphabetical order of my tables, which in my panic meant I couldn't see it!

RENAME TABLE ` Customer` TO `Customer`;

Abu Sufian · Answer 28 · 2017-09-19T09:22:41.993

1

Go to :xampp\mysql\data\dbname
inside dbname have tablename.frm and tablename.ibd file.
remove it and restart mysql and try again.

edited Sep 19 '17 at 09:22

answered Jun 14 '17 at 08:40

Abu Sufian

439
5
8

score 1 · Answer 29 · answered Dec 22 '19 at 21:02

I had the same issue in windows. In addition to copying the ib* files and the mysql directory under thd data directory, I also had to match the my.ini file.

The my.ini file from my previous installation did not have the following line:

innodb-page-size=65536

But my new installation did. Possibly because I did not have that option in the older installer. I removed this and restarted the service and the tables worked as expected. In short, make sure that the new my.ini file is a replica of the old one, with the only exception being the datadir, the plugin-dir and the port#, depending upon your new installation.

score 1 · Answer 30 · answered Feb 18 '23 at 18:52

1

The following worked for me, thanks to Alexey Vazhnov's comment. I ran this:

SET FOREIGN_KEY_CHECKS=0;
SHOW CREATE TABLE <tableName>;
SET FOREIGN_KEY_CHECKS=1;

And it restored my corrupted table <tableName>.

answered Feb 18 '23 at 18:52

reformed

4,505
11
62
88

1

I was about to delete a database and recreate it. this trick saved! – peterpeterson Feb 25 '23 at 05:45
Same here! Who would have thought! – reformed Feb 27 '23 at 15:03

score 1 · Answer 31 · answered Oct 13 '11 at 19:57

1

Its possible you have a hidden character in your table name. Those don't show up when you do a show tables. Can you do a "SHOW CREATE TABLE TABLE_ONE" and tab complete the "TABLE_ONE" and see if it puts in any hidden characters. Also, have you tried dropping and remaking the tables. Just to make sure nothing is wrong with the privileges and that there are no hidden characters.

answered Oct 13 '11 at 19:57

Hoopdady

2,296
3
25
40

tab completing doesnt help and I cant show create table because table "doesnt exists". from the hell – johnsmith Oct 13 '11 at 20:13

score 0 · Answer 32 · edited Oct 26 '12 at 06:36

I had the same problem, but it wasn't due to a hidden character or "schroedinger's table". The problem (exactly as noted above) appeared after a restore process. I'm using MySQL administrator version 1.2.16. When a restore has to be carried out, you must have unchecked ORIGINAL at the target schema and select the name of your data base from the drop box. After that the problem was fixed. At least that was the reason in my database.

score 0 · Answer 33 · answered Sep 22 '14 at 09:48

0

If there's a period in the table name, it will fail for SELECT * FROM poorly_named.table;

Use backticks to get it to find the table SELECT * FROM `poorly_named.table`;

answered Sep 22 '14 at 09:48

Chris

2,174
28
37

Fabien Haddadi · Answer 34 · 2016-08-18T09:23:33.077

0

In my case, I had that without doing a datadir relocation or any kind of file manipulation. It just happened one fine morning.

Since, curiously, I was able to dump the table, using mysqldump, despite MySQL was sometimes complaining about "table does not exist", I resolved it by dumping the schema + data of the table, then DROP-ing the table, and re CREATE it immediately after, followed by an import.

edited Aug 18 '16 at 09:23

answered Aug 10 '16 at 12:09

Fabien Haddadi

1,814
17
22

MySQL > Table doesn't exist. But it does (or it should)

34 Answers34

Linked

Related