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/

You might also like

Connect to MySQL over SSH with MySQL Workbench
If you don't have phpMyAdmin installed on your server or you don't feel comfortable using it there's...

MySQL can’t start after updating from 5.1 to 5.5
After updating a MySQL server from 5.1.73 to 5.5.4 it won't start. To see what was going on I checked...

Can’t change user options in MySQL Administrator
May be you installed MySQL 5 and you are not able to change user information, schema privileges and resources...

Can’t change user options in MySQL Administrator
May be you installed MySQL 5 and you are not able to change user information, schema privileges and resources...

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.