sql_mode がグローバルとセッションの 2 段で決まり、Laravel がセッション側を上書きしていることを示す図

【Laravel】MySQL の厳格モードは config で外せる

今回は共用サーバの Laravel が MySQL の厳格モードに引っかかった話です。sql_mode は接続ごとに上書きできるので、my.cnf を触れなくても config 側で指定できます。設定の 3 パターンを解説します!

sql_mode がグローバルとセッションの 2 段で決まり、Laravel がセッション側を上書きしていることを示す図

こんにちは。ダタク @iQeeeda です。

借りていた共用サーバの Laravel が MySQL の厳格モードに引っかかりました。
最初は MySQL のバージョンや設定ファイル (my.cnf) で何とかしようとしていたんです。

ところが、その設定ファイルには手が出せません。
SQL を大量に書き直すしかないと思っていました。

結論: 厳格モードは Laravel の config で外せる

先に結論です。

  • 厳格モードの設定値 sql_mode はサーバ全体と接続ごとの 2 段で決まる
  • 接続ごとの値 (セッション) は、アプリ側からいつでも書き換えられる
  • Laravel は config/database.php の設定で接続時に sql_mode を上書きする
  • 一括で緩めるなら strict を false、個別に選ぶなら modes で指定する
  • 厳格モードを外すと、止まっていたクエリも弾かれていた値もそのまま通るようになる

今回は「サーバ設定を触らずに MySQL の厳格モードを外す方法」という話をします。
それでは解説していきます。

MySQL の sql_mode はグローバルとセッションで決まる

グローバルの sql_mode とセッションの sql_mode が別々に決まることを示す図

たとえば ONLY_FULL_GROUP_BY というモードに引っかかると、 MySQL 8.4 の公式リファレンス に載っているようなエラーが返ります。

mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP
BY clause and contains nonaggregated column 'mydb.t.address' which
is not functionally dependent on columns in GROUP BY clause;
this is incompatible with sql_mode=only_full_group_by

厳格モードの正体は、この sql_mode というシステム変数です。
MySQL 8.4 の公式リファレンス によると、sql_mode には次の 2 つのスコープ (適用範囲) があり、それぞれ別の値を持てます。

  • グローバル (サーバ全体): 管理者がサーバ全体の既定として決める
  • セッション (接続ごと): つないでいるアプリが自分の接続の分だけ決める

起動時のグローバル値を決めるのは --sql-mode オプションか my.cnf などの設定ファイルです。
僕が my.cnf で何とかしようとしていたのは、Laravel の config から変えられると知らなかったからでした。

借りていた共用サーバで触れなかったのはグローバル側

動いているサーバのグローバル値を変えるには SYSTEM_VARIABLES_ADMIN 権限 (MariaDB では SUPER 権限) が要ります。
一方で、起動時の値を決める my.cnf も当時の環境では編集できませんでした。

ところがセッション側について、公式リファレンスにはこうあります。

Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

要するに、セッション側の変更は今つないでいる接続にだけ効くので、my.cnf を触れなくてもアプリ側からいつでも書き換えられるということです。

効いている sql_mode を確かめる

グローバルとセッションの値は 1 つの SQL で並べて確認できます。

SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode;

ただし @@SESSION.sql_mode に出るのは、その SQL を実行したクライアント自身の値です。
Laravel の接続で何が効いているかを見るには、CLI ではなくアプリ側から同じ SQL を流します。

use Illuminate\Support\Facades\DB;

$modes = DB::select('SELECT @@GLOBAL.sql_mode, @@SESSION.sql_mode');
dd($modes);

Laravel は接続のたびに sql_mode を上書きしている

Laravel が接続直後に sql_mode を送ってサーバの既定を上書きする流れの図

Laravel を使っているなら、厳格モードを決めているのはサーバ設定とは限りません。

現行の Laravel (13.x) で初期状態のままの config/database.php には、mysql 接続に 'strict' => true が入っています。
この 1 行を受けて、 MySqlConnector は接続を開いた直後に SET ... SESSION sql_mode='...'; を発行します。

文字コードの設定もある場合は、NAMES の指定と 1 文にまとまって実行されます。

SET NAMES '[文字コード]' COLLATE '[照合順序]', SESSION sql_mode='...';

strict が true のときに適用される 6 つのモード

この記事で「厳格モード」と呼ぶのは、Laravel が既定で有効にしている次の 6 つです。

  • ONLY_FULL_GROUP_BY
  • STRICT_TRANS_TABLES
  • NO_ZERO_IN_DATE
  • NO_ZERO_DATE
  • ERROR_FOR_DIVISION_BY_ZERO
  • NO_ENGINE_SUBSTITUTION

上の 6 つは、後述する modes を指定しておらず接続先が MySQL 8.0.11 以上のときに適用される値です。
MySqlConnector はそれより古いバージョンにつなぐときだけ NO_AUTO_CREATE_USER を足した 7 つをセットします。

なお config/database.php には mysql 接続とは別に mariadb 接続もあり、こちらも既定は 'strict' => true です。
MariaDbConnector にはサーバのバージョンによる分岐がなく、strict が true なら常に上の 6 つが適用されます。

config/database.php で sql_mode を指定する 3 つの方法

strict を false にする・modes で並べる・どちらも書かない、の 3 つの設定方法を並べた図

設定のパターンは 3 つあります。
1 と 2 は mysql 接続でも mariadb 接続でも同じ値が適用され、サーバのバージョンで変わりません。3 はサーバの既定がそのまま効くので、DB の種類やバージョンで結果が変わります。

書き換えるのはアプリが使っている接続です。.env の DB_CONNECTION (未設定なら config/database.php の default) の値を確認してください。

1. strict を false にして厳格モードを外す

strict を false にすると、適用される値は NO_ENGINE_SUBSTITUTION の 1 つだけになります。
つまり NO_ENGINE_SUBSTITUTION 以外のチェック (MySQL 8.0.11 以上なら 6 つのうち 5 つ) がまとめて外れると覚えておけば OK です。

config/database.php

'mysql' => [
    // ...
    // 厳格モードを外す
    'strict' => false,
],

2. modes で残すモードを並べる

modes を指定すると strict より優先され、配列に並べたモードがそのままカンマ区切りで適用されます。
ONLY_FULL_GROUP_BY だけ外したいときはこちらを使えば OK です。

config/database.php

'mysql' => [
    // ...
    // ONLY_FULL_GROUP_BY だけ落として、あとは残す
    'modes' => [
        'STRICT_TRANS_TABLES',
        'NO_ZERO_IN_DATE',
        'NO_ZERO_DATE',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ENGINE_SUBSTITUTION',
    ],
],

3. strict も modes も書かずにサーバの既定に任せる

どちらのキーも無いと Laravel は sql_mode に触りません。
サーバの既定がそのまま効くので、アプリ側で値を決めたくないときの選択肢になります。

ただしサーバの既定に ONLY_FULL_GROUP_BY が入っていれば、これを選んでもエラーは消えません。

書き換えたら config:cache を作り直す

Laravel は 設定をひとつのファイルにまとめてキャッシュできます 。
本番のデプロイ手順に config:cache を入れているなら、書き換えたあとに実行し直します。
なお、ローカル開発では設定を頻繁に変えるため使わないよう公式ドキュメントで注意されています。

php artisan config:cache

厳格モードを外すと通ってしまうクエリとデータ

厳格モードを外すと、拒否されていたクエリが通り、エラーで弾かれていた値も保存されるようになることを示す図

書き換えるのは config の数行ですが、外れるのはエラーだけではありません。
それまで防いでいたクエリやデータも通るようになるため、次の 4 つの役割だけは先に押さえておくのが安心です。

  • ONLY_FULL_GROUP_BY: GROUP BY に無く、その列から一意にも決まらない集計対象外のカラムをエラーにする
  • STRICT_TRANS_TABLES: トランザクション対応テーブルで指定どおりに入らない値があるとき、文をエラーで止める
  • NO_ZERO_DATE: 0000-00-00 のようなゼロ日付を警告・エラーの対象にする
  • NO_ZERO_IN_DATE: 2010-00-01 のように月や日だけゼロの日付を警告・エラーの対象にする

'strict' => false で日付の 2 つごと外すと、0000-00-00 や月日だけゼロの日付も警告なしでそのまま保存されます。
エラーで弾かれるのは STRICT_TRANS_TABLES や STRICT_ALL_TABLES のような strict 系モードと一緒に効いているときだけです。

一方で modes で日付の 2 つだけ残して strict 系モードを外すと、エラーにならず警告付きで通ります。
このとき NO_ZERO_DATE は 0000-00-00 をそのまま許可し、NO_ZERO_IN_DATE は 2010-00-01 のような日付を 0000-00-00 に置き換えて保存します。

また ONLY_FULL_GROUP_BY を外すと、GROUP BY に含まれないカラムを SELECT したクエリもそのまま通ります。
サーバがグループの中から任意の 1 値を選ぶため、返る結果が一定しなくなります。

※ ONLY_FULL_GROUP_BY を外さずに済ませる手もあります。対象のカラムを ANY_VALUE() 関数で囲めば、モードを外さずに同じ結果を得られることも公式リファレンスで紹介されています。

公式リファレンスの strict mode とは指す範囲が違う

MySQL の公式リファレンスが「strict mode」と呼ぶのは、STRICT_TRANS_TABLES か STRICT_ALL_TABLES のどちらか (または両方) が有効な状態のことです。
Laravel の 'strict' => true が適用する 6 つのモードは、そこに ONLY_FULL_GROUP_BY などを加えた広い範囲を指しています。

名前が似ているので、公式リファレンスを調べるときは Laravel の strict と混同しないよう注意してください。

MySQL と MariaDB で既定の sql_mode が違う

MySQL 8.4 の既定 6 つに ONLY_FULL_GROUP_BY があり、MariaDB の既定 4 つには無いことを示す対比図

サーバの既定に任せると、同じ SQL が環境によって通ったり落ちたりします。

MySQL 8.4 の既定には ONLY_FULL_GROUP_BY が入っています。Laravel が適用する 6 つと同じ顔ぶれです。
MariaDB の 10.2.4 以降の既定は STRICT_TRANS_TABLES を含む 4 つで、ONLY_FULL_GROUP_BY は入っていません。

※ modes (または 'strict' => false) を config で指定しておけば、サーバの既定やバージョンが環境ごとに違っても、Laravel からつないだセッションの値は揃います。

環境ごとに DB が違っても config 側で揃えておけば安心

当時、僕も本番は エックスサーバー 、ステージングは シンレンタルサーバー を使っていて、データベースのバージョンが違うのはまずいなと感じていました。
環境ごとに既定値やバージョンが違うと、同じ SQL でも挙動がズレるおそれがあります。

だからこそサーバ任せにせず、Laravel 側の設定で揃えておくのが確実です。
SQL を全部書き直す前に、まずは config/database.php を開いてみてください。

触れなかったのはサーバ側の設定だけ。接続側の設定なら、自分の手で変えられた。

関連記事

まだコメントはありません