Mysqldump got error 1146

We may come across the mysqldump error 1146 table doesn’t exist while we perform the database dump.

As part of our Server Management Services, we assist our customers with several MySQL queries.

Today, let us see how to fix this error in Plesk and Directadmin.

mysqldump: Got error: 1146: Table doesn‘t exist

Recently, while performing the database dump, some of our users notice the error:

mysqldump: Got error: Table ‘myDatabase.table’ doesn‘t exist when using LOCK TABLES

In order to check, we go to MySQL:

 mysql -u admin -p

Then we query for the tables:

show tables;

Here, we can find the table. However, when we query for that particular table:

select * from table

We get the same error:

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

We can try to repair it via:

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

However, the error may prevail:

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

The major causes of this can be:

• InnoDB tablespace might have been deleted and recreated but corresponding .frm files of InnoDB tables from the database directory were not removed, or .frm files were moved to another database
• Incorrect permissions and ownership on table’s files in MySQL data directory
• A corrupt table data.

How to fix it?

Moving ahead, let us see how our Support Techs fix this error in both Plesk and Directadmin.

Plesk

1. Initially, we try to connect to the server using SSH
2. Then we try to use --skip-lock-tables parameter with mysqldump to skip lock tables.
   For example,

   #mysqldump –skip-lock-tables -u -p database_name > /root/database_dump.sql

3. If it does not help, we check permissions and ownership on the table’s files in the MySQL data directory for the database that fails to dump. It should be mysql for both owner and group:
   • Find data dir location:
     RHEL/CentOS

     #grep datadir /etc/my.cnf
     datadir=/var/lib/mysql

     Debian/Ubuntu

     #grep -iR datadir /etc/mysql*
     /etc/mysql/mysql.conf.d/mysqld.cnf:datadir = /var/lib/mysql

   • Check permissions:

     # ls -la /var/lib/mysql/example_db/

   • Fix permissions:

     # chown -R mysql:mysql /var/lib/mysql/example_db/

4. If it is still not possible, we try to repair the table in the error using the native MySQL repair tool:

   # plesk db
   
   mysql> use example_db;
   mysql> REPAIR TABLE ;

   Note: We need to replace the  with table name in the error message.
5. If the issue still persists, most probably ibdata* file does not have the info about the table. However, the orphaned .frm files still persist on the file system. We remove it:
   • To verify that table is corrupt or not, we run:

     # plesk db
     
     mysql> use database example_db;
     mysql> desc ;

     If this command fails with the error, it means that ibdata* does not have the information about the table and we need to remove the .frm file.

   • To do so, we browse to the database directory /var/lib/mysql/example_db/ and move .frm file:

     # cd /var/lib/mysql/example_db/
     # mv .frm /root/.frm

6. If these options fail and we have no valid backups to restore, the only available option to save the database is to dump it with the innodb_force_recovery option

Directadmin

Suppose, we get the error for the User database and Table:

mysqldump error output: mysqldump: Got error: 1146: Table ‘user_db.table‘ doesn’t exist when using LOCK TABLES

1. In this case, we check to see if there are any other data files, or if it’s just the .frm file:

   cd /var/lib/mysql/user_db
   ls -la table.*

   If it’s just the table.frm file, then the rest of the data is likely lost. However, we may be able to rebuild the table.

2. To do so, we need to read the .frm file. We need the mysqlfrm tool for that, eg: yum install mysql-utilities. Once we install it, we check if it can be read:

   mysqlfrm –diagnostic table.frm

   This can output the full CREATE TABLE syntax.  We save this somewhere, until the end of the last ; character.

   Note, we can either delete the “CHARACTER SET “,  or change it to the correct charset.

3. Then, we remove the broken table.  To do so, we login to /phpMyAdmin and run the query:

   DROP TABLE user_db.table

4. Finally, we run the CREATE TABLE query from above, to rebuild the table.

[Finding it hard to fix? We’d be happy to assist you]

Conclusion

In short, we saw how our Support Techs fix the MySQL error for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

Found someone asking a similar question: MySQL > Table doesn’t exist. But it does (or it should).

Mike Dacre had the answer that solved my problem. The problem was that the ib_logfile0 and ib_logfile1 (and maybe some of the other ib* files in the mysql/ root directory) were inconsistent with my new installation of mysql. You can’t just drop in db files from the old mysql/ directory and expect it to work.

What I did to recover the database was to backup my current /var/lib/mysql/ on the fresh installation:

$ sudo service mysql stop # Stop mysql. Command could be different on different distros
$ sudo mv /var/lib/mysql ~/mysql.bku

Then copy the emergency backup directory to /var/lib

$ sudo cp -R /media/NAS/Backup/mysql /var/lib/

Then set the permissions appropriately (refer to ~/mysql.bku/ for reference if needed). There may be more efficient commands for this but I’m including what I know for completeness in case someone with less experience may need it.

$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo find /var/lib/mysql/ -type d -exec chmod 700 {} ;
$ sudo find /var/lib/mysql/ -type f -exec chmod 660 {} ;
$ sudo chmod 644 /var/lib/mysql/debian-5.1.flag # Not sure what this is but the permissions were a bit different so include it just in case

And start mysql again

$ sudo service mysql start # Again command might be different on different distros

Then I backed up the databases I needed:

$ mysqldump -u root -p mediawiki-1_19_1 -c | gzip -9 > wiki.2012-11-15.sql.gz

When I was finished I put the mysql/ directory back and then imported the databases from the dump files.

$ sudo service mysql stop
$ sudo mv /var/lib/mysql ~/mysql-discard # Too nervous to start typing "sudo rm -r /" for /var/lib/mysql, so move it away instead
$ sudo mv ~/mysql.bku /var/lib/mysql
$ sudo service mysql start

Problem solved, I have a proper export of the database now and mysql is running properly too. All that’s left is following the Restoring a wiki from backup guide.

Источник: http://kb.odin.com/ru/6586

Проблема

1. MySQL query failed: Incorrect information in file: ‘./psa/misc.frm’
2. При работе mysqldump и mysqlcheck появляется сообщение о несуществующей таблице (для проверки используйте учетную запись администратора MySQL):

   mysqlcheck -uadmin -p****** db_example
   db_example.BackupTasks
   error : Can't find file: 'BackupTasks.MYD' (errno: 2)
   

3. Невозможно выполнить запрос таблицы с оператором «SELECT»:

   mysql> select * from db_example.misc;
   ERROR 1033 (HY000): Incorrect information in file: './db_example/misc.frm'
   

4. Таблица не может быть восстановлена, так как ядро InnoDB не поддерживает восстановление.

   mysql> repair table misc;
   +-------------------------+--------+----------+---------------------------------------------------------+
   | Table | Op | Msg_type | Msg_text |
   +-------------------------+--------+----------+---------------------------------------------------------+
   | psa.APSApplicationItems | repair | note | The storage engine for the table doesn't support repair |
   +-------------------------+--------+----------+---------------------------------------------------------+
   

Причина

Повреждения InnoDB часто связаны с неисправностью оборудования. Сохранение поврежденных страниц происходит в результате сбоев питания или повреждений памяти. Также эта проблема может возникать, если вы храните базы данных InnoDB в сетевом хранилище (NAS).

Решение

Существует несколько способов восстановить MySQL:

I. Принудительное восстановление InnoDB

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 4

ПРИМЕЧАНИЕ. Вы можете увеличить эту опцию до 5 или 6 — пока не получите оптимальный дамп.

Запустите службу mysqld:

/etc/init.d/mysqld start

Создайте дамп всех баз данных:

mysqldump -uadmin -p****** -A > /root/dumpall.sql

Если при создании дампа возникла следующая ошибка:
Incorrect information in file: ‘xxxxxxxx.frm’ when using LOCK TABLES»`

увеличьте значение innodb_force_recovery и повторите попытку. Если вы не можете создать дамп баз данных, попробуйте использовать способ II (скопировать содержимое таблицы) или III (восстановить из резервной копии).

Остановите mysqld и удалите поврежденные данные:

/etc/init.d/mysqld stop
rm -rf /var/lib/mysql/*

Удалите опцию innodb_force_recovery из файла /etc/my.cnf и запустите mysqld:

/etc/init.d/mysqld start

В результате этого будет восстановлена главная база данных «mysql» и движок баз данных InnoDB.
Восстановите базы данных из дампа:

mysql -uadmin -p****** > dumpall.sql

II. Копирование содержимого таблицы

Остановите mysqld и сохраните резервную копию всех файлов, расположенных в папке /var/lib/mysql/:

/etc/init.d/mysqld stop
mkdir /root/mysql_backup
cp -r /var/lib/mysql/* /root/mysql_backup/

Добавьте опцию innodb_force_recovery в раздел [mysqld] в /etc/my.cnf. Эта опция позволит вам запустить mysqld и создать дамп базы данных.

/etc/my.cnf

[mysqld]
innodb_force_recovery = 1

Попробуйте создать копию:

CREATE TABLE <новая таблица> LIKE <поврежденная таблица>;
INSERT INTO <новая таблица> SELECT * FROM <поврежденная таблица>;

Если получилось, удалите поврежденную таблицу и присвойте ее имя новой.

DROP TABLE <поврежденная таблица>;
RENAME TABLE <новая таблица> TO <поврежденная таблица>;

III. Восстановление таблицы InnoDB

Восстановление таблиц InnoDB необходимо в случае возникновения следующей ошибки

mysql> USE databasename;
mysql> SELECT * FROM table1;
ERROR 1146 (42S02): TABLE 'databasename.table1' doesn't exist
mysql>

Или при попытке сделать дамп через mysqldump

[red@hellsrv ~]$ mysqldump -uroot -p databasename > databasename.sql
Enter password:
mysqldump: Got error: 1146: Table 'databasename.table1' doesn't exist when using LOCK TABLES
[red@hellsrv ~]$

ВниманиеДо начала любых действий рекомендуем создать резервную копию файлов базы

Создать резервную копию через mysqldump не получится (из-за ошибки). Потребуется копирование файлов базы на уровне файловой системы:

service mysqld stop
cp -R /var/lib/mysql/databasename /home/USERNAME/backup

Для того чтобы восстановить таблицы InnoDB, нам нужно узнать:

• узнать структуру таблиц
• иметь файлы с данными (имеется ввиду файлы на уровне файловой системы)

Таблица InnoDB на уровне файловой системы состоит из двух фалов:

• файл .frm хранит в себе структуру таблицы;
• файл .ibd собственно данные

План восстановления:

• выяснить структуру поврежденной таблицы;
• создать новую базу;
• создать в новой базе таблицу нужной структуры;
• скопировать данные в новую таблицу из старой;
• если данные окажутся поврежденными, можно попробовать восстановить их используя утилиту innochecksum

Применяем утилиту чтения структуры таблицы:

mysqlfrm --diagnostic table1.frm
CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Также желательно узнать кодировку старой базы:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = 'databasename';
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| cp1251                     | cp1251_general_ci      |
+----------------------------+------------------------+
1 ROW IN SET (0.00 sec)

Создаем новую базу:

mysql> CREATE DATABASE helldb CHARACTER SET cp1251 DEFAULT COLLATE cp1251_general_ci;
Query OK, 1 ROW affected (0.00 sec)

Создаем таблицу по выводу утилиты чтения структуры поврежденной таблицы:

mysql> USE databasename;
mysql> CREATE TABLE `table1` (
  `id` int(10) unsigned NOT NULL comment 'ID',
  `title` varchar(128) NOT NULL comment 'Title',
PRIMARY KEY `PRIMARY` (`id`)
) ENGINE=InnoDB;

Далее копируем данные:

• Очищаем автоматически созданный файл

  mysql> ALTER TABLE tables1 DISCARD TABLESPACE;
  Query OK, 0 ROWS affected (0.04 sec)

• Копируем файл с данными с поврежденной таблицы

  cp /home/USERNAME/tables1.ibd tables1.ibd
  chown mysql:mysql tables1.ibd

• Импортируем данные

  mysql> ALTER TABLE tables1 IMPORT TABLESPACE;
  Query OK, 0 ROWS affected, 1 warning (0.50 sec)

• Проверяем корректность чтения данных

  mysql> SELECT * FROM tables1 LIMIT 10;
  +-----+-----------+
  | id  | title     |
  +-----+-----------+
  |  1  | Title 1   |
  |  2  | Title 2   |
  |  3  | Title 3   |
  |  4  | Title 4   |
  +-----+-----------+
  4 ROWS IN SET (0.00 sec)

Далее можно импортировать восстановленную таблицу или базу целиком.

IV. Восстановление из резервной копии

Если приведенные выше инструкции не помогли, остается только восстановить базы данных из резервных копий.

Похожее