0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

MySQL 5.7→8.4移行でCSVの列順が静かに変わる — 並び番号の重複を検出・振り直し・検証する

0
Posted at

環境情報

項目 バージョン
移行元 MySQL 5.7.44
移行先 MySQL 8.4.x
アプリケーション EC-CUBE 4.3
PHP 8.2 / PDO MySQL
該当テーブル dtb_csv(CSV出力定義)
発生箇所 商品CSV / 受注CSV / 会員CSV の出力

MySQL 5.7から8.4へバージョンを上げただけで、アプリケーションコードは1行も変えていないのにCSVの列順が入れ替わる現象に遭遇しました。データは正しい、件数も一致している、なのに列の並びだけが実行ごとに変わる。移行検証でありがちな「件数一致だけを見る」チェックでは絶対に検出できない種類の破壊です。

この記事では、原因の特定から検出・振り直し・ロールバック付きの検証までを、実際に使ったSQLとPHPのコード付きで残します。

何が起きたのか

EC-CUBE 4.3のCSV出力は、dtb_csv テーブルの行1件が「CSVの1列」に対応します。各行は sort_no(並び番号)を持ち、出力時はこの番号の昇順で列が並びます。つまり 列順の根拠は sort_no ただ一つです。

ところが、拡張プラグインが項目を追加する際、既存の最大値+1を取らずに固定値(あるいは既存と同じ値)を sort_no に設定していました。結果として csv_type(CSVの種類)ごとに sort_no が重複した状態になっていました。

-- 重複の例: csv_type = 'product' の中で sort_no = 30 が2件
SELECT csv_id, csv_type, field_name, sort_no
FROM dtb_csv
WHERE csv_type = 'product'
ORDER BY sort_no, csv_id;
+--------+-----------+------------------+---------+
| csv_id | csv_type  | field_name       | sort_no |
+--------+-----------+------------------+---------+
|    101 | product   | product_code     |      10 |
|    102 | product   | product_name     |      20 |
|    103 | product   | price01          |      30 |
|    138 | product   | stock_unlimited  |      30 |  ← プラグイン追加分
|    104 | product   | category_id      |      40 |
+--------+-----------+------------------+---------+

5.7では表面化しなかった理由

MySQL 5.7では、ORDER BY sort_no のように同値が存在する並び替えにおいて、同値グループ内の順序は仕様上保証されていません。しかし実際にはテーブルスキャン順(多くの場合主キー順)で返ることが多く、結果として「登録した順」に見えていました。アプリケーション側もそれを前提に動いていたわけです。

MySQL 8.0以降はオプティマイザとソートアルゴリズムが変更され、同値グループ内の順序は実行計画・統計情報・ソートバッファのサイズなどで変動します。結果として、ある実行では逆順、ある実行では正順という、再現性すら安定しない状態になりました。

観点 MySQL 5.7 MySQL 8.4
同値 sort_no の順序 仕様上は不定(実質PK順になりがち) 仕様上も不定、実行計画依存で変動
再現性 ほぼ安定して「登録順」 実行ごとに変わることすらある
原因の見え方 問題が表面化しない 断続的に列順が壊れる
検証での検出 件数一致だけでは不可能 件数一致だけでは不可能

なぜ「静かに」壊れるのか

CSVを取り込む側のシステムは、ヘッダ行を見ずに列の位置で項目を判断していることが多いからです。列順が変われば、price の位置に stock が来ていても、取込側は「3列目は価格」として処理を続けます。エラーにならず、値だけがずれた状態で在庫や受注が登録される。これが最も厄介な点です。

対処は3段階

対処方針はシンプルに3段階に分けました。

  1. CSVの種類ごとに、sort_no の重複を検出する
  2. 重複があった種類だけ、(sort_no, csv_id) の順で1から振り直す
  3. 1トランザクション内で振り直し前後の有効な列順を比較し、違えばロールバック。2回目は何もしない冪等な作りにする

手順1: 重複の検出

まずは「どのCSV種類が壊れているか」を洗い出します。ここで重要なのは、有効な列(enabled = 1)だけを対象にすることです。無効な列が同値を持っていても出力には影響しませんが、後で有効化した瞬間に壊れます。検出は有効・無効の両方を見ておくのが安全です。

-- CSV種類ごとに sort_no が重複しているものを検出
SELECT csv_type, sort_no, COUNT(*) AS dup_count,
       GROUP_CONCAT(csv_id ORDER BY csv_id) AS csv_ids
FROM dtb_csv
GROUP BY csv_type, sort_no
HAVING COUNT(*) > 1
ORDER BY csv_type, sort_no;

手順2: 重複があった種類だけ振り直す

振り直しは ROW_NUMBER() で (sort_no, csv_id) の順に1から振るだけです。ただし同一テーブルをサブクエリで参照しながらUPDATEすると ERROR 1093 になり得るため、一時テーブルを挟みます。これは5.7時代の検証環境でも同じSQLで動かせるので、移行前後で手順を共通化できるメリットもあります。

-- 対象の csv_type だけを一時テーブルに退避して採番
CREATE TEMPORARY TABLE tmp_csv_sort AS
SELECT csv_id,
       ROW_NUMBER() OVER (PARTITION BY csv_type ORDER BY sort_no, csv_id) AS new_sort_no
FROM dtb_csv
WHERE csv_type IN ('product', 'order', 'customer');  -- 重複が見つかった種類のみ

-- 一時テーブル経由で更新(ERROR 1093 回避)
UPDATE dtb_csv c
JOIN tmp_csv_sort t ON t.csv_id = c.csv_id
SET c.sort_no = t.new_sort_no;

DROP TEMPORARY TABLE tmp_csv_sort;

ORDER BY sort_no, csv_id の第2キーに csv_id を必ず入れるのがポイントです。ここを省略すると「重複を解消するための採番」がまた不定順序になり、同じ事故を再生産します。

手順3: トランザクション内で前後比較+冪等化

振り直しは「壊れた状態を直す」操作なので、適用前に必ず前後の列順を比較して、意図しない変化があればロールバックします。さらにマイグレーションスクリプトは何度実行されるか分からないので、冪等にします。

<?php
// migrate_csv_sort_no.php
$pdo->beginTransaction();

try {
    $types = $pdo->query(
        "SELECT csv_type FROM dtb_csv
         GROUP BY csv_type, sort_no HAVING COUNT(*) > 1"
    )->fetchAll(PDO::FETCH_COLUMN);

    if (!$types) {
        // 重複なし = 何もしない(冪等)
        $pdo->commit();
        exit(0);
    }

    foreach ($types as $type) {
        // 振り直し前の「有効な列順」をハッシュ化して保持
        $before = fetchOrderHash($pdo, $type);

        $stmt = $pdo->prepare(
            "UPDATE dtb_csv c
             JOIN (
               SELECT csv_id,
                      ROW_NUMBER() OVER (PARTITION BY csv_type
                                         ORDER BY sort_no, csv_id) AS new_sort_no
               FROM dtb_csv WHERE csv_type = :t
             ) t ON t.csv_id = c.csv_id
             SET c.sort_no = t.new_sort_no"
        );
        $stmt->execute([':t' => $type]);

        $after = fetchOrderHash($pdo, $type);

        // field_name の並びが変わったら即ロールバック
        if ($before !== $after) {
            throw new RuntimeException("列順が変化: {$type}");
        }
    }

    $pdo->commit();
} catch (Throwable $e) {
    $pdo->rollBack();
    throw $e;
}

function fetchOrderHash(PDO $pdo, string $type): string
{
    $stmt = $pdo->prepare(
        "SELECT field_name FROM dtb_csv
         WHERE csv_type = :t AND enabled = 1
         ORDER BY sort_no, csv_id"
    );
    $stmt->execute([':t' => $type]);
    return hash('sha256', implode(',', $stmt->fetchAll(PDO::FETCH_COLUMN)));
}

ここでのミソは、比較対象を sort_no そのものではなく field_name の並びにしている点です。番号の振り直しは番号が変わることを目的にしているので、番号を比較しては意味がありません。「利用者から見た列の並びが変わっていないか」を検証する必要があります。

移行前チェックリストの見直し

今回の件を受けて、バージョンアップ前の確認項目の先頭に「並び順の根拠が一意か」を置きました。データが正しくても、順序の保証が変われば壊れる箇所があるからです。

優先 チェック項目 確認方法 理由
1 並び順の根拠が一意か GROUP BY ... HAVING COUNT(*) > 1 順序保証の変化で最初に壊れる
2 件数の一致 移行前後の COUNT(*) 比較 欠損・重複の検出
3 チェックサム一致 CHECKSUM TABLE / 全列ハッシュ 値の破壊検出
4 実行計画の変化 EXPLAIN の前後比較 性能劣化の予兆
5 文字照合順序 utf8mb4_0900_ai_ci への変更影響 検索結果の差分

「件数の一致検証と並び順の一意性、どちらを先に確認するか」と問われたら、私は並び順の一意性を先にします。件数は合っていても順序が壊れていれば、取込側で値がずれたまま正常終了してしまうからです。件数不一致はまだ気づけますが、列ずれは気づけません。

ハマりポイント

  • ORDER BY にタイブレークキーを入れ忘れる。ORDER BY sort_no だけの箇所は全て ORDER BY sort_no, csv_id に直す。アプリ側のSQLも同様。
  • ONLY_FULL_GROUP_BY。5.7からデフォルト有効ですが、8.4でも有効です。SELECT csv_type, field_name ... GROUP BY csv_type のような雑なクエリが移行後にエラーになります。
  • ERROR 1093。同一テーブルをサブクエリで参照するUPDATEは避け、一時テーブルか派生テーブルを使う。
  • 認証プラグインの変更。mysql_native_password から caching_sha2_password へ変わり、古いPDO/ドライバだと接続できないことがあります。
  • 照合順序のデフォルト変更。utf8mb4_0900_ai_ci になり、ORDER BY name の結果が5.7と変わる場合があります。これも「静かに変わる」系です。

FAQ

Q. 5.7で今まで動いていたのに、なぜ8.4で急に壊れるのですか?
A. 同値グループ内の順序は5.7でも仕様上は不定です。たまたま主キー順で返っていただけで、8.0以降のオプティマイザ変更でその「偶然」が成立しなくなった、というのが正確な理解です。バグではなく、未定義動作への依存が露呈した形です。

Q. sort_no にUNIQUE制約を付ければ再発しませんか?
A. 再発は防げますが、既存データの重複を先に解消する必要があります。また (csv_type, sort_no) の複合UNIQUEにしないと意味がありません。中長期的には制約追加をおすすめします。

Q. 振り直しで列順が変わってしまうことはありませんか?
A. (sort_no, csv_id) で採番するため、重複していた場合のみ順序が確定します。手順3のハッシュ比較で field_name の並びが変わった場合はロールバックするので、安全側に倒せます。

Q. 本番適用前に何を確認すべきですか?
A. ステージングで同じSQLを流し、出力CSVのヘッダ行を移行前後でdiffしてください。列数と列名の並びが完全一致していれば合格です。

まとめ

  • 列順の根拠が sort_no のように単一カラムで重複し得る設計は、MySQLのバージョンアップで静かに壊れる
  • 対策は「検出 → 重複分だけ振り直し → トランザクション内で並びを比較してロールバック」の3段階
  • マイグレーションスクリプトは冪等に。比較は番号ではなく利用者から見た列の並びで行う
  • 移行前チェックは「並び順の根拠が一意か」を件数一致より先に確認する

データが正しいことと、順序が保証されていることは別問題です。バージョンアップで後者が変わる可能性を、チェックリストの先頭に置いておくのが安全です。


この記事を書いた人

BENTEN Web Works — 業務自動化・システム開発のフリーランスエンジニアです。

GAS / Python / RPA を使った業務自動化や、Web制作・システム開発のご相談を承っています。
「こんなこと自動化できる?」というご質問だけでもお気軽にどうぞ。

👉 BENTEN Web Works — 詳細・お問い合わせはこちら
🐦 X(旧Twitter) — 日々の知見を発信中

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?