"Got timeout reading communication packets" with MySQL 5.7

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

"Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2016-12-29 11:37

Hi,

The server configuration :

Windows Server 2012
hMailServer 5.6.5-B2367
MySQL 5.7.17
MySQL user for hMailServer is hmailserver@127.0.0.1
MySQL and hMailServer are on the same machine

I have migrated yesterday MySQL from 5.6.21 version to 5.7.17 version. All functions very well in my own websites and also with hMailServer : emails sendings and reception. No errors displayed. Nothing bad.

But in the MySQL log file, I observe an error that I have never seen before over the years with MySQL 3.23, 4.0, 5.0, 5.5, 5.6. This error appears multiple times per minute. I think the equivalent english error message is : Got timeout reading communication packets (ER_NET_READ_INTERRUPTED)

...
2016-12-28T20:36:38.237608Z 6452 [Note] Connection 6452 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:36:38.284483Z 6454 [Note] Connection 6454 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:36:38.284483Z 6450 [Note] Connection 6450 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:37:53.128072Z 6689 [Note] Connection 6689 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:37:53.128072Z 6690 [Note] Connection 6690 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:37:53.128072Z 6691 [Note] Connection 6691 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:37:53.143697Z 6692 [Note] Connection 6692 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:37:53.487446Z 6693 [Note] Connection 6693 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:38:54.502939Z 6923 [Note] Connection 6923 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:38:54.502939Z 6924 [Note] Connection 6924 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-28T20:38:54.518564Z 6925 [Note] Connection 6925 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
...

In the MySQL configuration file, I use essentially settings related to charsets, buffer sizes and caches. I don't use the max_allowed_packet setting. I use the wait_timeout=30 and skip-name-resolve settings. Removing the skip-name-resolve setting changes nothing to my problem but after removing wait_timeout=30 (so default 28800 value is used) I observe that the error is much less frequent but still present. Only 3 times during all this night :

2016-12-29T04:44:34.308334Z 38 [Note] Connection 38 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-29T04:44:34.323958Z 39 [Note] Connection 39 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)
2016-12-29T05:38:35.535721Z 41 [Note] Connection 41 avortée vers la bd: 'hmailserver' utilisateur: 'hmailserver' hôte: '127.0.0.1' (Timeout en lecture des paquets reçus)

At the same hours, I see nothing in the hMailServer log file.

Does anybody has already observed this timeout error in the MySQL log file ? Do you think it is a MySQL or a hMailServer related problem ? How can we explain that hMailServer seems to function well and doesn't add an error in its own log ?

Thanks for your help.

Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2016-12-30 11:35

I have found the below post about the same error :
viewtopic.php?t=11460

But no exact solution given.

I think that this error is due to hMailServer keeping the MySQL connexion opened and when it retries to connect, MySQL generates an error if wait_timeout has passed.

I just hope it is that and it not masks a real communication problem between the two applications.

What I don't understand is why I have never seen this error over the years with previous MySQL installations but only with MySQL 5.7... My settings are the same.

I have had an idea to removes this error. Do you think hMailServer could read the MySQL wait_timeout value (with the SHOW command) and detects himself if a connexion has expired or is still alive ? If the timeout has been reached, hMailServer could so create immediately a new connexion and not reuse the expired one. To limit the numbers of wait_timeout interrogations, perhaps it's could be possible to just read the value once time when the hMailServer service starts. What do you think about that ?

User avatar
mattg
Moderator
Moderator
Posts: 19994
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by mattg » 2016-12-31 01:10

Julien@TLD wrote:The server configuration :

Windows Server 2012
hMailServer 5.6.5-B2367
MySQL 5.7.17
MySQL user for hMailServer is hmailserver@127.0.0.1
MySQL and hMailServer are on the same machine
So what happens if you remove the @127.0.0.1 from the mySQL user, or replace it with @localhost?

That thread seemed to suggest that may have been a solution.
SorenR is still a regular (more of a regular that they were then even) so perhaps see if they comment, if not perhaps PM them

My other suggestion would be to check for the database server name in the hMailserver.ini
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 1411
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Dravion » 2016-12-31 02:54

1) Open C:\ProgramData\MySQL\MySQL Server 5.7\my.ini
2) Change max_allowed_packet=4M (arround line 287) to max_allowed_packet=500M
3) Restart MySQL Server
4) Test again and inspect your MySQL Server log.

Reference [1]
http://dba.stackexchange.com/questions/ ... on-packets

Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2016-12-31 12:37

Hello,

Thanks for your answer.

I will send a private message to SorenR, yes.

I have done your verifications/tests :

* hmailserver.ini database settings are right (as a reminder, hMailServer works well despite the MySQL log error)
* 127.0.0.1 replaced by localhost or by % for the hmailserver user (+ flush privileges) : no changes, still errors
* max_allowed_packet=500M : no changes, still errors

The only thing which reduces/removes the log error is removing or increasing the wait_timeout setting.

I don't know if this is just a connection timeout problem or if there is behind another problem. Perhaps, the developper could help to see more clear ?

Also, what do you think about my suggestion of detecting the MySQL timeout setting value by hMailServer ?

Thanks

Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2016-12-31 13:11

Adding skip-host-cache and removing ONLY_FULL_GROUP_BY in the sql_mode variable does the same thing, still hMailServer timeout error for reading packets :(

User avatar
Dravion
Senior user
Senior user
Posts: 1411
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Dravion » 2016-12-31 17:27

One of the silent killers of MySQL Connections is the MySQL Packet.
...
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 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().
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
play a bit around with the values and report back. Start with 256 and increase.

Also try to change max_connections=400 and restart the mysql service.

Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2016-12-31 20:19

Thanks Dravion,

I already tried max_allowed_packet=256M and max_allowed_packet=500M as suggested. No changes so I returned to the default value (4 MB). I will try other values but I am sceptical.

To my knowledge, hMailServer doesn't store messages and attachments in the database so on my advise hMailServer SQL requests and results are not so big, no ?

Concerning max_connections, I use yet a value of 300. Habitually, when this value is reached, the MySQL server locks himself and doesn't create the timeout error observed. I will still try to increase the value and report back.

On my advise, the right question is what has changed between MySQL 5.6 and MySQL 5.7 that could have this impact on hMailServer connections.

Thanks for your help ;)

Julien@TLD
Normal user
Normal user
Posts: 53
Joined: 2005-09-12 11:57
Location: Marseille - France

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Julien@TLD » 2017-01-04 23:22

Re,

I have tested many many different MySQL server settings but same thing. I have also updated hMailServer to the build 2383, PHP to 7.0.14 (one line about MySQL in the release notes), libmysql.dll in the hMailServer dir to 5.7.17 (32 bit), rebooted the server, tried with IPv4 only or both IPv4/v6. I still see this same timeout error with a short wait_timeout value that I have never seen before MySQL 5.7 migration.

I found this 2006 thread concerning MySQL lost connexion (but another error message) :
viewtopic.php?t=5152

So I will keep my new wait_timeout=300 value even if it doesn't enchant me (high traffic website). Now, I just observe one or two "Got timeout reading communication packets" error per day (I don't understand why anyway)...

I keep thinking that hMailServer could perhaps better manage MySQL connexions lost by not trying to reuse a lost connexion which would removes this error in MySQL log file.

Martin, if you pass by there ;)

Thanks.

User avatar
Dravion
Senior user
Senior user
Posts: 1411
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Dravion » 2017-01-05 08:45

Hmm according to my understanding a " mysql network packet" has to be transmitted as a whole. If this not possible the mysql Database deamon aborts the session and reconnects to hMailServer again and again and this is what you are seeing in your errorlog.

This could be triggered by many sources (bad network cables, a faulty NIC Adapter Driver/Setting). In you case i would try to contact Oracle MySQL Support, maybe there is a patch or workarround avaiable.

SchultzITsolutions
New user
New user
Posts: 4
Joined: 2017-05-29 15:12

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by SchultzITsolutions » 2018-07-16 08:25

(almost) same issue here, but even worse in consequences...

Hmail Server: 5.6.7-B2425 (updated one week ago)
Database Server:
Server-Typ: MariaDB
Server-Version: 10.3.8-MariaDB-log (updated one week ago)
Datenbank-Client Version: libmysql - mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $

I hit the same error messages after a few hours of operation. But once these messages show up, the ONLY solution in my case (so far) is a reboot of the physical server!
(restarting Hmail deamon and MariaDB deamon does not help!)

Did anyone come across some real solutions yet?

any help would be appreciated.
kind regards
Ruediger Schultz


NB: yes, there are dozens of other applications using this database instance without any issues, it is only the combination of HMail Server and Database that are causing trouble

User avatar
Dravion
Senior user
Senior user
Posts: 1411
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: "Got timeout reading communication packets" with MySQL 5.7

Post by Dravion » 2018-07-17 13:15

MySQL only comes up with such Errors if something between hMailServer and MySQL tampering with the connection.

*Update your Networkcard Drivers
*Update your Windows to recent patchlevel
*Stop thirdparty, interfering Software
*Stop heavy CPU consuming processes

And in hMailServer log section enable full logging
and attach the logs to your next post reply.

Post Reply