先日携わっているプロジェクトにおいてとある不具合に遭遇しました。DBから取得したデータが一部欠損していたのです。調査したところデータを取得する際のクエリにおいてGROUP_CONCATが使用されており、そちらの上限をオーバーした事が原因でした。今回は直接Laravelと関係する訳ではありませんが、LAMP環境を運用する上で遭遇するかもしれない事象という事で備忘録としてまとめます。
GROUP_CONCAT()
GROUP_CONCATはmysqlの関数です。GROUP BYでまとめたデータをカンマ区切りなどで取得する際に使います。例えば、以下のようなfoodテーブルがあったとします。
# テーブル作成クエリ
CREATE TABLE `food` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL DEFAULT '',
`category` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
そして、そのテーブルに以下のようなデータが挿入されていたとします。
# データ挿入クエリ
INSERT INTO `food` (name, category) VALUES
('みかん', '果物'),
('とまと', '野菜'),
('玉ねぎ', '野菜'),
('きゅうり', '野菜'),
('りんご', '果物'),
('ぶどう', '果物');
GROUP_CONCATを使うと、カテゴリ毎に食べ物の名前をカンマ区切りでまとめて取得できます。以下のように。
SELECT category, GROUP_CONCAT(name) FROM food GROUP BY category; >> +----------+----------------------+ | category | GROUP_CONCAT(name) | +----------+----------------------+ | 果物 | みかん,りんご,ぶどう | | 野菜 | とまと,玉ねぎ,きゅうり | +----------+----------------------+ 2 rows in set (0.02 sec)
カンマ区切りの文字列なので、explode()などで配列に変換すれば使い勝手が良いですね。
GROUP_CONCATの上限
既にお気づきかと思いますがGROUP BYでまとめたデータが多ければ多いほどGROUP_CONCATの結果も長くなります。前項の例で言えば、例えばカテゴリーが果物のレコードが1000件あったなら、1000件分の果物の名前をカンマ区切りで結合して文字列として取得する訳です。とは言え、そんな長い文字列を取得する訳にも行かないので、mysql側でgroup_concat_max_lenというシステム変数によって取得できる最大の長さが制限されています。group_concat_max_lenのデフォルトの設定値は1024バイトです。
SHOW VARIABLES LIKE 'group_concat_max_len'; >> +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | group_concat_max_len | 1024 | +----------------------+-------+ 1 row in set (0.04 sec)
この制限値を超えた場合、超えた部分のデータは切り捨てられます。group_concat_max_lenの設定値を変えて、実際にこの挙動を確認してみましょう。
まず、GROUP_CONCATの上限を20バイトに設定してみます。
SET group_concat_max_len = 20;
そして、前項で実行したクエリをもう一度実行してみましょう。
SELECT category, GROUP_CONCAT(name) FROM food GROUP BY category; >> +----------+----------------------+ | category | GROUP_CONCAT(name) | +----------+----------------------+ | 果物 | みかん,りんご, | | 野菜 | とまと,玉ねぎ, | +----------+----------------------+ 2 rows in set, 2 warnings (0.00 sec)
果物はぶどうが、野菜はきゅうりのデータが欠損しています。そしてwarningが発生しています。
SHOW WARNINGS; >> +---------+------+---------------------------------+ | Level | Code | Message | +---------+------+---------------------------------+ | Warning | 1260 | Row 3 was cut by GROUP_CONCAT() | | Warning | 1260 | Row 6 was cut by GROUP_CONCAT() | +---------+------+---------------------------------+ 2 rows in set (0.00 sec)
3行目と６行目のデータがGROUP_CONCATによって省かれた旨の警告でした。ここでいう3行目、６行目とはGROUP BYで処理される前の結果セットであると推察します。
因みに文字列のバイト数を確認するにはLENGTH関数が使えます。
SELECT LENGTH('みかん,りんご,');
>>
+--------------------------------+
| LENGTH('みかん,りんご,') |
+--------------------------------+
| 20 |
+--------------------------------+
1 row in set (0.00 sec)
先ほど途中でカットされたデータのバイト数を確認すると丁度group_concat_max_lenに設定した値である20バイトとなっていますね。内訳は平仮名が１文字３バイト*6文字、カンマが１バイト*2文字で合計20バイトという訳です。この様にgroup_concat_max_lenで設定したバイト数以上の文字列を取得しようとした場合、制限を超えた分の文字列が欠損して取得されてしまうのです。そして、これがまさに今回私が遭遇した不具合の原因です。
GROUP_CONCATの設定を変更する
GROUP_CONCATの返却値の長さが決まっているなら、group_concat_max_lenを余裕を持たせた充分な値に設定する事で取得するデータの欠損を予防できます。アプリ内の特定の箇所にのみ設定値の引き上げが必要な場合はDB::statement()で一時的に設定すれば良さそうです。
// デフォルトの1024 byteから２倍に引き上げ
DB::statement('SET group_concat_max_len = 2048;')
あるいはアプリ全体で上限を引き上げたいのであればmysqlの設定ファイル、my.cnfに設定を追加してあげればOKです。
[mysqld] group_concat_max_len=2048
設定を反映させるにはmysqlの再起動が必要なのでお忘れなく。
まとめ
原因が分かるとなんて事ない不具合ですが、直接的なエラーが発生しない為気付くのに少し時間が掛かりました。経験上GROUP_CONCATを使う頻度は多くありませんが、こういう事もあるということを頭の片隅においておきたいものです。