[ZBX-5744] getScriptsByHosts function returns redundant information Created: 2012 Oct 25  Updated: 2017 May 30  Resolved: 2012 Nov 12

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Frontend (F)
Affects Version/s: 2.0.4rc1, 2.1.0
Fix Version/s: 2.0.4rc1, 2.1.0

Type: Incident report Priority: Minor
Reporter: Alexey Pustovalov Assignee: Unassigned
Resolution: Fixed Votes: 1
Labels: performance, scripts
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate

 Description   

It returns all hosts related with script host group AND all hosts related with NULL host_group.
So the next query:
mysql> SELECT DISTINCT s.*,hg.hostid FROM scripts s,hosts_groups hg WHERE (( (hg.hostid IN ('15097','15095','15096','15098','15099','15101','14869','15100','13266','13265','13259','13260','13261','13262','13263','13267','13264','14503','14507','14502','14504','14505','14506','14510','14511','10896','10894','10895','10891','10892','10893','10502','10501','15595','10503','10504','10500','12528','12526','12524','12527','12529','12622','12525','10206','10204','10205','10207','10216','11506','15596','15598','15609','15681','15602','15604','15606','15607','15601','15597','15599','15600','15603','11750','11748','11746','11749','11747','11751','11752','12627','12628','12624','12625','12626','12629','14188','14186','14187','11291','11289','12190','12187','12193','12192','12189','12191','12198','12200','13724','13728','13718','13722','13723','13719','13721','13725','13726','13727','13741','15715','15708','15709','15710','15714','15717','15718','15704','15706','15713','15707','15711','15705','15712')) AND hg.groupid=s.groupid) OR (s.groupid IS NULL AND scriptid BETWEEN 000000000000000 AND 099999999999999)) ORDER BY s.name;
will return 16974 rows in set (0.00 sec)!

The resulting array is a array of scripts with included all hosts information!

and php source code for maps.php is >20MB!



 Comments   
Comment by Alexey Pustovalov [ 2012 Oct 25 ]

Array with host scripts contains array with scripts which contains all related with them hosts.

Comment by Eduards Samersovs (Inactive) [ 2012 Oct 26 ]

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

Comment by Alexey Pustovalov [ 2012 Oct 30 ]

ZBX-5773 has been created as separate part of the problem. The main problem should be solved here.

Comment by Oleg Egorov (Inactive) [ 2012 Oct 31 ]

(1) Before fix events.php HTML page size is 93 kb, after 537 kb, possible junk data in HTML page

Eduards RESOLVED, Event page helps to detect problem, so ZBX-5773 is under question..

oleg.egorov CLOSED

oleg.egorov Again, this problem still exists. REOPENED.

oleg.egorov r31250 - CLOSED!

Comment by Oleg Egorov (Inactive) [ 2012 Nov 01 ]

Tested!

Comment by Eduards Samersovs (Inactive) [ 2012 Nov 02 ]

Fixed in versions pre-2.1.0 (beta) r31218, pre-2.0.4rc1 r31217

Comment by Pavels Jelisejevs (Inactive) [ 2012 Nov 02 ]

(2) What happened in this issue? Why hostids in script.get suddenly started returning hosts, and selectHosts was removed? Please, revert the fix.

Eduards RESOLVED

<richlv> that was a very good question... i tried to understand that by looking at relevant svn commits. i invite anybody to try and understand what was wrong and what was changed from the commit messages...

dev branch :
31097 - fixed hostid selection in Script->get() API
31130 - fixed hostid selection in Script->get() API
31142 - fixed hostid selection in Script->get() API
31208 - fixed hostid selection in Script->get() API

2.0 branch merge :
31217 - fixed redundant output hosts in Scripts API
trunk merge :
31218 - fixed redundant output hosts in Scripts API

dev branch again :
31226 - fixed selectHosts sub-query in Scripts
31230 - fixed selectHosts sub-query in Scripts
31231 - fixed selectHosts sub-query in Scripts

2.0 branch again :
31234 - revert fixed selectHosts sub-query in Scripts
trunk again :
31236 - revert fixed selectHosts sub-query in Scripts

i removed one changelog entry in r31238, as it seemed to be mistakenly left in place. hope i guessed correct.

jelisejev Yeah, the commit messages aren't very descriptive. But the problem is resolved. CLOSED.

Comment by Pavels Jelisejevs (Inactive) [ 2012 Nov 05 ]

(3) script.get countOutput is not working with scripts that are assigned to a host group.

jelisejev RESOLVED.

jelisejev CLOSED.

Comment by Oleg Egorov (Inactive) [ 2012 Nov 06 ]

Tested!

Comment by Pavels Jelisejevs (Inactive) [ 2012 Nov 07 ]

Fixed in 2.0.4rc1 r31301 and 2.1 r31302.

CLOSED.

Comment by Alexey Pustovalov [ 2012 Nov 09 ]

(4) Very hard query for users with limited permissions (users.type = 1):

mysql> EXPLAIN PARTITIONS
    -> SELECT DISTINCT s.* FROM scripts s,rights r,users_groups ug,hosts_groups hg WHERE hg.groupid=r.id AND r.groupid=ug.usrgrpid AND ug.userid=351
    -> AND (hg.groupid=s.groupid OR s.groupid IS NULL) AND (ug.usrgrpid=s.usrgrpid OR s.usrgrpid IS NULL)
    -> AND (s.groupid IN ('4','100','195','197','33','38','32','70','5','41','30','196','31','186','194','198','165','44','18','36','189','211','181','209',
    -> '213','263','214','237','238','13','240','208','253','255','258','265')) OR (s.groupid IS NULL AND scriptid BETWEEN 000000000000000 AND 099999999999999 ) ORDER BY s.name;
+----+-------------+-------+------------+-------+---------------------------------+----------------+---------+------+------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                   | key            | key_len | ref  | rows | Extra                                        |
+----+-------------+-------+------------+-------+---------------------------------+----------------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | ug    | NULL       | index | users_groups_1,c_users_groups_2 | users_groups_1 | 16      | NULL |  127 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | hg    | NULL       | index | hosts_groups_2                  | hosts_groups_2 | 8       | NULL | 4243 | Using index; Using join buffer               |
|  1 | SIMPLE      | r     | NULL       | ALL   | rights_1,rights_2               | NULL           | NULL    | NULL | 2029 | Using join buffer                            |
|  1 | SIMPLE      | s     | NULL       | ALL   | c_scripts_1,c_scripts_2         | NULL           | NULL    | NULL |    3 | Using where; Using join buffer               |
+----+-------------+-------+------------+-------+---------------------------------+----------------+---------+------+------+----------------------------------------------+
4 rows in set (0.00 sec)

this query was like this before fix:

mysql> explain SELECT  DISTINCT  s.*,hg.hostid FROM scripts s,rights r,users_groups ug,hosts_groups hg WHERE hg.groupid=r.id AND r.groupid=ug.usrgrpid AND ug.userid=351 AND
    -> (hg.groupid=s.groupid OR s.groupid IS NULL) AND (ug.usrgrpid=s.usrgrpid OR s.usrgrpid IS NULL) AND (( (hg.hostid IN ('91572','79114','79114','79114','33780',
    -> '79133','87273','91535','79111','33780','33897','79114','87273','79111','79111','78567','78567','33780','79111','87273','90508','11402','26408','79133','24410',
    -> '91539','78567','78567','78567','35967','78567','41790','92861','92860','73156','11759','78568','30852','83083','11784','74589','30289','78567','78567','90508',
    -> '11399','31275','11399','11399','34637','53314','91063','92558','92460','81573','78409','79087','78567','78567','89553','84804','78567','92861','26442','26442',
    -> '26442','11234','29740','11862','11399','30382','10017','10017','11399','11399','29740'))  AND hg.groupid=s.groupid) OR (s.groupid IS NULL AND scriptid BETWEEN 000000000000000 AND 099999999999999 )) ORDER BY s.name;
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys                   | key              | key_len | ref                | rows | Extra                           |
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
|  1 | SIMPLE      | ug    | ref  | users_groups_1,c_users_groups_2 | c_users_groups_2 | 8       | const              |    1 | Using temporary; Using filesort |
|  1 | SIMPLE      | r     | ref  | rights_1,rights_2               | rights_1         | 8       | zabbix.ug.usrgrpid |   53 |                                 |
|  1 | SIMPLE      | hg    | ref  | hosts_groups_1,hosts_groups_2   | hosts_groups_2   | 8       | zabbix.r.id        |   14 |                                 |
|  1 | SIMPLE      | s     | ALL  | c_scripts_1,c_scripts_2         | NULL             | NULL    | NULL               |    3 | Using where; Using join buffer  |
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
4 rows in set (0.00 sec)

I could not wait while the first query is executed. The second query executes very quick.

jelisejev RESOLVED in svn://svn.zabbix.com/branches/dev/ZBX-5744.

jelisejev Fixed in 2.0.4rc1 r31362 and 2.1 r31363. CLOSED.

Comment by Alexey Pustovalov [ 2012 Nov 11 ]

(5) Use of undefined constant API_OUTPUT_SHORTEN - assumed 'API_OUTPUT_SHORTEN' [ses/CScript.php:150]
maybe

define('API_OUTPUT_SHORTEN',             'shorten');

missed in defines.inc.php?

jelisejev Fixed directly in trunk r31389. CLOSED.

Generated at Fri Mar 29 09:44:51 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.