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 issue:
(500, '(pymysql.err.OperationalError) (2006, "MySQL server has gone away (BrokenPipeError(32, \'Broken pipe\'))")\n(Background on this error at: http://sqlalche.me/e/e3q8)')
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
Then restart MySQL server.
systemctl restart mysql