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

Stressful

are you sure that in the table name there are not hiidden char ..eg: char fo table name length .. – Parkinson

It could be a filesystem permission problem, database corruption or failed migration of your database from on OS to a different one (e.g. macOS/window to linux)? One thing you could try is Repair all tables in one go. If it is an InnoDB table, then it might be more complex to solve the problem. – Raceme

take a look at this also dba.stackexchange.com/questions/107429/… – Parkinson

@scaisEdge mysqldump should use the correct naming, it might be more a configuration or corruption problem. E.g. moving the config and/or database from a case insensitive filesystem to a case sensitive one. – Raceme

@scaisEdge, I have updated my question. – Stressful

On which OS you your run you mysql installation? Did you migrate the data from an other OS? What are the values for lower_case_file lower_case table_names? Is that table a InnoDB or a MyISAM table? – Raceme

@t.niese, yes, this database was in another box. How do I get the values for lower_case_file and lower_case table_names? – Stressful

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.

Richards

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.

Matins

Answer 3

As an addition to @Krzysztof Wesołowski's answer (can't comment yet), here's a bash oneliner to get all the tables that doesn't exist:

mysqlcheck --repair --all-databases | grep "doesn't exist" | awk '{print $4}' | sed "s/'//g"

Discant

Answer 4

We recently experienced a similar problem, with a surprising solution: The table name contained a character that looked like a regular Latin letter "a", but was actually the Cyrillic letter "а". Renaming the table fixed the problem.

We first noticed this error:

mysqldump: Got error: 1146: Table 'censored_db.censored_?' doesn't exist when using LOCK TABLES

Notice here the "?" character, that was already a hint

The censored_db database contained tables like censored, censored_а and censored_b. As phpMyAdmin lists tables alphabetically, it was strange that censored_а was listed after censored_b (instead of before), so that was another hint

Also clicking censored_а in phpMyAdmin does a SELECT shows the rows, but if you type manually (in Latin characters) SELECT, it of course says "table 'censored_db.censored_а' doesn't exist"

Repair

Answer 5

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.

Iny