Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-24044

zabbix 6.4.1 with Oracle database backend cannot collect information for proxy

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Unresolved
    • Icon: Major Major
    • None
    • 6.4.10, 7.0.0beta1
    • Server (S)

      Steps to reproduce:

      1. Zabbix server 6.x with Oracle db backend
      2. add proxy, and register 200+(300+) hosts with that proxy
      3. 13504:20240206:122746.024 cannot collect configuration data for proxy "zabbix_proxy": failed to get data from table "item_preproc"
      4. 13517:20240206:110559.456 [Z3005] query failed: [-1] ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT [select item_preprocid,item
        id,step,type,params,error_handler,error_handler_params from item_preproc where (

      Result:
      See log files

      Expected:
      expected that zabbix_server was able to collect and pass configuration to its proxies

       

      we have zabbix server 6.4.1 with oracle database backend with several zabbix-proxies connected
      we've getting an error "cannot collect configuration data for proxy "proxy_name": failed to get data from table "item_preproc""
      the reason for this error is:
      [Z3005] query failed: [-1] ORA-04036: PGA memory used by the instance exceeds PGA_AGGREGATE_LIMIT
      oracle terminates session due to error.
      the reason for this behaviour is that zabbix generates very long unbinded queries - 200Kb and even longer. Oracle just cannot parse it.

      we've tried to increase PGA - big enough queries still getting killed. and it takes a lot of time for oracle to parse it - it leads to timeouts.
      we've already have rather big limit - pga_aggregate_limit 12G

      i've found exact the same bug - ZBX-17121:

      I've solved the problem.

      Changed the file src/libs/zbxdbhigh/proxy.c from:
      #define ZBX_MAX_IDS_PER_SELECT 10000

      To:
      #define ZBX_MAX_IDS_PER_SELECT 100

      Rebuild the server and voila.

      but in version 6.4 - there is no parameter ZBX_MAX_IDS_PER_SELECT

      after a little digging - i've found parameter

      #define ZBX_MAX_SQL_SIZE 262144
      

      i've decreased it to 16384, rebuild zabbix_server binary
      but - no luck.

      after looking at sources - found out that configuration for proxy is collected at zabbix_server/proxyconfigread/proxyconfig_read.c
      ZBX_MAX_SQL_SIZE - isn't metioned there.

      also i've found constant MAX_EXPRESSIONS 950 at libs/zbxdbhigh/db.c in functions zbx_db_add_condition_alloc, zbx_db_add_str_condition_alloc

      may be decreasing it will help?

       

        1. failed_query_example.log
          346 kB
          Geo Quit
        2. zabbix_server.log
          0.1 kB
          Geo Quit

            zabbix.dev Zabbix Development Team
            geoquit Geo Quit
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Created:
              Updated: