MySQL Import bulk data appears MySQL server has gone away workaround

Need to import a SQL to user library of around 200M due to work needs

Execute command

source /tmp/user.sql

A MySQL server has gone away error occurred during the import process and the data import failed.

The error message is as follows:

ERROR2006 (HY000): MySQLServer has gone awayERROR2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID: 11current database:usererror 2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID: 12current database:usererror 2006 (HY000): MySQL server has gone awayerror Span class= "Hljs-number" >2006 (HY000): MySQL server has gone awayno connection. Trying to reconnect ... Connection ID: 13current database:user        

Began to think it was a timeout, so the value of connect_timeout and wait_timeout was adjusted.

The problem remains after the re-execution.


Workaround:

View data, found the max_allowed_packet parameter,
The official explanation is that the appropriate increase in the max_allowed_packet parameter allows the client to transfer large data to the server side, allowing the system to allocate more extended memory for processing.

View the value of MySQL Max_allowed_packet

mysql> show global variables like ‘max_allowed_packet‘;+--------------------+---------+| Variable_name      | Value   |+--------------------+---------+| max_allowed_packet | 4194304 |+--------------------+---------+

You can see that it is 4Mand then turn it up to 256M(1024*1024*256)

mysql> set global max_allowed_packet=268435456;Query OK, 0 rows affected (0.00 sec)mysql> show global variables like ‘max_allowed_packet‘;+--------------------+-----------+| Variable_name      | Value     |+--------------------+-----------+| max_allowed_packet | 268435456 |+--------------------+-----------+1 row in set (0.00 sec)

After modifying, perform the import, everything is OK, solve the problem.


Note:
Use the set global command to modify the value of the Max_allowed_packet, which will expire after restarting MySQL and revert to the default value.

If you want to not restore after reboot, you can open the my.cnf file, add max_allowed_packet = 256M .

MySQL Import bulk data appears MySQL server has gone away workaround