Zabbix Documentation 3.0

2.23.03.23.4In development:4.0Unsupported versions:1.82.02.4

User Tools

Site Tools


Sidebar

manual:config:items:itemtypes:odbc_checks

2.15 ODBC monitoring

2.15.1 Overview

ODBC monitoring corresponds to the Database monitor item type in the Zabbix frontend.

ODBC is a C programming language middle-ware API for accessing database management systems (DBMS). The ODBC concept was developed by Microsoft and later ported to other platforms.

Zabbix may query any database, which is supported by ODBC. To do that, Zabbix does not directly connect to the databases, but uses the ODBC interface and drivers set up in ODBC. This function allows for more efficient monitoring of different databases for multiple purposes - for example, checking specific database queues, usage statistics and so on. Zabbix supports unixODBC, which is one of the most commonly used open source ODBC API implementations.

2.15.2 Installing unixODBC

The suggested way of installing unixODBC is to use the Linux operating system default package repositories. In the most popular Linux distributions unixODBC is included in the package repository by default. If it's not available, it can be obtained at the unixODBC homepage: http://www.unixodbc.org/download.html.

Installing unixODBC on RedHat/Fedora based systems using the yum package manager:

shell> yum -y install unixODBC unixODBC-devel

Installing unixODBC on SUSE based systems using the zypper package manager:

# zypper in unixODBC-devel

The unixODBC-devel package is needed to compile Zabbix with unixODBC support.

2.15.3 Installing unixODBC drivers

A unixODBC database driver should be installed for the database, which will be monitored. unixODBC has a list of supported databases and drivers: http://www.unixodbc.org/drivers.html. In some Linux distributions database drivers are included in package repositories. Installing MySQL database driver on RedHat/Fedora based systems using the yum package manager:

shell> yum install mysql-connector-odbc

Installing MySQL database driver on SUSE based systems using the zypper package manager:

zypper in MyODBC-unixODBC

2.15.4 Configuring unixODBC

ODBC configuration is done by editing the odbcinst.ini and odbc.ini files. To verify the configuration file location, type:

shell> odbcinst -j

odbcinst.ini is used to list the installed ODBC database drivers:

[mysql]
Description = ODBC for MySQL
Driver      = /usr/lib/libmyodbc5.so

Parameter details:

Attribute Description
mysql Database driver name.
Description Database driver description.
Driver Database driver library location.

odbc.ini is used to define data sources:

[test]
Description = MySQL test database
Driver      = mysql
Server      = 127.0.0.1
User        = root
Password    =
Port        = 3306
Database    = zabbix

Parameter details:

Attribute Description
test Data source name (DSN).
Description Data source description.
Driver Database driver name - as specified in odbcinst.ini
Server Database server IP/DNS.
User Database user for connection.
Password Database user password.
Port Database connection port.
Database Database name.

To verify if ODBC connection is working successfully, a connection to database should be tested. That can be done with the isql utility (included in the unixODBC package):

shell> isql test
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

2.15.5 Compiling Zabbix with ODBC support

To enable ODBC support, Zabbix should be compiled with the following flag:

  --with-unixodbc[=ARG]   use odbc driver against unixODBC package

See more about Zabbix installation from the source code.

2.15.6 Item configuration in Zabbix frontend

Configure a database monitoring item:

Specifically for database monitoring items you must enter:

Type Select Database monitor here.
Key Enter db.odbc.select[unique_description,data_source_name]
The unique description will serve to identify the item in triggers etc.
The data source name (DSN) must be set as specified in odbc.ini.
User name Enter the database user name (optional if user is specified in odbc.ini)
Password Enter the database user password (optional if password is specified in odbc.ini)
SQL query Enter the SQL query
Type of information It is important to know what type of information will be returned by the query, so that it is selected correctly here. With an incorrect type of information the item will turn unsupported.

2.15.7 Important notes

  • Zabbix does not limit the query execution time. It is up to the user to choose queries that can be executed in a reasonable amount of time.
  • The Timeout parameter value from Zabbix server is used as the ODBC login timeout (note that depending on ODBC drivers the login timeout setting might be ignored).
  • The query must return one value only.
  • If a query returns more than one column, only the first column is read.
  • If a query returns more than one line, only the first line is read.
  • The SQL command must begin with select.
  • The SQL command mustn't contain any line breaks.
  • See also known issues for ODBC checks

2.15.8 Error messages

Starting from Zabbix 2.0.8 the ODBC error messages are structured into fields to provide more detailed information. Example:

Cannot execute ODBC query:[SQL_ERROR]:[42601][7][ERROR: syntax error at or near ";"; Error while executing the query]|
-------------------------  ---------   -----  |  ------------------------------------------------------------------- |
            |                  |         |    `- Native error code            `- error message.                      `- Record separator
            |                  |         `-SQLState
            `- Zabbix message  `- ODBC return code

Note that the error message length is limited to 2048 bytes, so the message can be truncated. If there is more than one ODBC diagnostic record Zabbix tries to concatenate them as far as the length limit allows.