Please come back for more

How To Fix Corrupted Table in MariaDB Database when Service not start with signal 6

Some times MariaDB get currupted and you can get : [ERROR] mysqld got signal 6 ;   that probably means that one of your databses are in bad state or have currupted Table data inside, here is how you can fix this , start mysql in recovery mode -> add this line to etc/my.cnf :

[mysqld]
port = 8889
innodb_force_recovery=3
innodb_purge_threads=0

 

*. The innodb_force_recovery have 6 levels, 3 include 1+2 so innodb_force_recovery=6 is the most intensive level. Now Lets try to start again :

systemctl start mariadb.service

This start procedure will start the DB in read-only mode ! now lets check all the databases :

mysqlcheck --all-databases

You will get all status of all the databases table including the Corrupted ones,

zabbix.sysmaps                                     OK
zabbix.sysmaps_elements                            OK
zabbix.sysmaps_link_triggers                       OK
zabbix.sysmaps_links                               OK
zabbix.task                                        OK
zabbix.task_close_problem                          OK
zabbix.timeperiods                                 OK
zabbix.trends                                      OK
zabbix.trends_uint
Warning  : InnoDB: The B-tree of index "PRIMARY" is corrupted.
error    : Corrupt
zabbix.trigger_depends                             OK
zabbix.trigger_discovery                           OK
zabbix.trigger_tag                                 OK
zabbix.triggers                                    OK
zabbix.users                                       OK
zabbix.users_groups                                OK
zabbix.usrgrp                                      OK
zabbix.valuemaps                                   OK

 

To Check one Database (zabbix in this examples):

 mysqlcheck -c zabbix

To Check all Databases on the server :

 mysqlcheck -c --all-databases

To Optimize one table from the Database :

mysqlcheck -o zabbix actions

To Repair the Database:

mysqlcheck --auto-repair zabbix

To Repair one table from the Database:

mysqlcheck -r zabbix trends.uint

You can combine check, optimize and repair functionality together using “–auto-repair”

mysqlcheck --auto-repair -c -o zabbix

lets assume you want to empty one table which have corrupted data inside, in this example zabbix.trends_uint have index corrupted, lets empty this table :

[root@localhost ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 226
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> TRUNCATE TABLE zabbix.trends_uint;
Query OK, 0 rows affected (0.03 sec)

MariaDB [(none)]> quit

Now stop the service :

systemctl stop mariadb.service

And then delete or # the lines we add to the etc/my.cnf

[mysqld]
#port = 8889
#innodb_force_recovery=3
#innodb_purge_threads=0

Then start the service again

systemctl stop mariadb.service

The service should run with no more errors !!!

 

Additional Useful Mysqlcheck Options

 


Good Luck

Leave a Reply

Your email address will not be published. Required fields are marked *