Aborted connection to db got an error writing communication packets

since I use my new dev server, I get this message at event log:

Aborted connection 5 to db: ‘database_name’ user: ‘user_name’ host: ‘localhost’ (Got an error writing communication packets)For more information, see Help and Support Center at http://www.mysql.com.

and this:

Aborted connection 12 to db: ‘database_name’ user: ‘user_name’ host: ‘localhost’ (Got an error reading communication packets)For more information, see Help and Support Center at http://www.mysql.com.

I’ve already search for solution, but all recommendations has not work. I’m using an api to sync data between iPad and MySQL server. Same api code works on live server very well, but on my dev server doesn’t. I’ve try to use the my.ini from live server, but I still to get this error. The error comes approximal between 95 and 113 seconds after sync did start.

Here are my my.ini settings:

[client]
port=3306
[mysql]
no-beep
default-character-set=utf8
[mysqld]
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 5.7Data
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
log-output=FILE
general-log=0
general_log_file="WIN2K16.log"
slow-query-log=1
slow_query_log_file="WIN2K16-slow.log"
long_query_time=10
log-error="WIN2K16.err"
server-id=1
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"
max_connections=250
query_cache_size=0
table_open_cache=2000
tmp_table_size=669M
thread_cache_size=10
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=2G
key_buffer_size=8M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=16M
innodb_buffer_pool_size=5G
innodb_log_file_size=48M
innodb_thread_concurrency=17
innodb_autoextend_increment=64
innodb_buffer_pool_instances=8
innodb_concurrency_tickets=5000
innodb_old_blocks_time=1000
innodb_open_files=300
innodb_stats_on_metadata=0
innodb_file_per_table=1
innodb_checksum_algorithm=0
back_log=80
flush_time=0
join_buffer_size=256K
max_allowed_packet=256M
max_connect_errors=100
open_files_limit=4161
query_cache_type=0
sort_buffer_size=256K
table_definition_cache=1400
binlog_row_event_max_size=8K
sync_master_info=10000
sync_relay_log=10000
sync_relay_log_info=10000
net_read_timeout = 60
max_execution_time = 3000

I hope some one can help me…

Thanks.

One of the silent killers of MySQL Connections is the MySQL Packet.

First, let’s figure out what a MySQL Packet is.

According to the page 99 of «Understanding MySQL Internals» (ISBN 0-596-00957-7), here are paragraphs 1-3 explaining MySQL Packets:

MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory—a cap
on the size of the packet, which this
option accomplishes.

The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().

This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.

Knowing this about MySQL Packets allows a Developer/DBA to size them up to accommodate multiple BLOBs inside one packet even if they are obnoxiously large. Definitely, a packet too small will cause problems for open connections in this respect.

According to the MySQL Documentation

• You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server’s max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.

• An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

To set the max_allowed_packet to 256M, you can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=256M

to cover future restarts of mysqld. To install the value now on the server, please run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 256;

Give it a Try !!!

The error log contains a record of critical errors that occurred during the server’s operation, table corruption, start and stop information.

SQL errors can also be logged in a separate file using the SQL_ERROR_LOG plugin.

Configuring the Error Log Output Destination

MariaDB always writes its error log, but the destination is configurable.

Writing the Error Log to a File

To configure the error log to be written to a file, you can set the log_error system variable. You can configure a specific file name. However, if a specific file name is not configured, then the log will be written to the ${hostname}.err file in the datadir directory by default.

The log_error system variable can be set in a server option group in an option file prior to starting up the server. For example, to write the error log to the default ${hostname}.err file, you could configure the following:

[mariadb]
...
log_error

If you configure a specific file name as the log_error system variable, and if it is not an absolute path, then it will be relative to the datadir directory. For example, if you configured the following, then the error log would be written to mariadb.err in the datadir directory:

[mariadb]
...
log_error=mariadb.err

If it is a relative path, then the log_error is relative to the datadir directory.

However, the log_error system variable can also be an absolute path. For example:

[mariadb]
...
log_error=/var/log/mysql/mariadb.err

Another way to configure the error log file name is to set the log-basename option, which configures MariaDB to use a common prefix for all log files (e.g. general query log, slow query log, error log, binary logs, etc.). The error log file name will be built by adding a .err extension to this prefix. For example, if you configured the following, then the error log would still be written to mariadb.err in the datadir directory:

[mariadb]
...
log-basename=mariadb
log_error

The log-basename cannot be an absolute path. The log file name is relative to the datadir directory.

Writing the Error Log to Stderr on Unix

On Unix, if the log_error system variable is not set, then errors are written to stderr, which usually means that the log messages are output to the terminal that started mysqld.

If the log_error system variable was set in an option file or on the command-line, then it can still be unset by specifying --skip-log-error.

Writing the Error Log to Syslog on Unix

On Unix, the error log can also be redirected to the syslog. How this is done depends on how you start MariaDB.

Syslog with mysqld_safe

If you start MariaDB with mysqld_safe, then the error log can be redirected to the syslog. See mysqld_safe: Configuring MariaDB to Write the Error Log to Syslog for more information.

Syslog with Systemd

If you start MariaDB with systemd, then the error log can also be redirected to the syslog. See Systemd: Configuring MariaDB to Write the Error Log to Syslog for more information.

systemd also has its own logging system called the journal, and some errors may get logged there instead. See Systemd:Systemd Journal for more information.

Writing the Error Log to Console on Windows

On Windows, if the console option is specified, and if the log_error system variable is not used, then errors are written to the console. If both options are specified, then the last option takes precedence.

Writing the Error Log to the Windows Event Viewer

On Windows, error log messages are also written to the Windows Event Viewer. You can find MariaDB’s error log messages by browsing Windows Logs, and then selecting Application or Application Log, depending on the Windows version.

In MariaDB 10.3 and before, you can find MariaDB’s error log messages by searching for the Source MySQL.

In MariaDB 10.4 and later, you can find MariaDB’s error log messages by searching for the Source MariaDB.

Configuring the Error Log Verbosity

The default value of the log_warnings system variable is 2.

The log_warnings system variable can be used to configure the verbosity of the error log. It can be changed dynamically with SET GLOBAL. For example:

SET GLOBAL log_warnings=3;

It can also be set either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
log_warnings=3

Some of the warnings included in each verbosity level are described below.

The log_warnings system variable only has an effect on some log messages. Some log messages are always written to the error log, regardless of the error log verbosity. For example, most warnings from the InnoDB storage engine are not affected by log_warnings. For a complete list of log messages affected by log_warnings, see the description of the log_warnings system variable.

Verbosity Level 0

If log_warnings is 0, then many optional warnings will not be logged. However, this does not prevent all warnings from being logged, because there are certain core warnings that will always be written to the error log. For example:

• If InnoDB strict mode is disabled, and if DDL is performed on a table that triggers a «Row size too large» error, then InnoDB will log a warning:

[Warning] InnoDB: Cannot add field col25 in table db1.tab because after 
  adding it, the row size is 8477 which is greater than maximum allowed 
  size (8126) for a record on index leaf page.

However, if InnoDB strict mode is enabled, then the same message will be logged as an error.

Verbosity Level 1

Default until MariaDB 10.2.3. If log_warnings is 1, then many types of warnings are logged. Some useful warnings are:

• Replication-related messages:

[Note] Error reading relay log event: slave SQL thread was killed
[Note] Slave SQL thread exiting, replication stopped in log 
  'dbserver-2-bin.000033'   at position 181420; 
  GTID position '0-263316466-368886'
[Note] Slave I/O thread exiting, read up to log 
  'dbserver-2-bin.000034', position 642; 
  GTID position 0-263316466-368887

• Messages related to DNS lookup failures:

[Warning] IP address '192.168.1.193' 
  could not be resolved: Name or service not known

• Messages related to the event scheduler:

[Note] Event Scheduler: Loaded 0 events

• Messages related to unsafe statements for statement-based replication:

[Warning] Unsafe statement written to the binary log using statement format since 
  BINLOG_FORMAT = STATEMENT. The statement is unsafe because 
  it uses a LIMIT clause. This 
  is unsafe because the set of rows included cannot be predicted.

Frequent warnings about unsafe statements for statement-based replication can cause the error log to grow very large. MariaDB will automatically detect frequent duplicate warnings about unsafe statements for statement-based replication. After 10 identical warnings are detected, MariaDB will prevent that same warning from being written to the error log again for the next 5 minutes.

Verbosity Level 2

Default from MariaDB 10.2.4. If log_warnings is 2, then a couple other different kinds of warnings are printed. For example:

• Messages related to access denied errors:

[Warning] Access denied for user 'root'@'localhost' (using password: YES)

• Messages related to connections that are aborted due to errors or timeouts:

[Warning] Aborted connection 35 to db: 'unconnected' user: 
  'user1@host1' host: '192.168.1.40' (Got an error writing communication packets)
[Warning] Aborted connection 36 to db: 'unconnected' user: 
  'user1@host2' host: '192.168.1.230' (Got an error writing communication packets)
[Warning] Aborted connection 38 to db: 'db1' user: 
  'user2' host: '192.168.1.60' (Unknown error) 
[Warning] Aborted connection 51 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (Got an error reading communication packets)
[Warning] Aborted connection 52 to db: 'db1' user: 
  'user3' host: '192.168.1.53' (Got timeout reading communication packets)

• Messages related to table handler errors:

[Warning] Can't find record in 'tab1'.
[Warning] Can't write; duplicate key in table 'tab1'.
[Warning] Lock wait timeout exceeded; try restarting transaction.
[Warning] The number of locks exceeds the lock table size.
[Warning] Update locks cannot be acquired during a READ UNCOMMITTED transaction.

• Messages related to the files used to persist replication state:
  • Either the default master.info file or the file that is configured by the master_info_file option.
  • Either the default relay-log.info file or the file that is configured by the relay_log_info_file system variable.

[Note] Reading Master_info: '/mariadb/data/master.info'  
  Relay_info:'/mariadb/data/relay-log.info'
[Note] Initialized Master_info from '/mariadb/data/master.info'
[Note] Reading of all Master_info entries succeded
[Note] Deleted Master_info file '/mariadb/data/master.info'.
[Note] Deleted Master_info file '/mariadb/data/relay-log.info'.

• Messages about a master’s binary log dump thread:

[Note] Start binlog_dump to slave_server(263316466), pos(, 4)

Verbosity Level 3

If log_warnings is 3, then a couple other different kinds of warnings are printed. For example:

• Messages related to old-style language options:

[Warning] An old style --language value with language specific 
  part detected: /usr/local/mysql/data/
[Warning] Use --lc-messages-dir without language specific part instead.

• Messages related to progress of InnoDB online DDL:

[Note] InnoDB: Online DDL : Start
[Note] InnoDB: Online DDL : Start reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : End of reading clustered index of the table and 
  create temporary files
[Note] InnoDB: Online DDL : Start merge-sorting index PRIMARY (1 / 3), 
  estimated cost : 18.0263
[Note] InnoDB: Online DDL : merge-sorting has estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 33 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 17 runs
[Note] InnoDB: Online DDL : merge-sorting current run 3 estimated 9 runs
[Note] InnoDB: Online DDL : merge-sorting current run 4 estimated 5 runs
[Note] InnoDB: Online DDL : merge-sorting current run 5 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 6 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Start building index PRIMARY (1 / 3), 
  estimated cost : 27.0395
[Note] InnoDB: Online DDL : End of building index PRIMARY (1 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ux1 (2 / 3), 
  estimated cost : 5.7895
[Note] InnoDB: Online DDL : merge-sorting has estimated 2 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Start building index ux1 (2 / 3), 
  estimated cost : 8.6842
[Note] InnoDB: Online DDL : End of building index ux1 (2 / 3)
[Note] InnoDB: Online DDL : Completed
[Note] InnoDB: Online DDL : Start merge-sorting index ix1 (3 / 3), 
  estimated cost : 6.1842
[Note] InnoDB: Online DDL : merge-sorting has estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 1 estimated 3 runs
[Note] InnoDB: Online DDL : merge-sorting current run 2 estimated 2 runs
[Note] InnoDB: Online DDL : End of  merge-sorting index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Start building index ix1 (3 / 3), 
  estimated cost : 9.2763
[Note] InnoDB: Online DDL : End of building index ix1 (3 / 3)
[Note] InnoDB: Online DDL : Completed

Verbosity Level 4

If log_warnings is 4, then a couple other different kinds of warnings are printed. For example:

• Messages related to killed connections:

[Warning] Aborted connection 53 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (KILLED)

• Messages related to all closed connections:

[Warning] Aborted connection 56 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (CLOSE_CONNECTION)

• Messages related to released connections, such as when a transaction is committed and completion_type is set to RELEASE:

[Warning] Aborted connection 58 to db: 'db1' user: 
  'user2' host: '192.168.1.50' (RELEASE)

Verbosity Level 9

If log_warnings is 9, then some very verbose warnings are printed. For example:

• Messages about initializing plugins:

[Note] Initializing built-in plugins
[Note] Initializing plugins specified on the command line
[Note] Initializing installed plugins

MySQL’s log_error_verbosity

MariaDB does not support the log_error_verbosity system variable added in MySQL 5.7.

Format

The format consists of the date (yyyy-mm-dd) and time, the thread ID, followed by the type of error (Note, Warning or Error) and the error message, for example:

2016-06-15 16:53:33 139651251140544 [Note] InnoDB: 
  The InnoDB memory heap is disabled

Until MariaDB 10.1.4, the format only consisted of the date (yymmdd) and time, followed by the type of error (Note, Warning or Error) and the error message, for example:

160615 16:53:08 [Note] InnoDB: The InnoDB memory heap is disabled

Rotating the Error Log on Unix and Linux

Unix and Linux distributions offer the logrotate utility, which makes it very easy to rotate log files. See Rotating Logs on Unix and Linux for more information on how to use this utility to rotate the error log.

Error Messages File

Many error messages are ready from an error messages file that contains localized error messages. If the server can’t find this file when it starts up, then you might see errors like the following:

[ERROR] Can't find messagefile '/usr/share/errmsg.sys'

If this error is occurring because the file is in a custom location, then you can configure this location by setting the lc_messages_dir system variable either on the command-line or in a server option group in an option file prior to starting up the server. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/

If you want to use a different locale for error messages, then you can also set the lc_messages system variable. For example:

[mariadb]
...
lc_messages_dir=/usr/share/mysql/
lc_messages=en_US

See Setting the Language for Error Messages for more information.

AWS support for Internet Explorer ends on 07/31/2022. Supported browsers are Chrome, Firefox, Edge, and Safari.
Learn more »