ZABBIX BUGS AND ISSUES
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-5744

getScriptsByHosts function returns redundant information

    Details

      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!

        Activity

        Hide
        Alexey Pustovalov added a comment -

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

        Show
        Alexey Pustovalov added a comment - Array with host scripts contains array with scripts which contains all related with them hosts.
        Hide
        Eduards Samersovs added a comment -

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

        Show
        Eduards Samersovs added a comment - Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-5744
        Hide
        Alexey Pustovalov added a comment -

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

        Show
        Alexey Pustovalov added a comment - ZBX-5773 has been created as separate part of the problem. The main problem should be solved here.
        Hide
        Oleg Egorov added a comment - - edited

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

        Eduards Samersovs 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!

        Show
        Oleg Egorov added a comment - - edited (1) Before fix events.php HTML page size is 93 kb, after 537 kb, possible junk data in HTML page Eduards Samersovs 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!
        Hide
        Oleg Egorov added a comment -

        Tested!

        Show
        Oleg Egorov added a comment - Tested!
        Hide
        Eduards Samersovs added a comment -

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

        Show
        Eduards Samersovs added a comment - Fixed in versions pre-2.1.0 (beta) r31218, pre-2.0.4rc1 r31217
        Hide
        Pavels Jelisejevs added a comment - - edited

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

        Eduards Samersovs 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.

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

        Show
        Pavels Jelisejevs added a comment - - edited (2) What happened in this issue? Why hostids in script.get suddenly started returning hosts, and selectHosts was removed? Please, revert the fix. Eduards Samersovs 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. Pavels Jelisejevs Yeah, the commit messages aren't very descriptive. But the problem is resolved. CLOSED.
        Hide
        Pavels Jelisejevs added a comment - - edited

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

        Pavels Jelisejevs RESOLVED.

        Pavels Jelisejevs CLOSED.

        Show
        Pavels Jelisejevs added a comment - - edited (3) script.get countOutput is not working with scripts that are assigned to a host group. Pavels Jelisejevs RESOLVED. Pavels Jelisejevs CLOSED.
        Hide
        Oleg Egorov added a comment -

        Tested!

        Show
        Oleg Egorov added a comment - Tested!
        Hide
        Pavels Jelisejevs added a comment -

        Fixed in 2.0.4rc1 r31301 and 2.1 r31302.

        CLOSED.

        Show
        Pavels Jelisejevs added a comment - Fixed in 2.0.4rc1 r31301 and 2.1 r31302. CLOSED.
        Hide
        Alexey Pustovalov added a comment - - edited

        (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.

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

        Pavels Jelisejevs Fixed in 2.0.4rc1 r31362 and 2.1 r31363. CLOSED.

        Show
        Alexey Pustovalov added a comment - - edited (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. Pavels Jelisejevs RESOLVED in svn://svn.zabbix.com/branches/dev/ZBX-5744. Pavels Jelisejevs Fixed in 2.0.4rc1 r31362 and 2.1 r31363. CLOSED.
        Hide
        Alexey Pustovalov added a comment - - edited

        (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?

        Pavels Jelisejevs Fixed directly in trunk r31389. CLOSED.

        Show
        Alexey Pustovalov added a comment - - edited (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 ? Pavels Jelisejevs Fixed directly in trunk r31389. CLOSED.

          People

          • Assignee:
            Pavels Jelisejevs
            Reporter:
            Alexey Pustovalov
          • Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: