MariaDB on Mac via Homebrew: MySQL impossible after computer restart

Question

I have installed MariaDB locally on my Mac using Homebrew. Everything works fine until I restart my computer. After a computer restart I cannot access my local database server anymore. The files in /opt/homebrew/var/mysql/are still there, but something seems to be broken and I have no idea what. The only solution I could find is to completely re-install MariaDB and set up all databases again which is obviously highly annoying.

Does anyone have any clues on the reason for this break? I did think about stopping MariaDB before shutting down the computer, but I forget that everytime. Also, it hasn’t always been this way. It’s a fairly recent behaviour.

[EDIT 1]

Here’s the error message when connecting to MySQL after a computer restart:

mysql -u root -p             
Enter password: 
ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock'

[EDIT 2]

I now tested to stop MariaDB before shutting down the computer – and the problem even occurred immediately after restarting MariaDB, before shutting down the computer. nabim sademba’s comments (thank you!) actually led me to the following output after rebooting the computer – even brew services start mariadb apparently does not start it:

% brew services list    
Name    Status  User  File
httpd   started myusername ~/Library/LaunchAgents/homebrew.mxcl.httpd.plist
mariadb none    root  
php     started myusername ~/Library/LaunchAgents/homebrew.mxcl.php.plist
[email protected] none          
[email protected] none          
[email protected] none          
[email protected] none          

% brew services start mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

% brew services list         
Name    Status  User  File
httpd   started myusername ~/Library/LaunchAgents/homebrew.mxcl.httpd.plist
mariadb stopped root  ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
php     started myusername ~/Library/LaunchAgents/homebrew.mxcl.php.plist
[email protected] none          
[email protected] none          
[email protected] none          
[email protected] none          
% 

And here’s /opt/homebrew/etc/my.cnf (after the failed restart of MariaDB)

#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# include *.cnf from the config directory
#
!includedir /opt/homebrew/etc/my.cnf.d

[EDIT 3]

And here’s some log data from /opt/homebrew/var/mysql/mycomputer.local.err with error messages about InnoDB (read in a different post to check that)

240815 19:48:34 mysqld_safe Starting mariadbd daemon with databases from /opt/homebrew/var/mysql
2024-08-15 19:48:34 0 [Note] Starting MariaDB 11.4.2-MariaDB source revision 3fca5ed772fb75e3e57c507edef2985f8eba5b12 as process 62946
2024-08-15 19:48:34 0 [Warning] Setting lower_case_table_names=2 because file system for /opt/homebrew/var/mysql/ is case insensitive
2024-08-15 19:48:34 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2024-08-15 19:48:34 0 [Note] InnoDB: Number of transaction pools: 1
2024-08-15 19:48:34 0 [Note] InnoDB: Using generic crc32 instructions
2024-08-15 19:48:34 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-08-15 19:48:34 0 [Note] InnoDB: Completed initialization of buffer pool
2024-08-15 19:48:34 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(234874341) at 234874341
2024-08-15 19:48:34 0 [ERROR] InnoDB: Log scan aborted at LSN 234874341
2024-08-15 19:48:34 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-08-15 19:48:34 0 [Note] InnoDB: Starting shutdown...
2024-08-15 19:48:34 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-08-15 19:48:34 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-08-15 19:48:34 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-08-15 19:48:34 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-08-15 19:48:34 0 [ERROR] Aborting
240815 19:48:34 mysqld_safe mysqld from pid file /opt/homebrew/var/mysql/digs-bb-mac9.local.pid ended

Answer 1

First, you can find out if you have an "InnoDB problem".

Use Terminal:

brew services

You should see this:

mariadb stopped  ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

To be sure locate /opt/homebrew/var/mysql/the_name_of_your_computer.err (I have homebrew installed in /opt so you eventually have to find the proper path, sorry I don't know it. You can use Shift + Command + . to see hidden files.) Find this error:

InnoDB: Missing FILE_CHECKPOINT(xxxxxxxxx) at xxxxxxxx

Solution

Add to /usr/local/etc/my.cnf.d/my.cnf

[mariadb]
innodb_force_recovery = 6

If homebrew is installed in /opt then use /opt/homebrew/etc/my.cnf (if the file doesn't exist, create it).

Now restart MariaDB in Terminal:

brew services restart mariadb

Check if MariaDB is running:

brew services

You probable see this:

mariadb started  ~/Library/LaunchAgents/homebrew.mxcl.mariadb.plist

Now you can use phpadmin to check and export the tables. I analyzed all tables and the only corrupted (actually not corrupted because it's not a table but a view) one was mysql/mysql.user:

Error 'mysql.user' is not of type 'BASE TABLE'

Unfortunately there is also this error:

Could not save recent table!
#1036 - Table 'pma__recent' is read only

So I could't repair the table/view (maybe someone can help with this) but at least I could export my databases. After exporting I decided to reinstall mariadb. :( I completely removed mysql folder (/opt/homebrew/var/mysql). I also removed innodb_force_recovery = 6 from my.cnf.

---

UPDATE: It seems that a new homebrew version 11.5.2 has been released which solves the problem. Try 'brew upgrade'. Please let me know if the upgrade itself helped resolve the corrupted database, as I upgraded after the process described below. Also check this thread: [email protected] Bottle Service - Unsafe Host Shutdown Corrupts InnoDB? #5389

---

UPDATE 2: DB corrupted again. From error log:

[Note] InnoDB: Loading buffer pool(s) from /opt/homebrew/var/mysql/ib_buffer_pool
[Note] Recovering after a crash using tc.log
[ERROR] Bad magic header in tc log
[ERROR] Crash recovery failed. Either correct the problem (if it's, for example, out of memory error) and restart, or delete tc log and start server with --tc-heuristic-recover={commit|rollback}

I stopped mariadb: brew services stop mariadb

I removed tc.log and added --tc-heuristic-recover=rollback to /opt/homebrew/Cellar/mariadb/11.5.2/homebrew.mxcl.mariadb.plist

More about adding arguments in homebrew.

I started mariadb: brew services start mariadb

DB still not working...

I checked error log:

[Note] Heuristic crash recovery mode
[Note] Please restart without --tc-heuristic-recover
[ERROR] Can't init tc log
[ERROR] Aborting

I removed --tc-heuristic-recover=rollback

I restarted mariadb: brew services restart mariadb

DB working...

Answer 2

The current version of MariaDB delivered by homebrew has a bug which corrupts the database on shutdown. It seems there is already a new version available, but not in homebrew yet.