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

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

mysqlphpmyadmin


Solution 1:[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.

Solution 2:[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.

Solution 3:[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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Kohjah Breese
Solution 2 Krzysztof Weso?owski
Solution 3 Daniel Koszta

Related Questions