Connecting Rails to MySQL

rails new blog -d mysql gets you a config/database.yml that works on the laptop it was generated on and fails on the next machine, and the reason is not in the file. Half of what makes a Rails process reach a MySQL server is decided by the client library rather than by anything you wrote, and the two client libraries Rails supports disagree about it.

Everything below was run today against MySQL 26.7.0 (Homebrew, arm64), Ruby 4.0.5, activerecord 8.1.4, mysql2 0.5.7 and trilogy 2.13.0, on an Apple M2 Max. The comparisons against PostgreSQL 17.7 were run on the same laptop. Every fenced block was executed, and the test file at the bottom is the one that holds the claims.

The command, and the three files it edits

Two commands cover the starting position. A new application:

rails new blog -d mysql

An application that already exists and is on something else:

bin/rails db:system:change --to=mysql

Run against a fresh -d postgresql app under git, db:system:change --to=mysql touched exactly three files:

 Dockerfile          |  4 ++--
 Gemfile             |  2 +-
 config/database.yml | 60 ++++++++++++++---------------------------------------
 3 files changed, 18 insertions(+), 48 deletions(-)

The Gemfile change is one line, and it leaves the comment above it lying:

 # Use postgresql as the database for Active Record
-gem "pg", "~> 1.1"
+gem "mysql2", "~> 0.5"

edit_gemfile in railties-8.1.4/lib/rails/generators/rails/db/system/change/change_generator.rb gsubs the gem name and the gem entry, and nothing in it looks at the comment. Cosmetic, but it is the first thing a reviewer reads in the diff.

The Dockerfile change is not cosmetic. The base stage gets default-mysql-client in place of postgresql-client, and the build stage gets default-libmysqlclient-dev in place of libpq-dev. --to=trilogy removes libpq-dev and adds nothing to the build stage at all, because Rails::Generators::Database::Trilogy#build_package returns nil. The valid values for --to are in DATABASES at lib/rails/generators/database.rb:6: mysql, trilogy, postgresql, sqlite3, mariadb-mysql, mariadb-trilogy. Four of the six are MySQL protocol.

The socket path in your database.yml came from a directory listing

config/database.yml generated on this machine carries a hardcoded socket and no host:

default: &default
  adapter: mysql2
  encoding: utf8mb4
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  socket: /tmp/mysql.sock

That path was not a default. Rails::Generators::Database::MySQL#socket, at railties-8.1.4/lib/rails/generators/database.rb:31, walks nine candidate paths and takes the first one that exists on the machine running the generator:

def socket
  @socket ||= [
    "/tmp/mysql.sock",                        # default
    "/var/run/mysqld/mysqld.sock",            # debian/gentoo
    "/var/tmp/mysql.sock",                    # freebsd
    "/var/lib/mysql/mysql.sock",              # fedora
    "/opt/local/lib/mysql/mysql.sock",        # fedora
    "/opt/local/var/run/mysqld/mysqld.sock",  # mac + darwinports + mysql
    "/opt/local/var/run/mysql4/mysqld.sock",  # mac + darwinports + mysql4
    "/opt/local/var/run/mysql5/mysqld.sock",  # mac + darwinports + mysql5
    "/opt/lampp/var/mysql/mysql.sock"         # xampp for linux
  ].find { |f| File.exist?(f) } unless Gem.win_platform?
end

So the committed file records where MySQL happened to live on one developer's laptop at one moment. A teammate on Debian gets the Debian path from the same repository and the error is this, which is the single most common "Rails cannot connect to MySQL" message there is:

Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

If no candidate exists when you run rails new, the socket: line is omitted entirely and the default block has neither a host nor a socket. That still connects locally, because with nothing specified libmysqlclient uses the socket anyway, so the omission is invisible until the file reaches a container where there is no socket to use.

host: localhost under mysql2 is not a hostname

Under the Rails mysql2 adapter, localhost is a keyword meaning "use the unix socket", and the port: sitting next to it in the YAML is discarded. Here is the proof, asking the server itself how each connection arrived. performance_schema.threads.connection_type is the column, and it answers Socket, TCP/IP or SSL/TLS:

def connection_type(config)
  ActiveRecord::Base.establish_connection(config)
  id = ActiveRecord::Base.lease_connection.select_value("select connection_id()")
  ActiveRecord::Base.lease_connection.select_value(
    "select connection_type from performance_schema.threads where processlist_id = #{id}"
  )
ensure
  ActiveRecord::Base.remove_connection
end

Four configurations, one server, run through bin/rails runner:

mysql2  host: localhost  port: 3306   Socket
mysql2  host: localhost  port: 9999   Socket
mysql2  host: 127.0.0.1  port: 3306   SSL/TLS
trilogy host: localhost  port: 3306   TCP/IP

The second row is the one that costs an afternoon. Port 9999 has nothing listening on it, the connection succeeds anyway, and every conclusion you draw about your port configuration from a working bin/rails console is worthless. Change localhost to 127.0.0.1 and the same config fails honestly:

Mysql2::Error::ConnectionError: Can't connect to MySQL server on '127.0.0.1:9999' (61)

The third row is worth knowing for a different reason. Nothing in that config asked for encryption, and the server reported the connection as SSL/TLS anyway: mysql2 0.5.7 here links libmysqlclient.24.dylib, which upgrades a TCP connection when the server offers a certificate. Trilogy on the same address and port reported TCP/IP, in the clear. So an application that moved from mysql2 to trilogy with no other change, and whose MySQL is across a network, turned TLS off in that commit.

The trilogy template ships a host and a socket, and the socket wins

The Rails trilogy adapter gets a different template, and db:system:change --to=trilogy writes a database.yml that sets both keys: host in the shared default block, socket inside each environment.

default: &default
  adapter: trilogy
  encoding: utf8mb4
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:
  host: <%= ENV.fetch("DB_HOST") { "127.0.0.1" } %>

development:
  <<: *default
  socket: /tmp/mysql.sock
  database: blog_development

Feed that same pair of keys to each adapter and they resolve it in opposite directions:

trilogy  host: 127.0.0.1 + socket: /tmp/mysql.sock   Socket
mysql2   host: 127.0.0.1 + socket: /tmp/mysql.sock   SSL/TLS

Point the socket at a path that does not exist and the divergence gets worse. Trilogy fails:

ActiveRecord::ConnectionNotEstablished: No such file or directory - No such file or directory - connect(2) for /tmp/nope.sock

mysql2 answers SSL/TLS, because it never looked at the socket in the first place. So the file that comes out of --to=trilogy has one setting that does nothing under one adapter and overrides everything under the other, and setting DB_HOST to a remote server does nothing at all in development until you also delete the socket: line.

caching_sha2_password refuses you after the password is right

Access denied means the password is wrong. This is the other error, the one that arrives when the password is right:

Mysql2::Error: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

caching_sha2_password is the only password plugin this server will hand out. information_schema.plugins lists sha256_password and caching_sha2_password and nothing else, and every row of mysql.user on a stock install, root included, has caching_sha2_password in its plugin column. On a first login, before the server has cached the credential, it will accept the password over a TLS connection or as an RSA-encrypted blob, and refuses anything else. ssl_mode: disabled in database.yml removes the first option, and mysql2 does not take the second unless you ask.

Two settings fix it and one famous instruction does not. The famous one first, because it is the top answer on every forum thread carrying this message:

$ mysql -u root -e "ALTER USER 'app'@'%' IDENTIFIED WITH mysql_native_password BY 'secret'"
ERROR 1524 (HY000) at line 1: Plugin 'mysql_native_password' is not loaded

mysql_native_password is not loaded on this server and cannot be switched on, since it is not in information_schema.plugins either. Neither is the variable the same threads tell you to set in my.cnf:

$ mysql -u root -e "select @@default_authentication_plugin"
ERROR 1193 (HY000) at line 1: Unknown system variable 'default_authentication_plugin'

The variable that replaced it is authentication_policy, and it reads *,, here. So the two pieces of advice a search for this error returns first are both things this server will refuse to do, and an hour goes into finding that out one command at a time.

The two settings that get a caching_sha2_password login through

Picking one of the two paths the plugin actually offers is the whole fix. Ask for TLS, which is the right answer over a network and costs a handshake:

production:
  adapter: mysql2
  ssl_mode: required

Or stay in the clear and let the client fetch the server's RSA public key, which is the right answer inside a private network where TLS is a certificate you do not want to manage:

production:
  adapter: mysql2
  ssl_mode: disabled
  get_server_public_key: true

Both keys go straight through database.yml into Mysql2::Client.new, and both were verified by reading connection_type back off the server: SSL/TLS for the first, TCP/IP for the second. Note what the second one costs: the password crosses the wire encrypted under the server's public key, but every query and every result after it is plaintext.

Trilogy needs neither. It implements the RSA path itself, unconditionally, in trilogy-2.13.0/ext/trilogy-ruby/src/client.c at send_public_key_request (line 561) and encrypt_password_with_public_key (line 568). After a FLUSH PRIVILEGES to empty the server's credential cache, trilogy authenticated the same user over an unencrypted TCP connection where mysql2 with ssl_mode: disabled raised. Convenient, and easy to mistake for "trilogy handles security for me", which it is the opposite of.

mysql2 or trilogy

Trilogy, unless something specific stops you. The argument is the linker, not the benchmark.

$ otool -L mysql2-0.5.7/lib/mysql2/mysql2.bundle
mysql2-0.5.7/lib/mysql2/mysql2.bundle:
    /Users/x/.rvm/rubies/ruby-4.0.5/lib/libruby.4.0.dylib (compatibility version 4.0.0, current version 4.0.5)
    /opt/homebrew/opt/mysql/lib/libmysqlclient.24.dylib (compatibility version 24.0.0, current version 24.0.0)
    /opt/homebrew/opt/zlib/lib/libz.1.dylib (compatibility version 1.0.0, current version 1.3.2)
    /opt/homebrew/opt/zstd/lib/libzstd.1.dylib (compatibility version 1.0.0, current version 1.5.7)
    /opt/homebrew/opt/openssl@3/lib/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
    /opt/homebrew/opt/openssl@3/lib/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0)
    /usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 1.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1356.0.0)

$ otool -L trilogy-2.13.0/lib/trilogy/cext.bundle
trilogy-2.13.0/lib/trilogy/cext.bundle:
    /Users/x/.rvm/rubies/ruby-4.0.5/lib/libruby.4.0.dylib (compatibility version 4.0.0, current version 4.0.5)
    /opt/homebrew/opt/openssl@3/lib/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
    /opt/homebrew/opt/openssl@3/lib/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1356.0.0)

(Run from the gem directory, with the home directory in the libruby line shortened to /Users/x. Nothing else is edited.)

mysql2 needs Oracle's client library present at build time and on the image at runtime, which is what default-libmysqlclient-dev in the Dockerfile buys and what this looks like when it is missing:

Cannot find include dir(s) /opt/nonexistent/include
*** extconf.rb failed ***
Could not create Makefile due to some reason, probably lack of necessary
libraries and/or headers.

Trilogy has no such dependency. It is a MySQL protocol implementation in C with OpenSSL for the TLS, which is why its Dockerfile build stage is shorter and why its gem installs on a machine that has never had MySQL on it.

Speed is not the argument. 5000 iterations of Post.where("views < ?", 500).limit(20).to_a against a 1000-row table, over the unix socket, two runs each:

mysql2   socket   0.417 ms/query, then 0.398
trilogy  socket   0.359 ms/query, then 0.362

Around 10% on a query that does almost nothing, which is the best case for a difference in driver overhead and still not a reason to migrate anything. Do not compare the TCP numbers from the same run: mysql2 was doing TLS and trilogy was not, so that column measures encryption, not drivers.

The cost of switching is real and it is not the gem line. Error classes change. The same rejected index reaches Ruby as two different messages, both wrapped in ActiveRecord::StatementInvalid:

Mysql2::Error: BLOB/TEXT column 'body' used in key specification without a key length
Trilogy::ProtocolError: 1170: BLOB/TEXT column 'body' used in key specification without a key length (trilogy_query_recv)

Any rescue Mysql2::Error in your codebase stops rescuing on the day you switch, silently, and the trilogy message carries the MySQL error number where the mysql2 one does not. Grep for the constant before you change the adapter.

A failed migration leaves the schema half applied

MySQL commits DDL implicitly, which means a Rails migration is not atomic. Here is the whole difference, run through the same Active Record on both servers:

ActiveRecord::Base.transaction do
  c.execute("create table ddl_probe (id int)")
  c.execute("insert into ddl_probe values (1)")
  raise ActiveRecord::Rollback
end
exists = c.table_exists?(:ddl_probe)
rows = exists ? c.select_value("select count(*) from ddl_probe") : nil
puts "#{label}: after ROLLBACK, table exists=#{exists}, rows=#{rows.inspect}"

postgresql 17.7: after ROLLBACK, table exists=false, rows=nil
mysql 26.7.0   : after ROLLBACK, table exists=true, rows=1

The CREATE TABLE committed itself, and it took the INSERT after it along. Play that out in a migration that creates two tables where the second one is wrong:

class TwoTables < ActiveRecord::Migration[8.1]
  def change
    create_table :alpha do |t|
      t.string :name
    end

    create_table :beta do |t|
      t.text :body, index: true
    end
  end
end

== 20260927090000 TwoTables: migrating ========================================
-- create_table(:alpha)
   -> 0.0042s
-- create_table(:beta)
bin/rails aborted!
StandardError: An error has occurred, all later migrations canceled: (StandardError)

Mysql2::Error: BLOB/TEXT column 'body' used in key specification without a key length
db/migrate/20260927090000_two_tables.rb:7:in 'TwoTables#change'

After that, alpha is in show tables and 20260927090000 is not in schema_migrations. Fix the migration, run it again, and the retry dies on the wreckage of the first attempt:

Mysql2::Error: Table 'alpha' already exists

Somebody has to drop alpha by hand before the migration can run, and on a production database that somebody is doing it at the point in the deploy where the new code is already out. The working practice is the one that is good manners on PostgreSQL and mandatory here: one schema change per migration file, and never a create_table and a data backfill in the same one. The rest of that argument, including the disable_ddl_transaction! cases where PostgreSQL gives up atomicity too, is in migrations that do not break, and the same failed migration run side by side on SQLite, PostgreSQL and MySQL is in what database Rails uses.

REPEATABLE READ is the default, and PostgreSQL's is not

@@global.transaction_isolation on this server is REPEATABLE-READ. show transaction_isolation on PostgreSQL 17.7 is read committed. An application ported between the two gets a different answer from the same code with nothing in the diff to point at.

What the difference looks like: one connection opens a transaction and reads a row twice, another connection commits a new value in between.

REPEATABLE-READ: first read 1, then another connection committed 99, second read 1
READ-COMMITTED:  first read 1, then another connection committed 99, second read 99

Under the MySQL default, every read inside ActiveRecord::Base.transaction is served from the snapshot taken at the first read of that transaction. A job that opens a transaction, calls an external API for two seconds, then re-reads the record to decide what to do is reading two-second-old data and cannot be made to see otherwise. Under PostgreSQL's default the second read is current. Neither is a bug. They are different defaults, and only one of them matches what the code was written against.

Set it per connection in database.yml if you are porting, rather than arguing about it in each job:

default: &default
  adapter: trilogy
  variables:
    transaction_isolation: "READ-COMMITTED"

Verified by reading @@session.transaction_isolation back after boot. The cost is that the isolation level is not only a visibility setting in InnoDB, and the MySQL 8.4 reference manual is explicit about both halves. On locking: under READ COMMITTED, "InnoDB locks only index records, not the gaps before them, and thus permits the free insertion of new records next to locked records", which changes which concurrent workloads deadlock. On replication: "Only row-based binary logging is supported with the READ COMMITTED isolation level." Neither was measured here; both are quoted from the manual page on InnoDB transaction isolation levels. If you inherited a server running binlog_format=STATEMENT, that second sentence is the one to settle before you change anything.

Four column defaults that are not the shape you left in Postgres

t.decimal :price with no precision is the expensive one. The identical migration on PostgreSQL 17.7 produced a bare numeric column that stored 0.1999e2. On MySQL:

price decimal(10,0) DEFAULT NULL

Scale zero. create!(price: 19.99) returned a record whose price is 19 before it ever reached the database, because Active Record's decimal type casts to the declared scale on assignment. No exception, no warning, and the cents are gone. Write t.decimal :price, precision: 10, scale: 2, which produced decimal(10,2) and kept 0.1999e2, and which is the advice in money and decimals for every database and merely load-bearing on this one.

t.text is 65535 bytes, not unlimited. 65535 bytes stored; one byte more raised ActiveRecord::ValueTooLong: Mysql2::Error: Data too long for column 'body' at row 1. The same column on PostgreSQL 17.7 took a 200000 byte string without comment. If the column holds anything user-generated, declare t.text :body, limit: 16.megabytes, which Active Record renders as longtext here and which did store 200000 bytes.

Indexes have a 3072 byte key limit and utf8mb4 counts four bytes per character, so a varchar(255) column costs 1020 bytes of it. Three of them fit in one composite index and four do not:

Mysql2::Error: Specified key was too long; max key length is 3072 bytes

Indexing a TEXT column at all needs a prefix length, which add_index has no argument for in the shorthand form:

Mysql2::Error: BLOB/TEXT column 'body' used in key specification without a key length

t.boolean is tinyint(1), which round-trips as true and false through Active Record and as 1 and 0 through bin/rails dbconsole. Worth knowing when you are reading production data by hand; otherwise not something you have to manage.

The database.yml worth committing

Two changes to what the generator writes, both verified by booting the app against them:

default: &default
  adapter: trilogy
  encoding: utf8mb4
  collation: utf8mb4_0900_ai_ci
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: <%= ENV.fetch("MYSQL_HOST", "127.0.0.1") %>
  port: <%= ENV.fetch("MYSQL_PORT", 3306) %>
  username: <%= ENV.fetch("MYSQL_USER", "root") %>
  password: <%= ENV["MYSQL_PASSWORD"] %>
  variables:
    transaction_isolation: "READ-COMMITTED"

development:
  <<: *default
  database: blog_development

No socket: anywhere, so every environment reaches the server the same way and a wrong port is an error instead of a silent fallback. 127.0.0.1 rather than localhost, for the same reason. The variables: block is a per-session SET, so it applies to every connection in the pool without touching my.cnf. Booted against that file, bin/rails runner printed:

Trilogy
READ-COMMITTED
posts=1000

max_connections: is what the Rails 8.1 generator writes where older applications have pool:, and setting both to different numbers raises RuntimeError: Ambiguous configuration before anything connects. That key, the shared: block and what DATABASE_URL does to the rest of the file are the subject of Rails database.yml, resolved, and none of it is adapter-specific.

The test file

Twenty-four examples across three files in a scratch app, holding every claim above that a test can hold:

$ bin/rails test test/models/
Run options: --seed 30024

# Running:

........................

Finished in 0.473327s, 50.7049 runs/s, 101.4098 assertions/s.
24 runs, 48 assertions, 0 failures, 0 errors, 0 skips

One of them had to be written twice, and the second version is the article in miniature. The schema tests open with create_table, which under the default transactional test wrapper produced:

ActiveRecord::StatementInvalid: Mysql2::Error: SAVEPOINT active_record_1 does not exist

The implicit commit had ended the transaction Rails opened around the example, so the savepoint it tried to release afterwards was gone. Four examples errored for a reason that had nothing to do with what they were testing. self.use_transactional_tests = false on that class is the fix, and any test class of yours that runs DDL needs the same line.

What this page does not cover

MariaDB, which Rails treats as a separate preconfiguration (mariadb-mysql and mariadb-trilogy in that DATABASES list) and whose authentication, JSON type and sequence support all differ from what is above. Replication and read replicas, where Rails 8.1's connects_to and automatic role switching are the same code on every adapter but the failover semantics are not. FULLTEXT indexes, which are the MySQL answer to the thing PostgreSQL full text search does with tsvector, and which deserve their own measurements rather than a paragraph here. Managed MySQL on RDS, PlanetScale or Vitess, none of which run on this laptop, so nothing about their connection limits or their DDL behaviour appears above. And every benchmark of MySQL against PostgreSQL, because the only two numbers here are driver overhead on one query shape on one machine, and a page that turned those into a verdict about the servers would be inventing the part that matters.

The scratch app that produced all of it is a plain rails new blog -d mysql plus three test files. Nothing in it is specific to this machine except the socket path, which is the point of the second section.