[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: |
|
Description |
Currently, zabbix server processes do not commit queries by default. |
Comments |
Comment by Glebs Ivanovskis (Inactive) [ 2016 Sep 16 ] |
Is it somehow related to auto-reconnection enabled for MySQL in 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. 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 |
Comment by Vladislavs Sokurenko [ 2016 Sep 19 ] |
Steps: [mysqld] init_connect='SET AUTOCOMMIT=0' sudo service mysql restart expected: Actual: |
Comment by Vladislavs Sokurenko [ 2016 Sep 19 ] |
Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-11221 Changes: |
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:
|
Comment by Martins Valkovskis [ 2016 Oct 12 ] |
(5) Documentation: RESOLVED sasha CLOSED |