Mysql crashed and won't start up

Question

Our production mysql server just crashed and won't come back up. It's giving a segfault error. I tried a reboot, and just don't know what else to try. Here is the stacktrace:

140502 14:13:05 [Note] Plugin 'FEDERATED' is disabled.
InnoDB: Log scan progressed past the checkpoint lsn 108 1057948207
140502 14:13:06  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 108 1058059648
InnoDB: 1 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 15 row operations to undo
InnoDB: Trx id counter is 0 562485504
140502 14:13:06  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
InnoDB: Apply batch completed
InnoDB: Starting in background the rollback of uncommitted transactions
140502 14:13:06  InnoDB: Rolling back trx with id 0 562485192, 15 rows to undo
140502 14:13:06  InnoDB: Started; log sequence number 108 1058059648
140502 14:13:06  InnoDB: Assertion failure in thread 1873206128 in file ../../../storage/innobase/fsp/fsp0fsp.c line 1593
InnoDB: Failing assertion: frag_n_used > 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.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
140502 14:13:06 - 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.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

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

thd: 0x0
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 = (nil) thread_stack 0x30000
140502 14:13:06 [Note] Event Scheduler: Loaded 0 events
140502 14:13:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
/usr/sbin/mysqld(my_print_stacktrace+0x2d) [0xb7579cbd]
/usr/sbin/mysqld(handle_segfault+0x494) [0xb7245854]
[0xb6fc0400]
/lib/tls/i686/cmov/libc.so.6(abort+0x182) [0xb6cc5a82]
/usr/sbin/mysqld(+0x4867e9) [0xb74647e9]
/usr/sbin/mysqld(btr_page_free_low+0x122) [0xb74f1622]
/usr/sbin/mysqld(btr_compress+0x684) [0xb74f4ca4]
/usr/sbin/mysqld(btr_cur_compress_if_useful+0xe7) [0xb74284e7]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x332) [0xb7429e72]
/usr/sbin/mysqld(btr_node_ptr_delete+0x82) [0xb74f4012]
/usr/sbin/mysqld(btr_discard_page+0x175) [0xb74f41e5]
/usr/sbin/mysqld(btr_cur_pessimistic_delete+0x3e8) [0xb7429f28]
/usr/sbin/mysqld(+0x526197) [0xb7504197]
/usr/sbin/mysqld(row_undo_ins+0x1b1) [0xb7504771]
/usr/sbin/mysqld(row_undo_step+0x25f) [0xb74c210f]
/usr/sbin/mysqld(que_run_threads+0x58a) [0xb74a31da]
/usr/sbin/mysqld(trx_rollback_or_clean_all_without_sess+0x3e3) [0xb74ded43]
/lib/tls/i686/cmov/libpthread.so.0(+0x596e) [0xb6f9f96e]
/lib/tls/i686/cmov/libc.so.6(clone+0x5e) [0xb6d65a4e]
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.

Any recommendations?

Solution 1

Ouch.

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.1/en/forcing-recovery.html
InnoDB: about forcing recovery.

Check the suggested webpage: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html.

Basically, try to start the MySQL server in a recovery mode and make a backup of your crashed tables.

Edit your /etc/my.cnf and add:

 innodb_force_recovery = 1

...to see if you can get into your database and get your data / find the corrupted table.

Usually, when this happens it's a re-build (at least of a corrupted table or two).

From http://chepri.com/mysql-innodb-corruption-and-recovery/:

1. Stop mysqld (service mysql stop).
2. Backup /var/lib/mysql/ib*

3. Add the following line into /etc/my.cnf:

   innodb_force_recovery = 1
   

   (they suggest 4, but its best to start with 1 and increment if it won't start)

4. Restart mysqld (service mysql start).

5. Dump all tables: mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld (service mysql stop).
8. Remove /var/lib/mysql/ib*
9. Comment out innodb_force_recovery in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump: mysql < dump.sql

Solution 2

I was facing this same error while using mysql:5.7 docker image. Main mistake was trying to create root user which exists by default. More information: https://github.com/docker-library/mysql/issues/129

As given in the above link, solution was to NOT set MYSQL_USER and MYSQL_PASSWORD in the environment variables while starting the docker image.

Solution 3

This happened to me in Laravel Homestead (Vagrant after a kernel panic running Mac OS Sierra 10.12.4 (16E195):

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.3 LTS
Release:    14.04
Codename:   trusty

$ mysql -V
mysql  Ver 14.14 Distrib 5.7.9, for Linux (x86_64) using  EditLine 
wrapper

Here are some resources you can try, although none of the repair options worked for me:

https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html

https://forums.mysql.com/read.php?22,603093,604631#msg-604631

https://support.plesk.com/hc/en-us/articles/213939865-How-to-fix-InnoDB-corruption-cases-for-the-MySQL-database

I tried adding force recovery to mysql config (start at 1 and go progressively higher since supposedly higher numbers can cause permanent corruption):

sudo nano /etc/mysql/my.cnf

[mysqld]
innodb_force_recovery = 1
#innodb-read-only=1
#innodb_purge_threads=0
#key_buffer_size=16M
#event-scheduler=disabled

In another window, run:

tail -f /var/log/mysql/error.log

Then try restarting mysqld with the various options enabled:

sudo /etc/init.d/mysql restart

If it times out, you can force restart mysql processes with:

# process id is first column with number, just ignore lines with grep because they list the process running 'grep mysql'
ps aux | grep mysql
sudo kill -9 <process-id>
sudo /etc/init.d/mysql restart

If it works, the log will show something like:

Version: '5.7.9' socket: '/var/run/mysqld/mysqld.sock' port: 3306 MySQL Community Server (GPL)

If it fails, the log will show something like:

InnoDB: Assertion failure in thread 140049488692992 in file log0recv.cc line 1420

---

When worse came to worse, I tried removing databases likely to be corrupt:

sudo ls -alt /var/lib/mysql

It turned out that the database I had been working on was the most recently modified one at the top of the list. Luckily I had a SQL dump for it from that day so was able to remove it:

sudo rm -rf /var/lib/mysql/<database_name>

I left all of the other files, and mysql was able to start anyway.

UPDATE: be sure to disable innodb_force_recovery = 1 once mysql is working again, otherwise you will get errors when you attempt to modify databases and tables.

Then I recreated the database with Sequel Pro, reimported my data and was able to move on without having to throw away all of the databases from my other projects.

Going forward, I must assume that any mysql database can get corrupted and try to keep daily backups and have the database recreation script documented or coded into my continuous integration tools.

Comments

[Janne Pikkarainen]

First things first; has someone changed MySQL configuration somehow? See the latest modification date for /etc/mysql/my.cnf or so.

[tilleryj]

No. Ended up having to set innodb_force_recovery=3 in order to do a mysqldump, then dropped and readded the DB. This fixed it.

[bombcar]

I'd first consider that you may have filesystem corruption or a bad disk.

[akuzminsky]

try all innodb_force_recovery values up to 6. And add innodb_purge_threads=0 - sometimes main thread can't start any, you'll see it in the error log

[nkanderson]

I know this is an old thread, but any elaboration on determining which databases need recovery?

[Andrew Thaddeus Martin]

@nicolekanderson I would also like some clarification on that point. After I run mysqldump, it doesn't indicate to me in any way that any of the databases were corrupt.

[Jonathan]

point 10 in the answer - try to restart the database server and read the error log, it should give the name of the crashed tables. mysqldump only gives you a copy of the tables, nothing esle.