MySQL: Error reading communication packets

Question

I get this warning in mysql,

[Warning] Aborted connection 21 to db: 'MyDB' user: 'MyUser' host: 'localhost' (Got an error reading communication packets)

I have been through few topics in google and according to some suggestion I increased the max_allowed_packet from 128 to 512 to 1024 still the same behaviour.

I am using Drupal 7, and yes there are lots of blob data types, but 1024 Mb of max_allowed_packet should be enough in my opinion.

Any other workaround how to overcome this warning ?

EDIT:

Added some settings as @Rolando's suggestions/answer, I still get the same warning.

My mysql config looks like this:

[client]
port        = 3306
socket      = /tmp/mysql.sock
default-character-set = utf8

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16K 
max_allowed_packet = 1024M 
table_open_cache = 128 
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 192K
# Query cache disabled
thread_cache_size = 8
myisam-recover = BACKUP
max_connections = 100
thread_concurrency = 10
tmp_table_size = 128M
max_heap_table_size = 128M
log_error                = /var/log/mysql/mysql-error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 2

log_warnings = 2

server-id   = 1
binlog-format = row
replicate-same-server-id = 0
auto-increment-increment = 2
auto-increment-offset = 1
log_bin = mysql-bin
log-slave-updates
relay-log=mysqld-relay-bin
expire_logs_days        = 10
max_binlog_size         = 100M

innodb_data_home_dir = /var/db/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/db/mysql
innodb_buffer_pool_size = 8G
character-set-server = utf8
#innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 2047M
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
innodb_flush_method = O_DIRECT

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
default-character-set = utf8

[mysql]
default-character-set = utf8

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M

[mysqlhotcopy]
interactive-timeout

[mysqld_save]
syslog

My application uses only InnoDB, but there are few database like mysql, which came with the standard mysql installations are only the ones which uses MyISAM engine type, I guess that should not be my concern though.

As you can see I have replication too, the warning is the same one in replicated server too, whose config is identical as this.

Answer 1

I am glad you said all your data is InnoDB so I can answer as follows: If max_allowed_packet is maxed out at 1G and you are still having issues, there is really only two places to look:

1. innodb_log_buffer_size : The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.
2. innodb_log_file_size : The size in bytes of each log file in a log group. The combined size of log files must be less than 4GB. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

I addressed something like about 2 years ago

• Aug 01, 2011 : How does max_allowed_packet affect the backup and restore of a database?
• Apr 20, 2011 : MySQL gives "Out of Memory" error when inserting a large file. From where is this filesize limitation arising?

SUGGESTIONS

You need to increase the InnoDB transaction logs. Here are the steps to safely increase innodb_log_buffer_size and innodb_log_file_size:

Step 01 : Add these to /etc/my.cnf

[mysqld]
innodb_log_buffer_size = 32M
innodb_log_file_size = 2047M

Step 02 : Run this in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Step 03 : Shutdown mysql

service mysql stop

Step 04 : Move the old logs aside

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Step 05 : Start mysql

service mysql start

That's it.

The InnoDB Infrastructure should now have enough logging space for different size BLOBs.

Give it a Try !!!

Answer 2

After reading @user19292's comment in Jan '16 on this old question, I upgraded from 5.7.9 to 5.7.12 and the problem went away.

Answer 3

I just spent around 5-6 hours changing options and trying different versions of MySQL, I always got the error.

I think it is eider because:

• my PHP code is not properly closing the db connection (it is a warning, not an error), mysql_close() or equivalent.
• or because nginx cache/proxy server is configured to close the connection if client closed it, the cache/proxy server doesn't wait for the origin server (where mysql also is).