MariaDB Service Troubleshooting Guide

MariaDB is an open-source relational database management system widely used in various applications. Although MariaDB usually runs stably, you might encounter service failures or performance issues in certain situations. This article provides a detailed guide on troubleshooting MariaDB services in ServBay, including common issues, diagnostic steps, and solutions. ServBay runs on macOS and includes different versions of MariaDB, so some commands require you to specify version-specific configuration files and directories.

Common Issues and Solutions

1. MariaDB Service Cannot Start

Possible Causes

• Configuration file errors
• Port occupied
• Permission issues

Solutions

1. Check the Configuration File: Ensure the my.cnf configuration file syntax is correct, located at:

   /Applications/ServBay/etc/mariadb/11.3/my.cnf

   Validate the configuration file using:

   mysqld --defaults-file=/Applications/ServBay/etc/mariadb/11.3/my.cnf --validate-config

2. Check Port Occupancy: Ensure the port used by MariaDB (default 3306) is not occupied by other processes. Check port occupancy using:

   lsof -i :3306

3. Check Permissions: Ensure the permissions of the MariaDB data directory and configuration files are correct. Check permissions using:

   ls -l /Applications/ServBay/db/mariadb/11.3
   ls -l /Applications/ServBay/etc/mariadb/11.3/my.cnf

4. Start the Service: Attempt to restart the MariaDB service:

   servbayctl restart mariadb 11.3

2. Cannot Connect to MariaDB

Possible Causes

• MariaDB service is not running
• Firewall blocking the connection
• User permission issues

Solutions

1. Check Service Status: Ensure the MariaDB service is running:

   servbayctl status mariadb 11.3

2. Check Firewall Settings: Ensure the firewall allows MariaDB's port (default 3306):

   sudo /usr/libexec/ApplicationFirewall/socketfilterfw --add /Applications/ServBay/bin/mysqld
   sudo /usr/libexec/ApplicationFirewall/socketfilterfw --unblockapp /Applications/ServBay/bin/mysqld

3. Check User Permissions: Ensure the user has sufficient permissions to connect to the database:

   SHOW GRANTS FOR 'your_username'@'localhost';

4. Connection Test: Test the connection using:

   mysql --defaults-file=/Applications/ServBay/etc/mariadb/11.3/my.cnf -u your_username -p -h localhost -P 3306

3. Performance Issues

Possible Causes

• Unoptimized queries
• Improper cache configuration
• Missing indexes

Solutions

1. Optimize Queries: Use EXPLAIN to analyze query performance and optimize accordingly:

   EXPLAIN SELECT * FROM your_table_name WHERE column_name = 'value';

2. Adjust Cache: Adjust the cache settings in my.cnf, for example:

   [mysqld]
   query_cache_size = 64M
   innodb_buffer_pool_size = 1G

3. Create Indexes: Ensure indexes are created for frequently queried columns:

   CREATE INDEX idx_column_name ON your_table_name(column_name);

4. Database Crashes

Possible Causes

• Hardware failure
• Software conflicts
• Configuration errors

Solutions

1. Check Error Logs: View the MariaDB error logs to find the specific cause of the crash. The error log path is:

   /Applications/ServBay/logs/mariadb/11.3.err

2. Repair the Database: Use the mysqlcheck tool to repair the database:

   mysqlcheck --defaults-file=/Applications/ServBay/etc/mariadb/11.3/my.cnf -u your_username -p --auto-repair --all-databases

3. Restore Data: If database files are damaged, recover data from backups. The backup file path is:

   /Applications/ServBay/backup/mariadb/11.3

5. Backup and Restore Issues

Possible Causes

• Corrupted backup files
• Incorrect restore commands

Solutions

1. Check Backup Files: Ensure backup files are complete and not corrupted. Check file size and integrity using:

   ls -lh /Applications/ServBay/backup/mariadb/11.3/your_backup.sql

2. Correctly Restore the Database: Use the mysql tool to restore the database correctly:

   mysql --defaults-file=/Applications/ServBay/etc/mariadb/11.3/my.cnf -u your_username -p your_database < /Applications/ServBay/backup/mariadb/11.3/your_backup.sql

Specific Issue: ib_logfile0 was not found and InnoDB: Missing FILE_CHECKPOINT(47594) at 47594

Possible Causes

• Bug in MariaDB 11.5.1

Error Logs

240618 22:37:46 mysqld_safe Starting mariadbd daemon with databases from /Applications/ServBay/db/mariadb/11.5
2024-06-18 22:37:46 0 [Note] Starting MariaDB 11.5.1-MariaDB-log source revision 65079ece42f383bdeb9b3f913a833a6f0123919d as process 3323
2024-06-18 22:37:46 0 [Warning] Setting lower_case_table_names=2 because file system for /Applications/ServBay/db/mariadb/11.5/ is case insensitive
2024-06-18 22:37:47 0 [Warning] --innodb-file-per-table is deprecated and will be removed in a future release
2024-06-18 22:37:47 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2024-06-18 22:37:47 0 [Note] InnoDB: Number of transaction pools: 1
2024-06-18 22:37:47 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-06-18 22:37:47 0 [Note] InnoDB: Initializing buffer pool, total size = 4.000GiB, chunk size = 64.000MiB
2024-06-18 22:37:47 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-18 22:37:47 0 [ERROR] InnoDB: File /Applications/ServBay/db/mariadb/11.5/ib_logfile0 was not found
2024-06-18 22:37:47 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-06-18 22:37:47 0 [Note] InnoDB: Starting shutdown...
2024-06-18 22:37:47 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-06-18 22:37:47 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-06-18 22:37:47 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-06-18 22:37:47 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-06-18 22:37:47 0 [ERROR] Aborting
240618 22:37:48 mysqld_safe mysqld from pid file /Applications/ServBay/tmp/mariadb-11.5.pid ended

240618 23:22:28 mysqld_safe Starting mariadbd daemon with databases from /Applications/ServBay/db/mariadb/11.5
2024-06-18 23:22:28 0 [Note] Starting MariaDB 11.5.1-MariaDB-log source revision 65079ece42f383bdeb9b3f913a833a6f0123919d as process 6667
2024-06-18 23:22:28 0 [Warning] Setting lower_case_table_names=2 because file system for /Applications/ServBay/db/mariadb/11.5/ is case insensitive
2024-06-18 23:22:29 0 [Warning] --innodb-file-per-table is deprecated and will be removed in a future release
2024-06-18 23:22:29 0 [Note] InnoDB: Compressed tables use zlib 1.3.1
2024-06-18 23:22:29 0 [Note] InnoDB: Number of transaction pools: 1
2024-06-18 23:22:29 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-06-18 23:22:29 0 [Note] InnoDB: Initializing buffer pool, total size = 4.000GiB, chunk size = 64.000MiB
2024-06-18 23:22:29 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-18 23:22:29 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(47594) at 47594
2024-06-18 23:22:29 0 [ERROR] InnoDB: Log scan aborted at LSN 47594
2024-06-18 23:22:29 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-06-18 23:22:29 0 [Note] InnoDB: Starting shutdown...
2024-06-18 23:22:29 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-06-18 23:22:29 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-06-18 23:22:29 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-06-18 23:22:29 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-06-18 23:22:29 0 [ERROR] Aborting
240618 23:22:29 mysqld_safe mysqld from pid file /Applications/ServBay/tmp/mariadb-11.5.pid ended

Solutions

Referencing Modifying MariaDB Settings, find my.cnf, and follow these detailed steps:

1. Add innodb_force_recovery = 6 under [mysqld] in my.cnf

   Edit the my.cnf configuration file, find [mysqld], and add the following line:

   innodb_force_recovery = 6

2. Start MariaDB

   Start MariaDB using the following command:

   servbayctl start mariadb 11.5

3. Backup MariaDB

   Use the mysqldump command to back up all databases:

   mysqldump -uroot -p --all-databases --opt > /Applications/ServBay/backup/mariadb/11.5/mariadb_backup.sql

4. Disable MariaDB Service

   In the ServBay management panel, disable the MariaDB 11.5 service

5. Remove innodb_force_recovery = 6 from my.cnf

   Edit the my.cnf configuration file and delete the following line:

   innodb_force_recovery = 6

6. Rename 11.5 Directory

   Rename the MariaDB data directory 11.5 to 11.5.bak:

   mv /Applications/ServBay/db/mariadb/11.5 /Applications/ServBay/db/mariadb/11.5.bak

7. Switch to Another Version of MariaDB

   Switch back to a non-problematic MariaDB version 11.5.0, or another version such as 11.3 or 11.4.

8. Restore MariaDB Backup

   Restore the database using the mysql command:

   mysql -uroot -p < /Applications/ServBay/backup/mariadb/11.5/mariadb_backup.sql

Summary

MariaDB is a powerful and flexible database management system, but you may encounter various issues during usage. This article provides an introduction on how to troubleshoot MariaDB services, including diagnosing and resolving common issues. We hope this article is helpful to you. If you have any questions or need further assistance, please feel free to contact us.