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:
- 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.
var google_conversion_label = «owonCMyG5nEQ0aD71QM»;
I am using windows XP. I am creating a table in phpMyAdmin using its built-in create table feature,
my database name is ddd.
It generates the following code:
CREATE TABLE `ddd`.`mwrevision` (
`asd` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`sddd` INT NOT NULL
) ENGINE = INNODB;
and the following error shows up:
MySQL said:
#1146 - Table 'ddd.mwrevision' doesn't exist
What might be the problem?
mins
6,27612 gold badges55 silver badges75 bronze badges
asked Jun 14, 2011 at 10:29
9
I also had same problem in past. All had happend after moving database files to new location and after updating mysql server. All tables with InnoDB engine disappeared from my database. I was trying to recreate them, but mysql told me 1146: Table 'xxx' doesn't exist all the time until I had recreated my database and restarted mysql service.
I think there’s a need to read about InnoDB table binaries.
answered Dec 7, 2011 at 4:43
sempashasempasha
6235 silver badges18 bronze badges
2
I had the same problem and can’t get a good tip for this over the web, so I shared this for you and for all who needs.
In my situation I copy a database (all files: frm, myd) to the data folder in MySQL data folder (using Wamp at home). All thing was OK until I want to create a table and have the error #1146 Table '...' doesn't exist!.
I use Wamp 2.1 with MySQL version 5.5.16.
My solution:
-
Export the database to file;
-
verify if exported file is really OK!!;
-
drop the database where I have issues;
-
create a new database with the same name that the last;
-
import the file to the database.
FOR ME IS PROBLEM SOLVED. Now I can create tables again without errors.
answered Jan 24, 2012 at 6:30
carloscarlos
1111 silver badge2 bronze badges
1
Restarting MySQL works fine for me.
answered Nov 2, 2014 at 18:24
Muhammad UsmanMuhammad Usman
10.3k22 gold badges72 silver badges107 bronze badges
0
In my case I ran this command even if the table wasn’t visible in PhpMyAdmin :
DROP TABLE mytable
then
CREATE TABLE....
Worked for me !
answered May 21, 2015 at 9:22
Jim 007Jim 007
3101 gold badge3 silver badges8 bronze badges
1
Check filenames.
You might need to create a new database in phpmyadmin that matches the database you’re trying to import.
Mayur Birari
5,8378 gold badges34 silver badges61 bronze badges
answered Nov 21, 2012 at 15:23
blargblarg
3,75311 gold badges41 silver badges69 bronze badges
2
I had the same problem. I tried to create a table in mysql and got the same error. I restarted mysql server and ran the command and was able to create/migrate table after restating.
answered May 10, 2013 at 11:10
thekosmixthekosmix
1,70320 silver badges34 bronze badges
Today i was facing same problem. I was in very difficult situation but what id did i create a table with diffrent name e.g (modulemaster was not creating then i create modulemaster1) and after creating table i just do the rename table.
answered Jun 12, 2013 at 11:57
I encountered the same problem today. I was trying to create a table users, and was prompted that ERROR 1146 (42S02): Table users doesn't exist, which did not make any sense, because I was just trying to create the table!!
I then tried to drop the table by typing DROP TABLE users, knowing it would fail because it did not exist, and I got an error, saying Unknown table users. After getting this error, I tried to create the table again, and magically, it successfully created the table!
My intuition is that I probably created this table before and it was not completely cleared somehow. By explicitly saying DROP TABLE I managed to reset the internal state somehow? But that is just my guess.
In short, try DROP whatever table you are creating, and CREATE it again.
answered Apr 6, 2015 at 17:51
XinXin
4,3325 gold badges18 silver badges15 bronze badges
As pprakash mentions above, copying the table.frm files AND the ibdata1 file was what worked for me.
In short:
- Shut your DB explorer client (e.g. Workbench).
- Stop the MySQL service (Windows host).
- Make a safe copy of virtually everything!
- Save a copy of the table file(s) (eg mytable.frm) to the schema data folder (e.g. MySQL Server/data/{yourschema}).
- Save a copy of the ibdata1 file to the data folder (i.e., MySQL Server/data).
- Restart the MySQL service.
- Check that the tables are now accessible, queryable, etc. in your DB explorer client.
After that, all was well. (Don’t forget to backup if you have success!)
answered Aug 17, 2016 at 23:46
SteveCinqSteveCinq
1,9001 gold badge16 silver badges22 bronze badges
Column names must be unique in the table. You cannot have two columns named asd in the same table.
answered Jun 14, 2011 at 10:32
OswaldOswald
31.1k3 gold badges43 silver badges68 bronze badges
8
run from CMD & %path%=set to mysql/bin
mysql_upgrade -u user -ppassword
answered Jun 14, 2011 at 11:04
Ravi ParekhRavi Parekh
5,1249 gold badges45 silver badges58 bronze badges
2
Recently I had same problem, but on Linux Server. Database was crashed, and I recovered it from backup, based on simply copying /var/lib/mysql/* (analog mysql DATA folder in wamp). After recovery I had to create new table and got mysql error #1146. I tried to restart mysql, and it said it could not start. I checked mysql logs, and found that mysql simply had no access rigths to its DB files. I checked owner info of /var/lib/mysql/*, and got 'myuser:myuser' (myuser is me). But it should be 'mysql:adm' (so is own developer machine), so I changed owner to ‘mysql:adm’. And after this mysql started normally, and I could create tables, or do any other operations.
So after moving database files or restoring from backups check access rigths for mysql.
Hope this helps…
thvwns
3,5483 gold badges41 silver badges57 bronze badges
answered Aug 23, 2013 at 8:32
The reason I was facing this was because I had two «models.py» files which contained slightly different fields.
I resolved it by:
- deleting one of the models.py files
- correcting references to the deleted file
- then running manage.py syncdb
answered Nov 11, 2013 at 7:02
I got this issue after copying mytable.idb table file from another location. To fix this problem I did the following:
ALTER TABLE mydatabase.mytable DISCARD TABLESPACE;
Copy mytable.idb
ALTER TABLE mydatabase.mytable IMPORT TABLESPACE;
Restart MySql
answered Apr 13, 2014 at 20:34
l0panl0pan
4667 silver badges11 bronze badges
1
I had the same issue. It happened after windows start up error, it seems some files got corrupted due to this. I did import the DB again from the saved script and it works fine.
answered Oct 31, 2014 at 22:51
I had this problem because of a trigger not working..Worked after I deleted the trigger.
answered Aug 2, 2016 at 12:21
DauleDKDauleDK
3,19311 gold badges54 silver badges96 bronze badges
In my case, MySQL’s parameter; lower_case_table_names was configured = 0.
It causes queries related with using upper cases will not work.
answered Aug 9, 2017 at 6:25
hiroponhiropon
1,6452 gold badges18 silver badges41 bronze badges
For me it was a table name upper/lower case issue. I had to make sure that table case name matched in a delete query, table notifications was not the same as Notifications. I fixed it by matching table name case with query and what MySQLWorkbench reported.
What is wierd is that this error showed up in a worked sql statement. Don’t know what caused this case sensitivity. Perhaps an auto AWS RDS update.
answered Mar 16, 2018 at 15:43
KahitarichKahitarich
3852 silver badges7 bronze badges
if you are modifying mysql bin->data dir’s and after that, your database import will not works
so you need to close wamp and after that start wamp
now database import will work fine
answered Jan 15, 2021 at 19:03
Hassan SaeedHassan Saeed
6,2261 gold badge37 silver badges37 bronze badges
Make sure you do not have a trigger that is trying to do something with the table mentioned in the error. I was receiving Error Code: 1146. Table 'exampledb.sys_diagnotics' doesn't exist on insert queries to another table in my production database. I exported the table schemas of my production database then searched for instances of exampledb.sys_diagnotics the schema SQL and found a debugging insert statement I had added to a table trigger in my development environment but this debug statement had been copied to production. The exampledb.sys_diagnotics table was not present on my production database. The error was resolved by removing the debug statement in my table trigger.
answered May 4, 2022 at 19:33
I am new to this site — be gentle please 
mySQL database on localhost works for an insert statement,but as soon as I change the database connection to the server connection it gives me the error: Error code 1146: 1146 without any error description. What could be the reason?
According to my error capturing logic, the connection is successful all the way up to the part of the query run. On the local version it works like a charm. any ideas?
::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
if($errors == 0)
{
$DBConnect = @mysql_connect("db.start.ca", "nokeekwe", "classifiedlol");
if($DBConnect === FALSE)
{
echo "<p>Unable to connect. " .
"Error code " . mysql_errno() . ": " .
mysql_error() . "</p>n";
$errors++;
}
else
{
$DBName = "nokeekwe";
$result = @mysql_select_db($DBName, $DBConnect);
if($result === FALSE)
{
echo "<p>Unable to select DB. " .
"Error code " . mysql_errno($DBConnect) .
": " . mysql_error($DBConnect) .
"</p>n";
$errors++;
}
}
}
if($errors > 0)
{
echo "<p>Please use your browser's BACK button" .
" to return to the form and fix your errors.</p>n";
}
if($errors == 0)
{
$title = stripslashes($_POST['title']);
$desc = stripslashes($_POST['desc']);
$req = stripslashes($_POST['req']);
$employer = stripslashes($_POST['employer']);
$phone = stripslashes($_POST['phone']);
$fax = stripslashes($_POST['fax']);
$email = stripslashes($_POST['email']);
$address = stripslashes($_POST['address']);
$insertJob = "INSERT INTO tbljobs (jobTitle, jobDesc, jobReq, jobEmployer, jobPhone, jobFax, jobEmail, jobAddress) VALUES ('$title', '$desc', '$req', '$employer', '$phone', '$fax', '$email', '$address')";
$QueryResult = mysql_query($insertJob, $DBConnect); // Run the Query Now woohoo.
if($QueryResult === FALSE)
{
echo "<p>Unable to save your job. " .
" Error code " .
mysql_errno($DBConnect) . ": " .
mysql_errno($DBConnect) . "</p>n";
//echo $insertJob;
$errors++;
}
else
{
echo "Job Lead Saved!";}
mysql_close($DBConnect);
}
Many Thanks
The 1146 Table Doesn’t Exist is a common error faced by MySQL server admins. While there are multiple reasons behind this error, fixing it is actually easy. In this article, we explain those reasons and provide solution to fix the issue.
Why the 1146 Table Doesn’t Exist Error Occurs?
As already mentioned, there are multiple reasons behind this issue. When you come across it, you’ll get the following error message:
Code:
Error : Table ‘mysql.innodb_index_stats’ doesn’t exist
status : Operation failedYou get this error because of the following reasons:
The InnoDB has crashed
InnoDB crashing is one of the main reasons why you have corrupt tables. InnoDB can crash if the server didn’t restart as intended or there was any type of process load. User abuse is another reasons behind the InnoDB crashing.
ibdata file is missing in MySQL datadir
The InnoDB utilizes data directory to function properly. The directory consists of ibdata file, log files, among other things. If those things aren’t available on the datadir, you’re going to face this issue. Migration, upgrades, or restoration are some events during which the ibdata file goes missing.
.frm files are placed at wrong places
InnoDB uses the .frm files to define the format of the tables. If this file is not available in the right places, InnoDB will fail to determine the table format, thus leading to the issue.
MySQL datadir has incorrect permission and ownership
Just like InnoDB, MySQL has a data directory too. It located at ‘/var/lib/mysql’ and store the database files. In case the permissions and ownership of the directory are not set appropriately, you’re going to face this error.
The tables are corrupted
There are many other ways in which the tables of an MySQL database can get corrupted. If that’s the case, you’ll come across this error message.
How to Fix this Issue?
Just like there are multiple reasons behind this issue, there are multiple ways you can fix it. Try the following solutions one by one:
Restart the MySQL Server
At times, something as simple as restarting the server can solve this issue. If the server didn’t restart properly, then restarting it once again will most likely fix the issue. If the problem persists, the continue on to the next recommended solutions.
Repair the tables
If you repair the tables, the corrupted tables will get repaired as well, thus solving the issue. Use the ‘myisamchk’ tool that comes with MySQL to repair the table.
Restore the latest backup
Restoring the latest backup (the one that was created just before the error emerged) can fix the issue. If that version is working, it must have tables that aren’t corrupt. It’s recommended that you take backups regularly, or at least once a week. This would help you restore the correct version in case you fix issues like this.
Copy the ibdata file from backup
If your ibdata file is missing, you can restore that from the backup file. It should fix the issue at hand. Restore the file to the data directory after you discard the tablespace. It’d avoid any kind of corruption.
InnoDB crash recovery
You can conduct an InnoDB crash recovery if none of the aforementioned solutions work.
So that’s how you fix the MySQL ‘1146 Table Doesn’t Exist’ error. For more assistance, contact the hosting support team.