skip to content

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%

answer

  1. strict: defaults to true
  2. STRICT_ALL_TABLES appended to the session
  3. NO_AUTO_VALUE_ON_ZERO added too
  4. explicit sql_mode replaces Rails' choice
  5. ValueTooLong versus silent truncation

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.

solid answer

~40 s

The MySQL adapters build one `SET` statement per connection. With the default `strict: true`, it sets `@@SESSION.sql_mode` to the server's mode plus `STRICT_ALL_TABLES` and `NO_AUTO_VALUE_ON_ZERO`, so MySQL rejects bad values: a 300-character special request in a `varchar(255)` raises `ActiveRecord::ValueTooLong`, a missing NOT NULL value raises `NotNullViolation`. With `strict: false`, Rails removes `STRICT_TRANS_TABLES`, `STRICT_ALL_TABLES` and `TRADITIONAL`, and MySQL truncates or coerces the value with only a warning, so the row saves damaged. `strict: :default` leaves the server's mode untouched. Setting `variables: { sql_mode: "..." }` sends exactly that string and skips Rails' additions. Other `variables:` entries become `@@SESSION.<name>` settings, and `:default` resets one to the server default.

code

yaml · 10 lines
yaml
default: &default
  adapter: trilogy
  encoding: utf8mb4
  strict: true            # the default: adds STRICT_ALL_TABLES

legacy_import:
  <<: *default
  variables:
    sql_mode: TRADITIONAL  # sent verbatim; strict: is ignored
    lock_wait_timeout: 30

go deeper

for a junior

Recall that Rails turns on MySQL strict mode by default and that strict: false lets MySQL truncate data silently.

for a middle

Explain the SET statement Rails builds, what strict: true, false and :default each send, and how variables: overrides it.

for a senior

Keep strict mode on, add validations for user-facing errors, and audit servers whose global sql_mode differs.

for a principal

Weigh data integrity against loading legacy data during a database migration, and set a firm end date for any relaxation.

## What MySQL's SQL mode is MySQL decides how forgiving to be through the **`sql_mode`** variable. Without strict mode, MySQL "fixes" bad input instead of rejecting it: a string longer than its column is cut off, an out-of-range number is clamped, and the statement succeeds with a warning nobody reads. PostgreSQL has no such switch; it always rejects those values. For a hotel-reservation app moving from PostgreSQL, that difference decides whether bad data fails loudly or lands in the `reservations` table quietly. ## What Rails sends on every connection `AbstractMysqlAdapter#configure_connection` builds a single statement: ```sql SET NAMES utf8mb4, @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'), @@SESSION.wait_timeout = 2147483 ``` The pieces come from `database.yml`: | Setting | What the adapter does | |---|---| | `strict: true` (default) | appends `STRICT_ALL_TABLES`, then `NO_AUTO_VALUE_ON_ZERO` | | `strict: false` | removes `STRICT_TRANS_TABLES`, `STRICT_ALL_TABLES` and `TRADITIONAL`, then appends `NO_AUTO_VALUE_ON_ZERO` | | `strict: :default` | sends no `sql_mode` at all; the server's setting stands | | `variables: { sql_mode: "..." }` | sends that string verbatim and skips everything above | | other `variables:` keys | `@@SESSION.<key> = <value>`; the value `:default` sends `DEFAULT` | | `wait_timeout:` | defaults to 2147483 seconds so idle pooled connections are not dropped | `NO_AUTO_VALUE_ON_ZERO` makes an explicit `0` in an auto-increment column store zero instead of generating the next id, which keeps fixture and import ids predictable. ## The too-long string, both ways The `special_requests` column is `varchar(255)` and a guest pastes 300 characters: 1. **Strict (default).** MySQL rejects the `INSERT` with error 1406, and the adapter translates it into `ActiveRecord::ValueTooLong`. The request fails; a model validation (`length: { maximum: 255 }`) would have turned it into a form error instead. 2. **`strict: false`.** MySQL stores the first 255 characters and raises a warning. `save` returns `true`, and the guest's request is silently cut off. The same split applies to other values the adapter maps: an out-of-range number becomes `ActiveRecord::RangeError` in strict mode and is clamped without it, and a `NOT NULL` column left out of the `INSERT` with no default becomes `ActiveRecord::NotNullViolation` in strict mode and gets an implicit default without it. ## Setting sql_mode by hand Teams sometimes need a specific mode, for instance to match a legacy server: ```yaml production: <<: *default variables: sql_mode: TRADITIONAL lock_wait_timeout: 30 ``` Because an explicit `sql_mode` replaces Rails' calculation, `strict:` is ignored and `NO_AUTO_VALUE_ON_ZERO` is not added. Write the whole mode you want, and test it: a mode without any strict flag reintroduces silent truncation. ## How to inspect the live mode You can confirm what a running app actually got from `bin/rails console`: ```ruby ApplicationRecord.with_connection do |c| c.select_value("SELECT @@SESSION.sql_mode") end ``` Compare it with the server's global value (`SELECT @@GLOBAL.sql_mode`). If the session lacks a strict flag, look for `strict: false`, `strict: :default` or an explicit `variables: sql_mode` in the environment's config, including any `DATABASE_URL` query parameters merged into it. ## Practical guidance - **Keep the default.** Strict mode is what makes MySQL fail like PostgreSQL did before the move. - **Validate in the model as well.** Database errors are a safety net; `length:` and `presence:` validations give users a form message instead of a 500. - **Treat `strict: false` as a migration aid only.** If legacy data cannot be loaded strictly, fix the data or the column, then switch strict mode back on. - **Check every environment.** A server administrator can change the global `sql_mode`; with `strict: :default` that change silently becomes your application's behaviour.

  • Why does adding validates :special_requests, length: { maximum: 255 } still matter when MySQL strict mode already rejects long strings?
    Strict mode turns the bad value into `ActiveRecord::ValueTooLong` raised from `save`, which is an exception and usually a 500 page. The validation runs first and adds a message to `errors`, so the form re-renders. The database check stays as the backstop for code paths that skip validations.
  • What does strict: :default do differently from strict: true in a Rails 8.1 MySQL config?
    With `:default`, the adapter sends no `sql_mode` assignment at all, so the session keeps whatever the server's global mode is, and `NO_AUTO_VALUE_ON_ZERO` is not added. With `true`, Rails appends `STRICT_ALL_TABLES` and `NO_AUTO_VALUE_ON_ZERO` to that mode for every connection.

  • Believes Rails leaves MySQL's sql_mode untouched by default
  • Sets strict: false to make imports pass and keeps it in production
  • Expects strict: true to still apply after setting variables: sql_mode
  • Thinks strict mode makes model length validations unnecessary
  • Assumes a truncated save returns false