MariaDB service continuosly fail to start

Question

I'm having some trouble on a server with MariaDB installed on.

The mariadb process stopped working suddenly for the first time a few days ago and when I try to reboot it doing

sudo service mariadb restart

i get this output

Redirecting to /bin/systemctl restart mariadb.service
Job for mariadb.service failed because the control process exited with error cod                                                                                                     e. See "systemctl status mariadb.service" and "journalctl -xe" for details.
[root@web-customer-01 ~]# systemctl status mariadb.service
● mariadb.service - MariaDB database server
   Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor preset: disabled)
   Active: failed (Result: exit-code) since Tue 2018-02-13 15:43:56 UTC; 24s ago
  Process: 10529 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=1/FAILURE)
  Process: 10528 ExecStart=/usr/bin/mysqld_safe --basedir=/usr (code=exited, status=0/SUCCESS)
  Process: 10498 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exited, status=0/SUCCESS)
 Main PID: 10528 (code=exited, status=0/SUCCESS)

Feb 13 15:43:54 web-customer-01.customer.it systemd[1]: Starting MariaDB database server...
Feb 13 15:43:54 web-customer-01.customer.it mariadb-prepare-db-dir[10498]: Database MariaDB is probably initialized in /var/lib/mysql already, nothing is done.
Feb 13 15:43:54 web-customer-01.customer.it mysqld_safe[10528]: 180213 15:43:54 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
Feb 13 15:43:54 web-customer-01.customer.it mysqld_safe[10528]: 180213 15:43:54 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
Feb 13 15:43:56 web-customer-01.customer.it systemd[1]: mariadb.service: control process exited, code=exited status=1
Feb 13 15:43:56 web-customer-01.customer.it systemd[1]: Failed to start MariaDB database server.
Feb 13 15:43:56 web-customer-01.customer.it systemd[1]: Unit mariadb.service entered failed state.
Feb 13 15:43:56 web-customer-01.customer.it systemd[1]: mariadb.service failed.

As a temporary solution I discovered that deleting these files, the service will reboot, but I'm not sure it's a [good] solution.

/var/lib/mysql/ib_logfile0
/var/lib/mysql/ib_logfile1

I noticed many of these lines in the mariadb log

180213 15:53:29  InnoDB: Error: page 16392 log sequence number 19972774982
InnoDB: is in the future! Current system log sequence number 731248348.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: for more information.

and then, as mentioned in the link above I added innodb_force_recovery in my.cnf

[mysqld]
innodb_force_recovery = 1

I tried to investigate the problem but I've no ideas about the possible causes and so I'm not able to find the correct solution.

-- EDIT

This is my /etc/my.cnf (obv I added innodb_force_recovery in these days)

[mysqld]
innodb_force_recovery = 1
innodb_buffer_pool_size=256M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
skip-name-resolve

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

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

[client]
port = 3306
socket = =/var/lib/mysql/mysql.sock

Answer 1

I came across this some time ago but I'm not sure how it has been fixed.

As far as I know, InnoDB has 3 files it uses for recovery

• ibdata1
• ib_logfile0
• ib_logfile1

If you delete those, the DB most likely will start BUT if the directory where those files are created have a different timestamp as other DB-related directories it throws errors because it checks timestamps of directories from when they were backed up. (if that makes any sense)

I found this that maybe could help:

Recovery Modes

Mode Description

0 The default mode while InnoDB is running normally. Until MariaDB 10.2.7, it was the only mode permitting changes to the data. From MariaDB 10.2.7, write transactions are permitted with innodb_force_recovery<=3.

1 (SRV_FORCE_IGNORE_CORRUPT) allows the server to keep running even if corrupt pages are detected. It does so by making redo log based recovery ignore certain errors, such as missing data files or corrupted data pages. Any redo log for affected files or pages will be skipped. You can facilitate dumping tables by getting the SELECT * FROM table_name statement to jump over corrupt indexes and pages.

2 (SRV_FORCE_NO_BACKGROUND) stops the master thread from running, preventing a crash that occurs during a purge. No purge will be performed, so the undo logs will keep growing.

3 (SRV_FORCE_NO_TRX_UNDO) does not roll back transactions after the crash recovery. Does not affect rollback of currently active transactions. Starting with MariaDB 10.2.7, will also prevent some undo-generating background tasks from running. These tasks could hit a lock wait due to the recovered incomplete transactions whose rollback is being prevented.

4 (SRV_FORCE_NO_IBUF_MERGE) does not calculate tables statistics and prevents insert buffer merges.

5 (SRV_FORCE_NO_UNDO_LOG_SCAN) treats incomplete transactions as committed, and does not look at the undo logs when starting.

6 (SRV_FORCE_NO_LOG_REDO) does not perform redo log roll-forward as part of recovery. Running queries that require indexes are likely to fail with this mode active. However, if a table dump still causes a crash, you can try using a SELECT * FROM tab ORDER BY primary_key DESC to dump all the data portion after the corrupted part.

So you could try different modes and see if that helps.

Answer 2

In my case, removing the innodb_additional_mem_pool_size = 128M did the trick.

Answer 3

innodb_buffer_pool_size=256M is probably too big for a mere 1GB of RAM. Change to 64M. Do you have any other apps running in the server? That would also cramp things severely.

What is in /etc/my.cnf.d? We need to see all the configuration settings to find out what else is tuned too big.

Or you can look into getting more RAM.