MYSQL GROUP_CONCATでまとめると内容が途中で途切れる現象に対処。

2023年9月26日

以前MYSQLで、複数データを1回で読む方法についての記事を書きました。

MYSQL １対複数のLEFT JOINを1行で表示できるようにする。

これはこれで使い勝手が良いのですが、今次のような現象が起こりました。

GROUP_CONTACTでまとめたものが途中で途切れている

どういうことかというと、今回100件ほどのデータをGROUP_CONTACTでまとめて読もうとしたところ、最初の10件ほどしか格納されていませんでした。

しかも、内容が途中で途切れていました。

最初は原因がよくわからず、ググってググって調べまくり、ようやく原因を突き止め対処することができました。

忘備録として記録いたします。

テスト環境では現象が発生せず、実機サーバーでのみ現象が発生。

今回、実機サーバーで動かしてから現象が発生しました。
なぜならば、テスト環境では現象が発生しなかったからです。

PHPソースは同じため、プログラムのバグとは考えにくい状況でした。
では、何が原因なのか？

とにかくググって、ググって調べまくりました。

原因はgroup_contactの上限値設定

ようやくヒントとなるような記事を見つけました。
それは、group_contactには1回で格納できる上限値があるということでした。

早速実機サーバーのphpmyadminで、上限値を調べてみました。
上限値は以下のSQL文で確認できます。

実機の上限値は約1Kでした。
それに対し、テスト環境の上限値は約1MBでした。

だからテスト環境では現象が異なっていました。
正確には読み取った結果が1MB以内であったため、現象が起こっていませんでした。

何とかするためには、この上限値を大きくする必要があります。

MYSQLの初期設定を変更すればよいのだけれど簡単ではない

MYSQLにはmy.cnfという名前の初期設定ファイルがあります。
このファイルの中にある「group_concat_max_len」の値を変更すればそれでOKです。

ただ、レンタルサーバーの場合、my.cnfは変更できないようになっています。
ではどうすればよいのでしょうか？

一時的に設定を記憶しているエリアを書き換えます。

MYSQLは、設定値を一時エリアに記憶しています。
一時エリアであれば、プログラムレベルから変更できます。

毎回書き換える必要がありますが、プログラム上に記載しておけば面倒ではありません。
次のSQL文を実行してやれば、group_concat_max_lenの値を一時的に変更できます。

疑問１：set session とは？

通常MYSQLの設定値を変更する場合は「set grobal」と表現します。
set grobalの場合は、my.cnfを共有しているユーザー全てに影響がでます。

つまりレンタルサーバーの場合は、他の利用者にも影響が発生します。
そのため、grobal変数の変更は禁止されている場合がほとんどです。

それに対しset sessionとは、1回のセッション時（起動している間のみ）変更する指定です。
1回こっきりで、次回起動時には初期値に戻ります。
他の利用者にも影響は出ません。

セッション値の変更であれば、プログラムレベルから値が変更できます。

疑問２：設定値 4294967295 の根拠は？

group_concat_max_lenの値は大きいに越したことはありませんが、限界があります。
ましてやレンタルサーバーの場合、誤動作の原因にもなりかねません。

そこでここまでなら大丈夫であろうと思われる値として32ビットの上限値を設定しました。

32ビットは16進数で0xFFFFFFFF と表記します。
これを10進数で表現すると、4294967295になります。

最近のサーバーは標準で64ビットであるため、32ビットの上限値であればサーバーの限界値を超えることはありません。

GROUP_CONCATの上限値が気になる場合は、別の方法を検討することも大事

GROUP_CONTACTには格納できる上限サイズがあることが今回わかりました。
つまり、一度に読める件数に限界があるということです。

データは日々増加するものです。
いつか上限を超えてしまうような設計であれば、見直しが必要です。

例えば一度に読み込むデータの上限数を決めておき、複数回にわたって読み込むなどです。

今回はデータの中身の集計作業を行うプログラムを組んでいたところ、1件ずつ読み込んでいたら集計時間が分単位かかってしまいました。

あまりにも遅すぎたため、GROUP_CONTACTで同グループデータをまとめて読むことで高速化していました。

今回の現象はGROUP_CONTACTを使用しなければそもそも発生しません。
もし速度に問題がないのであれば、無理に使用しないほうが無難のようにも感じました。

また本記事がお役にたてば幸いです。