Processing and Analysis of server has gone away problems in Mass data Import of mysql

This article mainly introduces mysql mass data import server has gone away problem processing analysis, hope to give you some additional and updated knowledge, if there are other problems to understand, you can continue to pay attention to my update article in the industry information.

Due to work needs, you need to import a sql of about 200M into the user library.

execution command

mysql> use userDatabase changedmysql> source /tmp/user.sql

MySQL server has gone away error occurred during import, data import failed.

The error message is as follows:

ERROR 2006 (HY000): MySQL server has gone awayERROR 2006 (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 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect... Connection id: 13Current database: user

Initially thought it was a timeout, so increased connect_timeout and wait_timeout values.

The problem remains after re-execution.

Solution:

Check the profile, found max_allowed_packet parameter,

The official explanation is that increasing the max_allowed_packet parameter appropriately allows the system to allocate more extended memory to process large data from client to server.

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's 4M, and then it's 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 modification, import is executed, everything is normal, and the problem is solved.

Note:

Use the set global command to modify the max_allowed_packet value, which will expire after restarting mysql and revert to the default value.

If you want to restart without restoring, you can open my.cnf file and add max_allowed_packet = 256M.

This article explains how to solve MySQL server has gone away when importing large quantities of data from MySQL. Please pay attention to more related content.