MySQLでビッグデータインポートのエラーおよび対応

mysql
development
mysql

MySQLでのデータインポート時にエラーが発生

  • 環境：Windows10、    XAMPP Version: 5.6.15、
  • ローカル環境（Windows10）のあるMySQLにphpmysqladminでデータをインポートする際に、以下のエラーが発生 
    Static analysis:
1 errors were found during analysis.

Unrecognized keyword. (near "ON" at position 25)

SQL query:  編集 
SET FOREIGN_KEY_CHECKS = ON; 
MySQL のメッセージ:  
#2006 - MySQL server has gone away

  • なぜDBサーバーが停止（MySQL server has gone away）か、どこで何が悪いかよくわからない

問題解明：BLOBタイプのフィールドに大きなデータインポート

  • インポートされたテーブル（batch）を確認してみたら、１レコードがインポート済み
  • インポート元のテーブル（batch）に3レコードがあります
  • インポート元のレコードが見てみたら、BLOBタイプ、３．１ＭＢのデータもあります。これを見ると、大きなデータをインポートしようとするときに、何かのデータを読み込む制限を超えたようです
  • my.iniファイルで以下の設定を変更しました
    • max_allowed_packet = 1M  ➡  max_allowed_packet = 16M
    • sort_buffer_size = 512K  ➡  sort_buffer_size = 2M
    • read_buffer_size = 256K ➡ read_buffer_size = 2M
    • read_rnd_buffer_size = 512K   ➡ 　 read_rnd_buffer_size = 2M
  • MySQLサーバーを再起動して再インポートを行いました。テーブルbatchが正常にインポートされました。
  • ただし、インポートが進むと、別のエラーが発生 
    MySQL のメッセージ:  
#1118 - The size of BLOB/TEXT data inserted in one transaction is greater than 10% of redo log size. Increase the redo log size using innodb_log_file_size.

  • エラーは「innodb_log_file_size」の設定にあるようです。以下の設定を行いました。
    • innodb_log_file_size = 5M ➡ innodb_log_file_size = 25M
  • その後正常にインポートが完了しました