How to resolve MySQL ‘1146 table doesn’t exist’ errors in your server
In our role as Hosting 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:
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- Repair the tables – MySQL has tools such as ‘myisamchk’ to repair corrupt databases and tables.
- 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.
- 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.
- 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.