[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: PNG File postgres.PNG    

 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.
What happens, if you try to convert to UTF8 in sql query?

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"
But history syncer "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!
Zabbix works with utf-8, you need to convert any other encoding before feeding data do zabbix.

Regards,
Andrey

Generated at Wed Jul 16 10:49:52 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.