Got an error reading communication packets

2018-04-27T15:30:46.946313+08:00 417211 [Note] Aborted connection 417211 to db: 'dynetmanage30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.409704+08:00 417787 [Note] Aborted connection 417787 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.409714+08:00 417788 [Note] Aborted connection 417788 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.409719+08:00 417789 [Note] Aborted connection 417789 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.410232+08:00 417782 [Note] Aborted connection 417782 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.410245+08:00 417779 [Note] Aborted connection 417779 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

2018-04-27T19:25:53.410255+08:00 417781 [Note] Aborted connection 417781 to db: 'dyppne30' user: 'root' host: '192.168.100.27' (Got an error reading communication packets)

解决方法

1、参数max_allowed_packet

更改 max_allowed_packet大小

max_allowed_packet指server接受数据包大小

最小1k，最大1G

过小的话，单个数据过大，数据库连接会报错，而且接下来的连接都会报错

此参数默认4MB

更改大些

You must increase this value if you are using large BLOB columns or long strings. It should be as big as the largest BLOB you want to use. The protocol limit for max_allowed_packet is 1GB. The value should be a multiple of 1024; nonmultiples are rounded down to the nearest multiple.

2、参数innodb_log_buffer_size

更改参数

innodb_log_buffer_size

Default Value (>= 5.7.6)    16777216      (16MB)

Default Value (<= 5.7.5)    8388608       (8MB)

Minimum Value (>= 5.7.6)    1048576    (1M)

Minimum Value (<= 5.7.5)    262144   (256KB)

Maximum Value    4294967295     (4GB)

The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value changed from 8MB to 16MB with the introduction of 32k and 64k innodb_page_size values. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For related information, see InnoDB Memory Configuration, and Section 8.5.4, “Optimizing InnoDB Redo Logging”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

 

注意：. innodb_log_buffer_sizeshould be set to at least 16M (the default) when using 32k or 64k page sizes.

 

简单说下innodb_page_size

Specifies the page size for all InnoDB tablespaces in a MySQL instance. You can specify page size using the values 64k, 32k, 16k (the default), 8k, or 4k. Alternatively, you can specify page size in bytes (65536, 32768, 16384, 8192, 4096).

innodb_page_size can only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 14.6.1, “InnoDB Startup Configuration”.

3、参数innodb_log_file_size

innodb_log_file_size

50331648

Minimum Value (>= 5.7.11)    4194304    (4MB)

Minimum Value (<= 5.7.10)    1048576    (1MB)

Maximum Value    512GB / innodb_log_files_in_group

The minimum innodb_log_file_size value was increased from 1MB to 4MB in MySQL 5.7.11.