Mysql : Aborted connection .../... (Got an error reading communication packets)

Question

This error message had submitted many times in this forum.

I have tried the following without success.

Context

This is a Symfony1/Doctrine1 web application. The error appear on my computer and on a serveur (both on Ubuntu). At the browser level (Chrome) I've got an error :

POST http://*****/administration_dev.php/utilisateur net::ERR_EMPTY_RESPONSE

Here's my computer details :

$ uname -a
Linux Bureau 4.4.0-31-generic #50-Ubuntu SMP Wed Jul 13 00:07:12 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux
$ cat /etc/lsb-release 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=16.04
DISTRIB_CODENAME=xenial
DISTRIB_DESCRIPTION="Ubuntu 16.04 LTS"
$ dpkg -l |grep php
ii  libapache2-mod-php                          1:7.0+35ubuntu6                                             all          server-side, HTML-embedded scripting language (Apache 2 module) (default)
ii  libapache2-mod-php7.0                       7.0.4-7ubuntu2.1                                            amd64        server-side, HTML-embedded scripting language (Apache 2 module)
ii  php                                         1:7.0+35ubuntu6                                             all          server-side, HTML-embedded scripting language (default)
.../...
$ dpkg -l |grep mysql-
ii  mysql-client-5.7                            5.7.13-0ubuntu0.16.04.2                                     amd64        MySQL database client binaries
ii  mysql-client-core-5.7                       5.7.13-0ubuntu0.16.04.2                                     amd64        MySQL database core client binaries
ii  mysql-common                                5.7.13-0ubuntu0.16.04.2                                     all          MySQL database common files, e.g. /etc/mysql/my.cnf
ii  mysql-server                                5.7.13-0ubuntu0.16.04.2                                     all          MySQL database server (metapackage depending on the latest version)
ii  mysql-server-5.7                            5.7.13-0ubuntu0.16.04.2                                     amd64        MySQL database server binaries and system database setup
ii  mysql-server-core-5.7                       5.7.13-0ubuntu0.16.04.2                                     amd64        MySQL database server binaries

The error appear when I try to create a new user during the save process.

Proposition tried

#1 MySQL Error Reading Communication Packets

$ mysql -uroot -p -e "SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;"
Enter password: 
$ mysql -uroot -p -e "SHOW VARIABLES LIKE 'max_allowed_packet';"
Enter password: 
+--------------------+------------+
| Variable_name      | Value      |
+--------------------+------------+
| max_allowed_packet | 1073741824 |
+--------------------+------------+
$ tail -f -n0 /var/log/mysql/error.log
2016-07-24T19:41:32.736817Z 23 [Note] Aborted connection 23 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:32.906433Z 24 [Note] Aborted connection 24 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:33.056226Z 25 [Note] Aborted connection 25 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:33.212496Z 26 [Note] Aborted connection 26 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:33.363754Z 27 [Note] Aborted connection 27 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:33.516194Z 28 [Note] Aborted connection 28 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:41:33.668201Z 29 [Note] Aborted connection 29 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)

#2 MySQL: Error reading communication packets

$ cat /etc/mysql/conf.d/david.cnf 
[mysqld]
sql-mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet = 256M
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M
$ mysql -uroot -p -e "SET GLOBAL innodb_fast_shutdown = 0;"
$ sudo service mysql stop
$ sudo mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
$ sudo mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
$ sudo service mysql start
$ tail -f -n0 /var/log/mysql/error.log
2016-07-24T19:56:02.342912Z 7 [Note] Aborted connection 7 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:02.494024Z 8 [Note] Aborted connection 8 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:02.642960Z 9 [Note] Aborted connection 9 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:02.787908Z 10 [Note] Aborted connection 10 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:02.933718Z 11 [Note] Aborted connection 11 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:03.084066Z 12 [Note] Aborted connection 12 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)
2016-07-24T19:56:03.235319Z 13 [Note] Aborted connection 13 to db: 'pfs2' user: 'pfs2' host: 'localhost' (Got an error reading communication packets)

I'm running out of ideas and suggestions so I'm calling for help here.

Thanks for your support, I will give you more information if you need.

David.

score 2 · Answer 1 · edited Jan 13 '22 at 11:12

You are running MySQL 5.7 with PHP on the one server. This shows me that your server is not dedicated to MySQL. PHP and MySQL are evidently competing for RAM.

I also do not see any attempt to set the InnoDB buffer pool size. The default value for innodb_buffer_pool_size is 128M (134217728).

Please keep in mind something about max_allowed_packet. As the variable name suggests, it is the maximum size allowed for a MySQL Packet. According the MySQL Documentation, the MySQL Packet does not start out as 1G. Its initial size is based on the value of net_buffer_length. The MySQL Packet will then dynamically expand to max_allowed_packet as needed.

Your attempts to tune around the MySQL Packet will be nullified by a lack of RAM.

SUGGESTION #1

Install PHP on another server that can be a dedicated web server.
Increase the amount of RAM on the DB Server

SUGGESTION #2

If you cannot afford another server, then just increase RAM and the Buffer Pool size.

SUGGESTION #3 (OPTIONAL)

Since you are using MySQL 5.7, you can expand the Buffer Pool dynamically.

Do the following to set it to 2GB:

mysql> SET GLOBAL innodb_buffer_pool_size = 1024 * 1024 * 1024 * 2;

score 1 · Accepted Answer · answered Aug 03 '16 at 02:00

The solution was in the code. It seems a PHP exception wasn't handle and the application crash before closing an opened sql transaction.

The mysql error message may take its source from this unconfortable state, I can't say more.

Rolando, thanks for your time.

Mysql : Aborted connection .../... (Got an error reading communication packets)

2 Answers2

SUGGESTION #1

SUGGESTION #2

SUGGESTION #3 (OPTIONAL)