[ZBX-8489] LLD based on ODBC request answer size limit Created: 2014 Jul 18  Updated: 2017 May 30  Resolved: 2016 Sep 07

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: 2.2.4
Fix Version/s: 2.2.15rc1, 3.0.5rc1, 3.2.0beta2

Type: Incident report Priority: Major
Reporter: Andrey Shpak Assignee: Unassigned
Resolution: Fixed Votes: 2
Labels: limits, odbc
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
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

Attachments: Text File error-proxy-MS-SQL-answer-second-try.txt     Text File error-proxy-MS-SQL-answer.txt     Text File error-proxy-second-try.log     Text File error-proxy.log     Text File good-proxy-MS-SQL-answer.txt     Text File good-proxy.log    
Issue Links:
Duplicate

 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.



 Comments   
Comment by Marc [ 2014 Jul 18 ]

The value gets truncated at 2048 Bytes or characters. Should be documented at least.

Comment by Oleksii Zagorskyi [ 2014 Jul 19 ]

On https://www.zabbix.com/documentation/2.2/manual/discovery/low_level_discovery is a 2048 characters limit mentioned for IBM DB2.
But that's a different case.

We see that value truncated to 2047 bytes! when returned from odbc_DBfetch() function.
Note that the value is 1748 characters! long and contains russian letters.
In the zabbix function I don't see any limitations.

So it's not related to JSON or LLD at all.
Maybe there is a limitation in libodbc?

Could you check returned value using "isql" command line tool ?

Comment by Andrey Shpak [ 2014 Oct 23 ]

Hi! I am now able to return to this server. If i make isql query I got this: 

SQL> 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('')
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| XML_F52E2B61-18A1-11d1-B105-00805F49916B                                                                                                                                                                                                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

But i still do not understand what it means....

Comment by Andrey Shpak [ 2014 Dec 15 ]

Same result on CentOS 7

Comment by Aleksandrs Saveljevs [ 2015 Jan 15 ]

The problem is in odbc_DBselect() function, where we bind all columns to buffers of length MAX_STRING_LEN, which is 2048 bytes.

Comment by Aleksandrs Saveljevs [ 2015 Jan 15 ]

Meanwhile, note that ZBXNEXT-2321 introduces "db.odbc.discovery" item into pre-2.5.0 trunk for an easier way to do low-level discovery through ODBC.

Comment by Andrey Shpak [ 2015 Jan 21 ]

Is db.odbc.discovery will be limited with 2048 characters?

Comment by Alexander Shutov [ 2015 Feb 13 ]

some problem on zabbix 2.4.2 centos 7.1
my query 

"select convert(xml,'{"data":['+ dd + ' ]}') from (  (SELECT STUFF((SELECT ',' + '{"{#DBNAME}":"' + [Name] + '"}' FROM master..sysdatabases ORDER BY [Name] FOR XML PATH('')), 1, 1, '') as dd))   as df"

for LLD Databases MSSQL has truncated and return only 2047 bytes

Comment by Oleksii Zagorskyi [ 2015 Dec 14 ]

See also (2) in ZBX-6590

Comment by Viktors Tjarve [ 2016 Aug 04 ]

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

Comment by Andris Zeila [ 2016 Aug 05 ]

(1) The following code is redundant:

ColLenArray[i] = ALIGNBUF(ColLenArray[i]);

As the buffers are allocated per column (not a single buffer for the whole row) the lenth alignment is not needed.

viktors.tjarve RESOLVED in r62070.

wiper CLOSED

Comment by Andris Zeila [ 2016 Aug 05 ]

(2) Buffer overrun on 64 bit systems:

	ColLenArray = (SQLINTEGER *) zbx_malloc(ColLenArray, pdbh->col_num * sizeof(SQLINTEGER));
	memset(ColLenArray, 0, sizeof(SQLLEN) * (size_t)pdbh->col_num);

SQLINTEGER is defined as int (4 bytes), while SQLLEN is long (8 bytes on 64 bit nix systems).

Also the ColLenArray should be declared as SQLLEN* rather than SQLINTEGER.

viktors.tjarve RESOLVED in r62071.

wiper CLOSED

Comment by Andris Zeila [ 2016 Aug 05 ]

(3) Possible buffer overrun:

		pdbh->row_data[i] = zbx_malloc(pdbh->row_data[i], ColLenArray[i]);
		if (0 != CALLODBC(SQLBindCol(pdbh->hstmt, (SQLUSMALLINT)(i + 1), SQL_C_CHAR, pdbh->row_data[i],
				ColLenArray[i] + 1, &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
				"Cannot bind column in ODBC result"))

The allocated column size is ColLenArray[i], while the size passed to SQLBindCol function is ColLenArray[i] + 1.

viktors.tjarve RESOLVED in r62072.

wiper CLOSED

Comment by Andris Zeila [ 2016 Aug 05 ]

(4) Allocation problem:

	pdbh->row_data = zbx_malloc(pdbh->row_data, ColLenArray[pdbh->col_num - 1] + ALIGNBUF(sizeof(SQLINTEGER)));
	memset(pdbh->row_data, 0, sizeof(char *) * (size_t)pdbh->col_num);

The row_data is an array of columns, so it's size should be sizeof(char *) * pdbh->col_num, like in memset() call. Also the memset is redundant as we allocate all columns right after setting their pointers to 0.

viktors.tjarve RESOLVED in r62075.

wiper CLOSED

Comment by Andris Zeila [ 2016 Aug 31 ]

(5) Please review few code improvements in r62086

viktors.tjarve Looks good. CLOSED

Comment by Andris Zeila [ 2016 Aug 31 ]

Successfully tested

Comment by Viktors Tjarve [ 2016 Aug 31 ]

Released in:

  • pre-2.2.15rc1 r62097
  • pre-3.0.5rc1 r62101
  • pre-3.2.0beta2 r62108
Comment by Alexander Vladishev [ 2016 Sep 04 ]

(6) Documentation must be updated:

viktors.tjarve RESOLVED

sasha Thanks! CLOSED

Comment by Sandis Neilands (Inactive) [ 2016 Sep 27 ]

This correction caused a regression with some UnixODBC drivers (ZBX-11213).

Generated at Fri Apr 19 22:41:28 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.