[ZBX-24193] ODBC items whose SQL queries with Chinese search conditions return incorrect results after proxy restart Created: 2024 Mar 06  Updated: 2024 Mar 08

Status: Open
Project: ZABBIX BUGS AND ISSUES
Component/s: Proxy (P), Server (S)
Affects Version/s: 6.0.26
Fix Version/s: None

Type: Incident report Priority: Trivial
Reporter: Shuang Fu Assignee: Michal Kudlacz
Resolution: Unresolved Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

RHEL 8.3
odbc driver: msodbcsql18-18.3.2.1-1.x86_64

Attachments: JPEG File 0307-data-item.jpg     JPEG File 0307-isql-cmd.jpg     JPEG File 0307-isql-script.jpg     JPEG File 0307-item-connstring.jpg     JPEG File 0307-restart-proxy.jpg     PNG File charset.png     PNG File data-afterRestart-odbc-chinese.png     PNG File data-afterRestart-odbc-detail.png     PNG File data-afterRestart-odbc-english.png     JPEG File data-beforeRestart-english.jpg     JPEG File data-beforeRestart-odbc-chinese.jpg     JPEG File data-beforeRestart-odbc-detail.jpg     PNG File image-2024-03-07-08-21-23-342.png     PNG File image-2024-03-07-08-21-58-342.png     JPEG File image-2024-03-08-09-43-42-233.png     JPEG File item-odbc-chinese.jpg     JPEG File item-odbc-detail.jpg     JPEG File item-odbc-english.jpg     Text File query_result.log     JPEG File restart-proxy.jpg     JPEG File version-odbc.jpg    

 Description   

Within the first hour after the proxy restarts, ODBC items whose SQL queries with Chinese search conditions return incorrect results.
The targeting database here is MSSQL (Microsoft SQL Server).

Steps to reproduce:

1. To compare query results for the same record, three ODBC items were created, using different SQL query statement:

    Query with Chinese search condition:

select count(*) from ... where [job].[description] like '%无描述%'

    Query with English search condition:

select count(*) from ... where [job].[job_id] like '%-DA86-%'

    Query for content:

select [job].[job_id], [job].[description] from ...

    refer screenshots: item-odbc-chinese.jpg, item-odbc-english.jpg, item-odbc-detail.jpg

 

2. Confirm that the above three items can synchronously get correct query results before the proxy restarts.

    Due to the presence of actual data records, whether using Chinese or English search conditions, the returned row count consistently shows as 1.

    refer screenshots: data-beforeRestart-odbc-chinese.jpg, data-beforeRestart-english.jpg, data-beforeRestart-odbc-detail.jpg

 

3. restart proxy

sudo systemctl restart zabbix-proxy

Result:

  • Item with Chinese search condition, the returned row count sometimes showing as 0 (incorrect result) and other times as 1.
  • Item with English search condition, the returned row count consistently shows as 1. 
  • Item with content consistently shows the presence of record.

  refer screenshots: data-afterRestart-odbc-chinese.png, data-afterRestart-odbc-english.png, data-afterRestart-odbc-detail.png

 

Expected:

  In our environment, business monitoring usually involves Chinese search conditions, and it is essential that the returned results are accurate. 

 Comments   
Comment by Michal Kudlacz [ 2024 Mar 07 ]

Hello!

Thank you for contacting Zabbix support and your well-detailed problem description!

Comparison of your queries in both languages shows they work on two different columns:

I'd recommend setting consistent character set for all database items, matching the OS.

It will likely be that i.e. your UnixODBC driver uses ISO-8859-1 encoding while you use UTF-8 everywhere else. In this case, you'd probably need to recompile it.

Some checks to do - please adjust paths if locations differ

grep -i charset /etc/odbc.ini /etc/odbcinst.ini

echo $LANG $LC_ALL $LC_CTYPE

Kind regards,
Michal

Comment by Shuang Fu [ 2024 Mar 07 ]

Hello,

Thank you for your reply!
It seems that the character set is not explicitly specified in the odbc.ini and odbcinst.ini files. 
The character set used by the OS is zh_CN.utf8.

As you recommend setting consistent character set for all database items, what other configurations need to be confirmed?

Additional test results to share:

  • The issue only occurs after the proxy restarts and lasts for approximately half an hour; then the Chinese queries work fine again;
  • Currently, it appears that this issue is specific to MSSQL; as there is no such issue with PostgreSQL.

 

Best Regards,

Shuang Fu

Comment by Shuang Fu [ 2024 Mar 08 ]

To determine which component(zabbix or odbc driver) caused the issue, I took another comparative test:

1. Create odbc item and ISQL script, with the same Chinese query statement.

    odbc item (refer 0307-item-connstring.jpg)

    isql script (refer 0307-isql-script.jpg)

2. Confirm that the odbc item and isql script can synchronously get correct query results before the proxy restarts.

    Both returned row count consistently shows as 1.

3. restart proxy at 2024-03-07 17:52 (refer 0307-restart-proxy.jpg)

4. Compare the query results 

   odbc item: the returned row count sometimes showing as 0 (incorrect result) and other times as 1.

   
   isql script: the returned row count consistently shows as 1.  query_result.log

   

Based on the test results, it seems that there is no issue with the ODBC driver regarding character sets. The problem appears to be on the Zabbix side.

Generated at Fri Apr 04 14:46:12 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.