Error 1146 table doesn t exist

Last updated on JUNE 17, 2022

Applies to:

Symptoms

While waiting for clone to finish...

Changes

 In MySQL Version 8.0.28 and later, it does not seem possible to reproduce.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

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.

If this command fails with the error, it means that

show tables;

show tables;

show tables;

show tables;

 mysql -u admin -p

Directadmin

Suppose, we get the error for the User database and Table:

 mysql -u admin -p

1. In this case, we check to see if there are any other data files, or if it’s just the .frm file:

    mysql -u admin -p
   

   6

   If it’s just the table.frm file, then the rest of the data is likely lost. However, we may be able to rebuild the table.

2. To do so, we need to read the .frm file. We need the mysqlfrm tool for that, eg: yum install mysql-utilities. Once we install it, we check if it can be read:

    mysql -u admin -p
   

   7

   This can output the full CREATE TABLE syntax.  We save this somewhere, until the end of the last ; character.

   Note, we can either delete the “CHARACTER SET “,  or change it to the correct charset.

3. Then, we remove the broken table.  To do so, we login to /phpMyAdmin and run the query:

    mysql -u admin -p
   

   8
4. Finally, we run the CREATE TABLE query from above, to rebuild the table.

[Finding it hard to fix? We’d be happy to assist you]

Conclusion

In short, we saw how our Support Techs fix the MySQL error for our customers.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

In our role as Support Engineers for web hosts, we manage servers with various services such as web, database, mail, control panels, FTP, etc.

MySQL is the most commonly used database server in Linux hosting and handling the databases and resolving the errors associated with it, is a common task that we perform.

A commonly noticed error in MySQL server is ‘1146 table doesn’t exist’. Today we’ll see what causes this ‘1146 table doesn’t exist’ error in MySQL and how to fix it.

Error : Table ‘mysql.innodb_index_stats’ doesn’t exist
status : Operation failed

What causes MySQL ‘1146 table doesn’t exist’ error

MySQL table errors happen due to many reasons, the major ones we’ve come across include:

1. InnoDB crash – When the InnoDB server crash due to any process load or user abuse, or if the server was not restarted properly, it can get corrupt and cause table errors to show up.
2. Missing ibdata file in the MySQL datadir – InnoDB has a data dictionary – the ibdata file and log files, which are crucial for InnoDB to function. If during migrations or restorations, these files go missing, it can prevent InnoDB tables from functioning right.
3. Improperly placed .frm files – In InnoDB, tables have ‘.frm’ files that define the table format. If these files get deleted or were missed to copy over to the proper database directory, then the tables can show errors.
4. Incorrect permissions and ownership of MySQL datadir – MySQL has a data directory, usually ‘/var/lib/mysql’ that stores the databases. If the permission and ownership of this directory is not adequate for MySQL to access it, errors would occur.
5. Corrupt tables or improper table names – If the database tables got corrupt due to improper server shut down or incomplete queries, or if the table name format is not correct, the ‘1146 table doesn’t exist’ error may show up.

[ You don’t have to lose your sleep over server errors. Our expert server support specialists monitor & maintain your servers 24/7/365 and keep them rock solid. ]

How to fix MySQL ‘1146 table doesn’t exist’ error

Inorder to fix the error ‘1146 table doesn’t exist’, we adopt different techniques, after analyzing the root cause of the error.

1. Restart MySQL server – If the error has happened due to improper server shut down or MySQL service related errors, we restart the service and check if it fixes the issue. If the service doesn’t start properly, we further investigate and fix the error.
2. Repair the tables – MySQL has tools such as ‘myisamchk’ to repair corrupt databases and tables.  
3. Backup restore – Restoring database backups is the final resort to get the tables back to working condition. We always configure and maintain the backups in our customers’ servers up to date, inorder to ensure that there is no data loss or down time due to unexpected crashes or errors.
4. Copy ibdata file – If the ‘ibdata’ file is missing, we copy it from the backup and restore it to the data directory for MySQL, after discarding the tablespace to avoid any corruptions or errors.
5. InnoDB crash recovery – In case where the backup is incomplete or ibdata file is also corrupt, we’ve still been able to recover the tables via our expert crash recovery methods. Read the post ‘Database crash rescue‘ to know more.

[ Use your time to build your business. We’ll take care of your servers. Hire Our server experts to resolve and prevent server issues. ]

At Bobcares, our 24/7 Web Support Specialists constantly monitor all the services in the server and proactively audit the server for any errors or corruption in them.

With our systematic debugging approach for service or other software errors, we have been able to provide an exciting support experience to the customers.

If you would like to know how to avoid downtime for your customers due to errors or other service failures, we would be happy to talk to you.

How do I fix error 1146?

How do I fix a table that doesn't exist in MySQL?

How to repair a table in MySQL?

How to repair MySQL database command line?