0

we created a script that converts CSV Data to SQL. Today i wanted to import a sql file created from csv via the shell. As result i became the error message

ERROR 2006 (HY000) at line 6: MySQL server has gone away

The filesize is arround 15 Megabyte, so i was wondering because i imported Databases with more than 11 Gigabyte before without any trouble. So we startet to check the SQL File again and again and couldnt find any issues with it. So i thought maybe there is a limitation for the lengh of the query (currently we use max_allowed_packet = 160M).160 MB should be enough. I began to split the SQL file to half and then to a quarter, suddenly it works.

So i hope somebody could tell me what is wrong with our query and how to avoid this crash, it seems there is something resulting in a kind of buffer overflow.

This is a short example of the sql file http://pastebin.com/ZViw69SM The full sql got 175416 lines.

Deex
  • 317
  • 2
  • 13
  • http://stackoverflow.com/questions/10474922/error-2006-hy000-mysql-server-has-gone-away maybe helpful. – cFreed Jan 18 '16 at 02:17
  • Thanks but we are already on 160MB, maybe the query is yust bad itself. I mean i will try to raise it to astronomic sizes but that looks somekind the wrong direction, i would need about 1 GB to import 15MB. – Deex Jan 18 '16 at 02:21
  • Okay testet it with 3GB and 6GB - the same error. – Deex Jan 18 '16 at 02:28
  • DIdn't read your post with sufficient attention! Since you say that you already successfully ran larger files the issue shouldn't be there. So what about refactoring your query to multiple `INSERT`ing, i.e. write `INSERT INTO `huge` (`Unknown0`,`Unknown1`,`Unknown2`) VALUES ('xxx','yyy','zzz');` for _each_ record? It might either suddenly work (because I think that bufers never hold more than one "query action" at a time) or merely stop at the place where an issue occurs: then you can look at the resulting `huge` to locate it. – cFreed Jan 18 '16 at 02:36
  • This would be an idea, i will try to seek for a way to split it after some length and tell you if this is working. – Deex Jan 18 '16 at 03:55

1 Answers1

0

Okay we fixed it with Multiple inserts with a Maxmium of 1000 Lines. Thanks alot CFreed.

Deex
  • 317
  • 2
  • 13