-
Incident report
-
Resolution: Fixed
-
Major
-
2.2.4
-
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
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.