Как починить InnoDB таблицу?

Question

[nopld]

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

Судя по логам, сначала случилось:

2017-07-10T09:15:43.210390Z 381102 [ERROR] /usr/sbin/mysqld: The table 'documents' is full

Потом mysql упал:

2017-07-10 12:18:55 0x9b172450  InnoDB: Assertion failure in thread 2601985104 in file os0file.cc line 1697
InnoDB: Failing assertion: offset > 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
09:18:55 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=9
max_threads=151
thread_count=3
connection_count=3
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 75741 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x98c127e8
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 9b171df0 thread_stack 0x30000

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (98c86d68): Can't read from address 98c86d68
Connection ID (thread ID): 381102
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2017-07-10T09:18:56.308889Z 0 [Warning] Changed limits: max_open_files: 1024 (requested 5000)
2017-07-10T09:18:56.309168Z 0 [Warning] Changed limits: table_open_cache: 431 (requested 2000)
2017-07-10T09:18:56.473648Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2017-07-10T09:18:56.475765Z 0 [Warning] 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2017-07-10T09:18:56.496118Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.18-0ubuntu0.16.04.1) starting as process 17800 ...

А потом решил рестартовать и восстанавливаться:

2017-07-10T09:18:58.101572Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12005386752
2017-07-10T09:18:58.981408Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12009002417
2017-07-10T09:18:59.360917Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12001716736
2017-07-10T09:19:00.760206Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12006959616
2017-07-10T09:19:01.280326Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 12009002417
2017-07-10T09:19:01.282555Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-07-10T09:19:01.282655Z 0 [Note] InnoDB: Starting crash recovery.
2017-07-10T09:19:01.286151Z 0 [Warning] InnoDB: Page 7 in the doublewrite buffer is not within space bounds: page [page id: space=123, page number=12714]
2017-07-10T09:19:01.286270Z 0 [Warning] InnoDB: Page 8 in the doublewrite buffer is not within space bounds: page [page id: space=123, page number=112881]
2017-07-10T09:19:01.288855Z 0 [Warning] InnoDB: Page 16 in the doublewrite buffer is not within space bounds: page [page id: space=123, page number=65624]

Пока я это обнаружил, прошло штук двадцать таких циклов.

Насколько я понимаю, мне надо увеличить MAX_ROWS: ALTER TABLE table_name MAX_ROWS=1000000000 AVG_ROW_LENGTH=nnn;

Но, чтобы выполнить этот запрос, нужно запустить MySQL, который из-за битой таблицы (?) не запускается. Добавленный в конфиг innodb_force_recovery=[1-6] запуститься ему помог.

Что делать?

Вариант, при котором нужно снести таблицу вместе с данными, подойдёт: хоть бэкапа и нет, но данные восстановить достаточно легко.

Comments

[lubezniy]

Что со свободным местом на диске, на котором лежит файл с данными InnoDB?

[lubezniy]

И ещё. Версия MySQL случайно не 32-битная?

[nopld]

lubezniy: свободно ~30 гигов, занято БД ~10. То есть, проблема не в месте.

И ещё. Версия MySQL случайно не 32-битная?

Вот тут мне сложно ответить:

# mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for Linux (armv7l) using  EditLine wrapper

[lubezniy]

nopld: Общий размер базы и файла innodb какой? Возможно, случилось переполнение целого из-за ограничений архитектуры. Во всяком случае, на stackoverflow описывали такую проблемы, решили переходом с x86 на x64.

[Axian Ltd.]

armv7l судя по всему 32-х битный АРМ. Базу можно перетащить в другою ОС и там чинить.
Если можно снести таблицу, так снесите, чего ее ждать

[nopld]

lubezniy:
> Общий размер базы и файла innodb какой?

:/var/lib/mysql# ls -la
total 176172
drwx------  7 mysql mysql     4096 Jul 13 08:33 .
drwxr-xr-x 34 root  root      4096 Jul 11 09:43 ..
-rw-r-----  1 mysql mysql       56 Jul  8 03:24 auto.cnf
drwxr-x---  2 mysql mysql     4096 Jul 11 10:12 clarity
-rw-r--r--  1 root  root         0 Jul  8 03:24 debian-5.7.flag
-rw-r-----  1 mysql mysql      723 Jul  8 03:55 ib_buffer_pool
-rw-r-----  1 mysql mysql 67108864 Jul 13 07:17 ibdata1
-rw-r-----  1 mysql mysql 50331648 Jul 13 08:28 ib_logfile0
-rw-r-----  1 mysql mysql 50331648 Jul 13 07:12 ib_logfile1
-rw-r-----  1 mysql mysql 12582912 Jul 12 00:44 ibtmp1
drwxr-x---  2 mysql mysql     4096 Jul  8 03:24 mysql
drwxr-x---  2 mysql mysql     4096 Jul  8 03:24 performance_schema
drwxr-x---  2 mysql mysql     4096 Jul 11 09:43 phpmyadmin
drwxr-x---  2 mysql mysql    12288 Jul  8 03:24 sys

Я правильно понял вопрос?

> Возможно, случилось переполнение целого из-за ограничений архитектуры. Во всяком случае, на stackoverflow описывали такую проблемы, решили переходом с x86 на x64.
Переход на x64 мне не особо нравится, это единственный вариант?

Axian Ltd.:
> Если можно снести таблицу, так снесите, чего ее ждать
Снес, снова заполнил данными, она снова упала.

Покопался в конфиге, сделал

innodb_file_per_table=1
innodb_data_file_path=ibdata1:64M:autoextend

ibdata1 так и висит размером в 64 мегабайта и не увеличивается.

Если дописать

innodb_data_file_path=ibdata1:64M:autoextend:max:512M;ibdata2:64M:autoextend

, то файл ibdata2 не появляется (хотя я так понимаю, что должен).

[nopld]

nopld: нашел в документации, что autoextend может быть только у последнего файла.

При

innodb_data_file_path=ibdata1:64M;ibdata2:64M:autoextend
innodb_force_recovery=6

База запустилась. Сейчас по инструкции делаю дамп, снесу БД и восстановлю из дампа, посмотрим, что получится.

Не получилось:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `documents`': Lost connection to MySQL server during query (2013)

[Axian Ltd.]

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

[nopld]

Axian Ltd.: Вот это хороший совет, спасибо, проверю.

UPD: а можно ли как-то по логам MySQL проверить, есть ли у меня незавершенные транзакции? Потому что
а) у меня в коде есть только одно место, где они используются, и там есть COMMIT;
б) насколько я понимаю, даже если коммита нет, транзакция всё равно завершиться по выходу из скрипта.

UPD2:

It contains several classes for information vital for InnoDB

Table Data Pages
Table Index Pages
Data Dictionary
MVCC Control Data
Undo Space
Rollback Segments
Double Write Buffer (Pages Written in the Background to avoid OS caching)
Insert Buffer (Changes to Secondary Indexes)

Не заметил в этом списке транзакций, да и судя по логу у меня проблема с doublewrite buffer. Может, его просто выключить?

[Axian Ltd.]

Приведите свой фай конфигурации mysql, пожалуйста, что бы можно было понять стоит ли что-то включать или отключать.
Посмотреть транзакции кажется можно посмотреть так: show innodb status.
Глянь те еще вот на это, https://stackoverflow.com/questions/7598373/displa... транзации еще могут копиться если соединение рветься.

[nopld]

Axian Ltd.:

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

innodb_buffer_pool_size=64M
innodb_file_per_table=1
innodb_data_home_dir=
innodb_data_file_path=ibdata1:512M;ibdata2:256M;ibdata3:256M:autoextend
innodb_log_file_size=256M
tmp_table_size=256M
max_heap_table_size=256M

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

Даже с полностью выключенными транзакциями в скрипте, таблица снова свалилась с теми же симптомами:

2017-07-16T09:48:52.611852Z 292262 [ERROR] /usr/sbin/mysqld: The table 'documents' is full
...
2017-07-16T09:53:34.385128Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 14571730991
...
2017-07-16T09:53:34.385769Z 0 [Note] InnoDB: Database was not shutdown normally!
2017-07-16T09:53:34.385861Z 0 [Note] InnoDB: Starting crash recovery.
2017-07-16T09:53:34.396534Z 0 [Warning] InnoDB: Page 37 in the doublewrite buffer is not within space bounds: page [page id: space=83, page number=49152]
...

[Axian Ltd.]

innodb_data_home_dir=
Странный ИМХО выбор

innodb_data_file_path=ibdata1:512M;ibdata2:256M;ibdata3:256M:autoextend
С какой целью размеры заданы явно? И почему всего три?
-------------------
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
как заданы?

[nopld]

Axian Ltd.:

innodb_data_home_dir=
Странный ИМХО выбор

Не могу прокомментировать, взял из какого-то рецепта "как исправить битую таблицу".

innodb_data_file_path=ibdata1:512M;ibdata2:256M;ibdata3:256M:autoextend
С какой целью размеры заданы явно?

Я выше писал, потому что по какой-то причине 1 файл с autoextend не разрастается, а просто постоянно висит одного размера.

И почему всего три?

Всего — о есть должно быть больше?

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
transaction-isolation = READ-COMMITTED
как заданы?

По умолчанию, их я не трогал.

[Axian Ltd.]

Насколько я помню innodb_flush_method по-умолчанию NULL, что означает использование настройки fsync и для innodb. Опять же, насколько помню O_DIRECT и READ-COMMITTED рекомендуемые значения для Linux.
И я бы поставил innodb_flush_log_at_trx_commit = 2 - это также влияет на завершение транзакций.
Чтобы не возвращаться, используете для настойки mysql какой-нибудь скрипт оптимизации. Он сам поставит более-менее вменяемые значения для текущей платформы. Например mysqltuner.pl от Major Hayden (http://mysqltuner.com)