Bug #15220 "MySQL server has gone away" on INSERT INTO
Submitted: 24 Nov 2005 11:31 Modified: 24 Nov 2005 12:55
Reporter: Frank Osterberg Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.14 & 5.0.15 OS:Windows (Win)
Assigned to: CPU Architecture:Any

[24 Nov 2005 11:31] Frank Osterberg
Description:
I try to execute a simple but large INSERT INTO sql statement either per mysql prompt table < file, per odbc or per query browser and it always returns:

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

I tried this with the stable base installation of 4.1.14 and 5.0.15.
The sql insertion script as uncompressed ascii (utf8) file is between 2MB and 3MB in size. (you can download it per link below)

Before i reported I have read:
http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
and as a result set the max_allowed_packet variable to 16MB:
-----------------------------------
mysql> set @@max_allowed_packet := (16*1024*1024);
Query OK, 0 rows affected (0.00 sec)
-----------------------------------
however inserting still fails with the same error.

i tried to set the log level when starting mysql command prompt with: 
-----------------------------------
mysql -uroot -p --log-warnings=2 test < Insert.sql
mysql: unknown variable 'log-warnings=2'
-----------------------------------
so that doesn't work for me somehow..

executing fewer statements works, but i would really like to be able to execute all the statement at once, anyhow so thats a workaround i guess (a sucky one though!)

Well the Table create scrip insert statement in the: "How to repeat:" section

How to repeat:
Here is the table create script, execute this to create the table that the INSERT INTO script will try to insert into :)

CREATE TABLE `mittelwert` (
  `MittelwertID` INT UNSIGNED NOT NULL PRIMARY KEY auto_increment,
  `CalculatedOn` TIMESTAMP NOT NULL,
  `_StartZelltestID` INT UNSIGNED NOT NULL,
  `Duration` INT UNSIGNED NOT NULL,
  `Count` INT UNSIGNED NOT NULL default 0,
  `UsedCount` INT UNSIGNED NOT NULL default 0,
  `AverageDateTime` DATETIME default NULL, 
  `Eta` double default NULL,
  `EtaStd` double default NULL,
  `EtaMin` double default NULL,
  `EtaMax` double default NULL,
  `Uoc` double default NULL,
  `UocStd` double default NULL,
  `UocMin` double default NULL,
  `UocMax` double default NULL,
  `Jsc` double default NULL,
  `JscStd` double default NULL,
  `JscMin` double default NULL,
  `JscMax` double default NULL,
  `FF` double default NULL,
  `FFStd` double default NULL,
  `FFMin` double default NULL,
  `FFMax` double default NULL,
  `FFCount` INT UNSIGNED default NULL,
  `Rs` double default NULL,
  `RsStd` double default NULL,
  `RsMin` double default NULL,
  `RsMax` double default NULL,
  `Rsh` double default NULL,  
  `RshStd` double default NULL,
  `RshMin` double default NULL,
  `RshMax` double default NULL,
  `JRev1` double default NULL,
  `JRev1Std` double default NULL,
  `JRev1Min` double default NULL,
  `JRev1Max` double default NULL,    
  `JRev2` double default NULL,
  `JRev2Std` double default NULL,
  `JRev2Min` double default NULL,
  `JRev2Max` double default NULL,  
  `Insol` double default NULL,
  `InsolStd` double default NULL,
  `InsolMin` double default NULL,
  `InsolMax` double default NULL,
  `Tcell` double default NULL,
  `TcellStd` double default NULL,  
  `TcellMin` double default NULL,
  `TcellMax` double default NULL,  
  `Umpp` double default NULL,
  `UmppStd` double default NULL,
  `UmppMin` double default NULL,
  `UmppMax` double default NULL,
  `Impp` double default NULL,  
  `ImppStd` double default NULL,
  `ImppMin` double default NULL,
  `ImppMax` double default NULL,
  `Ivld1` double default NULL,
  `Ivld1Std` double default NULL,
  `Ivld1Min` double default NULL,
  `Ivld1Max` double default NULL,
  `Uvld1` double default NULL,
  `Uvld1Std` double default NULL,
  `Uvld1Min` double default NULL,
  `Uvld1Max` double default NULL,
  `IRevmax` double default NULL,
  `IRevmaxStd` double default NULL,
  `IRevmaxMin` double default NULL,
  `IRevmaxMax` double default NULL,
  `IRevmaxCount` INT UNSIGNED default NULL,  
  UNIQUE (`_StartZelltestID`,`Duration`,`Count`,`UsedCount`),
  KEY `AverageDateTime` (`AverageDateTime`)
) TYPE=InnoDB;

now the link to the file containing the INSERT INTO sql statement:
http://raven.is-a-geek.net/allowview/Insert.sql

executing the insert into script on should cause the above error (does at least for me, in both 4.1 & 5.0)

Suggested fix:
No idea, if i knew how to fix it then i probably would't be posting it as a bug
[24 Nov 2005 12:55] Frank Osterberg
The problem was that setting the max_allowed_packet from the mysql command prompt didn't work as i thought. I tried to set it using:
set @@max_allowed_packet := (16*1024*1024);
and when i then show variables like 'max%'; it showed 16777216,
but the script still failed, when i then connected with another client and executed select @@ax_allowed_client; it showed 1MB again..

not sure how this all works, but it just it in the my.ini under mysqld as follows:
max_allowed_packet = 16777216;
then it seems to work so far... whohoo :)