`mysql` services won't start after 9.0.1 update #5539

thinkverse · 2024-08-04T22:01:11Z

thinkverse
Aug 4, 2024

Output of `brew config`

HOMEBREW_VERSION: 4.3.12
ORIGIN: https://github.com/Homebrew/brew
HEAD: 874d2da45344d3b27aa740e555b0210d8c474220
Last commit: 6 days ago
Core tap JSON: 04 Aug 21:35 UTC
Core cask tap JSON: 04 Aug 21:35 UTC
HOMEBREW_PREFIX: /opt/homebrew
HOMEBREW_CASK_OPTS: []
HOMEBREW_MAKE_JOBS: 10
Homebrew Ruby: 3.3.4 => /opt/homebrew/Library/Homebrew/vendor/portable-ruby/3.3.4/bin/ruby
CPU: 10-core 64-bit arm_blizzard_avalanche
Clang: 15.0.0 build 1500
Git: 2.46.0 => /opt/homebrew/bin/git
Curl: 8.7.1 => /usr/bin/curl
macOS: 14.6-arm64
CLT: 15.3.0.0.1.1708646388
Xcode: 15.4
Rosetta 2: false

Output of `brew doctor`

Your system is ready to brew.

Description of issue

I upgraded the mysql formula today, it went from 8.3 to 9.0.1 (Homebrew/homebrew-core#170446) and now I cannot start the service anymore. 🤷‍♂️

doctor doesn't seem to find any issues, I get the following error when running services start mysql.

Bootstrap failed: 5: Input/output error
Try re-running the command as root for richer errors.
Error: Failure while executing; `/bin/launchctl bootstrap gui/501 /Users/thinkverse/Library/LaunchAgents/homebrew.mxcl.mysql.plist` exited with 5.

Re-running with sudo for richer errors doesn't return any different either and running it with --verbose wasn't much luck.

==> Generated service file for mysql:
   <?xml version="1.0" encoding="UTF-8"?>
   <!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
   <plist version="1.0">
   <dict>
   	<key>KeepAlive</key>
   	<true/>
   	<key>Label</key>
   	<string>homebrew.mxcl.mysql</string>
   	<key>LimitLoadToSessionType</key>
   	<array>
   		<string>Aqua</string>
   		<string>Background</string>
   		<string>LoginWindow</string>
   		<string>StandardIO</string>
   		<string>System</string>
   	</array>
   	<key>ProgramArguments</key>
   	<array>
   		<string>/opt/homebrew/opt/mysql/bin/mysqld_safe</string>
   		<string>--datadir=/opt/homebrew/var/mysql</string>
   	</array>
   	<key>RunAtLoad</key>
   	<true/>
   	<key>WorkingDirectory</key>
   	<string>/opt/homebrew/var/mysql</string>
   </dict>
   </plist>


/bin/launchctl enable gui/501/homebrew.mxcl.mysql
/bin/launchctl bootstrap gui/501 /Users/thinkverse/Library/LaunchAgents/homebrew.mxcl.mysql.plist
Bootstrap failed: 5: Input/output error
Try re-running the command as root for richer errors.
Error: Failure while executing; `/bin/launchctl bootstrap gui/501 /Users/thinkverse/Library/LaunchAgents/homebrew.mxcl.mysql.plist` exited with 5.

I've tried uninstalling, installing, and re-installing and cannot get it to start. I ran services list and mysql seems to be run under root. IDK if that's the issue.

Name        Status  User       File
mysql       stopped root       ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

Does anyone have any ideas or should I wait for the formula to be updated?

Answered by simon-tma

Aug 5, 2024

This is due to Homebrew never releasing a MySQL 8.4 version and you can only upgrade to 9.0 from 8.4.

My fix:

Download and install MySQL 8.4 from https://dev.mysql.com/downloads/mysql/
Find where it was installed to (for me, this was /usr/local/mysql-8.4.2-macos14-x86_64)
Run <path to mysql>/bin/mysqld_safe --datadir=$(brew --prefix)/var/mysql (e.g. /usr/local/mysql-8.4.2-macos14-x86_64/bin/mysqld_safe --datadir=$(brew --prefix)/var/mysql)
Run mysqladmin shutdown
Run brew services start mysql
Uninstall MySQL 8.4

View full answer

simon-tma · 2024-08-05T07:18:43Z

simon-tma
Aug 5, 2024

This is due to Homebrew never releasing a MySQL 8.4 version and you can only upgrade to 9.0 from 8.4.

My fix:

Download and install MySQL 8.4 from https://dev.mysql.com/downloads/mysql/
Find where it was installed to (for me, this was /usr/local/mysql-8.4.2-macos14-x86_64)
Run <path to mysql>/bin/mysqld_safe --datadir=$(brew --prefix)/var/mysql (e.g. /usr/local/mysql-8.4.2-macos14-x86_64/bin/mysqld_safe --datadir=$(brew --prefix)/var/mysql)
Run mysqladmin shutdown
Run brew services start mysql
Uninstall MySQL 8.4

3 replies

thinkverse Aug 5, 2024
Author

Awesome, thank you, that worked! 👍

ulrichborchers Aug 5, 2024

Had the same issue, the fix also worked for me

peylix Aug 11, 2024

Thank you a lot. That really fixed my problem 👍🏻

wokenlex · 2024-08-05T10:43:53Z

wokenlex
Aug 5, 2024

Does it means what everyone who previously had mysql, installed by brew, will run into it?

For the most of the users it's safe now to make something like

brew install [email protected]

to avoid autoupdate to the 9.0, and spend weekends to safe upgrade, because right now I already lost the half of the day.

0 replies

ulrichborchers · 2024-08-05T11:39:48Z

ulrichborchers
Aug 5, 2024

Just for completeness' sake ... I had found this in /usr/local/var/mysql/<host>.err:

2024-08-05T07:15:12.008545Z 1 [ERROR] [MY-014060] [Server] Invalid MySQL server upgrade: Cannot upgrade from 80300 to 90001. Upgrade to next major version is only allowed from the last LTS release, which version 80300 is not.

In case it helps to identify the problem, for anyone who runs into this, as well.

MySQL does not allow the 8.4 version to be skipped and is obviously able to detect that.

0 replies

nosnilmot · 2024-08-05T12:11:26Z

nosnilmot
Aug 5, 2024

To revert to mysql 8.3:

export HOMEBREW_NO_AUTO_UPDATE=1 HOMEBREW_NO_INSTALL_FROM_API=1 
brew update
curl -o "$(brew formula mysql)" https://raw.githubusercontent.com/Homebrew/homebrew-core/579f331b486821a037eccd3cb36832b18141b5b3/Formula/m/mysql.rb
brew reinstall mysql

(this will leave your brew repo with uncommitted modifications which brew doctor will warn about and provides instructions on how to clean up once a proper fix is available, hopefully in the form of a clean upgrade path via mysql 8.4 or at least a separate [email protected] formula)

0 replies

nosnilmot · 2024-08-05T13:32:24Z

nosnilmot
Aug 5, 2024

mysql 8.4 is now available:

brew services stop mysql
brew install [email protected]
brew services start [email protected]

and then you should be able to just stop/start to migrate to 9, if you want to

brew services stop [email protected]
brew services start mysql

8 replies

thinkverse Aug 7, 2024
Author

but it's still giving me the mysql_native_password is not loaded issue.

mysql_native_password was deprecated in 8.4 and removed in 9.0¹, Oracle wrote a post with a solution in it - MySQL 9.0 – it’s time to abandon the weak authentication method, that could be useful to you, apart from that I recommend googling around.

https://dev.mysql.com/doc/relnotes/mysql/9.0/en/news-9-0-0.html#mysqld-9-0-0-deprecation-removal ↩

konnano Aug 7, 2024

Try editing $(brew --prefix)/etc/my.cnf

RobertChalmers Aug 7, 2024

Thanks for getting back. I'm getting closer...
I'm into the databases, and can look around. Discovered this. Bit of a Catch-22 situation.

I started with this;
mysqld_safe --skip-grant-tables &

then after establishing it was working,

mysql> ALTER USER 'postfix'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'xxxxxxxxxx';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement`

Tried altering just one db, but of course grant tables are excluded... and if I start it With grant tables, I can't get to it because it's looking for the supposedly depreciated mysq;_native_password.

The trouble is now as well, because it's now [email protected], almost none of the old posts anywhere are relevant. I keep trying.

RobertChalmers Aug 7, 2024

NanmiCoder Sep 15, 2024

Thank you, it perfectly solved my problem.

RobertChalmers · 2024-08-07T15:04:34Z

RobertChalmers
Aug 7, 2024

So here's the short answer. The long winded one is just above.
Make sure MySQL is stopped and unloaded in the Syscontrol plist.

I went from 8.3 to 9.0.1 and everything stopped.
So I followed the steps above and ended up with the brew install of MySQL again, which gave me 9.0.1. Sweet.
Stopped it all running,
then
$mysqld_safe --skip-grant-tables &

mysql> SELECT user, host, plugin FROM mysql. user WHERE plugin = "mysql_native_password";

FLUSH PRIVILEGES;

then for each user you want to alter.. (all of them?)

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'xxxxxxxx';

Check them all with;
SELECT user, host, plugin FROM mysql. user WHERE plugin = "caching_sha2_password";

and it works.
my website is back up again.
Thank you to the original posters in this thread. I owe you a collective beer.

0 replies

RobertChalmers · 2024-08-07T20:16:44Z

RobertChalmers
Aug 7, 2024

see my post here. It’s this problem exactly. #5539

…

---------- Robert Chalmers (YouTube Creator) ***@***.*** ***@***.***

On 7 Aug 2024, at 14:46, konnano ***@***.***> wrote: Try editing $(brew --prefix)/etc/my.cnf — Reply to this email directly, view it on GitHub <#5539 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AA6KCQV7OVYYKQ3UFFUFW7TZQIQLTAVCNFSM6AAAAABL7FWH42VHI2DSMVQWIX3LMV43URDJONRXK43TNFXW4Q3PNVWWK3TUHMYTAMRWGUYDQMA>. You are receiving this because you commented.

1 reply

Konstrukteur Aug 8, 2024

Thanks so much for sharing. You saved my afternoon and probably my night sleep.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Homebrew

`mysql` services won't start after 9.0.1 update #5539

{{title}}

Replies: 7 comments 12 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

mysql services won't start after 9.0.1 update #5539

Output of brew config

Output of brew doctor

Description of issue

Replies: 7 comments · 12 replies

thinkverse Aug 5, 2024 Author

thinkverse Aug 7, 2024 Author

Footnotes

`mysql` services won't start after 9.0.1 update #5539

Output of `brew config`

Output of `brew doctor`

Replies: 7 comments 12 replies

thinkverse Aug 5, 2024
Author

thinkverse Aug 7, 2024
Author