mysqld crashing on startup after unsafe reboot of the host

Question

I have a situation where I dont know whats the best way out. I had a running vm which stopped because of power outage. On the next boot, mysql started to crash. /var/log/mysqld.log has below lines repeated 1000s of times until I shutdown mysql daemon.

Version: '5.5.29'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Remi
180529 14:47:06  InnoDB: Assertion failure in thread 139810525968128 in file trx0purge.c line 829
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
12:47:06 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.
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=30408704
read_buffer_size=131072
max_used_connections=0
max_threads=151
thread_count=0 
connection_count=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 359993 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 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 = 0 thread_stack 0x100000
/usr/libexec/mysqld(my_print_stacktrace+0x2e)[0x78569e]
/usr/libexec/mysqld(handle_fatal_signal+0x493)[0x66faf3]
/lib64/libpthread.so.0(+0xf500)[0x7f286c73d500]
/lib64/libc.so.6(gsignal+0x35)[0x7f286ab848a5]
/lib64/libc.so.6(abort+0x175)[0x7f286ab86085]
/usr/libexec/mysqld[0x830064]
/usr/libexec/mysqld[0x8303d1]
/usr/libexec/mysqld[0x8ec3ef]
/usr/libexec/mysqld[0x8e2d75]
/usr/libexec/mysqld[0x82ed25]
/usr/libexec/mysqld[0x821f2c]
/usr/libexec/mysqld[0x824c43]
/lib64/libpthread.so.0(+0x7851)[0x7f286c735851]
/lib64/libc.so.6(clone+0x6d)[0x7f286ac3a11d]
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.
180529 14:47:06 mysqld_safe Number of processes running now: 0
180529 14:47:06 mysqld_safe mysqld restarted
180529 14:47:06 [Note] Plugin 'FEDERATED' is disabled.
180529 14:47:06 InnoDB: The InnoDB memory heap is disabled
180529 14:47:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180529 14:47:06 InnoDB: Compressed tables use zlib 1.2.3
180529 14:47:06 InnoDB: Using Linux native AIO
180529 14:47:06 InnoDB: Initializing buffer pool, size = 479.0M
180529 14:47:06 InnoDB: Completed initialization of buffer pool
180529 14:47:06 InnoDB: highest supported file format is Barracuda.
180529 14:47:06  InnoDB: Waiting for the background threads to start
180529 14:47:07 InnoDB: 1.1.8 started; log sequence number 9832719702
180529 14:47:07 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
180529 14:47:07 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
180529 14:47:07 [Note] Server socket created on IP: '0.0.0.0'.

Contents of my.cnf is:

[mysqld]
query_cache_limit=30M
max_allowed_packet=999M
query_cache_size=250M
innodb_file_per_table=1
thread_stack=1M
key_buffer=29M
thread_concurrency=4
table_cache=128
innodb_buffer_pool_size=479M
thread_cache_size=4
join_buffer_size=14M
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

As suggested I could try recover InnoDB but just wanted to be sure that I am really doing it right and it wont cause any damage to the entire db.

Answer 1

Suggestions for your my.cnf-ini [mysqld] section

REMOVE or lead with # the following to allow defaults to work for you, please

query_cache_limit=30M
max_allowed_packet=999M
query_cache_size=250M
thread_stack=1M
key_buffer=29M
thread_concurrency=4
thread_cache_size=4
join_buffer_size-14M

This relief will likely allow you to continue and avoid large RAM footprint. Use of MySQLCalculator.com for 2 minutes will illustrate why you should not use some your requested values.

Answer 2

Suggestion to consider for your my.cnf-ini [mysqld] section

#sort_buffer_size=?????  # lead with # to allow default to work for you.

And post the current value so we know what you were trying to use.

Please post in your question, your complete my.cnf-ini.

Answer 3

It looks like you have InnoDB corruption and if there are no backups of the databases, you have to follow InnoDB force recovery procedure as it is described in the MySQL documentation: https://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

In general, the algorithm looks like the following:

1. Add innodb_force_recovery directive to my.cnf file (/etc/my.cnf CentOS, /etc/mysql/my.cnf Debian like systems). Set the value of the parameter as 1 for a starter and try to start mysql server. If it fails, increase it to 2 and try to start it. Keep increasing it until the MySQL server starts. The max value for innodb_force_recovery is 6.
2. When the server is started, dump all the databases with mysqldump

3. Next, start restoration procedure, but before create a backup of the entire mysql data directory, as default it is /var/lib/mysql:

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

4. Stop the MySQL server, remove all subdirectories and files from /var/lib/mysql directory
5. Remove innodb_force_recovery directive from my.cnf file, add skip_gran_tables directive, so you can connect to the server without admin password and start the MySQL server.
6. Restore the databases from the dumps created on the step 2. The database should be created as create database db_name before restoring.

Do not forget to remove skip_gran_tables directive after all and restart the MySQL server to apply changes.