【MySQL】max_allowed_packetエラーでSQLがインポートできないときの対処法（XAMPP）｜ブログ

はじめに 本記事では、XAMPP環境でMySQLにSQLファイルをインポートする手順と、 大容量データのインポート時によく発生する maxallowedpacket エラーの対処方法について解説します。 maxallowedpacket エラーは、大きなSQLファイルやBLOBデータを扱う際に発生しやすいエラーですが、設定ファイルの場所や反映方法など、意外と見落としがちなポイントがあります。 同じところで詰まらないための備忘録としてまとめました。 なお、本記事では XAMPP 環境を前提に解説しています。 設定ファイルの場所や再起動方法などは環境ごとに異なりますが、MySQLの基本的な操作コマンドや、SQLファイルをインポートする考え方自体は他の環境でも共通です。もし、他の環境で同様のエラーが出た方にも参考になれば幸いです。 先に結論 SQLをインポートするときは以下のコマンドを使う mysql -u root -p yourdatabasename < C:/path/to/your/file.sql ※yourdatabasenameは保存先のデータベース名 C:/path/to/your/file.sql はsqlファイルの保存先を指定 前提条件 XAMPP環境でMySQLを使用していること インポートするSQLファイルが手元にあること MySQLにデータをインポートする手順 ①XAMPPのShellを起動 XAMPPのコントロールパネルから Shell を起動します。 ②MySQLのコマンドラインクライアントを起動 mysql -u root -p -u：ユーザー名を指定 -p：パスワード入力を促すオプション ③パスワードを入力 パスワードを求められたら、MySQLのrootユーザーのパスワードを入力します。 （XAMPPの初期設定では空欄のまま Enter の場合もあります） Enter password: ④データベースを選択（または作成） 操作対象のデータベースを指定します。 USE yourdatabasename; ※yourdatabasenameを操作したいデータベース名を指定 データベースの一覧を確認したい場合は、以下を実行します。 SHOW DATABASES; もし対象のデータベースが存在しない場合は、作成します。 CREATE DATABASE yourdatabasename; ※yourdatabasenameに作成したいデータベース名を指定 ⑤SQLファイルをインポート 以下のコマンドでSQLファイルを実行します。 SOURCE C:/path/to/your/file.sql; 補足：コマンドラインから直接インポートする方法 大容量のSQLファイルをインポートする場合、 mysqlにログインして SOURCE コマンドを使う方法では、 maxallowedpacket エラーが発生することがあります。 その場合は、以下のように mysql にログインせず、 Shell（コマンドライン）から直接SQLファイルを読み込む方法の方が、 環境によっては安定してインポートできる場合があります。 mysql -u root -p yourdatabasename < C:/path/to/your/file.sql ※yourdatabasenameは保存先のデータベース名 C:/path/to/your/file.sql はsqlファイルの保存先を指定 なぜ < file.sql の方が安定する場合があるのか SOURCE と < file.sql の決定的な違い SOURCE の流れ（不安定になりやすい） SQLファイル ↓ mysqlクライアントがファイルを読み込む ↓ SQL文として解釈・組み立てる ↓ SQL文単位でMySQLサーバーに送信 SOURCE コマンドは mysql クライアントがSQLファイルの内容を読み込み、 セミコロン（;）単位で1つのSQL文として組み立てた上でMySQLサーバーへ送信します。 そのため、1つのSQL文が非常に大きい場合、送信時のデータサイズが maxallowedpacket を超え、エラーが発生することがあります。 < file.sql の流れ（比較的安定しやすいケース） SQLファイル ↓ OSのリダイレクト機能 ↓ mysqlクライアントの標準入力（stdin） ↓ MySQLサーバー < file.sql は、OSのリダイレクト機能によって SQLファイルの内容が 標準入力として mysql に渡されます。 この場合、mysql クライアントはファイルを直接操作するのではなく、 入力ストリームとして受け取ったSQLを順に処理します。 この処理方法の違いにより、SOURCE コマンドよりも maxallowedpacket エラーが発生しにくくなる場合があります。 ⑥インポート結果を確認 インポートが完了したら、テーブルが作成されているか確認します。 SHOW TABLES; ※ phpMyAdminなどのGUIツールを使って確認しても問題ありません。 maxallowedpacket エラーの対処方法 発生するエラー例 大きなSQLファイルをインポートした際、次のようなエラーが表示されることがあります。 ERROR 1153 (08S01): Got a packet bigger than 'maxallowedpacket' bytes 原因 maxallowedpacket は、MySQLが一度に送受信できるデータサイズの上限を示す設定です。 デフォルト値は比較的小さいため、 大きなBLOBデータ 大量のINSERT文を一度に実行するSQL などを扱うと、このエラーが発生します。 現在の設定値を確認 まずは現在の設定値を確認します。 SHOW VARIABLES LIKE 'maxallowedpacket'; 結果例： +--------------------+-----------+ | Variablename | Value | +--------------------+-----------+ | maxallowedpacket | 67108864 | // 64MB +--------------------+-----------+ 対処方法①：MySQL設定ファイルを変更する my.cnf または my.ini を編集して、maxallowedpacket の値を増やします。 ※ XAMPP環境では、一般的に「xampp/mysql/bin/my.ini」に存在します。 設定例： [mysqld] maxallowedpacket=256M datadir=c:/xampp/mysql/data [client] maxallowedpacket=256M [mysqld]：MySQLサーバー側の最大パケットサイズ [client]：mysqlコマンドなどクライアント側の最大パケットサイズ 設定変更後の確認 MySQLサーバーを必ず再起動した後、再度設定を確認します。 SHOW VARIABLES LIKE 'maxallowedpacket'; 結果例： +--------------------+------------+ | Variablename | Value | +--------------------+------------+ | maxallowedpacket | 268435456 | //256MB +--------------------+------------+ maxallowedpacketの変更が反映できたら、SQLファイルをインポートしてみましょう。 設定が反映されない場合のチェックポイント MySQLサーバーを再起動していない 編集した my.ini / my.cnf が読み込まれていない 設定ファイルが複数存在しており、別のファイルが優先されている 特にXAMPPでは my.iniが複数存在するケースがあるため、 maxallowedpacket の設定が重複していないか注意が必要です。 おわりに 私自身、maxallowedpacketの値を変更してもまったく反映されず、 何度も設定ファイルと再起動を繰り返して苦労しました。 最終的な原因は、別の設定ファイルが優先されて読み込まれていたことでした。 （my.ini が複数存在し、片方の設定が有効になっていました） XAMPP以外の環境でも考え方は同じで、 「どの設定ファイルが読み込まれているか」 を確認することが重要です。 同様のエラーで困っている方の参考になれば幸いです。