Upgrade the version of MySQL installed in your local machine with Ruby on Rails

This is the note about what I did when I upgraded MySQL from v5 to v8 in my local machine

Photo by Rubaitul Azad on Unsplash

Background

Last week, I had to upgrade the version of MySQL from v5 to v8 in my local machine. I usually use docker image for the databases recently to avoid the problems, which someone’s environment works well but the other’s one doesn’t. However, there are only two engineers in the project and the development environment hasn’t been organized yet. So I hoped there wouldn’t be any problem. However, I got the following error after I executed brew upgrade mysql , added the path to bash file like export PATH=”/opt/homebrew/opt/mysql@8.0/bin:$PATH” and trid to connect mysql.

ERROR 2002 (HY000): Can’t connect to local MySQL server through socket '/tmp/mysql.sock' (111)

Then I started to investigate the problem.

What I did

I thought there wouldn’t be running MySQL server after I saw the error. Then I tried to start the server with the mysql.server start command. However, I couldn’t to start the server at all but I got the following error.

Mysql ERROR! The server quit without updating PID file

I couldn’t get the reason why I couldn’t start the server only from the logs. So I checked the error logs with the command.

cat /opt/homebrew/var/mysql/My-Name-MacBook-Pro.local.err

Then I found the following error at the end of the outputs.

2024-08-29T21:01:42.254088Z 0 [ERROR] [MY-000077] [Server] /opt/homebrew/opt/mysql@8.0/bin/mysqld: Error while setting value 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' to 'sql_mode'.
2024-08-29T21:01:42.254168Z 0 [ERROR] [MY-010119] [Server] Aborting
2024-08-29T21:01:42.254192Z 0 [Note] [MY-010120] [Server] Binlog end

After I looked at the logs, I didn’t get the sense for a while. So I decided to open the all settings file for MySQL and found the reason. In my .my.conf file, I found the following settings.

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Then I understood what was happening in my machine. The sql_mode variables worked well in v5 MySQL but it became invalidated in v8 so that the server didn’t start at all. After I removed the settings, the server started.

However, I got another error when I tried to run the rails server. I forgot to save the actual output but the error said there wasn’t v5 MySQL in the machine. Strangly, rails still tried to use the old MySQL. Just in case, I checked the installed packages with the brew list command but I didn’t find mysql@5.0 anymore. So I assumed something wrong with the mysql2 gem. I just uninstalled it and installed it again like below.

bundle exec gem uninstall mysql2
bundle install

Then I tried to start the server and it succeeded finally. I didn’t investigate well but I guess the mysql2 gem cached the version when it was installed.

That’s it!