MariaDB Crash Recovery: Fixing "InnoDB: Operating system error", OOM Kills, and Connection Limits

Understanding MariaDB Crashes

When a MariaDB instance crashes, the immediate symptom is usually a failed connection attempt returning ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2) or a sudden termination of active queries.

Before taking drastic measures, it's critical to identify the root cause. A crash is usually the result of one of the following:

1. Hardware/OS Resource Exhaustion: The Linux Out-Of-Memory (OOM) killer, a full disk, or CPU starvation.
2. InnoDB Corruption: Data page or redo log corruption preventing the storage engine from initializing.
3. Configuration Limits: Hitting max_connections, open_files_limit, or excessive replication lag that stalls the server.

Step 1: Diagnose the Crash

The first step in any database crash recovery is examining the logs. Do not immediately attempt to restart the database without checking why it stopped.

1. Check the MariaDB Error Log The default location is usually /var/log/mysql/error.log or /var/log/mariadb/mariadb.log. Look for the last entries before the crash.

tail -n 100 /var/log/mysql/error.log

Look for errors like:

• [ERROR] InnoDB: Attempted to open a previously opened tablespace
• [ERROR] InnoDB: Database page corruption on disk or a failed file read
• [ERROR] mysqld got signal 11 ; (Segmentation fault)

2. Check for OOM Kills If MariaDB disappears without any error in its own log, the Linux kernel likely terminated it to save the system from running out of memory.

dmesg -T | grep -i oom
# Or check syslog/messages:
grep -i "killed process" /var/log/messages

If you see Killed process 12345 (mysqld) total-vm:..., you have an OOM issue.

3. Check Disk Space and Permissions A 100% full disk will cause MariaDB to crash when it attempts to write to the binary log, redo log, or temporary tables.

df -h /var/lib/mysql
ls -la /var/lib/mysql

Ensure the mysql user owns all files in the data directory. A mariadb permission denied error often occurs after moving the data directory or restoring backups with the wrong user (e.g., root).

Step 2: Fix OOM and Resource Exhaustion

If the server ran out of memory, you must tune your MariaDB buffers. The biggest culprit is usually the innodb_buffer_pool_size.

1. Open /etc/mysql/mariadb.conf.d/50-server.cnf (or /etc/my.cnf).
2. Reduce innodb_buffer_pool_size. A rule of thumb is 50-70% of total RAM, but if you have a 2GB VPS, setting it to 1.5GB will cause OOM kills when the OS or connection threads need memory.
3. Check connection memory limits. High max_connections combined with large per-thread buffers (join_buffer_size, sort_buffer_size) can easily exceed available RAM.

Fixing "Too Many Connections" If the error is ERROR 1040 (HY000): Too many connections, the server isn't strictly crashed, but it is inaccessible.

1. Connect using the dedicated admin port if configured, or restart the service if absolutely necessary.
2. Increase max_connections in my.cnf:

   [mysqld]
   max_connections = 500
   

3. Ensure your application is properly closing database connections to avoid connection leaks.

Step 3: InnoDB Crash Recovery (Corruption)

If MariaDB refuses to start due to InnoDB corruption, you must use the innodb_force_recovery parameter. Warning: Levels 4-6 can permanently corrupt your data. Always back up the data directory (cp -a /var/lib/mysql /var/lib/mysql_backup) before proceeding.

1. Edit your MariaDB configuration file:

   [mysqld]
   innodb_force_recovery = 1
   

2. Attempt to start MariaDB (systemctl start mariadb).
3. If it fails, increment the value (2, then 3, up to 6) and try again.
4. Crucial Step: Once MariaDB starts in recovery mode, it is strictly read-only. You must immediately dump your data using mysqldump.

   mysqldump -u root -p --all-databases --routines --triggers > all_databases_rescue.sql
   

5. Stop MariaDB, completely remove the corrupted data directory, initialize a new data directory (mariadb-install-db), remove the innodb_force_recovery line, start the fresh database, and restore the SQL dump.

Step 4: Resolving Replication Lag and Deadlocks

While not hard crashes, these cause severe application disruption.

• MariaDB Deadlock: Occurs when two transactions hold locks the other needs. MariaDB automatically rolls back one transaction. Find the culprit by running SHOW ENGINE INNODB STATUS; and looking at the LATEST DETECTED DEADLOCK section. Fix this at the application level by ensuring transactions acquire locks in a consistent order.
• MariaDB Replication Lag: Check SHOW SLAVE STATUS\G (or SHOW REPLICA STATUS\G). If Seconds_Behind_Master is high, check for slow queries (mariadb slow query log) blocking the replication thread, or consider enabling parallel replication (slave_parallel_threads).
• MariaDB Timeout: If connections are dropping with MySQL server has gone away or Lock wait timeout exceeded, increase innodb_lock_wait_timeout or net_read_timeout / net_write_timeout, and optimize the slow queries causing the locks.