MySQL Big Data Management

MySQL Big Data Management#

If you try to insert big data chunks to the MySQL server, you might run into the following error message:

Server has gone away

It may be caused by too small a value in the max_allowed_packet option in the MySQL server.

The issue is especially prevalent on older versions of the MariaDB server. The default value for the max_allowed_packet option differs depending on the MariaDB version:

  • 16777216 (16M) >= MariaDB 10.2.4

  • 4194304 (4M) >= MariaDB 10.1.7

  • 1048576 (1M) < MariaDB 10.1.7

Check the Current max_allowed_packet Value#

To check the current value for the max_allowed_packet use the following command:

echo "SHOW VARIABLES LIKE 'max_allowed_packet'" | mysql

The returned value specifies the maximum size of the package that the MySQL server can receive.

Variable_name   Value
max_allowed_packet  1048576

If the size of data sent to the MySQL server is bigger than the max_allowed_packet, then the server throws an error and closes the connection.

Increasing the New max_allowed_packet Value#

To increase the max_allowed_packet value, add the following lines to the MySQL server configuration file (the configuration file by default is located in the /etc/my.cnf path).

[mysqld]
max_allowed_packet=16M

Then restart MySQL server.

systemctl restart mysql
Contents