MySQL database is a relational database system which contains tables, these tables are formally described and they contain data within them. The data could be accessed and modify with various ways and there is no need for changing the order of tables for it. Pretty cool, Isn’t it? But sometimes corruption or mishandling of the data can lead us to errors. SQL Error 1146 Table doesn’t exist is also one of those common errors which can take place during MySQL handling.
So, in this handout, I’ll elaborate Error 1146 a.k.a. Table doesn’t exist, the reason behind this error and how to resolve MySQL code 1146. So without wasting time, let’s get started.
Why Error 1146 Takes Place?
There are multiple reasons and catalyst to this error. Some of the causes to error 1146 are stated below:
InnoDB crash and missing data files:
As we all know that InnoDB is the transaction-safe (ACID compliant) storage engine for MySQL database, it offers multiple capabilities like Rollback, Commit and crash recovery for data of MySQL database. The InnoDB is also prone to corruption, so if it is not handled correctly there is a chance of crash of InnoDB. InnoDB is functional because of ibdata file and log file. It acts as a data dictionary to it. If by any reason these files go missing, there will be no chance of working InnoDB and MySQL will show you error 1146.
Improper directory accessing permissions:
MySQL has a data storage directory where it stores all the database. Most of the time path is ‘/var/lib/mysql’. If the ownership and access permissions are not right and MySQL is not able to access this directory path then the MySQL code 1146 will be thrown.
Corruption in data tables:
Tables of MySQL database can get corrupt due to several reasons like improper server shutdown, incomplete queries, user abuse, wrong formats etc. So if the table got corrupt the database can show error ‘table doesn’t exist’.
.frm file missing
.frm file contains the format and structure of the database. If anyhow this file is not copied to the database directory or it gets deleted then there will be an error message for error code a.k.a. Table doesn’t exist.
How to resolve MySQL Error 1146 ‘Table doesn’t exist’?
To fix error 1146 from MySQL database, we can use multiple remedies and DIYs like:
This is the best alternative you should use. It will surely recreate all the lost tables from the database and MySQL code 1146 will also get solved but keep in mind that there should be a backup file to restore. So always keep the habit of creating backup time to time, it’s a good practice.
Restart the Server:
Try to restart the server if any improper server shutdown took place. There is a chance to get it back in working condition and removing error code.
Repair Database Tables:
You can repair database tables using MySQL CLI. All you need to do is to follow the stated steps:
Step 1. Using SSH, log in to the server.
Step 2. From the command line, execute the following command:
mysql -u [username] -p
Note: Replace username with your username and remove the brackets.
Step 3. Now enter your password for the username.
Step 4. Again enter the command:
Note: provide the name of the database without brackets.
Step 5. Now execute the following command:
Step 6. Now repair table using following commands:
(a) Check for tables with error:
check table [tablename];
(b) Repair tables:
repair table [tablename];
Step 7. Now quit using quit command.
Keep a copy of .frm file:
You can keep a copy of .frm file and whenever the error 1146 arrises, you can paste the .frm file to the schema folder. It can fix error 1146.
Copy the ibdata file from backup:
You should keep a copy of ibdata file as a backup so whenever the ‘table doesn’t exist’ takes place you can paste it to the data directory of MySQL server.
Rely on Third Party Tools:
If still, the SQL error 1146 is taking place, you should go for a professional tool rather relying on the DIYs. You can use MySQL data Recovery tool. It is the best tool to recover the corrupt tables and to fix MySQL code 1146 ‘table doesn’t exist’.
So, this was MySQL code 1146 ‘table doesn’t exist’. I’ve also tried to give you some quick remedies and DIYs to resolve this error. It becomes really hard and disappointing when you face such errors cause these type of errors can lead you to lose all your valuable data. Try your best and keeping the backup of data is the best precaution you can take. It will help you in such a critical situation to restore your MySQL database in the previous condition and it will also fix SQL error 1146. So hope for the best and do it.
You Might Like: