MySQL cannot start due to a database corruption

1
votes

Logs:

Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: Byte offset 0, len 16384, i/o type 10. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: If you get this error at mysqld startup, please check that Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: your my.cnf matches the ibdata files that you have in the Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: MySQL server. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: 2017-10-03 22:00:46 7fe26c4fd780 InnoDB: Assertion failure in thread 140610456508288 in file fil0fil.cc line 5601 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: We intentionally generate a memory trap. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: Submit a detailed bug report to http://bugs.mysql.com. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: If you get repeated assertion failures or crashes, even Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: immediately after the mysqld startup, there may be Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: corruption in the InnoDB tablespace. Please refer to Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: InnoDB: about forcing recovery. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: 171003 22:00:46 [ERROR] mysqld got signal 6 ; Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: This could be because you hit a bug. It is also possible that this binary Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: or one of the libraries it was linked against is corrupt, improperly built, Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: or misconfigured. This error can also be caused by malfunctioning hardware. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: To report this bug, see https://mariadb.com/kb/en/reporting-bugs Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: We will try our best to scrape up some info that will hopefully help Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: diagnose the problem, but since we have already crashed, Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: something is definitely wrong and this may fail. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Server version: 10.0.31-MariaDB-0ubuntu0.16.04.2 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: key_buffer_size=16777216 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: read_buffer_size=131072 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: max_used_connections=0 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: max_threads=153 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: thread_count=0 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: It is possible that mysqld could use up to Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 352327 K bytes of memory Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Hope that's ok; if not, decrease some variables in the equation. Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Thread pointer: 0x0 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: Attempting backtrace. You can use the following information to find out Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: where mysqld died. If you see no messages after this, something went Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: terribly wrong... Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: stack_bottom = 0x0 thread_stack 0x30000 Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(my_print_stacktrace+0x3d)[0xc1d4ad] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(handle_fatal_signal+0x3bf)[0x7449df] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /lib/x86_64-linux-gnu/libpthread.so.0(+0x11390)[0x7fe26b613390] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7fe26abe2428] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7fe26abe402a] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xab1c8b] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa7a4ec] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa7b4f4] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa5f4c5] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa236e2] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa17fad] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa18b2d] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa1997e] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0xa03d28] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0x9364c5] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(_Z24ha_initialize_handlertonP13st_plugin_int+0x5e)[0x746ade] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0x5d7f15] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(_Z11plugin_initPiPPci+0x530)[0x5d8600] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld[0x528c13] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(_Z11mysqld_mainiPPc+0x570)[0x52ea30] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf0)[0x7fe26abcd830] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: /usr/sbin/mysqld(_start+0x29)[0x523f09] Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains Oct 03 22:00:46 ip-172-31-3-124 mysqld[4294]: information that should help you find out what is causing the crash. Oct 03 22:00:46 ip-172-31-3-124 mysqld_safe[4311]: mysqld from pid file /var/run/mysqld/mysqld.pid ended Oct 03 22:01:17 ip-172-31-3-124 /etc/init.d/mysql[4590]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Oct 03 22:01:17 ip-172-31-3-124 /etc/init.d/mysql[4590]: [61B blob data] Oct 03 22:01:17 ip-172-31-3-124 /etc/init.d/mysql[4590]: error: 'Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")'

System is Ubuntu 16.04.

I backed up the database files and added:

[mysqld] innodb_force_recovery=6

and I still cannot start mysql.

Any suggestions?

1
mysqldatabasecorruption
I'm in your same situation, every time power goes down unexpectedly, my mariadb/innodb tables get corrupted. This shouldn't happen in the first place, but the fact I can't manage to automate the recovery make me think that mariadb/innodb is real crapware. I don't have anything precious in my tables, it's just icinga logging mostly useless stuff, I can't really loose so much time for data so worthless! – Avio

1 Answers

0
votes

From MySQL's own page: Forcing InnoDB recovery

They say NOT to use a value above 3 because:

If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

Unfortunately, the my.cnf values for InnoDB seem correct (which is what got me in trouble once).

I hate to make this an official answer, but you might need to find a backup before the corruption of some key files.

InnoDB files are NOT like ISAM, you can't just recreate the tables and then overwrite the files with a copy you have. A lot of info is kept in the filed addressed innodb_data_file_path.

IF you had innodb_file_per_table and no backup exists, then you might have to "reinstall" mysql and recreate the databases and move the tables from the current file backup you have and use this procedure to recover. I have used this once.

InnoDB lost table but file exists