Using MySQL with Rails 6 and installing MySQL on Mac (macOS Mojave)
Tags: rails, mysql
Published: 2019-09-26 06:34:19

I recently had to switch databases from Sqlite to MySQL after already creating a Rails app. I also had to install MySQL on my Mac.

This article goes over how I did this and fixed a weird OS-related error I got when trying to install the `mysql` gem.


# Install MySQL on your Mac (with homebrew)

If you use homebrew for package management, you can install MySQL by using the following command on the command line:

```
brew install mysql
```

After it finishes installing, make sure you go over the instructions that homebrew gives you:

```
We've installed your MySQL database without a root password. To secure it run:
    mysql_secure_installation

MySQL is configured to only allow connections from localhost by default

To connect run:
    mysql -uroot

To have launchd start mysql now and restart at login:
  brew services start mysql
Or, if you don't want/need a background service you can just run:
  mysql.server start
```

You can set up a password with the `mysql_secure_installation` command.

You can start up MySQL right away with the `brew services start mysql` command.

Next, let's set up Rails to use MySQL!


# Use MySQL with a brand new Rails app

If you haven't created your application yet, you can run the `rails new` command with a flag that configures your database to use MySQL instead of the default Sqlite:

```
rails new appname -d mysql
```

That will set up the Gemfile to use the `mysql2` gem and make a `config/database.yml` with some default MySQL configurations.


# Switching databases in an existing Rails 6 application

Rails 6 adds the handy `rails db:system:change` command that will automatically create the code needed to switch databases, which involves editing the `config/database.yml` file and adding the `mysql2` gem to the `Gemfile`.

Use the following command from within your Rails application directory (you can also change to other databases, too. See [here](https://gorails.com/episodes/rails-6-db-system-change-command]:

```
rails db:system:change --to=mysql
```

It will ask you if you want to overwrite the `config/database.yml` file. Type `y` and press enter and you'll see that Rails has updated the following files:

```
force  config/database.yml
 gsub  Gemfile
 gsub  Gemfile
```

This adds the `mysql` gem to the Gemfile. Before installing it, open up the Gemfile, look for the line that starts with `gem mysql`, and check which version of the gem it's using. It may be an older version, so you can check the [Ruby Gems directory](https://rubygems.org/gems/mysql2/) for the newest version and use that instead if you like.

Run `bundle install` to install the `mysql2` gem. If everything goes well, all you need to do is create the database (skip down to the "Create the database" section below). I ran into an error, which I'll discuss below.

## Manually configure Rails for MySQL

To manually set up MySQL, first, remove the `gem sqlite3` line from the Gemfile and add `gem mysql2` in its place.

Configure the `config/database.yml` like below. These are just basic configurations provided by Rails (some comments cut out to save space), but adjust as needed:

```yml
# config/database.yml

# MySQL. Versions 5.5.8 and up are supported.
#
# Install the MySQL driver
#   gem install mysql2
#
# Ensure the MySQL gem is defined in your Gemfile
#   gem 'mysql2'
#
# And be sure to use new-style password hashing:
#   https://dev.mysql.com/doc/refman/5.7/en/password-hashing.html
#
default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

development:
  <<: *default
  database: myapp_development

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: myapp_test

production:
  <<: *default
  database: myapp_production
  username: myapp
  password: <%= ENV['PACHISLO_DATABASE_PASSWORD'] %>
```


# Error when installing the mysql2 gem

When trying to install the `mysql` gem, I got a crazy long error like this:

```
ERROR:  Error installing mysql2:
	ERROR: Failed to build gem native extension.

    current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
/Users/morinoko/.rbenv/versions/2.6.3/bin/ruby -I /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/2.6.0 -r ./siteconf20190926-66829-1k4yip5.rb extconf.rb
checking for rb_absint_size()... yes
checking for rb_absint_singlebit_p()... yes
checking for rb_wait_for_single_fd()... yes
-----
Using mysql_config at /usr/local/bin/mysql_config
-----
checking for mysql.h... yes
checking for errmsg.h... yes
checking for SSL_MODE_DISABLED in mysql.h... yes
checking for SSL_MODE_PREFERRED in mysql.h... yes
checking for SSL_MODE_REQUIRED in mysql.h... yes
checking for SSL_MODE_VERIFY_CA in mysql.h... yes
checking for SSL_MODE_VERIFY_IDENTITY in mysql.h... yes
checking for MYSQL.net.vio in mysql.h... yes
checking for MYSQL.net.pvio in mysql.h... no
checking for MYSQL_ENABLE_CLEARTEXT_PLUGIN in mysql.h... yes
checking for SERVER_QUERY_NO_GOOD_INDEX_USED in mysql.h... yes
checking for SERVER_QUERY_NO_INDEX_USED in mysql.h... yes
checking for SERVER_QUERY_WAS_SLOW in mysql.h... yes
checking for MYSQL_OPTION_MULTI_STATEMENTS_ON in mysql.h... yes
checking for MYSQL_OPTION_MULTI_STATEMENTS_OFF in mysql.h... yes
checking for my_bool in mysql.h... no
-----
Don't know how to set rpath on your system, if MySQL libraries are not in path mysql2 may not load
-----
-----
Setting libpath to /usr/local/Cellar/mysql/8.0.17_1/lib
-----
creating Makefile

current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
make "DESTDIR=" clean

current directory: /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2/ext/mysql2
make "DESTDIR="
compiling client.c
compiling infile.c
compiling mysql2_ext.c
compiling result.c
compiling statement.c
linking shared-object mysql2/mysql2.bundle
ld: library not found for -lssl
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [mysql2.bundle] Error 1

make failed, exit code 2

Gem files will remain installed in /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/gems/mysql2-0.5.2 for inspection.
Results logged to /Users/morinoko/.rbenv/versions/2.6.3/lib/ruby/gems/2.6.0/extensions/x86_64-darwin-18/2.6.0-static/mysql2-0.5.2/gem_make.out
```

After some googling, it seems like this error sometimes shows up if you don't have command-line tools installed (or it got removed when upgrading the mac OS).

Try to install them with:

```
xcode-select --install
```

If it installs, that may have fixed your problem. Run `bundle install` again and it should work.

For me, however, I got a notice telling me that the command line tools were already installed:

```
xcode-select: error: command line tools are already installed, use "Software Update" to install updates
```

To fix this, run the installer with 

```
sudo open /Library/Developer/CommandLineTools/Packages/macOS_SDK_headers_for_macOS_10.14.pkg
```

or

```
sudo installer -pkg /Library/Developer/CommandLineTools/Packages/macOS_SDK_headers_for_macOS_10.14.pkg -target /
```

(I used the first command)

It turns out that this headers package was not included (missing?) in the newest version of Xcode, so the above command allows you to install it manually.

Now try running `bundle install` again and hopefully it will work (if not, you'll have to keep googling)!


# Create the database

You will have to create the database from scratch and re-run any migrations if you had some already.

First, since Rails overwrote the `config/database.yml` file, make sure you open it up and make any necessary changes like editing the database names.

When everything is configured correctly, create the database:

```
# Create the database
rails db:create

#=> Created database 'mydatabase_development'
#=> Created database 'mydatabase_pachislo_test'

# Run migrations
rails db:migrate
```

Now you should be able to boot up your application with `rails server`!

## References

- [mysql2 Ruby Gems page](https://rubygems.org/gems/mysql2/)
- [Article & video](https://gorails.com/episodes/rails-6-db-system-change-command) on the Rails 6's new `rails db:system:change` command
- Ruby on Rails Guides: [Configuring a MySQL or MariaDB Database](https://guides.rubyonrails.org/configuring.html#configuring-a-mysql-or-mariadb-database)
- [Japanese article on how to fix macOS-related problem](https://qiita.com/gonzaemon111/items/ea181ba3ff582a124213)