Fix Drupal MySQL Error: Got a Packet Bigger Than max_allowed_packet

Introduction

While this topic may applicable to all mysql/mariadb users who faced this issue. The exact error is:

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: 
"SQLSTATE[08S01]: Communication link failure: 1153 Got a packet bigger than 
'max_allowed_packet' bytes: INSERT INTO {key_value_expire} (name, collection, value, expire) VALUES

.
.
.

Drupal\\Core\\Database\\DatabaseExceptionWrapper: SQLSTATE[HY000]: 
General error: 2006 MySQL server has gone away: UPDATE {users_field_data} 
SET access=:db_update_placeholder_0\nWHERE uid = :db_condition_placeholder_0; Array\n(\n    
[:db_update_placeholder_0] => 1586010179\n    [:db_condition_placeholder_0] => 1\n)\n in 
core/modules/user/src/UserStorage.php on line 59 #0 core/lib/Drupal/Core/Database/Connection.php(659): 
Drupal\\Core\\Database\\Connection->handleQueryException(Object(PDOException),

When I got this error

This error comes generally when you are doing a heavy insert operation on db, and your db instance is not configired to handle such big chunk of data in one query.

In drupal when I added a field in a content type where a field is allowed multiple number of times. In drupal, it is configured with “Unlimited” occurrence. And on the website, when we click on “Add More” for that particular field. This error comes up. Note that on the web, the http status will be 500. You need to check your drupal logs at backend.

The Solution

You should first check this particular variable in your db configuration. Run following query:

SHOW VARIABLES LIKE 'max_allowed_packet'; 

The result is in bytes. The default value should be around 1MB. You should consider increasing it to at least 10 MB.

Open your mysql configuration file. In centos, its at

/etc/my.cnf

Edit the file, and under section: [mysqld]. See example below:

[mysqld]
datadir=/var/lib/mysql
.
.
.

max_allowed_packet=16M

In above example, it is set to 16MB in size. Note, there might be other sections in your config file like [mysqld_safe]. It should be changed under [mysqld] section.

After this change, you need to restart mysql server.

service mysqld restart

OR 

service mariadb restart

This will solve the problem.