Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-11515

Can't query oracle database

XMLWordPrintable

    • Icon: Incident report Incident report
    • Resolution: Duplicate
    • Icon: Critical Critical
    • None
    • 3.2.1
    • Server (S)
    • CentOS 7

      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 ... :/

            Unassigned Unassigned
            LITWIN Mariusz Litwińczuk
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: