|
I forgot to put logs recorde in zabbix_server.log:
here what I found:
Thank you.
|
|
What do you mean by "not working"? What is the result of isql query?
|
|
Sorry for not specifying better. I just receive an error on the format returned by query. The log written are similar to the following:
Keep in mind that in all my sql I fetch to return just integer values.
Before upgrading zabbix I also updated the system using yum update;
|
|
isql launched under user zabbix returns correct values:
|
|
Looks like it is encoding-related...
Just checking... Same versions of UnixODBC and Oracle ODBC driver for 3.0 and 3.2?
|
|
Well I haven't changed Oracle ODBC version which is the same as previous installation:
this is the contents of odbcinst.ini:
[Oracle12c]
Description=ODBC driver per Oracle
Driver64=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
FileUsage=1
Driver Logging=7
and this is the content of odbc.ini:
[RACDB1]
Driver = Oracle12c
Trace = yes
ServerName = RACDB1
Tracefile = /tmp/odbc_oracle_racdb_dns.log
Unfortunately that trace file does not exist
|
|
Can you run isql as zabbix user? What are locale settings for zabbix user?
I can see only one change related to ODBC in ChangeLog between 3.0.4 and 3.2.0, namely ZBX-8489. But I can't see how it can be related.
|
|
Sure:
I also launched zabbix_server using command line and not as a service using systemctl but I have the same results.
the following are some commands:
|
|
Thank you for the information!
This line is suspicious:
I'm not an expert in Oracle configuration, but shouldn't NLS_LANG variable be set for correct results?
Could you provide us with a piece of log file where Zabbix performs ODBC item polling at DebugLevel=4? (You can enable it specifically for unreachable poller at runtime.)
|
|
Sorry can you explain better where should I enable that setting? Is it a zabbix related setting or ODBC related? and which logfile? Zabbix?
Thank you
|
|
NLS_LANG is environment variable from which Oracle derives locale settings.
Yes, Zabbix log file. Either set DebugLevel=4 in configuration file or use zabbix_server -R log_level_increase="unreachable poller" (see section "Runtime control" here for details).
|
|
I have exactly same issue, and the strange thing is: PostgreSQl ODBC items fails with same error.
|
|
Hello,
I was able to reproduce the bug.
The same db (Oracle 11.2.0.4), the same query.
Before upgrade (zabbix 3.0.3):
2511:20160916:140622.057 In substitute_key_macros() data:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
2511:20160916:140622.058 In substitute_simple_macros() data:'{HOST.HOST}'
2511:20160916:140622.058 End substitute_simple_macros() data:'ora.zabbix.example.com_zabdb'
2511:20160916:140622.058 End of substitute_key_macros():SUCCEED data:'db.odbc.select[host.cpu.pct,ora.zabbix.example.com_zabdb]'
2511:20160916:140622.058 In substitute_simple_macros() data:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
2511:20160916:140622.058 In substitute_simple_macros() data:'{$ORA_USER}'
2511:20160916:140622.058 In DCget_user_macro() macro:'{$ORA_USER}'
2511:20160916:140622.058 End of DCget_user_macro()
2511:20160916:140622.058 End substitute_simple_macros() data:'user'
2511:20160916:140622.058 In substitute_simple_macros() data:'{$ORA_PASS}'
2511:20160916:140622.058 In DCget_user_macro() macro:'{$ORA_PASS}'
2511:20160916:140622.058 End of DCget_user_macro()
2511:20160916:140622.058 End substitute_simple_macros() data:'password'
2511:20160916:140622.058 In get_value() key:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
2511:20160916:140622.058 In get_value_db() key_orig:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
2511:20160916:140622.058 In db_odbc_select() query:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
2511:20160916:140622.058 In odbc_DBconnect() db_dsn:'ora.zabbix.example.com_zabdb' user:'user'
2511:20160916:140622.084 End of odbc_DBconnect():SUCCEED
2511:20160916:140622.084 In odbc_DBselect() query:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
2511:20160916:140622.085 odbc_DBselect() selected 1 columns
2511:20160916:140622.085 End of odbc_DBselect()
2511:20160916:140622.085 In odbc_DBfetch()
2511:20160916:140622.087 odbc_DBfetch() fetched [0 col]: '15.3513971210838'
2511:20160916:140622.087 End of odbc_DBfetch()
2511:20160916:140622.089 End of db_odbc_select():SUCCEED
2511:20160916:140622.089 End of get_value_db():SUCCEED
2511:20160916:140622.089 End of get_value():SUCCEED
2511:20160916:140622.089 In activate_host() hostid:10108 itemid:345412 type:11
2511:20160916:140622.089 End of activate_host()
2511:20160916:140622.089 End of get_values():1
After upgrade (zabbix 3.2.0):
17007:20160916:143334.579 In substitute_key_macros() data:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
17007:20160916:143334.579 In substitute_simple_macros() data:'{HOST.HOST}'
17007:20160916:143334.579 End substitute_simple_macros() data:'ora.zabbix.example.com_zabdb'
17007:20160916:143334.579 End of substitute_key_macros():SUCCEED data:'db.odbc.select[host.cpu.pct,ora.zabbix.example.com_zabdb]'
17007:20160916:143334.579 In substitute_simple_macros() data:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
17007:20160916:143334.579 In substitute_simple_macros() data:'{$ORA_USER}'
17007:20160916:143334.579 In DCget_user_macro() macro:'{$ORA_USER}'
17007:20160916:143334.579 End of DCget_user_macro()
17007:20160916:143334.579 End substitute_simple_macros() data:'user'
17007:20160916:143334.579 In substitute_simple_macros() data:'{$ORA_PASS}'
17007:20160916:143334.579 In DCget_user_macro() macro:'{$ORA_PASS}'
17007:20160916:143334.579 End of DCget_user_macro()
17007:20160916:143334.579 End substitute_simple_macros() data:'password'
17007:20160916:143334.579 In get_value() key:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
17007:20160916:143334.579 In get_value_db() key_orig:'db.odbc.select[host.cpu.pct,{HOST.HOST}]'
17007:20160916:143334.579 In db_odbc_select() query:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
17007:20160916:143334.579 In odbc_DBconnect() db_dsn:'ora.zabbix.example.com_zabdb' user:'user'
17007:20160916:143334.655 End of odbc_DBconnect():SUCCEED
17007:20160916:143334.655 In odbc_DBselect() query:'select value from v$sysmetric where metric_name = 'Host CPU Utilization (%)' and group_id=2;'
17007:20160916:143334.657 odbc_DBselect() selected 1 columns
17007:20160916:143334.657 End of odbc_DBselect()
17007:20160916:143334.657 In odbc_DBfetch()
17007:20160916:143334.665 odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:0 err_msg:'[Oracle][ODBC]String data, right truncated.'
17007:20160916:143334.666 odbc_DBfetch() fetched [0 col]: ▒▒d<'
17007:20160916:143334.666 End of odbc_DBfetch()
17007:20160916:143334.669 End of db_odbc_select():NOTSUPPORTED
17007:20160916:143334.669 End of get_value_db():NOTSUPPORTED
17007:20160916:143334.669 Item [ora.zabbix.example.com_zabdb:db.odbc.select[host.cpu.pct,{HOST.HOST}]] error: Received value ??d<] is not suitable for value type [Numeric (float)]
17007:20160916:143334.669 End of get_value():NOTSUPPORTED
Setting NLS_LANG produces no effect. IMHO it wasn't supposed to, since we were asking for numeric data.
As far as I could dig out, reversing the following change at odbc_DBselect (src/libs/zbxdbhigh/odbc.c) helps to get rid of the problem:
< MAX_STRING_LEN, &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
---
> pdbh->data_len[i], &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
Seems like Oracle (and possibly PostgreSQL) ODBC driver sets buffer length to 0 during SQLColAttribute(..., SQL_DESC_OCTET_LENGTH, ...), which is incorrect and leaves no buffer to fit selected value in.
MySQL driver behaves better and sets buffer length to non-zero value.
|
|
Dear akint, huge thanks for the logs! Now it's clear that we allocate less space that is actually needed for column contents. Looks like SQLColAttribute() with SQL_DESC_OCTET_LENGTH parameter returns required size without null-terminator (some docs).
Can you give this simple patch a try?
|
Seems like Oracle (and possibly PostgreSQL) ODBC driver sets buffer length to 0 during SQLColAttribute(..., SQL_DESC_OCTET_LENGTH, ...), which is incorrect and leaves no buffer to fit selected value in.
Really? This is not nice... Can you point me to their documentation or bug tracker or wherever this information comes from?
|
|
Hello,
concerning the following line:
SQLRowCount returns -1
it returns the same values in zabbix 3.0.4 using isql.
That "SQLRowCount returns -1" returns even if I select/fetch many rows like this:
I don't know if this information might be helpful or not.
regards.
|
|
glebs.ivanovskis, the patch you provided did not work:
12948:20160919:140029.776 In substitute_key_macros() data:'db.odbc.select[test,{HOST.HOST}]'
12948:20160919:140029.776 In substitute_simple_macros() data:'{HOST.HOST}'
12948:20160919:140029.776 End substitute_simple_macros() data:'Oracle'
12948:20160919:140029.776 End of substitute_key_macros():SUCCEED data:'db.odbc.select[test,Oracle]'
12948:20160919:140029.776 In substitute_simple_macros() data:'select 1 from dual '
12948:20160919:140029.776 End substitute_simple_macros() data:'select 1 from dual '
12948:20160919:140029.776 In substitute_simple_macros() data:EMPTY
12948:20160919:140029.776 In substitute_simple_macros() data:EMPTY
12948:20160919:140029.776 In get_value() key:'db.odbc.select[test,{HOST.HOST}]'
12948:20160919:140029.776 In get_value_db() key_orig:'db.odbc.select[test,{HOST.HOST}]'
12948:20160919:140029.776 In db_odbc_select() query:'select 1 from dual '
12948:20160919:140029.777 In odbc_DBconnect() db_dsn:'Oracle' user:''
12948:20160919:140029.807 End of odbc_DBconnect():SUCCEED
12948:20160919:140029.807 In odbc_DBselect() query:'select 1 from dual '
12948:20160919:140029.808 odbc_DBselect() selected 1 columns
12948:20160919:140029.808 End of odbc_DBselect()
12948:20160919:140029.808 In odbc_DBfetch()
12948:20160919:140029.809 odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:0 err_msg:'[Oracle][ODBC]String data, right truncated.'
12948:20160919:140029.809 odbc_DBfetch() fetched [0 col]: '▒▒▒d<'
12948:20160919:140029.809 End of odbc_DBfetch()
12948:20160919:140029.811 End of db_odbc_select():NOTSUPPORTED
12948:20160919:140029.811 End of get_value_db():NOTSUPPORTED
12948:20160919:140029.811 Item [Oracle:db.odbc.select[test,{HOST.HOST}]] error: Received value [???d<] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
12948:20160919:140029.811 End of get_value():NOTSUPPORTED
12947:20160919:140228.908 In substitute_key_macros() data:'db.odbc.select[test,{HOST.HOST}]'
12947:20160919:140228.908 In substitute_simple_macros() data:'{HOST.HOST}'
12947:20160919:140228.908 End substitute_simple_macros() data:'PostgreSQL'
12947:20160919:140228.908 End of substitute_key_macros():SUCCEED data:'db.odbc.select[test,PostgreSQL]'
12947:20160919:140228.908 In substitute_simple_macros() data:'select 1 '
12947:20160919:140228.908 End substitute_simple_macros() data:'select 1 '
12947:20160919:140228.909 In substitute_simple_macros() data:EMPTY
12947:20160919:140228.909 In substitute_simple_macros() data:EMPTY
12947:20160919:140228.909 In get_value() key:'db.odbc.select[test,{HOST.HOST}]'
12947:20160919:140228.909 In get_value_db() key_orig:'db.odbc.select[test,{HOST.HOST}]'
12947:20160919:140228.910 In db_odbc_select() query:'select 1 '
12947:20160919:140228.911 In odbc_DBconnect() db_dsn:'PostgreSQL' user:''
12947:20160919:140228.928 End of odbc_DBconnect():SUCCEED
12947:20160919:140228.928 In odbc_DBselect() query:'select 1 '
12947:20160919:140228.929 odbc_DBselect() selected 1 columns
12947:20160919:140228.929 End of odbc_DBselect()
12947:20160919:140228.929 In odbc_DBfetch()
12947:20160919:140228.929 odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:-2 err_msg:'Fetched item was truncated.'
12947:20160919:140228.929 odbc_DBfetch() fetched [0 col]: '<D8><FE><98>d<'
12947:20160919:140228.929 End of odbc_DBfetch()
12947:20160919:140228.929 End of db_odbc_select():NOTSUPPORTED
12947:20160919:140228.930 End of get_value_db():NOTSUPPORTED
12947:20160919:140228.930 Item [PostgreSQL:db.odbc.select[test,{HOST.HOST}]] error: Received value [???d<] is not suitable for value type [Numeric (unsigned)] and data type [Decimal]
12947:20160919:140228.930 End of get_value():NOTSUPPORTED
|
Really? This is not nice... Can you point me to their documentation or bug tracker or wherever this information comes from?
glebs.ivanovskis, I have slightly modified odbc_DBselect for debugging purposes:
diff --git a/src/libs/zbxdbhigh/odbc.c b/src/libs/zbxdbhigh/odbc.c
index 9678bb9..1db3e6d 100644
--- a/src/libs/zbxdbhigh/odbc.c
+++ b/src/libs/zbxdbhigh/odbc.c
@@ -281,7 +281,7 @@ ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, char *query)
ZBX_ODBC_RESULT result = NULL;
SQLRETURN rc;
- zabbix_log(LOG_LEVEL_DEBUG, "In %s() query:'%s'", __function_name, query);
+ zabbix_log(LOG_LEVEL_INFORMATION, "In %s() query:'%s'", __function_name, query);
clean_odbc_strerror();
@@ -309,6 +309,7 @@ ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, char *query)
{
goto end;
}
+ zabbix_log(LOG_LEVEL_INFORMATION, "%s() data_len[%i]'s value after SQLColAttribute = %i", __function_name, i, pdbh->data_len[i]);
}
pdbh->row_data = zbx_malloc(pdbh->row_data, sizeof(char *) * (size_t)pdbh->col_num);
@@ -317,11 +318,13 @@ ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, char *query)
{
pdbh->row_data[i] = zbx_malloc(NULL, pdbh->data_len[i]);
if (0 != CALLODBC(SQLBindCol(pdbh->hstmt, (SQLUSMALLINT)(i + 1), SQL_C_CHAR, pdbh->row_data[i],
- pdbh->data_len[i], &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
+ MAX_STRING_LEN, &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
"Cannot bind column in ODBC result"))
{
goto end;
}
+ zabbix_log(LOG_LEVEL_INFORMATION, "%s() data_len[%i]'s value after SQLBindCol = %i", __function_name, i, pdbh->data_len[i]);
+ zabbix_log(LOG_LEVEL_INFORMATION, "%s() row_data[%i]'s actual length = %i", __function_name, i, sizeof(pdbh->row_data[i]));
}
zabbix_log(LOG_LEVEL_DEBUG, "%s() selected %i columns", __function_name, pdbh->col_num);
After that I got the following messages in logs:
9259:20160919:124148.374 In odbc_DBselect() query:'select 1 '
9259:20160919:124148.375 odbc_DBselect() data_len[0]'s value after SQLColAttribute = 0
9259:20160919:124148.379 odbc_DBselect() data_len[0]'s value after SQLBindCol = 0
9259:20160919:124148.379 odbc_DBselect() row_data[0]'s actual length = 8
9263:20160919:124148.404 In odbc_DBselect() query:'select 1 from dual '
9263:20160919:124148.405 odbc_DBselect() data_len[0]'s value after SQLColAttribute = 20
9263:20160919:124148.405 odbc_DBselect() data_len[0]'s value after SQLBindCol = 20
9263:20160919:124148.405 odbc_DBselect() row_data[0]'s actual length = 8
9261:20160919:124148.409 In odbc_DBselect() query:'select 1 from dual '
9261:20160919:124148.410 odbc_DBselect() data_len[0]'s value after SQLColAttribute = 0
9261:20160919:124148.410 odbc_DBselect() data_len[0]'s value after SQLBindCol = 0
9261:20160919:124148.410 odbc_DBselect() row_data[0]'s actual length = 8
The only thing I could found about SQL_DESC_OCTET_LENGTH is this post:
https://community.oracle.com/message/4470785#4470785
I had to change PostgreSQL driver (default unixODBC driver returned incorrect "SQL_DESC_OCTET_LENGTH" attribute for VARCHARS - 0 instead of actual length)...
|
|
Dear akint, this is very valuable information! As you can see, the problem is not entirely on Zabbix side. Please consider filing bug reports for Oracle and PostgreSQL ODBC driver maintainers. This is sad, but I guess we need to allocate the minimum of MAX_STRING_LEN and SQLColAttribute(..., SQL_DESC_OCTET_LENGTH, ...) to work around such driver behaviour.
|
|
Could this also be the reason for this problem: https://www.zabbix.com/forum/showthread.php?p=189369 ?
|
|
Yes, looks like so.
|
|
Hi all, for information, I have this issue with odbc request on an Oracle 11 db server. This issue sometimes freeze zabbix server, I activate debug log, but nothing more than de the log in this bug report. I disable all odbc item and I don't have freeze anymore...
|
|
Hello, glebs.ivanovskis
This looks like the same problem with use Zabbix 3.2.0 (unixODBC + FreeTDS):
1. /etc/odbcinst.ini
[FreeTDS]
Description=TDS driver (Sybase/MS SQL)
Driver=libtdsodbc.so
Setup=libtdsS.so
CPTimeout=
CPReuse=
UsageCount=4
2. Check connect
isql -v smv ZabbixMonitoring Passwd
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select sum(PostsCount) as PostsCount from [Buzz_Aggregates2].[dbo].[PostLimits] (nolock) where UserId = 1 and ReportId = 282 and [Timestamp] > dateadd(hour, -1, getutcdate())
+------------+
| PostsCount |
+------------+
| 2110 |
+------------+
3. Debug info from zabbix_server.log
43914:20160920:180729.745 In substitute_simple_macros() data:'select sum(PostsCount) as PostsCount from [Buzz_Aggregates2].[dbo].[PostLimits] (nolock) where UserId = 1 and ReportId = 282 and [Timestamp] > dateadd(hour, -1, getutcdate())'
43914:20160920:180729.745 In substitute_simple_macros() data:'{$ZABBIX_L}'
43914:20160920:180729.745 In DCget_user_macro() macro:'{$ZABBIX_L}'
43914:20160920:180729.745 End of DCget_user_macro()
43914:20160920:180729.746 End substitute_simple_macros() data:'ZabbixMonitoring'
43914:20160920:180729.746 In substitute_simple_macros() data:'{$ZABBIX_P}'
43914:20160920:180729.746 In DCget_user_macro() macro:'{$ZABBIX_P}'
43914:20160920:180729.746 End of DCget_user_macro()
43914:20160920:180729.746 End substitute_simple_macros() data:'Passwd'
43914:20160920:180729.746 In get_value() key:'db.odbc.select[BzzUserDelay3,{$BLOGANSPRODDSN}]'
43914:20160920:180729.746 In get_value_db() key_orig:'db.odbc.select[BzzUserDelay3,{$BLOGANSPRODDSN}]'
43914:20160920:180729.746 In db_odbc_select() query:'select sum(PostsCount) as PostsCount from [Buzz_Aggregates2].[dbo].[PostLimits] (nolock) where UserId = 1 and ReportId = 282 and [Timestamp] > dateadd(hour, -1, getutcdate())'
43914:20160920:180729.746 In odbc_DBconnect() db_dsn:'smv' user:'ZabbixMonitoring'
43914:20160920:180729.780 End of odbc_DBconnect():SUCCEED
43914:20160920:180729.780 In odbc_DBselect() query:'select sum(PostsCount) as PostsCount from [Buzz_Aggregates2].[dbo].[PostLimits] (nolock) where UserId = 1 and ReportId = 282 and [Timestamp] > dateadd(hour, -1, getutcdate())'
43914:20160920:180729.794 odbc_DBselect() selected 1 columns
43914:20160920:180729.794 End of odbc_DBselect()
43914:20160920:180729.794 In odbc_DBfetch()
43914:20160920:180729.794 odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:0 err_msg:'[FreeTDS][SQL Server]Data truncated'
43914:20160920:180729.794 odbc_DBfetch() fetched [0 col]: '211'
43914:20160920:180729.794 End of odbc_DBfetch()
43914:20160920:180729.796 End of db_odbc_select():SUCCEED
43914:20160920:180729.796 End of get_value_db():SUCCEED
43914:20160920:180729.796 End of get_value():SUCCEED
43914:20160920:180729.796 In activate_host() hostid:10547 itemid:46889 type:11
43914:20160920:180729.796 End of activate_host()
43914:20160920:180729.796 End of get_values():1
43914:20160920:180729.796 In get_values()
43914:20160920:180729.796 In DCconfig_get_poller_items() poller_type:0
43914:20160920:180729.797 End of DCconfig_get_poller_items():1
|
|
Dear ericm, I think your issue is a bit different. Please consider creating a separate bug report.
Dear idinmukhametov, thank you for sharing this information. Seems that in your case situation is not that bad (only the last character is truncated) and a simple fix I posted earlier can solve your problem.
|
|
Dear glebs.ivanovskis
Unfortunately, in my case, all values are truncated to three characters.
And all with an error:
odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:0 err_msg:'[FreeTDS][SQL Server]Data truncated'
tail -f zabbix_server.log | grep odbc_DBfetch
7234:20160921:162130.934 odbc_DBfetch() fetched [0 col]: '290'
7234:20160921:162130.934 End of odbc_DBfetch()
7237:20160921:162140.317 In odbc_DBfetch()
7237:20160921:162140.317 odbc_DBfetch() fetched [0 col]: '185'
7237:20160921:162140.317 End of odbc_DBfetch()
7236:20160921:162149.118 In odbc_DBfetch()
7236:20160921:162149.118 odbc_DBfetch() fetched [0 col]: '643'
7236:20160921:162149.118 End of odbc_DBfetch()
7238:20160921:162208.326 In odbc_DBfetch()
7238:20160921:162208.326 odbc_DBfetch() fetched [0 col]: '140'
7238:20160921:162208.326 End of odbc_DBfetch()
7237:20160921:162208.983 In odbc_DBfetch()
7237:20160921:162208.983 odbc_DBfetch() fetched [0 col]: '366'
7237:20160921:162208.984 End of odbc_DBfetch()
7234:20160921:162211.054 In odbc_DBfetch()
7234:20160921:162211.054 odbc_DBfetch() fetched [0 col]: '291'
7234:20160921:162211.055 End of odbc_DBfetch()
7236:20160921:162211.349 In odbc_DBfetch()
7236:20160921:162211.349 odbc_DBfetch() fetched [0 col]: '419'
7236:20160921:162211.349 End of odbc_DBfetch()
7238:20160921:162212.128 In odbc_DBfetch()
7238:20160921:162212.128 odbc_DBfetch() fetched [0 col]: '116'
7238:20160921:162212.128 End of odbc_DBfetch()
7236:20160921:162216.581 In odbc_DBfetch()
7236:20160921:162216.581 odbc_DBfetch() fetched [0 col]: '328'
7236:20160921:162216.581 End of odbc_DBfetch()
7234:20160921:162217.624 In odbc_DBfetch()
7234:20160921:162217.624 odbc_DBfetch() fetched [0 col]: '204'
|
|
Dear idinmukhametov, so far you've shown that 4 character result "2110" gets truncated to 3 character "211" and that other (unknown to me) results get truncated to 3 characters too. To prove that I am wrong regarding last character truncation, please provide an example where more that 4 character result gets truncated to 3 characters by Zabbix.
|
|
Dear glebs.ivanovskis
root@:~# isql -v pzmvblog ZabbixMonitoring Pass
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select [Value] = Cnt from [ADMIN].dbo.PostDeleteForZabbix
+------------+
| Value |
+------------+
| 25478790 |
+------------+
SQLRowCount returns 1
1 rows fetched
26975:20160921:170859.007 In get_value() key:'db.odbc.select[GetPrizmaPostForDelete,pzmvblog]'
26975:20160921:170859.007 In get_value_db() key_orig:'db.odbc.select[GetPrizmaPostForDelete,pzmvblog]'
26975:20160921:170859.007 In db_odbc_select() query:'select [Value] = Cnt from [ADMIN].dbo.PostDeleteForZabbix'
26975:20160921:170859.007 In odbc_DBconnect() db_dsn:'pzmvblog' user:'ZabbixMonitoring'
26975:20160921:170859.066 End of odbc_DBconnect():SUCCEED
26975:20160921:170859.066 In odbc_DBselect() query:'select [Value] = Cnt from [ADMIN].dbo.PostDeleteForZabbix'
26975:20160921:170859.071 odbc_DBselect() selected 1 columns
26975:20160921:170859.071 End of odbc_DBselect()
26975:20160921:170859.071 In odbc_DBfetch()
26975:20160921:170859.071 odbc_Diag(): rc_msg:'SQL_SUCCESS_WITH_INFO' rec_nr:1 sql_state:'01004' native_err_code:0 err_msg:'[FreeTDS][SQL Server]Data truncated'
26975:20160921:170859.071 odbc_DBfetch() fetched [0 col]: '254'
26975:20160921:170859.071 End of odbc_DBfetch()
26975:20160921:170859.075 End of db_odbc_select():SUCCEED
26975:20160921:170859.075 End of get_value_db():SUCCEED
26975:20160921:170859.075 End of get_value():SUCCEED
|
|
Thank you!
|
|
Dear glebs.ivanovskis,
It makes sense to wait for a patch to this problem in the near future?
|
|
I am attaching the patch which reverts destructive ZBX-8489 changes (svn merge -c -62108 ^/trunk). As to packages, I am almost sure that this issue will be fixed in 3.2.1, but I have no information when it is going to be released.
|
|
glebs.ivanovskis, I have done some more digging, hopefully it could save some time for you.
First of all, documentation of ODBC says:
https://msdn.microsoft.com/en-us/library/ms712499(v=vs.85).aspx
They [the values for transfer octet length] come from the SQL_DESC_OCTET_LENGTH of a field of a descriptor for all character and binary types. There is no descriptor field that holds this information for other types.
https://msdn.microsoft.com/en-us/library/ms711786(v=vs.85).aspx
The column (or parameter) size of numeric data types is defined as the maximum number of digits used by the data type of the column or parameter, or the precision of the data. For character types, this is the length in characters of the data; for binary data types, column size is defined as the length in bytes of the data.
So it seems that returning zero as SQL_DESC_OCTET_LENGTH for numeric types is a correct behaviour. But even for those, who returns actual size in bytes, I suppose, the length of character representation should be taken, since all columns are bound as an SQL_C_CHAR.
I have done some tests:
- For all databases I created table (test_var varchar, test_int int);
- Wrote a simple piece of code that asks the following attributes for all columns of all databases:
- SQL_DESC_OCTET_LENGTH and SQL_DESC_PRECISION via SQLColAttribute;
- column size via SQLDescribeCol
- It gave me the following results:
Using DSN MySQL
column[0], type: varchar
SQL_DESC_OCTET_LENGTH: 10242
SQL_DESC_PRECISION: 0
size via SQLDescribeCol: 10241
column[1], type: integer
SQL_DESC_OCTET_LENGTH: 4
SQL_DESC_PRECISION: 10
size via SQLDescribeCol: 10
Using DSN Oracle
column[0], type: VARCHAR2
SQL_DESC_OCTET_LENGTH: 255
SQL_DESC_PRECISION: 0
size via SQLDescribeCol: 255
column[1], type: DECIMAL
SQL_DESC_OCTET_LENGTH: 0
SQL_DESC_PRECISION: 38
size via SQLDescribeCol: 38
Using DSN PostgreSQL
column[0], type: varchar
SQL_DESC_OCTET_LENGTH: 255
SQL_DESC_PRECISION: 0
size via SQLDescribeCol: 255
column[1], type: int4
SQL_DESC_OCTET_LENGTH: 4
SQL_DESC_PRECISION: 0
size via SQLDescribeCol: 10
A bit strange, that PostgreSQL didn't give precision for int4 type, but it seems to me that SQLDescribeCol probably may be considered as a source of column's size.
I have tested it like this and it seems to be working for me:
diff --git a/src/libs/zbxdbhigh/odbc.c b/src/libs/zbxdbhigh/odbc.c
index 9678bb9..a666c81 100644
--- a/src/libs/zbxdbhigh/odbc.c
+++ b/src/libs/zbxdbhigh/odbc.c
@@ -303,8 +303,8 @@ ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, char *query)
for (i = 0; i < pdbh->col_num; i++)
{
- if (0 != CALLODBC(SQLColAttribute(pdbh->hstmt, (SQLUSMALLINT)(i + 1), SQL_DESC_OCTET_LENGTH, NULL, 0,
- NULL, &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
+ if (0 != CALLODBC(SQLDescribeCol(pdbh->hstmt, (SQLUSMALLINT)(i + 1), NULL, 0,
+ NULL, NULL, &pdbh->data_len[i], NULL, NULL), rc, SQL_HANDLE_STMT, pdbh->hstmt,
"Cannot execute ODBC query"))
{
goto end;
@@ -315,7 +315,7 @@ ZBX_ODBC_RESULT odbc_DBselect(ZBX_ODBC_DBH *pdbh, char *query)
for (i = 0; i < pdbh->col_num; i++)
{
- pdbh->row_data[i] = zbx_malloc(NULL, pdbh->data_len[i]);
+ pdbh->row_data[i] = zbx_malloc(NULL, ++pdbh->data_len[i]);
if (0 != CALLODBC(SQLBindCol(pdbh->hstmt, (SQLUSMALLINT)(i + 1), SQL_C_CHAR, pdbh->row_data[i],
pdbh->data_len[i], &pdbh->data_len[i]), rc, SQL_HANDLE_STMT, pdbh->hstmt,
"Cannot bind column in ODBC result"))
|
|
I tried using SQLDescribeCol with larger data (80k) and it still returned 8190 column size. But we could use SQLGetData to retrieve data after fetching rather than binding columns.
wiper Apparently it's not so simple. While using SQLGetData with SQL_C_CHAR type worked as expected with postgresql, mysql decided that converting text data to hex format is a good idea.
|
|
Fixed in development branch svn://svn.zabbix.com/branches/dev/ZBX-11213
|
|
The correction seems reasonable. We encourage proactive testing by community since there is still a risk that there are differing interpretations of the ODBC API (which admittedly is quite wide).
As a mitigation for this risk (e.g. workaround) we could introduce hidden (by default) configuration parameters:
- use isql binary directly instead of ODBC API;
- user configurable maximum field width (e.g. revert changes for
ZBX-8489 and use user-configured size in SQLBindCol() instead of MAX_STRING_LEN).
ZBXNEXT-3461
glebs.ivanovskis FYI isql uses the following logic:
static SQLUINTEGER
OptimalDisplayWidth( SQLHSTMT hStmt, SQLINTEGER nCol, int nUserWidth )
{
SQLUINTEGER nLabelWidth = 10;
SQLULEN nDataWidth = 10;
SQLUINTEGER nOptimalDisplayWidth = 10;
SQLCHAR szColumnName[MAX_DATA_WIDTH+1];
*szColumnName = '\0';
SQLColAttribute( hStmt, nCol, SQL_DESC_DISPLAY_SIZE, NULL, 0, NULL, (SQLLEN*)&nDataWidth );
SQLColAttribute( hStmt, nCol, SQL_DESC_LABEL, szColumnName, sizeof(szColumnName), NULL, NULL );
nLabelWidth = strlen((char*) szColumnName );
/*
* catch sqlserver var(max) types
*/
if ( nDataWidth == 0 ) {
nDataWidth = max_col_size;
}
nOptimalDisplayWidth = max( nLabelWidth, nDataWidth );
if ( nUserWidth > 0 )
nOptimalDisplayWidth = min( nOptimalDisplayWidth, nUserWidth );
if ( nOptimalDisplayWidth > max_col_size )
nOptimalDisplayWidth = max_col_size;
return nOptimalDisplayWidth;
}
|
|
Tested.
|
|
svn://svn.zabbix.com/branches/dev/ZBX-11213 contains the fix for v2.2. Am I right to guess that I have to wait until 3.2.1rc2 or 3.2.1 is released to test the fix on v3.2 database?
sandis.neilands The correction will be merged to 3.0, 3.2, trunk branches shortly (you'll see message in this thread from wiper when this is done). If you compile from sources you don't have to wait until the official releases.
|
|
When will this problem be solved?
|
|
Released in:
- pre-2.2.16rc1 r62900
- pre-3.0.6rc1 r62901
- pre-3.2.2rc1 r62902
- pre-3.3.0 r62903
|
|
Thank you. And when the deb package will be made?
http://repo.zabbix.com/zabbix/3.2/debian/pool/main/z/zabbix/
|
|
The fix is commited to the source brances, but will be included only in next releases.
|
|
Could anyone tell me how to fix it by myself? Change code at somewhere?
Thanks a lot.
|
|
mark.chan, you might want to discuss that in the channels listed at http://zabbix.org/wiki/Getting_help
|
|
Thank you richlv, however my computer cannot connect to the channels=.="
|
|
This problem exists in version 3.0.5 (when using MSSQL):
[SQL_ERROR] : [22003] [0] [Microsoft] [ODBC Driver 13 for SQL Server] Numeric value out of ] |
|
|
[SQL_ERROR] : [22003] [0] [Microsoft] [ODBC Driver 13 for SQL Server] Numeric value out of ]
zabbix_proxy (Zabbix) 3.0.5
Revision 62889 30 September 2016
proxy does not accept a value more than 1000 (when using MSSQL)
|
|
Yes,
Released in pre-3.0.6rc1 r62901
means that the fix was merged in 3.0.6 release candidate branch and will be released with 3.0.6
If somebody wants to compile the 3.0.5 version with this fix:
- check out the 3.0.5 sources from svn://svn.zabbix.com/tags/3.0.5
- merge the patch from 3.0 branch: svn merge ^/branches/3.0 -c r62901 (ignore the conflicts in changelog)
- follow with the usual Zabbix build procedure (./bootstrap.sh, ./configure <options>, make dbschema, make install)
|
|
ZBX-11515
Problem are still alive.
More about this problem here: https://www.zabbix.com/forum/showpost.php?p=192795&postcount=14
|
|
Just letting all the watchers know that ODBC code was refactored and hopefully improved in ZBX-10576. I will greatly appreciate if you have a moment to test out trunk with ODBC drivers you normally use and report issues (if any). Feel free to poke me if you need help compiling from sources. Thank you!
|
Generated at Fri Apr 26 05:35:19 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.
ZBX-11213.patch
snapshot01.png