The solution to the problem that MySQL server has gone away when mysql imports large batches of data

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

Execute the command

mysql> use user
Database changed
mysql> source /tmp/user.sql

appears during the import processMySQL server has gone awayError, data import failed.

The error message is as follows:

ERROR 2006 (HY000): MySQL server has gone awayERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    11Current database: userERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    12Current database: userERROR 2006 (HY000): MySQL server has gone awayERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    13Current database: user

At first I thought it was caused by a timeout, so I increased the values ​​of connect_timeout and wait_timeout.

The problem remains after re-executing.
Solution:

Check the information and found the max_allowed_packet parameter,
The official explanation is to increase it appropriately The max_allowed_packet parameter allows the system to allocate more extended memory for processing when transferring large data from the client to the server.

Check 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 4M, and then increase it 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 modification, execute the import, everything is normal, and the problem is solved.
Note:
Use the set global command to modify the value of max_allowed_packet. It will become invalid after restarting mysql and will be restored to the default value.

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

This article explains the solution to the problem of MySQL server has gone away when mysql imports large batches of data. For more related content, please pay attention to the PHP Chinese website.

Related recommendations:

mysql Search examples of data within N kilometers nearby

mysql method to automatically reconnect when connection is interrupted

php A class for converting HTML entity numbers and non-ASCII strings

The above is the detailed content of The solution to the problem that MySQL server has gone away when mysql imports large batches of data. For more information, please follow other related articles on the PHP Chinese website!