[#ZBX-11515] Can't query oracle database

I have problem with query Oracle database (12c) from zabbix 3.2 (installed on Centos 7 + zabbix from rpm packages).
I also installed oracle instant-client and ODBC package.

I need to query database with different queries. Now I created item "Database monitor" with simple query.
In item page is described now as "Not supported".

Quote:

[root@zabbix3 ~]# yum list installed | grep -i odbc
mysql-connector-odbc.x86_64 5.2.5-6.el7 @RHEL-7.2
oracle-instantclient12.1-odbc.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64
php-odbc.x86_64 5.4.16-36.el7_1 @RHEL-7.2
unixODBC.x86_64 2.3.1-11.el7 @base
unixODBC-devel.x86_64 2.3.1-11.el7 @RHEL-7.2

[root@zabbix3 ~]# yum list installed | grep -i oracle
oracle-instantclient12.1-basic.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64
oracle-instantclient12.1-devel.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64
oracle-instantclient12.1-jdbc.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-jdbc-12.1.0.2.0-1.x86_64
oracle-instantclient12.1-odbc.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-odbc-12.1.0.2.0-1.x86_64
oracle-instantclient12.1-sqlplus.x86_64 12.1.0.2.0-1 @/oracle-instantclient12.1-sqlplus-12.1.0.2.0-1.x86_64

– odbc.ini configuration
Quote:

[root@zabbix3 ~]# cat /etc/odbc.ini
[<DATABASE NAME>]
Driver=Oracle
ServerName=//aa.bb.cc.dd:1521/<service name>
Database=<DATABASE LINK NAME>
DSN=<DATABASE NAME>
Port=1521
#UserID=MY_DATABASE_USER>
#Password=DATABASE_USER_PASSWORD

I created odbc connection and it's fully working from OS commandline.
Also manually and as command.

Fired up manually - works.
Quote:

[root@zabbix3 ~]# isql -v <DATABASE LINK NAME> <USERNAME>/<PASSWORD> -b < sql.txt
+-----------------------------------------+
| COUNT(1) |
+-----------------------------------------+
| 465 |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched

Quote:
[root@zabbix3 ~]# isql -v <DATABASE LINK NAME> <USERNAME>/<PASSWORD>
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(1) from sm_zas_obiekty_glowne
+-----------------------------------------+
| COUNT(1) |
+-----------------------------------------+
| 465 |
+-----------------------------------------+
SQLRowCount returns -1
1 rows fetched

– I can also get that data from shell (create a script?), but this is not acceptable solution for me.
Quote:

[root@zabbix3 ~]# isql -v <DATABASE NAME> <USERNAME>/<PASSWORD> -b < sql.txt | head -4 | tail -1 | awk '{print $2}'
465

– In zabbix debug log i see that:
Quote:

41821:20161121:085047.824 In odbc_DBconnect() db_dsn:'<DATABASE NAME>' user:'<DATABASE USERNAME>'
41821:20161121:085047.910 End of odbc_DBconnect():SUCCEED
41821:20161121:085047.910 In odbc_DBselect() query:'select count(1) from sm_zas_obiekty_glowne'
41821:20161121:085047.914 odbc_DBselect() selected 1 columns
41821:20161121:085047.914 End of odbc_DBselect()
41821:20161121:085047.914 In odbc_DBfetch()
41821:20161121:085047.915 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.'
41821:20161121:085047.915 odbc_DBfetch() fetched [0 col]: '<B8>W<A1><A1><95>^?'
41821:20161121:085047.915 End of odbc_DBfetch()
41821:20161121:085047.919 End of db_odbc_select():NOTSUPPORTED
41821:20161121:085047.919 End of get_value_db():NOTSUPPORTED
41821:20161121:085047.919 Item [proxy2b.kgp.lan:db.odbc.select[<DATABASE NAME>_test,<DATABASE NAME>]] error: Received value [?W???^?] is not suitable for value type [Numeric (float)]
41821:20161121:085047.919 End of get_value():NOTSUPPORTED
41821:20161121:085047.919 In activate_host() hostid:10177 itemid:24047 type:11
41821:20161121:085047.919 End of activate_host()
41821:20161121:085047.919 End of get_values():1

= it's look like there are problem with connect to database.
Quote:

41821:20161121:085047.915 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.'
41821:20161121:085047.915 odbc_DBfetch() fetched [0 col]: '<B8>W<A1><A1><95>^?'

I tried:

add/remove user + password to odbc configuration.
use password (or without it when added to odbc) in zabbix configuration
change type of data gathered from zabbix to text / log
change number precision (float / usigned)

... all without success. As I see, this is a problem with connection to database.
It's strange because it's work without any problem from linux commandline (in both version with password in odbc or without it (isql -v <DATABASE LINK>)

Any help?

One note : from time to time this connection message from database in log (Received value [....]) are changed from one to another - like [?G?t] or [XSWISS] [?] [?H?t] - like random value ... :/

[ZBX-11515] Can't query oracle database Created: 2016 Nov 23 Updated: 2017 May 30 Resolved: 2016 Dec 13
Status:	Closed
Project:	ZABBIX BUGS AND ISSUES
Component/s:	Server (S)
Affects Version/s:	3.2.1
Fix Version/s:	None

[ZBX-11515] Can't query oracle database Created: 2016 Nov 23 Updated: 2017 May 30 Resolved: 2016 Dec 13