[ZBX-11221] Zabbix should set autocommit=1 for every connection. Created: 2016 Sep 16  Updated: 2017 May 30  Resolved: 2016 Oct 17

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: 3.0.5rc1
Fix Version/s: 2.2.16rc1, 3.0.6rc1, 3.2.2rc1, 3.4.0alpha1

Type: Incident report Priority: Blocker
Reporter: Andrei Gushchin (Inactive) Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: database
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate

 Description   

Currently, zabbix server processes do not commit queries by default.
So, zabbix depends on the MySQL configurations, where the autocommit may be disabled.



 Comments   
Comment by Glebs Ivanovskis (Inactive) [ 2016 Sep 16 ]

Is it somehow related to auto-reconnection enabled for MySQL in ZBX-6163?

neogan No I suppose it's not related.

Comment by Andrei Gushchin (Inactive) [ 2016 Sep 16 ]

When autocommit disabled, trappers generate usual heartbeat proxy updates which lock hosts table forever.
So update became very slow every update for 120 sec.

Query    16444  Updating            zabbixdb  1.1.1.1  zabbix     00:48.910  update hosts set lastaccess=1474009754 where hostid=13379                                                 
Query    16484  Updating            zabbixdb  1.1.1.1  zabbix     00:48.550  update hosts set lastaccess=1474009754 where hostid=13570                                                 
Query    16470  Updating            zabbixdb  1.1.1.1  zabbix     00:47.786  update hosts set lastaccess=1474009755 where hostid=13591                                                 
Query    16467  Updating            zabbixdb  1.1.1.1  zabbix     00:47.728  update hosts set lastaccess=1474009755 where hostid=13567                                                 
Query    16466  Updating            zabbixdb  1.1.1.1  zabbix     00:46.488  update hosts set lastaccess=1474009756 where hostid=13559                                                 
Query    16465  Updating            zabbixdb  1.1.1.1  zabbix     00:46.481  update hosts set lastaccess=1474009756 where hostid=13586                                                 
Query    16402  Updating            zabbixdb  1.1.1.1  zabbix     00:46.338  update hosts set lastaccess=1474009756 where hostid=13563                                                 
Query    16495  Updating            zabbixdb  1.1.1.1  zabbix     00:43.754  update hosts set lastaccess=1474009759 where hostid=13586                                                 
Query    16478  Updating            zabbixdb  1.1.1.1  zabbix     00:41.909  update hosts set lastaccess=1474009761 where hostid=13590                                                 
Query    16431  Updating            zabbixdb  1.1.1.1  zabbix     00:40.458  update hosts set lastaccess=1474009762 where hostid=13591                                                 
Query    16415  Updating            zabbixdb  1.1.1.1  zabbix     00:39.106  update hosts set lastaccess=1474009764 where hostid=13584                                                 
Query    16426  Updating            zabbixdb  1.1.1.1  zabbix     00:38.739  update hosts set lastaccess=1474009764 where hostid=13559                                                 
Query    16457  Updating            zabbixdb  1.1.1.1  zabbix     00:32.768  update hosts set lastaccess=1474009770 where hostid=13580                                                 
Query    16458  Updating            zabbixdb  1.1.1.1  zabbix     00:31.431  update hosts set lastaccess=1474009771 where hostid=13578                                                 
Query    16450  Updating            zabbixdb  1.1.1.1  zabbix     00:30.550  update hosts set lastaccess=1474009772 where hostid=13581                                                 
Query    16404  Updating            zabbixdb  1.1.1.1  zabbix     00:29.772  update hosts set lastaccess=1474009773 where hostid=13583             
Comment by richlv [ 2016 Sep 16 ]

shouldn't trappers (and passive proxy pollers as well, i guess) just perform the updates in a more intelligent manner ?

Comment by Marc [ 2016 Sep 16 ]

Personally, I'd prefer COMMITs are issued by the application only. On each COMMIT the database has to ensure that transaction logs are written properly and synced reliable to storage. What is unlikely very efficient when having autocommit=on. Well, when being able to do asynchronous COMMITs, then this might not be such a big deal
On the other hand, this request appears to be related to MySQL only - so, I actually should not mind about this at all

Comment by Vladislavs Sokurenko [ 2016 Sep 19 ]

Steps:
1. Setup zabbix_agent to be monitored by proxy.
2. Create action to auto register host.
3. Create monitored by proxy.
4. Start agent, server and proxy.
5. See that agent is auto registered.
6. Kill zabbix agent, server and proxy and delete host in front end.
7. Change mysql configuration file so that auto commit is set to 0 during connect.
sudo vim /etc/mysql/my.cnf and add

[mysqld]
init_connect='SET AUTOCOMMIT=0'

sudo service mysql restart
8. Start agent, server and proxy.

expected:
Agent is discovered.

Actual:
You see in frontend that agent is not discovered.

Comment by Vladislavs Sokurenko [ 2016 Sep 19 ]

Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-11221

Changes:
Set auto commit to true, right after we connected to MySQL server from zabbix_server, zabbix_proxy or frontend.

Comment by Andris Zeila [ 2016 Sep 21 ]

(1) [SF] Database connection should fail if setting autocommit failed.

vso RESOLVED in r62650, r62651, r62652

wiper CLOSED

Comment by Andris Zeila [ 2016 Sep 21 ]

(2) [SF] We should not try setting autocommit if the database connection failed.

vso RESOLVED in r62650, r62651, r62652

wiper CLOSED

Comment by Andris Zeila [ 2016 Sep 22 ]

(3) [F] Coding style. In php code the opening { should not be on a new line. And it would be probably better to try setting autocommit before DBexecute('SET NAMES utf8');. I'd suggest the following patch, but it really should be double checked by php developer.

Index: frontends/php/include/db.inc.php
===================================================================
--- frontends/php/include/db.inc.php	(revision 62673)
+++ frontends/php/include/db.inc.php	(working copy)
@@ -57,13 +57,12 @@
 					$error = 'Error connecting to database: '.trim(mysqli_connect_error());
 					$result = false;
 				}
+				elseif (TRUE != mysqli_autocommit($DB['DB'], TRUE)) {
+					$error = 'Error setting auto commit.';
+					$result = false;
+				}
 				else {
 					DBexecute('SET NAMES utf8');
-					if(TRUE != mysqli_autocommit($DB['DB'], TRUE))
-					{
-						$error = 'Error setting auto commit.';
-						$result = false;
-					}
 				}
 
 				if ($result) {

oleg.egorov Thank you for patch. Resolved in r63014

CLOSED

Comment by Andris Zeila [ 2016 Sep 22 ]

Server side tested

Comment by Oleg Egorov (Inactive) [ 2016 Oct 12 ]

(4) [F] No string changes

oleg.egorov CLOSED

Comment by Oleg Egorov (Inactive) [ 2016 Oct 12 ]

Fixed in:

  • pre-2.2.16rc1 r63109
  • pre-3.0.6rc1 r63110
  • pre-3.2.2rc1 r63111
  • pre-3.3.0 (trunk) rr63112
Comment by Martins Valkovskis [ 2016 Oct 12 ]

(5) Documentation:

RESOLVED

sasha CLOSED

Generated at Thu Mar 28 12:36:01 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.