Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Are all your tables InnoDB ? –  RolandoMySQLDBA Apr 25 '13 at 17:08
    
@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. –  Jhilke Dai Apr 25 '13 at 21:15
    
@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 –  Jhilke Dai Apr 30 '13 at 8:02

1 Answer 1

up vote 1 down vote accepted

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_file_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_buffer_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

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 !!!

share|improve this answer
    
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. –  Jhilke Dai Apr 26 '13 at 12:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.