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

LLD based on ODBC request answer size limit

    Details

    • Type: Incident report
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 2.2.4
    • Fix Version/s: 2.2.15rc1, 3.0.5rc1, 3.2.0beta2
    • Component/s: Proxy (P), Server (S)
    • Labels:
    • Environment:
      CentOS 6.4 based proxy, CentOS 6.4 based server, MS SQL 2012 Database (target object)
      uname -r = 2.6.32-431.20.3.el6.x86_64

      Description

      We have several locations with shopping equipment like weighing-machines, POS terminals, etc. We make MS SQL query to get JSON answer with hardware installed in our location directly from location DB.

      In our first location query works like a charm and LLD works fine.

      For the second location (big one) LLD doesn't work, zabbix reports "Value should be a JSON object."

      Zabbix setup is the same in each location (different proxy servers, but proxy installed by one man with standart instruction). DBs and tables has different names, but inside db structure is the same, so we changed only names in query.

      Here is query for first location:

      select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#WMDEVICEID}":"' + convert(varchar(50),Obor._IDRRef,1) + '",' + '"{#WMDEVICENAME}":"' + Obor._Description + '"}' from [tk17-2013].dbo._reference51 Obor where Obor._Marked = 0x00 and Obor._fld765 like '%????%' or Obor._fld765 like '%POS%' FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')
      

      Here is query for second location:

      select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#WMDEVICEID}":"' + convert(varchar(50),Obor._IDRRef,1) + '",' + '"{#WMDEVICENAME}":"' + Obor._Description + '"}' from [tk16-2013].dbo._reference51 Obor where Obor._Marked = 0x00 and Obor._fld754 like '%????%' or Obor._fld765 like '%POS%' FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')
      

      Here is my research and problem debug:

      I make restart each of proxy with debuglevel=4

      Here zabbix proxy debug log copy paste from good proxy server (file good-proxy.log):

       12133:20140717:174056.529 In odbc_DBselect() query:'select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#WMDEVICEID}":"' + convert(varchar(50),Obor._IDRRef,1) + '",' + '"{#WMDEVICENAME}":"' + Obor._Description + '"}' from [tk17-2013].dbo._reference51 Obor where Obor._Marked = 0x00 and Obor._fld765 like '%????%' or Obor._fld765 like '%POS%' FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')'
       12133:20140717:174056.530 odbc_DBselect() selected 1 columns
       12133:20140717:174056.530 End of odbc_DBselect()
       12133:20140717:174056.530 In odbc_DBfetch()
       12133:20140717:174056.530 odbc_DBfetch() fetched [0 col]: '{"data":[{"{#WMDEVICEID}":"0x93EE000C296CC90C11E3340355F55BAE","{#WMDEVICENAME}":"?????? POS ?????????? ????????-??? (4.0) ?? MT17"},{"{#WMDEVICEID}":"0x93EE000C296CC90C11E3340617597579","{#WMDEVICENAME}":"???? 23.081 ?1"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAE9","{#WMDEVICENAME}":"???? 23.083 ?3"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAEB","{#WMDEVICENAME}":"???? 23.082 ?2"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAEC","{#WMDEVICENAME}":"???? 23.084 ?4"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAED","{#WMDEVICENAME}":"???? 23.085 ?5 (???????? ?????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAEE","{#WMDEVICENAME}":"???? 23.086 ?6 (??????? ?????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAEF","{#WMDEVICENAME}":"???? 23.087 ?7"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF0","{#WMDEVICENAME}":"???? 23.088 ?8 (????/???????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF1","{#WMDEVICENAME}":"???? 23.089 ?9 (?????? ???????????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF2","{#WMDEVICENAME}":"???? 23.090 ?10 (?????? ???????????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF3","{#WMDEVICENAME}":"???? 23.093 ?13 (???????????? ???????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF4","{#WMDEVICENAME}":"???? 23.094 ?14 (?????? ???????????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF7","{#WMDEVICENAME}":"???? 23.097 ?17 (????/???????)"},{"{#WMDEVICEID}":"0xBEFC000C2915558011E351B7F7F0BAF8","{#WMDEVICENAME}":"???? 23.098 ?18"}]}'
       12133:20140717:174056.530 End of odbc_DBfetch()
       12133:20140717:174056.531 End of get_value_db():SUCCEED
       12133:20140717:174056.531 End of get_value():SUCCEED
      

      Here debug log copy paste from JSON error proxy (file error-proxy.log):

        3571:20140718:124948.522 In odbc_DBselect() query:'select '{"data":[', (SELECT STUFF((SELECT ',' + '{"{#WMDEVICEID}":"' + convert(varchar(50),Obor._IDRRef,1) + '",' + '"{#WMDEVICENAME}":"' + Obor._Description + '"}' from [tk16-2013].dbo._reference51 Obor where Obor._Marked = 0x00 and Obor._fld754 like '%????%' or Obor._fld765 like '%POS%' FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')'
        3571:20140718:124948.524 odbc_DBselect() selected 1 columns
        3571:20140718:124948.524 End of odbc_DBselect()
        3571:20140718:124948.524 In odbc_DBfetch()
        3571:20140718:124948.524 odbc_DBfetch() fetched [0 col]: '{"data":[{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A14DFE593A1C","{#WMDEVICENAME}":"???? 03 (24.63) ??? SM-300"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A2D054AE6692","{#WMDEVICENAME}":"???? 06 (24.66) ?????? (????) SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A3A1221C063B","{#WMDEVICENAME}":"???? ???????????????? ?1 (24.96) ???????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0456","{#WMDEVICENAME}":"???? ???????????????? ?2 (24.95) ?????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0457","{#WMDEVICENAME}":"???? ???????????????? ?3 (24.94) ?????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0458","{#WMDEVICENAME}":"???? ???????????????? ?4 (24.93) ?????????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0459","{#WMDEVICENAME}":"???? ???????????????? ?5 (24.92) ?????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045A","{#WMDEVICENAME}":"???? ???????????????? ?6 (24.91) ?????????"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045C","{#WMDEVICENAME}":"???? 08 (24.68) ???? SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045F","{#WMDEVICENAME}":"???? 11 (24.71) ???? SM-300"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0461","{#WMDEVICENAME}":"???? 13 (24.73) ??????? (?????? ??????) SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0462","{#WMDEVICENAME}":"???? 14 (24.74) ??????? (?????? ??????) SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0463","{#WMDEVICENAME}":"???? 15 (24.75) ?????. ?????? SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0464","{#WMDEVICENAME}":"???? 16 (24.76) ?????. ?????? SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0465","{#WMDEVICENAME}":"???? 17 (24.77) ?????. ?????? SM-100"},{"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448'
        3571:20140718:124948.524 End of odbc_DBfetch()
        3571:20140718:124948.524 End of get_value_db():SUCCEED
        3571:20140718:124948.525 End of get_value():SUCCEED
      

      I changed query with new line delimetr, and tried in our big location, no result.

      Here is new log, data transfer stops in the same place (file error-proxy-second-try.log):

      23786:20140717:164348.953 In odbc_DBselect() query:'select '{"data":[', (SELECT STUFF((SELECT ',' +  CHAR(10) + '{"{#WMDEVICEID}":"' + convert(varchar(50),Obor._IDRRef,1) + '",' + '"{#WMDEVICENAME}":"' + Obor._Description + '"}' from [tk16-2013].dbo._reference51 Obor where Obor._Marked = 0x00 and Obor._fld754 like '%????%' or Obor._fld765 like '%POS%' FOR XML PATH('')), 1, 1, '')) ,']}' FOR XML PATH('')'
       23786:20140717:164349.001 odbc_DBselect() selected 1 columns
       23786:20140717:164349.001 End of odbc_DBselect()
       23786:20140717:164349.001 In odbc_DBfetch()
       23786:20140717:164349.001 odbc_DBfetch() fetched [0 col]: '{"data":[
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A14DFE593A1C","{#WMDEVICENAME}":"???? 03 (24.63) ??? SM-300"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A2D054AE6692","{#WMDEVICENAME}":"???? 06 (24.66) ?????? (????) SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A3A1221C063B","{#WMDEVICENAME}":"???? ???????????????? ?1 (24.96) ???????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0456","{#WMDEVICENAME}":"???? ???????????????? ?2 (24.95) ?????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0457","{#WMDEVICENAME}":"???? ???????????????? ?3 (24.94) ?????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0458","{#WMDEVICENAME}":"???? ???????????????? ?4 (24.93) ?????????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0459","{#WMDEVICENAME}":"???? ???????????????? ?5 (24.92) ?????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045A","{#WMDEVICENAME}":"???? ???????????????? ?6 (24.91) ?????????"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045C","{#WMDEVICENAME}":"???? 08 (24.68) ???? SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A045F","{#WMDEVICENAME}":"???? 11 (24.71) ???? SM-300"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0461","{#WMDEVICENAME}":"???? 13 (24.73) ??????? (?????? ??????) SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0462","{#WMDEVICENAME}":"???? 14 (24.74) ??????? (?????? ??????) SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0463","{#WMDEVICENAME}":"???? 15 (24.75) ?????. ?????? SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0464","{#WMDEVICENAME}":"???? 16 (24.76) ?????. ?????? SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C296DDEF311E3A448D44A0465","{#WMDEVICENAME}":"???? 17 (24.77) ?????. ?????? SM-100"},
      {"{#WMDEVICEID}":"0xBF0E000C'
       23786:20140717:164349.001 End of odbc_DBfetch()
       23786:20140717:164349.002 End of get_value_db():SUCCEED
       23786:20140717:164349.002 End of get_value():SUCCEED
      

      But directly in SQL we got this answer:

      I tried to change CHAR(10) just for space, still no luck.

      I included SQL answers from SQL Managment tool for each of query:
      Files:
      good-proxy-MS-SQL-answer.txt
      error-proxy-MS-SQL-answer.txt
      error-proxy-MS-SQL-answer-second-try.txt

      Each SQL answer validated with http://jsonlint.com/ and checked for hiden symbols.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              a.shpak Andrey Shpak
            • Votes:
              2 Vote for this issue
              Watchers:
              9 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: