Query a MySQL server remotely using the "Database Monitor"

[fast.ryder]

Hello!

I need to perform several direct SQL queries on a remote MySQL server using the Zabbix native Database monitor component.

I have set up a new host, gave it an IP Address, no agent port (no agent installed on it - that is the whole point) and created only one item.

The item is of type "Database Monitor", and contains the DSN I created in the /etc/odbc.ini file, the user / pass and the query I need to perform.

However when I enable the host and the item I get this error:

Parameter [db.odbc.select[zabbix_sample]] is not supported by agent on host [Database Host] Old status [0]

Should this not "just work" ? Do I need to insert the ODBC connection strings elsewhere?

There is absolutely no documentation on the Database Monitor feature.. A real shame.

Thanks in advance for any help,

Ivo Pereira
IT Consultant
Portugal

[sanlee]

I ran into the same problem, and I ended up using "External Check" that invokes a bash script, which runs a query on MySQL console.

[fast.ryder]

Re-compiled with ODBC support and still no luck

Hello, there.

I had forgotten that when I upgraded my Zabbix binaries to new 1.6 version I did not remember to check the ./configure for the ODBC parameter that was missing.

Now I recompiled Zabbix (installing first the unixODBC-devel libraries) and added the --with-odbc option.

Zabbix now connects to the remote host and returns this error:

'zabbix_sample\nsql=select User from user' : Error: failed connection [[unixODBC][Driver Manager]Invalid string or buffer length] (0)

I have a remote database named Zabbix, a query user with a password and I granted the necessarY privileges to the user.

I already tried to specifiy all the parameters that show in the database monitor (DSN, User, Password, etc) but I find it strange that no database option appears.

No luck, still.

Cheers,

Ivo Pereira
IT Consultant
Portugal

[sanlee]

How did you set up ODBC monitor?

I kept on getting Unsupported error on Zabbix.

This is DSN I used:
DSN=JDBCAppender\nuser=ID\npassword=PW\nsql=SELECT count(*) from information_schema.parameters;

I can connect to the database I am trying to connect through ODBC on test app provided by EasySoft, but I can not connect using Zabbix.

[Alexei]

The functionality is not officially supported yet. Please wait.

[Spectroman]

it is now?

I just installed the version 1.6.5 and it seems to communicate with the server, but I got a real strange error:

Failed to connect to DSN 'XXXXXXXX' : Error: failed connection [[unixODBC][FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist] (20009)

using the following config:

DSN=XXXXXXXX
user=BLABLABLA
password=bliblibli
sql=select * from ActiveNodeHistory

my unixodbc is working fine via isql, and php... any hints?

[phoemix]

Hello,

with 1.6.7 i'm getting the following error message:
failed connection [[unixODBC][Driver Manager]Invalid string or buffer length] (0)

I'm trying to use unixODBC with postgresql module. the username and password is supplied in the odbc.ini.
item key: db.odbc.select[dbsize_pdms]
additional parameters: DSN=pgdb1\nsql=SELECT pg_database_size('pdms');

of course, this query does work from the isql cli utility.

what am I doing wrong? how can this be fixed?

[dagun]

Hi ,Alexei

The functionality is not officially supported yet. Please wait.

How long do we wait for the functionality???

[samwise]

interested

Interested here as well.

[stan.vasiljev]

I have similar problems. How I can enable ODBC feature on zabbix server ? I tried to recompile binary with "--with-odbc" option. But still have such lines in the server log:
**** Enabled features ****
...
ODBC: NO
...

Of course "Database monitoring" items is not supported too.

[stan.vasiljev]

Of course they are not supported and ODBC feature is not enabled.
--with-odbc option has no sence for ./configure script.
There is other option for unixODBC support: --with-unixodbc

Unfortunately, there is no any documentation or help about ODBC support in the Zabbix

[chh]

Hello,

unixodbc works with isql, the zabbix item not (zabbix 1.8.2).

15383:20100614:171314.958 Failed to connect to DSN 'myodbc' : Error: failed connection [[unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'server.domain.com' (4)] (2003)
15383:20100614:171314.958 Item [HOST:db.odbc.select[unixodbc]] error: failed connection [[unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'server.domain.com' (4)] (2003)
15383:20100614:171314.958 Parameter [HOST:db.odbc.select[unixodbc]] is not supported by agent Old status [0]

unixodbc-trace:

----------------
[ODBC][15383][1276524821.522471][__handles.c][459]
Exit:[SQL_SUCCESS]
Environment = 0x816e3a8
[ODBC][15383][1276524821.522626][SQLSetEnvAttr.c][189]
Entry:
Environment = 0x816e3a8
Attribute = SQL_ATTR_ODBC_VERSION
Value = 0x3
StrLen = 0
[ODBC][15383][1276524821.522687][SQLSetEnvAttr.c][356]
Exit:[SQL_SUCCESS]
[ODBC][15383][1276524821.522743][SQLAllocHandle.c][375]
Entry:
Handle Type = 2
Input Handle = 0x816e3a8
[ODBC][15383][1276524821.522799][SQLAllocHandle.c][493]
Exit:[SQL_SUCCESS]
Output Handle = 0x816ccc8
[ODBC][15383][1276524821.522856][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x816ccc8
Attribute = SQL_ATTR_LOGIN_TIMEOUT
Value = 0x5
StrLen = 0
[ODBC][15383][1276524821.522919][SQLSetConnectAttr.c][681]
Exit:[SQL_SUCCESS]
[ODBC][15383][1276524821.522983][SQLConnect.c][3654]
Entry:
Connection = 0x816ccc8
Server Name = [myodbc][length = 7 (SQL_NTS)]
User Name = [xxx][length = 3 (SQL_NTS)]
Authentication = [********][length = 8 (SQL_NTS)]
UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

DIAG [HY000] [MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'server.domain.com' (4)

[ODBC][15383][1276524824.528017][SQLConnect.c][4021]
Exit:[SQL_ERROR]
[ODBC][15383][1276524824.529195][SQLGetDiagRec.c][680]
Entry:
Connection = 0x816ccc8
Rec Number = 1
SQLState = 0xbfe3c2ea
Native = 0xbfe3c2f4
Message Text = 0xbfe3c286
Buffer Length = 100
Text Len Ptr = 0xbfe3c2fa
[ODBC][15383][1276524824.529315][SQLGetDiagRec.c][717]
Exit:[SQL_SUCCESS]
SQLState = HY000
Native = 0xbfe3c2f4 -> 2003
Message Text = [[unixODBC][MySQL][ODBC 5.1 Driver]Can't connect to MySQL server on 'server.domain.com' (4)]
[ODBC][15383][1276524824.529379][SQLFreeHandle.c][286]
Entry:
Handle Type = 2
Input Handle = 0x816ccc8
[ODBC][15383][1276524824.529434][SQLFreeHandle.c][337]
Exit:[SQL_SUCCESS]
[ODBC][15383][1276524824.529495][SQLFreeHandle.c][219]
Entry:
Handle Type = 1
Input Handle = 0x816e3a8
----------------

I have tested with unixodbc-2.2.11-13 (debian lenny package) + libmyodbc-3.51 and unixodbc-2.3.0 + libmyodbc-5.1, it's the same problem.

Database Monitor-Item:

db.odbc.select[unixodbc]
DSN=myodbc
user=xxx
password=password
sql=show tables;

Any ideas?

Thx & greetings Chris

[dagun]

Dsn

The DSN is already set rightly??

[chh]

>unixodbc works with isql

yes, it is. the unixodbc-trace shows up the right username and server. i have tested with odbcinst and the options -l for system dsn but it's the same problem.

greetings

[netgeek1979]

Forgive the messiness

For CentOS:

yum install freetds unixODBC

Add to end of /etc/odbcinst.ini

[TDS]
Driver = /usr/lib64/libtdsodbc.so.0

Remember the names must match (case included) to how you are going to call the DSN from inside of zabbix. I recommend keeping it the hostname from zabbix's perspective and lowercased.

Add to /etc/freetds.conf (this needs to be done for all MS SQL database servers that will have SQL queries run)

[sqlservername.local]
host = sqlservername.local
port = 1234
tds version = 8.0

Add to /etc/odbc.ini (same deal...)

[sqlservername.local]
Driver = TDS
Servername = sqlservername.local
PORT = 1234