Mysql crashed and won't start up
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/:
- Stop
mysqld(service mysql stop). - Backup
/var/lib/mysql/ib* -
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)
Restart
mysqld(service mysql start).- Dump all tables:
mysqldump -A > dump.sql - Drop all databases which need recovery.
- Stop
mysqld(service mysql stop). - Remove
/var/lib/mysql/ib* - Comment out
innodb_force_recoveryin/etc/my.cnf - Restart
mysqld. Look at mysql error log. By default it should be/var/lib/mysql/server/hostname.com.errto see how it creates newib*files. - 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.