MySQL server has gone away

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I am using MariaDB version 10.x with binary logs

after the mysql server has been backup with innobackupex

1- i ran an insert into table… select … statement containing blobs . 19121 rows are generated.

2- shutdown the db , move it to another folder xmysql

3- restore the db with innobackup to a new mysql folder

4- run mysqlbinlog on the binary logs in xmysql (parameters used: --disable-log-bin) to a file mybinlog

5- run mysql --max_allowed_packet=2147483648 --binary-mode <mybinlog , try also inside mysql source mybinlog

6- i got MySQL server has gone away

i tried the same process after insert into table… select … limit 5 , all is working fine

it seems that the huge quantity of rows with blob causes the issue ,how i can solve this ?

Thanks for your help

2

Hi kpaskal;

Generally a “server gone away” during import error is either max_allowed_packet or wait_timeout related. I see that you are setting your max_allowed_packet for the mysql client to 2G (though the max is 1G, so it should default back to that), but what is your server value of max_allowed_packet set to? The import will be limited by whichever one of those is the lowest, so if your server is set to the default, then that is likely the issue. After that I would check the wait_timeout value on your server and increase that if it is set to something smaller than the default.

All that aside, the MySQL manual has a pretty good page on this issue that may help:
[url]https://dev.mysql.com/doc/refman/5.5/en/gone-away.html[/url]

-Scott

3

Hi Scott:

I met the " MySQL server has gone away" problem when I tried to apply the binlog to a test database in order to update its data. I had three binlog files and one of them is 2G and the others are 1G approximately. After I met the problem, I set the “max_allowed_packet” to 1G and the “wait_timeout” to 2880000, but I got the error still. Is there anything I can do to solve it? BTW, my database version is MySQL 5.6.25.

4

have you tried mysqltuner-perl?

mysqltuner.com