2006 mysql gone away fix

[zlofenix]

Extract with overwrite, works with zabbix 2.0.2.
1. Added CLIENT_INTERACTIVE flag to mysql_real_connect - using interactive_timeout, default is 28800.
2. Added DBPing() function - just "select 1;" to reset timeout and prevent loosing connection to db.
2.1 Added to query and select functions timer var
2.2 if current time - timer var > 190 => select 1 in DBPing()
3. Added DBPing() to many _loop functions, every 200 sec
4. Rewrited zbx_tcp_accept and added zbx_tcp_accept_timeout with timeout 200 for main_trapper_loop
5. added check to not start nodewatcher if NodeID = 0 in config

Ну в общих чертах:
Добавил функцию, кидающую select 1 в базу, если не было запросов более 190 секунд и добавил в большинство loop функций пинг базы и проверку на NodeID = 0 в конфиге, чтобы не запускать лишний форк nodewatcher'a

[ucs75]

Thank you!

I can't believe there aren't more views to this already. This is the one MAIN problem I'm seeing after upgrading to version 2.0.2. The log is full of MySQL server has gone away errors.

Previously I had all three components on a single server, with the upgrade, I set frontend, core, and database on three separate servers. But have been plagued by this issue -- which I believe is the reason for the periodic Zabbix is not running messages on the frontend admin header. It's very frustrating.


Has anyone else used and tested this patch? Any feedback?

[zlofenix]

Over 12h uptime - no errors, still 12 connects to db from zabbix

[ucs75]

Still seeing gone away error

I SWEAR I already posted this, but it's not showing up. So I'll repost....

As seen below, I'm still getting mysql gone away errors....
I don't know that it's affecting anything other than my OCD need to have zero errors.

3264:20120830:225529.482 server #1 started [configuration syncer #1]
  3265:20120830:225529.485 server #2 started [db watchdog #1]
  3266:20120830:225529.546 server #3 started [poller #1]
  3269:20120830:225529.560 server #4 started [poller #2]
  3270:20120830:225529.592 server #5 started [poller #3]
  3277:20120830:225529.608 server #7 started [poller #5]
  3279:20120830:225529.609 server #9 started [trapper #1]
  3276:20120830:225529.615 server #6 started [poller #4]
  3280:20120830:225529.618 server #10 started [trapper #2]
  3281:20120830:225529.619 server #11 started [trapper #3]
  3282:20120830:225529.621 server #12 started [trapper #4]
  3283:20120830:225529.622 server #13 started [trapper #5]
  3284:20120830:225529.624 server #14 started [icmp pinger #1]
  3285:20120830:225529.625 server #15 started [alerter #1]
  3287:20120830:225529.627 server #16 started [housekeeper #1]
  3287:20120830:225529.627 executing housekeeper
  3288:20120830:225529.628 server #17 started [timer #1]
  3289:20120830:225529.630 server #18 started [http poller #1]
  3291:20120830:225529.637 server #20 started [history syncer #1]
  3292:20120830:225529.638 server #21 started [history syncer #2]
  3293:20120830:225529.640 server #22 started [history syncer #3]
  3294:20120830:225529.641 server #23 started [history syncer #4]
  3298:20120830:225529.654 server #25 started [proxy poller #1]
  3299:20120830:225529.656 server #26 started [self-monitoring #1]
  3227:20120830:225529.659 server #0 started [main process]
  3295:20120830:225529.664 server #24 started [escalator #1]
  3278:20120830:225529.673 server #8 started [unreachable poller #1]
  3290:20120830:225529.690 server #19 started [discoverer #1]
  3278:20120830:225534.943 resuming Zabbix agent checks on host [lamp22 - removed4security.com]: connection restored
  3278:20120830:225534.957 resuming SNMP checks on host [Firewall]: connection restored
  3287:20120830:225634.800 housekeeper deleted: 78879 records from history and trends, 4000 records of deleted items, 0 events, 0 alerts, 0 sessions
  3279:20120830:232440.868 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232441.680 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232445.905 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232449.954 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232451.638 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232454.044 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232459.091 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232500.922 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232501.653 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232505.937 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232510.963 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232511.619 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232513.176 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232515.996 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232519.224 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232522.017 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232524.307 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232529.357 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3279:20120830:232530.214 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,status from hosts where host='lamp22 - removed4security.com' and status in (0,1) and proxy_hostid is null and hostid between 0 and 99999999999999]
  3293:20120830:232921.429 item [removed4security.com:mem.value.cache] became supported
  3294:20120830:232951.730 item [removed4security.com:mem.value.cache] became not supported: Received value [2.14773e+09] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
  ...
  ...
  3278:20120830:234500.026 resuming Zabbix agent checks on host [removed4security]: connection restored  
  3281:20120830:235037.866 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235038.026 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235042.892 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235047.930 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235048.051 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235408.050 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235408.423 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235408.542 [Z3005] query failed: [2006] MySQL server has gone away [SELECT 1;]
  3281:20120830:235411.633 [Z3005] query failed: [2006] MySQL server has gone away [select hostid,status from hosts where host='removed4security' and status in (0,1) and proxy_hostid is null and hostid between 0 and 99999999999999]
  3287:20120830:235649.253 executing housekeeper
  3287:20120830:235755.686 housekeeper deleted: 81518 records from history and trends, 4000 records of deleted items, 0 events, 0 alerts, 0 sessions

[ucs75]

trying one more thing....

I really think that the patch should have resolved this, and that the change I'm making is pointless.

HOWEVER...I just changed the TCP timeout on the firewall between DMZ (zabbix core) and LAN (MySQL) from 15 minutes of inactivity to 120 minutes.

Just for this one access rule -- not globally

Will keep an eye on it, and see of this fixes it for good.

[zlofenix]

Well, this patch fixes error, but interactive_timeout MUST be over 200, check your db config.

[ucs75]

Confirmed (Mostly)

After updating the firewall rule, and applying the patch, I no longer have mysql has gone away messages in the zabbix_server.log. Has been running for multiple days.

The only test left to fully confirm would be to test again without the patch and with the firewall rules in place. But it's looking very good so far!

Thanks zlofenix!

[zlofenix]

Uptime: 25 days
Errors: 0

[zlofenix]

Bump time

Patch for zabbix 2.0.6.