CSVデータをmysqlにインポートできない

Question

mysqlにcsvデータをインポートできずに困っています。

mysql for excelを使い、10000行ほどあるデータをインポートとしようとした結果、パケットの量が許可されてる量よりも多い（エラー１１５３）が出たので、自分で調べたところ、C:\ProgramData\MySQL\MySQL Server 8.0のmy.iniファイルを書き替えました

max_allowed_packet=4M　の4mを40mに書き換え、サービスでmysqlを停止して、開始しようとしたところ、サービスは起動して停止しました、と出たため、使えなくなりました。

原因究明のため、バックアップでとってたmy.iniファイルに置き換えるとサービスは開始できました。
少しでもmy.iniをいじるとサービスが起動しなくなります。

サービスに自分でmysqlを登録しなくてはいけないとあったため、いったん削除して、また登録しました。

コマンドプロンプトで

C:\ProgramData\MySQL\MySQL Server 8.0>mysqld --remove MySQL80

で消して、

C:\ProgramData\MySQL\MySQL Server 8.0>sc create Mysql binPath= "C:\Program Files\MySQL\MySQL Server 8.0\bin\mysql.exe"

でサービスを作成したところ、エラー1053サービスは開始要求に制限時間内に応答しませんでした、と出ました

お手上げです。
経理志望で会社に入ったのになぜ管理科にいるのだろう...

mysql80
windows10pro
x64

そのほか、抜けている情報もあると思いますのでご連絡ください。
よろしくお願いします。

Answer 1

max_allowed_packet=4M　の4mを40mに書き換え、サービスでmysqlを停止して、開始しようとしたところ、サービスは起動して停止しました、と出たため、使えなくなりました。

であれば、max_allowed_packet=4Mは書き換えずに、

mysql for excelを使い、10000行ほどあるデータをインポート

を先頭100行くらいにして、インポートできるか試してください。

Comments

[masaking]

100行でインポートを試しました。
できました。
あとは、同じ作業を１００回だけ繰り返せばよいでしょうか？

[Orlofsky]

残りの全行をimportしては？

[masaking]

エラー1153がやはり出たので無理でした

[Orlofsky]

max_allowed_packet=4M
を8M or 16MB で試す。
データを全体の半分 or 四分の一 と刻んで import てみてる。

エラーメッセージは要約しないでそのままコピペする習慣を身に着けてください。https://teratail.com/help/question-tips#questionTips3-4-2

[nandymak]

コメントしたつもりでしたが、書き込まれていませんでした。

1行のcsvのサイズ(Byte数)はどれくらいですか？
現状1回あたり4MBを超えるとエラーのようなので、とりあえずデータを入れるには

４MB÷1行のByte数で概算の件数を割り出して、その件数にcsvを分割して入れれば入るはずですよね。
100回は大変なので、なるべく少なくなるけど、制限までに少し余裕があるくらいがいいですよね。
分割したファイルの取り込みが漏れたりダブらないように注意してください。

それとは別に、4Mはどこまで増やせることができるかは調べたほうがよいでしょう。
Qiitaとか見ていると8M/16Mあたりは大丈夫のようです。
PCのメモリはもっと搭載していると思うので、ほかのパラメータとの絡みで40Mはダメなのか、
my.iniの修正ミスかもしれません。

[masaking]

コメントありがとうございます。
とりあえずデータを入れるために、https://okwave.jp/qa/q5343047.htmlを参考に、csvファイルをエクセルで開き、
=SUM(LENB(A2:AG2))
と関数を打ったところ、＃value!になりました。
A2からAG2までの列にデータが入っています。
どうすれば行あたりのバイト数を計算できるでしょうか？

[nandymak]

Lenbするならカンマで区切らずにＡ列に放り込んでB列に式を入れてください。
それで１行あたりのMaxとAVGを取って4Mbを超えないような件数に分割してください。

[masaking]

すいません、各セルごとのバイト数をLENB関数で出し、その結果のセルAH2からBO2に対して合計し、それを300行下におろした後average関数を使うことにより、行当たり平均1236バイトだということがわかりました。検索した結果、４MBは、4000000バイトと等しいということがわかったので、だいたい余裕を見て3000行ほど一括で登録できると計算できました。

また、実際にやってみた結果登録することができました。
3000行あたりなら4回同じ作業をするだけなので、まだ何とかなりそうです。

[nandymak]

csvファイルをExcelに取り込んだらカンマが見えなくなるので、項目ごとのLenbを取るとカンマの分だけ少なく見えますので、若干注意が必要です。