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 gt  SHOW TABLES   -----------------------    Tables in database       -----------------------    TABLE ONE                 TABLE TWO                 TABLE THREE              -----------------------  mysql gt  SELECT   FROM TABLE ONE  ERROR 1146  42S02   Table  database TABLE ONE  doesn t exist

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  gt dbase mysql stop mysql service remove  var lib mysql rename  var lib mysql-backup to  var lib mysql start mysqld create the database mysqldump  lt  dbase mysql

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Do mysqldump to database   mysqldump -u user -ppass dbname  gt  D  Back-ups dbname sql  Restore database  mysql -u user -ppass dbname  lt  D  Back-ups dbname sql    Now all tables in database were restored completely  Try    SELECT   FROM dbname tablename

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to exists

Examples related to database-table