mysqldump: Got error: 1146: Table ' myDatabase.table' doesn't exist when using LOCK TABLES

Question

I'm trying to get dump of my database:

mysqldump myDatabase > myDatabase.sql

but I'm getting this error:

mysqldump: Got error: 1146: Table 'myDatabase.table' doesn't exist when using LOCK TABLES

When I go to mysql:

 mysql -u admin -p

I query for the tables:

show tables;

I see the table. but when I query for that particular table:

 select * from table;

I get the same error:

ERROR 1146 (42S02): Table 'myDatabase.table' doesn't exist

I tried to repair:

mysqlcheck -u admin -p --auto-repair --check --all-databases

but get the same error:

Error    : Table 'myDatase.table' doesn't exist

Why I'm getting this error or how can I fix this error?

I'll really appreciate your help

Answer 1

For me the problem was resolved by going to /var/lib/mysql (or wherever you raw database files are stored) and deleting the .frm file for the table that the errors says does not exist.

Answer 2

I had an issue with doing mysqldump on the server, I realized that tables that if that tables were not used for longer time, then I do not need those (old applications that were shutdown).

The case: Cannot do backup with mysqldump, there are tables that are not needed anymore and are corrupted

At first I get the list of corrupted tables

mysqlcheck --repair --all-databases -u root -p"${MYSQL_ROOT_PASSWORD}" > repair.log

Then I analyze the log with a Python script that takes it at stdin (save as ex. analyze.py and do cat repair.log| python3 analyze.py)

#!/usr/bin/env python3
import re
import sys

lines = sys.stdin.read().split("\n")
tables = []

for line in lines:
    if "Error" in line:
        matches = re.findall('Table \'([A-Za-z0-9_.]+)\' doesn', line)
        tables.append(matches[0])

print('{', end='')
print(",".join(tables), end='')
print('}', end='')

You will get a list of corrupted databases.

Do an export with mysqldump

mysqldump -h 127.0.0.1 -u root  -p"${MYSQL_ROOT_PASSWORD}"  -P 3306  --skip-lock-tables --add-drop-table --add-drop-database --add-drop-trigger  --all-databases --ignore-table={table1,table2,table3 - here is output of the previous command} > dump.sql

Turn off the database, move /var/lib/mysql to /var/lib/mysql-backup, start database.

On a clean database just import the dump.sql, restart database, enjoy an instance without corrupted tables.

Answer 3

I recently came across a similar issue on an Ubuntu server that was upgraded to 16.04 LTS. In the process, MySQL was replaced with MariaDB and apparently the old database couldn't be automatically converted to a compatible format. The installer moved the original database from /var/lib/mysql to /var/lib/mysql-5.7.

Interestingly, the original table structure was present under the new /var/lib/mysql/[database_name] in the .frm files. The new ibdata file was 12M and the 2 logfiles were 48M, from which I concluded, that the data must be there, but later I found that initializing a completely empty database results in similar sizes, so that's not indicative.

I installed 16.04 LTS on a VirtualBox, installed MySQL on it, then copied the mysql-5.7 directory and renamed it to mysql. Started the server and dumped everything with mysqldump. Deleted the /var/lib/mysql on the original server, initialized a new one with mysql_install_db and imported the sql file from mysqldump.

Note: I was not the one who originally did the system upgrade, so there may be a few details missing, but the symptoms were similar to yours, so maybe this could help.