Сегодня надо было перенести огромный сайт. Там не тысячи, а десятки тысяч таблиц. При mysqldump вылезает ошибка вида: «mysqldump: Got error: 1146: "Table doesn't exist" when using LOCK TABLES» или «mysqldump: Got error: 29: "File 'table.MYD' not found (Errcode: 23 "Too many open files in system")" when using LOCK TABLES». Перемещаю я его уже второй раз, так что ошибка мне знакома. Приступим!
На самом деле интернет прямо-таки пестрит сообщениями, что эта ошибка означает нарушение связности InnoDB. Возможно, это так. Но я ещё не видел нарушения связности, выглядящего таким образом :). Да и сообщение про Too many open files in system однозначно говорит о том, что база данных разрослась (жаль только, это сообщение выскакивает гораздо реже). Есть на самом деле 2 метода решения проблемы.
Первый — простой — увеличить количество открытых файлов. Откройте файл /etc/security/limits.conf, поставьте что-то вроде «* hard nofile 8192» и этого должно хватить для большинства пользователей. Хватило и мне в первый переезд сервера. Целостность данных в порядке, а вот работа сайта может на некоторое время прерываться, если вы попытаетесь начать записывать в таблицу, которая сейчас архивируется. Идеальным будет перевести сайт в режим «только для чтения».
Второй — ещё более простой. Всё делается очень просто: архивируются файлы, создаются неработоспособные бэкапы, портится структура БД, всё это делается очень просто. Работоспособность сайта при этом не страдает, опять же! «Что это за способ?», — спросите вы. Спрашивали — отвечаю: это mysqldump --skip-lock-tables.
Способ реально очень простой, но вы реально можете испортить себе жизнь в будущем. Перевод сайта в режим «только для чтения» строго обязателен, если вы не хотите получить неработоспособную резервную копию. Возможно, лучше даже повесить статичную страницу-заглушку с информацией о том, что ведутся технические работы и сайт недоступен.
Попытался сделать дамп (бэкап) БД через родную для MySQL утилиту mysqldump и получил ошибку:
Вместо table_name имя несуществующей таблицы. Т.е. сразу после введения в консоль/терминал команды:
получаю такую ошибку. Файл дампа создаётся, но он пустой, утилита mysqldump после выдачи этой ошибки перестаёт работать.
Попытки ухода от проблемы
Не стал обращать внимание на mysqldump и взял другие инструменты пытаясь убежать от проблемы, так сказать, решил применить альтернативные пути решения. Пробовал сделать дамп базы через менеджер баз данных phpMyAdmin и всё получилось, но при импорте (поднятии) дампа возникли ошибки. Так же пробовал сделать тамп через родной для MySQL графический менеджер БД MySQL Workbench, но он тоже стал ругаться и выдавать эту обишку ибо он так же пользуется утилитой командной строки mysqldump при экспорте БД. Пробовал экспортировать дамп БД так же при помощи Sypex Dumper, он сперва вроде работал, но потом тоже выдал аналогичную ошибку. Короче говоря зря я только тратил время с этими альтернативными инструментами работы с БД. Если не работает родной mysqldump, то и другие программы врядли помогут ибо с базой что-то не так и надо разбираться.
Попытки решения проблемы
Что же это за “doesn’t exist when using LOCK TABLES” такой. Придётся разобраться. Если перевести текст сообщения об ошибке, то в нём говорится примерно следующее: “Таблица `table_name` не существует при использовании команды LOCK TABLES”. Т.е. не была найдена указанная таблица, что понятно, ведь её никто там не создавал и быть её не должно.
Если посмотреть базу через разные графические менеджеры БД вроде браузерного phpMyAdmin или десктопного MySQL Workbench, то такой таблицы в базе действительно нет и не должно быть, но СУБД MySQL почему-то считает, что она там есть или должна быть, однако если посмотреть базу через родной консольный менеджер БД mysql (MySQL monitor), то такая таблица там будет в общем списке таблиц. Надо разбираться.
Поискал ответы на свои вопросы в служебной таблице information_schema, но это ни к чему не привело. Сделал пакетную проверку и восстановление всех таблиц базы данных через родную утилиту mysqlcheck, но это не помогло. При проверке утилита так же нашла эту несуществующую таблицу и стала ругаться, что она не найдена, но работу доделала до конца:
Решение проблемы
Воспользовался стандартным родным консольным менеджером БД, который так и называется mysql, он же полностью MySQL monitor. Зашёл под нужным пользователем БД, выбрал базу, вывел список таблиц базы и оказалось в этом списке действительно есть та самая несуществующая таблица, которая была указана в тексте сообщения об ошибке. Так же при попытке создать таблицу с таким именем получаешь сообщение об ошибке, что такая таблица уже существует. Решил посмотреть что же есть в этой таблице. Получил сообщение об ошибке, что такой таблицы не существует, что не удивительно, ведь её и не должно существовать, но СУБД MySQL считает, что она есть и выводит её в общем списке таблиц. Решил удалить эту таблицу и тоже получил сообщение, что такой таблицы нет и удалять нечего. После этого вновь запросил список всех таблиц базы данных и о чудо, это несуществующей таблицы в списке больше нет.
Таким образом, что бы решить проблему “Got error: 1146: Table `table_name` doesn’t exist when using LOCK TABLES” при работе с БД надо пользоваться родным консольным менеджером БД MySQL monitor (mysql). Попытайтесь сперва создать таблицу с таким именем и получите сообщеине об ошибке, что такая таблица уже есть в БД. Попытайтесь удалить эту таблицу и получите сообщение, что её и так нет. Во время одного из этих действий СУБД MySQL ещё раз проверит базу и убедится, что такой таблицы нет и вычеркнет её из мета информации БД, т.е. забудет про эту несуществующую таблицу, не будет выводить её в списке всех таблиц и не будет выводить эту ошикбу. Скорее всего проверка целостности базы происходит при попытке удаления этой несуществующей таблицы, поэтому пробовать создавать её и не нужно. Так же, возможно, пользоваться консольным MySQL monitor тоже не обязательно и можно послать SQL-запрос СУБД на удаление этой таблицы откуда удобно, просто в MySQL monitor эта таблица сперва отображается в общем списке а в остальных менеджерах баз данных не показывается. В общем точно не знаю что в моём алгоритме действий лишнее, а что необходимое, я лишь говорю как я решил эту проблему. Задача нетривиальная и попытаться воссоздать эту ошибку с целостностью базы ещё раз для учебных целей оказалось не просто. У меня был лишь один проход решения проблемы, поэтому, что точно её решило я не знаю.
Для тех кто всё ещё не понял, скажу кратко. Просто воспользуйетесь консольным MySQL monitor и через него попробуйте удалить эту несуществующую таблицу. При запросе удаления СУБД MySQL проверит базу, поймёт, что такой таблицы действительно нет и всё будет в порядке. Проблема решена, вот и всё.
На всякий случай прикладываю список консольных команд и SQL-запросов, которые я использовал в ходе решения этой проблемы. Хотел их писать сразу по ходу изложения, но решил, что это не нужно для тех кто и так знает, а для остальных (забывчивых) напишу список ниже, названия файлов, пользователей, таблиц и баз, естественно взяты для примера, подставляйте свои.
Для начала консольная команды.
Попытка сделать дамп базы через утилиту mysqldump:
Пакетная проверка и восстановление всех таблиц базы данных через родную утилиту mysqlcheck:
Вход в консольный менеджер баз данных MySQL monitor с указанием данных:
Далее работает непосрдественно с БД, поэтому теперь пойдут SQL-запросы.
Просмотр всех доступных для пользователя (для просмотра) баз данных:
Выбор необходимой рабочей базы данных для работы с ней:
Просмотр всех доступных для пользователя таблиц выбранной базы данных:
Просмотр содержимого указанной таблицы (с лимитом записей/строк):
Удаление таблицы из базы данных:
Следует понимать, что несуществующая таблица, это ошибка структуры базы данных, т.е. надо копать в эту сторону, восстанавливать структуру БД, а не таблиц.
I’m trying to get dump of my database:
but I’m getting this error:
When I go to mysql:
I query for the tables:
I see the table. but when I query for that particular table:
I get the same error:
I tried to repair:
but get the same error:
Why I’m getting this error or how can I fix this error?
I’ll really appreciate your help
2 Answers 2
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.