[email protected] Bottle Service - Unsafe Host Shutdown Corrupts InnoDB?

[troyxmccall]

Output of brew config

HOMEBREW_VERSION: 4.3.1-13-gb9a9b0a
ORIGIN: https://github.com/Homebrew/brew
HEAD: b9a9b0a5d10e3dda8148fde434456441c4db0f52
Last commit: 14 hours ago
Core tap HEAD: ba592bd9f41b9edf3e8f69d119be11447354c45c
Core tap last commit: 18 minutes ago
Core tap JSON: 22 May 13:44 UTC
Core cask tap HEAD: 6e0f78e8b8fe1f46e02d0daf02892d5765d6860d
Core cask tap last commit: 28 minutes ago
Core cask tap JSON: 22 May 13:44 UTC
HOMEBREW_PREFIX: /usr/local
HOMEBREW_BAT_THEME: Dracula
HOMEBREW_CASK_OPTS: ["--appdir=/Applications"]
HOMEBREW_DISPLAY: /private/tmp/com.apple.launchd.Zk2v8NYDGN/org.xquartz:0
HOMEBREW_EDITOR: vim
HOMEBREW_MAKE_JOBS: 12
HOMEBREW_NO_ANALYTICS: set
HOMEBREW_SORBET_RUNTIME: set
Homebrew Ruby: 3.3.1 => /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/3.3.1/bin/ruby
CPU: dodeca-core 64-bit westmere
Clang: 14.0.0 build 1400
Git: 2.45.1 => /usr/local/bin/git
Curl: 8.4.0 => /usr/bin/curl
macOS: 12.7.5-x86_64
CLT: 14.2.0.0.1.1668646533
Xcode: 14.2

Output of brew doctor

N/A

Description of issue

there seems to be an issue when running the latest [email protected] bottle as a brew service

shutting down your host while the brew service is running (even if you don't have any active Queries/Procedures) seems to corrupt InnoDB

on a macOS reboot, the brew service will fail with

tail /usr/local/var/mysql/my-mac.err

2024-05-21 11:44:56 0 [Note] InnoDB: Completed initialization of buffer pool
2024-05-21 11:44:56 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(102890851) at 110803559
2024-05-21 11:44:56 0 [ERROR] InnoDB: Log scan aborted at LSN 110803559
2024-05-21 11:44:56 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-05-21 11:44:56 0 [Note] InnoDB: Starting shutdown...
2024-05-21 11:44:56 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-05-21 11:44:56 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-05-21 11:44:56 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-05-21 11:44:56 0 [ERROR] Aborting
240521 11:44:56 mysqld_safe mysqld from pid file /usr/local/var/mysql/my-mac.pid ended

there are ways around this, like setting

[mariadb]
innodb_force_recovery = 6

in /usr/local/etc/my.cnf.d/my.cnf

but then you have to

• repair all your tables
• export all your tables to a sql file,
• delete all your tables (redundant if you also delete the datadir below, but let's do it all)
• shutdown mariadb,
• remove your data dir /usr/local/var/mysql/ just to be sure
• also clean out your /usr/local/etc/my.cnf.d/* folder, again just to be sure with a fresh install (also backup then remove your ~/.my.cnf
• start mariadb
• re-import

and if you restart macOS again, with brew services running you'll brick InnoDB all over again and have to do this repair process again

[tobycurl]

I have had similar corruption on that version of MariaDB.

After getting a backup of all the databases I emptied out my whole installation to try to get it working again.

I found that stopping the service with "brew services stop [email protected]" claimed it had stopped but the process was still running and at 100% CPU. This meant that MariaDB couldn't then be restarted.

I also tried version 10.6 which had the same issue.

I have now installed the latest version of MariaDB (11.3.2). This does seem to stop the process correctly when stopping the service.

So it's maybe an issue with the current 10.X versions?

Output of brew config:

HOMEBREW_VERSION: 4.3.1
ORIGIN: https://github.com/Homebrew/brew.git
HEAD: e0bc557e7b991cb23583679e1cf1c8a92b793aeb
Last commit: 4 days ago
Core tap JSON: 24 May 08:10 UTC
Core cask tap JSON: 24 May 08:10 UTC
HOMEBREW_PREFIX: /usr/local
HOMEBREW_CASK_OPTS: []
HOMEBREW_EDITOR: subl
HOMEBREW_MAKE_JOBS: 6
Homebrew Ruby: 3.3.1 => /usr/local/Homebrew/Library/Homebrew/vendor/portable-ruby/3.3.1/bin/ruby
CPU: hexa-core 64-bit kabylake
Clang: 15.0.0 build 1500
Git: 2.45.1 => /usr/local/bin/git
Curl: 8.4.0 => /usr/bin/curl
macOS: 14.4.1-x86_64
CLT: 15.3.0.0.1.1708646388
Xcode: 15.4

[zsavajji]

Got the same issue on Sonoma 14.5 and MariaDB 11.4.2

2024-06-05  9:00:39 0 [Note] Starting MariaDB 11.4.2-MariaDB source revision 3fca5ed772fb75e3e57c507edef2985f8eba5b12 as process 3374
2024-06-05  9:00:39 0 [Warning] Setting lower_case_table_names=2 because file system for /opt/homebrew/var/mysql/ is case insensitive
2024-06-05  9:00:39 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2024-06-05  9:00:39 0 [Note] InnoDB: Number of transaction pools: 1
2024-06-05  9:00:39 0 [Note] InnoDB: Using generic crc32 instructions
2024-06-05  9:00:39 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-06-05  9:00:39 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-05  9:00:39 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer
2024-06-05  9:00:39 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(84933786464) at 84933786464
2024-06-05  9:00:39 0 [ERROR] InnoDB: Log scan aborted at LSN 84933786464
2024-06-05  9:00:39 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-06-05  9:00:39 0 [Note] InnoDB: Starting shutdown...
2024-06-05  9:00:39 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-06-05  9:00:39 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-06-05  9:00:39 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-06-05  9:00:39 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-06-05  9:00:39 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-06-05  9:00:39 0 [ERROR] Aborting

I resolved the issue by truncating the ib_logfile0, that made mariaDB restart without errors.

Could it be related to brew or to the macOS update?

Thanks

[stychos]

Got the same issue on Sonoma 14.5 and MariaDB 11.4.2

2024-06-05  9:00:39 0 [Note] Starting MariaDB 11.4.2-MariaDB source revision 3fca5ed772fb75e3e57c507edef2985f8eba5b12 as process 3374
2024-06-05  9:00:39 0 [Warning] Setting lower_case_table_names=2 because file system for /opt/homebrew/var/mysql/ is case insensitive
2024-06-05  9:00:39 0 [Note] InnoDB: Compressed tables use zlib 1.2.12
2024-06-05  9:00:39 0 [Note] InnoDB: Number of transaction pools: 1
2024-06-05  9:00:39 0 [Note] InnoDB: Using generic crc32 instructions
2024-06-05  9:00:39 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2024-06-05  9:00:39 0 [Note] InnoDB: Completed initialization of buffer pool
2024-06-05  9:00:39 0 [Note] InnoDB: Resetting space id's in the doublewrite buffer
2024-06-05  9:00:39 0 [ERROR] InnoDB: Missing FILE_CHECKPOINT(84933786464) at 84933786464
2024-06-05  9:00:39 0 [ERROR] InnoDB: Log scan aborted at LSN 84933786464
2024-06-05  9:00:39 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2024-06-05  9:00:39 0 [Note] InnoDB: Starting shutdown...
2024-06-05  9:00:39 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2024-06-05  9:00:39 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-06-05  9:00:39 0 [Note] Plugin 'wsrep-provider' is disabled.
2024-06-05  9:00:39 0 [Warning] 'innodb-file-format' was removed. It does nothing now and exists only for compatibility with old my.cnf files.
2024-06-05  9:00:39 0 [ERROR] Unknown/unsupported storage engine: InnoDB
2024-06-05  9:00:39 0 [ERROR] Aborting

I resolved the issue by truncating the ib_logfile0, that made mariaDB restart without errors.

Could it be related to brew or to the macOS update?

Thanks

Same on Ventura, looks like not a macOS update issue.

[dotsam]

It looks like this is an upstream bug (MDEV-34129 and MDEV-34254) and has a possible pending fix for the 10.11.x series in MariaDB/server#3253

[camille-hdl]

I have this problem as well.

With 10.11, mariadbd fails to stop with the message "Signal handler thread did not exit in a timely manner. Continuing to wait for it to stop..".
With 11.4 however, mariadbd shuts down correctly (at least, the process ends), but the "Missing FILE_CHECKPOINT" InnoDB error is still occurring.
Truncating ib_logfile0 does not allow MariaDB to restart, only giving the message [ERROR] InnoDB: ib_logfile0 is empty, and LSN is unknown. since, as I understand it, a valid log file is required by InnoDB now.

The only way to recover i've found is to completely remove mariadb, reinstall and re-import the databases.

I have tried using brew services, mysql.server start/stop, mysqld_safe or mysqld directly, without success.

I'm using macOS 14.5 on Apple silicon.

[camille-hdl]

in case that's relevant, the issue does not happen with mysql 5.7 on the same system with the same database.

[mazzelman]

I updated my machine yesterday, and i think i have the same problem. I uninstalled mariabd and installed it again. I disabled the auto start. But it still crashes. This is really annoying. As i am not that good with mariadb, is there a solution for the moment? Can i go back to a previous version of mariadb?

[mazzelman]

I have now installed an older version. That is working for now. I will describe what i did. But please keep in mind, that i have this running here on my mac for testing purposes. I'm not responsible when something breaks! This will erase all of your database files. So this is only working, if you have a backup!

1. uninstall mariadb

brew uninstall mariadb

Brew says that the mecab files are still there, so i did a:

sudo rm -rf /opt/homebrew/etc/mecabrc

After that i removed other files too:

rm -rf /opt/homebrew/var/mysql
rm -rf /opt/homebrew/etc/my.cnf
rm -rf /opt/homebrew/etc/my.cnf.d
rm -rf /opt/homebrew/etc/my.cnf.default

Then i restarted the mac.

2. install [email protected]

To install mariadb i choosed an older version.

brew install [email protected]

After the install brew mentions that this version is not in the path, because it is an older version. But brew tells me, that when i need this in my path (to work with it in the console) i can do a:

echo 'export PATH="/opt/homebrew/opt/[email protected]/bin:$PATH"' >> ~/.zshrc

Important! I use zsh. If you use another terminal like bash, i hope brew will give you the right copy and paste command!

Restart the terminal so that you can use the command mysql

3. Set a root password

sudo mysql

here you have to type in your account password, that you use for your mac user!

If you typed in your password correct, you will enter this mysql command thingy...

Here i did:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newrootpassword';

Important! Replace newrootpassword with your own password. You can choose a new pasword for the mysql root user.

Then write exit to leave the mysql console

exit

After that i imported back my databases with phpmyadmin. And did a whole restart of the system. And it works for me again!

I hope this helps for now. 🖖

[joubertvasc]

Worked for me. Thank you very much. The version 11.4.2 is broken.

[abubakar-iqbal]

Thanks you soo much it's worked for me as well. But you don't mention the mysql data will be loss.

[suleodu]

I got ERROR 2002 (HY000): Can't connect to local server through socket '/tmp/mysql.sock' (2) when i ran sudo mysql

[tino-codes]

you need to start the service with brew services start [email protected]

[apexdivision]

I am having the same problem with MariaDB latest 11.4.2 on macOS 14.5 on M1.
Going ahead as suggested by @mazzelman for now.
Ironically could not find much info on this version error on net, is it only me who is having this issue?

[joubertvasc]

No, I was blowing my mind for days until find this post.

[mikeho]

Having this issue as well... going to downgrade to an older version, similar to above...

But @mazzelman just a FYI -- 10.10 is deprecated and not considered LTS.

See MariaDB Server Releases for more info.

I've gone ahead and downgraded to 10.6 (LTS).

[mazzelman]

@mikeho
Hi, thanks for pointing out. 👍

[renekreijveld]

I have the same problems in MariaDB 11.4.2 and 11.2.4.
I initially downgraded to MariaDB 11.2.4, but that had the same problem. After uninstalling everything I installed 10.6.18.
With that version I experience the following:

After starting MariaDB with:

brew services start [email protected]

I see the following processes:

501 13392 1 0 2:52pm ?? 0:00.02 /bin/sh /opt/homebrew/opt/[email protected]/bin/mysqld_safe --datadir=/opt/homebrew/var/mysql
501 13505 13392 0 2:52pm ?? 0:00.62 /opt/homebrew/opt/[email protected]/bin/mariadbd --basedir=/opt/homebrew/opt/[email protected] --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/[email protected]/lib/plugin --log-error=/opt/homebrew/var/mysql/MacBook-Pro.local.err --pid-file=MacBook-Pro.local.pid

Then, when I stop MariaDB with the following:

brew services stop [email protected]

I get a conformation that MariaDB is stopped:

==> Successfully stopped '[email protected]' (label: [email protected])

But if I check the running processes I still see these:

501 13505 1 0 2:52pm ?? 0:52.26 /opt/homebrew/opt/[email protected]/bin/mariadbd --basedir=/opt/homebrew/opt/[email protected] --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/[email protected]/lib/plugin --log-error=/opt/homebrew/var/mysql/MacBook-Pro.local.err --pid-file=MacBook-Pro.local.pid

I can kill that process with kill -9. If I then restart MariaDB there is no InnoDB corruption. So this way I have a usable workaround.

[reckart]

I have the impression that issue started with Sonoma... I only ran into it after upgrading the OS yesterday.

I wonder if anybody can confirm the issue with Ventura.

[landwire]

I have the same problem on an M3 Apple with 11.4.2.
Any fix in the pipeline?

[renekreijveld]

I downgraded to version 10.6.18. That solved it for now.

[joubertvasc]

I downgraded to 10.10 and my head is on my neck again.

[troyxmccall]

I just moved to run MariaDB on docker since versioning is critical in my dev workflow.

services:

  mariadb:
    image: mariadb:10.11
    restart: unless-stopped
    command: --default-authentication-plugin=mysql_native_password
    environment:
      MYSQL_ROOT_PASSWORD: yourNewRootPasswordHere
      MYSQL_DATABASE: mydatabase
      MYSQL_USER: docker
      MYSQL_PASSWORD: yourNewDockerPasswordHere
    volumes:
     - mariadb_10_11:/var/lib/mysql
    ports:
      - "3306:3306"

  automysqlbackup:
    image: databack/mysql-backup
    restart: unless-stopped
    environment:
      DB_SERVER: mariadb
      DB_TYPE: mysql
      DB_HOST: mariadb
      DB_PORT: 3306
      DB_USER: root
      DB_PASS: yourNewRootPasswordHere
      DB_DUMP_FREQ: 1440
      DB_DUMP_TARGET: /backup
    volumes:
      - automysqlbackup:/backup
      - mariadb_10_11:/var/lib/mysql:ro # Read-only access to MariaDB data

volumes:
  mariadb_10_11:
  automysqlbackup:      

[reckart]

I upgraded to Sonoma (Apple M2) yesterday and that is where the problems started.

FWIW, I have tried 11.4, 10.10 and 10.6 now and all seem to have the problem that brew services stop mariadb... leaves the mariadb server process hanging around.

I get a message about the showdown in the log:

2024-06-20  8:16:53 0 [Note] /opt/homebrew/opt/[email protected]/bin/mariadbd (initiated by: unknown): Normal shutdown

but running ps aux | grep mysql, I can see that the process continues to be there

myuser         40589   0.0  0.3 411358560  73552   ??  S     8:16AM   1:38.39 /opt/homebrew/opt/[email protected]/bin/mariadbd --basedir=/opt/homebrew/opt/[email protected] --datadir=/opt/homebrew/var/mysql --plugin-dir=/opt/homebrew/opt/[email protected]/lib/plugin --log-error=/opt/homebrew/var/mysql/myhost.err --pid-file=myhost.pid

Even kill PID does not work. When I try that, I get another message in the log

2024-06-20  8:18:33 0 [Warning] Signal handler thread did not exit in a timely manner. Continuing to wait for it to stop...

And I think that is the root cause of the problem: the server process that is not stopping.

Eventually, when the system is restarted, it will be force-stopped - which is bad for the data files. Or when trying to stop/restart the service (in particular with 11.4), it seems that the service script might not be checking properly if another deamon is already running which can then lead to a corrupted redo log.

[reckart]

With Mariadb 11.4.2, the service seems to stop properly - but when I restart the service I have the corrupt DB.

[legentbv]

When I downgraded to 10.6.18 stopping MariaDB also doesn't work. I need to kill the process with kill -9.
But, the databases don't get corrupted. If I don't kill the process and reboot the Macbook the databases also don't get corrupted.

[reckart]

• https://jira.mariadb.org/browse/MDEV-34350 is about the broken ib_logfile0 after restart - no fix yet
• https://jira.mariadb.org/browse/MDEV-34129 is about the process not shutting down - this should be fixed in 10.6.19 when it is released

[stychos]

I tried all LTS versions available in Homebrew, all are broken. My databases are big and always get corrupted on forced shutdown. Switched to Percona Server for now, but it is not available on ARM.

Upd: I'm on Ventura.

[klodeckl]

Same here. I downgraded to 10.6. But process stays up when stopping as described above, kill -9 process pid works then. A soon fix would be very helpful.

[magic-thomas]

[reckart]

The issue is already being worked on: https://jira.mariadb.org/browse/MDEV-34422

From the comments, downgrading to anything before 10.8 should help. However, until a 10.6.19 you will then hit https://jira.mariadb.org/browse/MDEV-34129.