Tag Archives: gone away

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

I once had a project with one MySQL table that had over 2 million rows. First time I tried moving that project from one computer to another I had the error “ERROR 2006 (HY000) at line ###: MySQL server has gone away” come out while restoring the backup. After a LOT of research on the internet my coworker and I found out that the problem had 2 origins:

  • Some long queries exceded the max_allowed_packet size
  • mysqldump was creating extremelly long insert sentences for that huge table

Solving the firts issue is easy: just increase the max_allowed_packet value in my.cnf of the destination server:

[mysqld]
max_allowed_packet = 16M

To solve the second issue we used the --skip-extended-insert option of mysqldump. The drawbacks of this option are that the dumps are much larger and that restoring those backups takes more time. You can see in this post of High Availability MySQL blog some benchmarks done with and without --skip-extended-insert and how a normal dump for 16M rows would take around 170 seconds whereas this workaround would take around 1,100 seconds. Almost 7 times more.

Remember that --opt is used by default in mysqldump and it stands for:

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

That’s the reason we use --skip-extended-insert to override the default --extended-insert behaviour that leads to a myslqdump command more or less like this one:

$ mysqldump --add-drop-table --skip-extended-insert --skip-quick -u user_name -p database_name | gzip > database_name_`date +%Y%m%d_%H%M%S`.sql.gz

mysql

Ref: http://dba.stackexchange.com/questions/5292/mysql-server-has-gone-away-obstructing-import-of-large-dumps
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
http://coding-journal.com/error-2006-hy000-at-line-mysql-server-has-gone-away/