[ZBX-20152] Special characters recevied via ODBC are stored as ? Created: 2021 Nov 01 Updated: 2022 May 24 Resolved: 2022 May 24 |
|
Status: | Closed |
Project: | ZABBIX BUGS AND ISSUES |
Component/s: | None |
Affects Version/s: | 5.4.6 |
Fix Version/s: | None |
Type: | Problem report | Priority: | Trivial |
Reporter: | Guido | Assignee: | Andrey Tocko (Inactive) |
Resolution: | Won't fix | Votes: | 0 |
Labels: | None | ||
Remaining Estimate: | Not Specified | ||
Time Spent: | Not Specified | ||
Original Estimate: | Not Specified | ||
Environment: |
MSSQL Server 2019 for retreiving the data from, Zabbix Server 5.4.6 with Postgres 13 to store the zabbix data |
Attachments: |
![]() |
Description |
We are getting data from a SQL Server into zabbix via an ODBC connection in the SQL Database the field we retrieve contrains some special (finnish) characters, they seems to be retreived correctly if we check the Zabbix logging but they are stored wrong in the PostgresDB
See logging below 7182:20211101:092132.065 In zbx_ipc_service_recv() timeout:1 7208:20211101:092132.123 zbx_setproctitle() title:'poller #5 [got 0 values in 0.000234 sec, getting values]' 7208:20211101:092132.123 In get_values() 7208:20211101:092132.123 In DCconfig_get_poller_items() poller_type:0 7208:20211101:092132.123 End of DCconfig_get_poller_items():1 7208:20211101:092132.123 In substitute_key_macros_impl() data:'db.odbc.select[guido,CMS]' 7208:20211101:092132.123 End of substitute_key_macros_impl():SUCCEED data:'db.odbc.select[guido,CMS]' 7208:20211101:092132.123 In substitute_simple_macros_impl() data:'select cast (name as nvarchar(max)) collate SQL_Scandinavian_CP850_CI_AS from mhb_own.organisations where organisation_id='DF9A4101D301C160B3189518D00E5A8BF15D911B1FF031FB3AF6E561BD237F30'' 7208:20211101:092132.123 In substitute_simple_macros_impl() data:'sqldbadmin' 7208:20211101:092132.123 In substitute_simple_macros_impl() data:'MySecretPassword' 7208:20211101:092132.123 In get_value() key:'db.odbc.select[guido,CMS]' 7208:20211101:092132.123 In get_value_db() key_orig:'db.odbc.select[guido,CMS]' query:'select cast (name as nvarchar(max)) collate SQL_Scandinavian_CP850_CI_AS from mhb_own.organisations where organisation_id='DF9A4101D301C160B3189518D00E5A8BF15D911B1FF031FB3AF6E561BD237F30'' 7208:20211101:092132.123 In zbx_odbc_connect() dsn:'CMS' user:'mySQLUser' 7178:20211101:092132.135 End of zbx_ipc_service_recv():2 7178:20211101:092132.135 zbx_setproctitle() title:'preprocessing manager #1 [queued 0, processed 0 values, idle 5.005897 sec during 5.005952 sec]' 7178:20211101:092132.135 In zbx_ipc_service_recv() timeout:1 7208:20211101:092132.172 zbx_odbc_connect() connected to Microsoft SQL Server(15.00.2000) using libtdsodbc.so(01.01.0020) 7208:20211101:092132.172 End of zbx_odbc_connect() 7208:20211101:092132.172 In zbx_odbc_select() query:'select cast (name as nvarchar(max)) collate SQL_Scandinavian_CP850_CI_AS from mhb_own.organisations where organisation_id='DF9A4101D301C160B3189518D00E5A8BF15D911B1FF031FB3AF6E561BD237F30'' 7208:20211101:092132.174 selected all 1 columns 7208:20211101:092132.174 End of zbx_odbc_select() 7208:20211101:092132.174 In zbx_odbc_query_result_to_string() 7208:20211101:092132.174 In zbx_odbc_fetch() {color:#ffab00}7208:20211101:092132.174 column #1 value:'Sähkövirst'{color} 7208:20211101:092132.174 End of zbx_odbc_fetch() 7208:20211101:092132.174 End of zbx_odbc_query_result_to_string():SUCCEED 7208:20211101:092132.174 End of get_value_db():SUCCEED 7208:20211101:092132.174 End of get_value():SUCCEED 7208:20211101:092132.174 In zbx_activate_item_interface() interfaceid:0 itemid:38792 type:11 7208:20211101:092132.174 End of zbx_activate_item_interface() 7208:20211101:092132.175 In zbx_preprocess_item_value() 7208:20211101:092132.175 End of zbx_preprocess_item_value() 7208:20211101:092132.175 In zbx_ipc_socket_write() 7208:20211101:092132.175 End of zbx_ipc_socket_write():SUCCEED 7208:20211101:092132.175 End of get_values():1 7208:20211101:092132.175 zbx_setproctitle() title:'poller #5 [got 1 values in 0.052027 sec, idle 5 sec]' 7178:20211101:092132.175 End of zbx_ipc_service_recv():1 7178:20211101:092132.175 In preprocessor_add_request() 7178:20211101:092132.175 In preprocessor_sync_configuration() 7178:20211101:092132.175 In DCconfig_get_preprocessable_items() 7178:20211101:092132.175 End of DCconfig_get_preprocessable_items() items:0 7178:20211101:092132.175 End of preprocessor_sync_configuration() item config size: 0, history cache size: 0 7178:20211101:092132.175 In preprocessor_enqueue() itemid: 38792 7178:20211101:092132.175 In preprocessor_enqueue_dependent() itemid: 38792 7178:20211101:092132.175 End of preprocessor_enqueue_dependent() 7178:20211101:092132.175 End of preprocessor_enqueue() 7178:20211101:092132.175 In preprocessor_assign_tasks() 7178:20211101:092132.175 In preprocessor_get_next_task() 7178:20211101:092132.175 End of preprocessor_get_next_task() 7178:20211101:092132.175 End of preprocessor_assign_tasks() 7178:20211101:092132.175 End of preprocessor_add_request() 7178:20211101:092132.175 In zbx_ipc_service_recv() timeout:1 7188:20211101:092132.400 zbx_setproctitle() title:'http poller #1 [got 0 values in 0.001038 sec, getting values]' 7188:20211101:092132.400 In process_httptests() 7188:20211101:092132.401 query [txnlev:0] [select h.hostid,h.host,h.name,t.httptestid,t.name,t.agent,t.authentication,t.http_user,t.http_password,t.http_proxy,t.retries,t.ssl_cert_file,t.ssl_key_file,t.ssl_key_password,t.verify_peer,t.verify_host,t.delay from httptest t,hosts h where t.hostid=h.hostid and t.nextcheck<=1635758492 and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)] 7188:20211101:092132.401 End of process_httptests() 7188:20211101:092132.401 query [txnlev:0] [select min(t.nextcheck) from httptest t,hosts h where t.hostid=h.hostid and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)] 7188:20211101:092132.402 No httptests to process in get_minnextcheck. 7188:20211101:092132.402 zbx_setproctitle() title:'http poller #1 [got 0 values in 0.000918 sec, idle 5 sec]' 7192:20211101:092132.441 zbx_setproctitle() title:'history syncer #2 [processed 0 values, 0 triggers in 0.000142 sec, syncing history]' 7192:20211101:092132.441 In zbx_sync_history_cache() history_num:1 7192:20211101:092132.441 In DCmass_prepare_history() history_num:1 7192:20211101:092132.441 End of DCmass_prepare_history() 7192:20211101:092132.441 In DBmass_add_history() 7192:20211101:092132.441 In zbx_history_add_values() 7192:20211101:092132.441 query [txnlev:1] [begin;] {color:#ffab00}7192:20211101:092132.441 query [txnlev:1] [insert into history_text (itemid,clock,ns,value) values (38792,1635758492,174978917,'S?hk?virst');{color} {color:#ffab00}]{color} 7192:20211101:092132.441 query [txnlev:1] [commit;] 7192:20211101:092132.444 End of zbx_history_add_values() 7192:20211101:092132.444 End of DBmass_add_history() 7192:20211101:092132.444 In DCmass_update_trends() 7192:20211101:092132.444 End of DCmass_update_trends() 7192:20211101:092132.444 query [txnlev:1] [begin;] 7192:20211101:092132.444 In DBmass_update_items() |
Comments |
Comment by Andrey Tocko (Inactive) [ 2021 Nov 08 ] |
SQL query applies "SQL_Scandinavian_CP850_CI_AS" encoding to output. Zabbix works with UTF8, so it converts it before saving to DB. |
Comment by Guido [ 2021 Nov 08 ] |
We tried to solve the issue by adding this QL_Scandinavian_CP850_CI_A encoding but it did not help, also without the behaviour is exactly the same. It looks also that is is retrieved correct in zabbix if you see the logging 7208:20211101:092132.174 In zbx_odbc_fetch() {color:#ffab00}7208:20211101:092132.174 column #1 value:'Sähkövirst'{color} 7208:20211101:092132.174 End of zbx_odbc_fetch()
But stored wrong
7192:20211101:092132.441 query [txnlev:1] [begin;] {color:#ffab00}7192:20211101:092132.441 query [txnlev:1] insert into history_text (itemid,clock,ns,value) values (38792,1635758492,174978917,'S?hk?virst');{color} {color:#ffab00}{color} 7192:20211101:092132.441 query [txnlev:1] [commit;] |
Comment by Andrey Tocko (Inactive) [ 2021 Nov 08 ] |
Indeed poller saves "Sähkövirst" Show zabbix db encoding and collation in postgres please: \l zabbix |
Comment by Guido [ 2021 Nov 09 ] |
as you can see in the attachment the encoding is UTF-8 |
Comment by Guido [ 2021 Dec 27 ] |
Hello, is there any update on this issue? I see the status is "need info" but what info is still needed?
Br Guido |
Comment by Andrey Tocko (Inactive) [ 2022 May 24 ] |
Hello! Regards, |