[ZBX-6163] [Z3005] query failed: [2006] MySQL server has gone away Created: 2013 Jan 22  Updated: 2019 Feb 26  Resolved: 2014 Oct 16

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: None
Fix Version/s: 2.5.0

Type: Incident report Priority: Minor
Reporter: Etienne CHAMPETIER Assignee: Unassigned
Resolution: Fixed Votes: 9
Labels: database, mysql, patch, timeout
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

mysql

Attachments: File zabbix-mysql.patch    
Issue Links:
Duplicate
is duplicated by ZBX-7793 Zabbix doesn't alter mySQL wait_timeo... Closed
is duplicated by ZBX-3986 MySQL server has gone away Closed

 Description   

Hi
The "MySQL server has gone away" error appens if a mysql connection exceeds "wait_timeout" or "max_allowed_packet".
http://dev.mysql.com/doc/refman/5.5/en/gone-away.html

Some config have small "wait_timeout" by default (600 seconds for mysql IUS rpm) so it flood zabbix_server.log for nothing (the "permanent" connection get kicked out, and so zabbix fail to use the connection)

Using mysql automatic reconnection allow the mysqlclient to try to reconnect once and if it fail again display the error.
http://dev.mysql.com/doc/refman/5.5/en/auto-reconnect.html

Attached is a small patch that add automatic reconnection for mysql (and clear the server log file from "spam")

 Comments   
Comment by Dimitri Bellini [ 2013 Feb 06 ]

I have the same problem... after tuning mysql using the suggested paramenter like below:
wait_timeout = 28000
max_allowed_packet = 64M
innodb_buffer_pool_size=32G
with MySQL Partitioned 5.5 Version

My server is 4 CPU Xeon (4Core) and 64GB of RAM and high performance SAN DISK on RHEL 5.x 64Bit

Comment by loredo [ 2013 Oct 25 ]

This happens here too.
Incerasing max_allowed_packet didn't change anything.

Found this issue was fixed with a patch for zabbix 2.0.6 here: https://www.zabbix.com/forum/showthread.php?t=28644
It is connected to the type of connection : interactive or no interactive.
We have version 2.0.9 and not sure if this patch will work with this version.

Comment by Jean Baptiste Favre [ 2013 Nov 21 ]

I'm currently testing this patch against the latest 2.2.0 release.

Will report status in a few days.

Comment by Aaron Blythe [ 2013 Dec 24 ]

@Jean Baptiste Favre, any luck on the test?

Comment by Wolfgang Alper [ 2014 Jan 30 ]

Happens here as well on proxy with local mysql database using unix domain socket to communicate with proxy.
Nearly no load on Proxy.
Version Zabbix 2.2.2rc1 (revision 41561).

Comment by Oleksii Zagorskyi [ 2014 Feb 13 ]

Would be better to implement it not only for mysql, but for all supported dbengines. We know precedents when DBAs decreased the timeout for Oracle db.
Then need to check does any supported dbengine allow to rewrite the global db's "wait timeout" parameter.

I got an idea - what if we after the "MySQL server has gone away" error would also add short statistic when a process had activity with database last time? Or time elapsed after last activity with db.
It will help do distinguish cases when db's "wait timeout" was exceeded and it will indicate not a db's issue but just misconfiguration.

For example I have a very small "production" zabbix installation (current trunk), server config file is almost default(# StartPollers=5), real NVPS is just 1,01.
I have the "MySQL server has gone away" errors in log - generated by different pollers.
Approximately one error record every 8 hours.

I suppose only experienced users can guess what could be the cause and classify this case as not critical one.

Comment by Timur Bakeyev [ 2014 Feb 13 ]

You can also consider using:

SET SESSION wait_timeout=84600

on connection, with big enough timeout value.

Comment by Juris Miščenko (Inactive) [ 2014 Sep 17 ]

Changes implemented in development branch located at svn://svn.zabbix.com/branches/dev/ZBX-6163

Comment by Andris Zeila [ 2014 Oct 07 ]

(1) From MYSQL 5.1 manual:

Note: mysql_real_connect() incorrectly reset the MYSQL_OPT_RECONNECT option to its default value before MySQL 5.1.6. Therefore, prior to that version, if you want reconnect to be enabled for each connection, you must call mysql_options() with the MYSQL_OPT_RECONNECT option after each call to mysql_real_connect().

jurism Option setting has been moved after connection creation. RESOLVED.

wiper CLOSED

Comment by Andris Zeila [ 2014 Oct 09 ]

Successfully tested

Comment by Juris Miščenko (Inactive) [ 2014 Oct 15 ]

Changes merged into 2.5.0 (trunk) at r49901.

Comment by Aleksandrs Saveljevs [ 2014 Oct 16 ]

(3) We have a convention to order databases in db.c in alphabetical order: IBM DB2, MySQL, Oracle, PostgreSQL, SQLite3. New conditionally compiled code should be moved to the proper place.

sasha Already fixed in r49915.

CLOSED

Generated at Fri Apr 26 02:13:27 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.