skip to content

MySQL & Trilogy

Rails reaches MySQL and MariaDB through the mysql2 or trilogy adapters, with utf8mb4, strict mode and no transactional DDL. Interviewers ask what breaks when an app moves off PostgreSQL.

part ofRuby on Railsread it →
on this pageshow

explore

questions

5

In a Rails 8.1 app on MySQL, why does database.yml set encoding: utf8mb4, and what does adding collation: change?

level: juniorimportance: must knowfreq 45%

answer

  1. four bytes per character, not three
  2. SET NAMES on every connection
  3. _ci collations ignore case
  4. unique index treats Ann and ann alike
  5. case_sensitive: true forces BINARY

basics

~20 s

utf8mb4 is MySQL's full UTF-8, so emoji and other four-byte characters survive; Rails sends SET NAMES utf8mb4 on each connection. collation: adds COLLATE to that, choosing how strings compare and sort, and the common _ci collations are case-insensitive.

solid answer

~40 s

The generated `config/database.yml` for MySQL has `encoding: utf8mb4`, and the adapter turns it into `SET NAMES utf8mb4` on every new connection. MySQL's older `utf8` stores at most three bytes per character, so emoji in a guest's note would be rejected or mangled; utf8mb4 holds every Unicode character, and `create_database` also defaults the database to utf8mb4. Adding `collation:` (for example `utf8mb4_0900_ai_ci`) appends `COLLATE` to that statement; a column can also take `collation:` in a migration. Collation decides comparison: names ending `_ci` are case-insensitive, so `where(email: "[email protected]")` finds `[email protected]` and a unique index rejects both. Rails' uniqueness validator follows the column's collation unless you pass `case_sensitive: true`, which compares with `BINARY`.

go deeper

for a junior

Recall that encoding: utf8mb4 is full UTF-8 for MySQL and that _ci collations compare without case.

for a middle

Explain how SET NAMES carries encoding and collation, column-level collation:, and how the uniqueness validator follows it.

for a senior

Audit a migration from PostgreSQL for case-insensitive lookups, unique indexes and old three-byte tables.

for a principal

Set a collation policy per kind of data, weighing user-friendly matching against exactness for codes and tokens.

## Two settings, two jobs When a hotel-reservation app moves from PostgreSQL to MySQL, the first lines anyone touches are in `config/database.yml`. The generated MySQL configuration (for the `mysql2` or `trilogy` adapter) starts like this: ```yaml default: &default adapter: trilogy encoding: utf8mb4 max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %> username: root ``` | Key | What Rails does with it | What it controls | |---|---|---| | `encoding:` | `SET NAMES <encoding>` when each connection is configured | the character set strings travel in | | `collation:` | appended as `COLLATE <collation>` to the same `SET NAMES` | how strings compare and sort on that connection | Both are applied in the adapter's `configure_connection`, in a single `SET` statement that also carries the SQL mode and any `variables:`. The collation is only sent when an encoding is set. ## Why utf8mb4 and not utf8 In MySQL, the character set historically called `utf8` stores at most **three bytes** per character. Many characters need four: emoji, some CJK characters, many symbols. A guest who writes "Late arrival, sorry" with a smiley in the special-requests field would have that text rejected or mangled. `utf8mb4` is the real four-byte UTF-8, which is why Rails generates it and why `create_database` defaults a new database to `utf8mb4`. Coming from PostgreSQL, where a UTF8 database simply holds any character, this is a setting that can be silently wrong on an old MySQL server: tables created years ago with the three-byte set keep it until they are converted. ## What collation changes A **collation** is the rule set for comparing strings. Its suffix tells you the rules: - `_ci` means **case-insensitive**; MySQL 8's default for utf8mb4, `utf8mb4_0900_ai_ci`, is also accent-insensitive (`_ai`); - `_cs` means case-sensitive, and `_bin` compares the raw bytes. That matters for a reservation app in very concrete ways: 1. **Lookups.** `Guest.where(email: "[email protected]")` finds `[email protected]` under a `_ci` collation. On PostgreSQL the same query found nothing. 2. **Unique indexes.** A unique index on `guests.email` rejects `[email protected]` if `[email protected]` exists, raising `ActiveRecord::RecordNotUnique`. 3. **Sorting and grouping.** `order(:last_name)` and `group(:city)` follow the collation's rules, so "Zürich" and "Zurich" can land together. ## Choosing collation per column The connection-level `collation:` is a default for that session. A column can override it in a migration: ```ruby add_column :reservations, :confirmation_code, :string, collation: "utf8mb4_bin" ``` A confirmation code like `Ab3x` must not match `AB3X`, so a binary collation suits it, while guest emails usually want the case-insensitive default. `db/schema.rb` records the table's charset and collation, and any column collation, so a schema load recreates them. ## How the uniqueness validator follows collation Rails' `validates :email, uniqueness: true` builds its existence query differently depending on the option: - **no `case_sensitive:` option**: a plain comparison, so the column's collation decides and a `_ci` column treats `Ann` and `ann` as duplicates; - **`case_sensitive: true`**: the MySQL adapter wraps the value in `BINARY` when the column's collation is case-insensitive, forcing an exact match; - **`case_sensitive: false`**: a case-insensitive comparison. Keep the validator and the index in agreement. A validator that says "different" while the unique index says "same" turns a friendly form error into `RecordNotUnique` in production. ## Where else the two keys are used - **Creating databases.** `bin/rails db:create` passes `encoding:` as the database's `CHARACTER SET` and `collation:` as its `COLLATE`, so new development and test databases match production only if the configs agree. - **The schema dump.** `db/schema.rb` writes the table's `charset:` and `collation:` into each `create_table` line, so `db:schema:load` recreates them on a fresh database. - **Tests.** A test database created with different settings makes case-sensitivity bugs pass locally and fail in production; keep the `default` block shared. ## Checklist when moving from PostgreSQL - Set `encoding: utf8mb4` everywhere and check existing tables' charset. - Decide which columns need case-sensitive or binary collation (codes, tokens, slugs). - Re-run queries that relied on PostgreSQL's case-sensitive equality. - Review `uniqueness` validations against the indexes beside them.

  • Why can a Rails app on MySQL raise ActiveRecord::RecordNotUnique even though validates :email, uniqueness: { case_sensitive: true } passed?
    With `case_sensitive: true`, the MySQL adapter compares using `BINARY`, so `[email protected]` and `[email protected]` look different to the validator. The unique index uses the column's `_ci` collation, which treats them as the same value, so the INSERT fails. Make the validator and the index agree, usually by dropping the option.
  • When does a database.yml collation: setting have no effect in Rails 8.1 on MySQL?
    When no `encoding:` is configured: the adapter only adds `COLLATE` to a `SET NAMES <encoding>` clause, so a collation on its own is never sent. The generated config always sets `encoding: utf8mb4`, so in practice this bites hand-written configs.

  • Says MySQL's utf8 character set stores every Unicode character
  • Assumes MySQL string equality is case-sensitive like PostgreSQL's
  • Uses case_sensitive: true while the unique index uses a _ci collation
  • Thinks collation only affects ORDER BY, not equality or uniqueness
  • Gives a confirmation-code column the default case-insensitive collation
open as a page

A Rails 8.1 migration fails halfway on MySQL after working on PostgreSQL; why is the schema left half-changed, and how do you recover and prevent it?

level: seniorimportance: must knowfreq 45%

basics

~20 s

MySQL commits each DDL statement immediately, so Rails does not wrap MySQL migrations in a transaction. Steps before the failure stay applied while the version is never recorded; recover by finishing or undoing those steps by hand, and prevent it with one schema change per migration.

open as a page

In Rails 8.1, how do the mysql2 and trilogy adapters differ for a MySQL or MariaDB app, and what changes when you switch?

level: middleimportance: should knowfreq 35%

basics

~20 s

Both talk to MySQL and MariaDB through the same AbstractMysqlAdapter, so SQL, strict mode and schema behaviour match. mysql2 wraps the C libmysqlclient; trilogy is a self-contained client, so builds need no MySQL dev headers, and it refuses prepared_statements: true.

open as a page

In a Rails 8.1 MySQL database.yml, what do strict: and variables: sql_mode change, and what happens to a too-long string in each mode?

level: middleimportance: should knowfreq 35%

basics

~20 s

Rails defaults to strict: true, appending STRICT_ALL_TABLES to the session's sql_mode, so a string longer than its column raises ActiveRecord::ValueTooLong. strict: false strips strict modes and MySQL truncates silently; variables: sql_mode sets the mode verbatim instead.

open as a page

In Rails 8.1, why does upsert_all code ported from PostgreSQL break on MySQL, and which row does MySQL update without unique_by:?

level: seniorimportance: should knowfreq 25%

basics

~20 s

MySQL has no conflict target, so the MySQL adapters raise ArgumentError for unique_by: and generate ON DUPLICATE KEY UPDATE. That fires on a clash with the primary key or any unique index, so with two unique indexes MySQL may update a different row than intended.

open as a page