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.

• RolandoMySQLDBA: Are all your tables InnoDB ?
• user18530: @RolandoMySQLDBA, Hi, Yes all the tables are innodb, I did according to your answer in some other question like this in this website, but I still get the warning.
• user18530: @RolandoMySQLDBA, I have edited my question and did what you suggested, I am still getting that warning. I have mysql.cnf here, can you please have a look at it, I might be missing something
• user19292: I started to get this error on MySQL 5.5.35 with Drupal 6. I never understood the problem but it went away on upgrading to 5.7.7. Now it has returned with 5.7.9. I have isolated an insert query (less than 6000 characters of text) which succeeds on 5.7.7 but causes an abort on 5.7.9. It only fails when executed remotely, not locally. So, same client, both versions of server running side by side on the same machine, same sql_mode, same character set, huge max_allowed_packet. I'm foxed. Did you ever resolve 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 !!!

• user18530: Thank you very much for the response, I have edited my question to add mysql.cnf file. I did as you suggested, but still get the warnings. I can see max_allowed_packet in mysqldump is just 16Mb but I guess that is not the cause. key_buffer_size is just 16Kb and again it should be something with MyISAM and I am not using MyISAM storage engine in application.
• dgo: Also - for anyone who encounters this while working with an apache server, I also had to restart that service. Thank you very much for this - I was clueless.

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.

• Jesus Uzcanga: I'm using 5.7.23 and having the same issue
• Sliq: Same here, error exists with 5.7.26. In your case the upgrade probably also reset the configs, so this might have solved your problem.

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).