Database monitor for remote host

[ninguno]

I'm using zabbix 1.8.8 in Ubuntu 10.04. I already compile zabbix server with odbc support and install unixodbc drivers.

Database monitor works for a database hosted in the same server of the zabbix server (localhost), but when trying to use it for a remote mysql instance doesn't work. Of couse I added /etc/odbc.ini entries, and with the command 'isql dsn user pass' test the connectio that works ok.

I activate Debug log level in 4 and this is the log I'm getting in zabbix server

4716:20111115:120641.780 Item [MyRemoteDatabase:db.odbc.select[myremotedatabase_jobs_lasthour]] became not supported: failed connection [[unixODBC][MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (4)] (2003)
4716:20111115:120641.780 In DCadd_nextcheck()
4716:20111115:120641.780 End of DCadd_nextcheck()
4716:20111115:120641.780 query [txnlev:1] [update items set lastclock=1321369600,prevorgvalue='5568398157.0000 00',prevvalue=lastvalue,lastvalue='1212.600000' where itemid=18440;

Another thing I can mention is that the connection between server and remote is a bit slow (it can take up to 10 seconds to connect), but as I say testing with the isql command works ok. Nevetherless I change the 'Timeout' configuration parameter in zabbix_server.conf to 30 to see if this could fix the problem but it didn't work.

Anyone knows what the problem could be or any extra thing I can test? Maybe the database monitor use another timeout value?

Thanks in advance

[ninguno]

Some more tests I made, the error message
[MyRemoteDatabase:db.odbc.select[myremotedatabase_jobs_lasthour]] became not supported: failed connection [[unixODBC][MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'XXX.XXX.XXX.XXX' (4)] (2003)

Is thrown five seconds after the connection attempt start

4697:20111116:205436.549 In substitute_simple_macros() data:'db.odbc.select[myremotedatabase_jobs_lasthour]'
4697:20111116:205436.549 In substitute_simple_macros() data:'DSN=test
user=test
password=test
sql=SELECT COUNT(*) FROM jobs WHERE created_at >= ADDDATE(NOW(), INTERVAL -1 DAY)'
4697:20111116:205436.549 In get_value() key:'db.odbc.select[myremotedatabase_jobs_lasthour]'
4697:20111116:205436.549 In database monitor: db.odbc.select[myremotedatabase_jobs_lasthour]
4697:20111116:205436.549 ODBC connect [test] [test]
4698:20111116:205436.564 In get_values()
4698:20111116:205436.564 In DCconfig_get_poller_items() poller_type:0

Is maybe same kind of zabbix timeout???

I also made a test using the mysql C connector directly and it connects ok after ten seconds.

/* Simple C program that connects to MySQL Database server*/
#include <mysql.h>
#include <stdio.h>
main() {
   MYSQL *conn;
   MYSQL_RES *res;
   MYSQL_ROW row;
   char *server = "XXX.XXX.XXX.XXX";
   char *user = "test";
   char *password = "test"; /* set me first */
   char *database = "test";
   conn = mysql_init(NULL);
   /* Connect to database */
   if (!mysql_real_connect(conn, server,
         user, password, database, 3306, NULL, CLIENT_MULTI_STATEMENTS)) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }
   /* send SQL query */
   if (mysql_query(conn, "show tables")) {
      fprintf(stderr, "%s\n", mysql_error(conn));
      exit(1);
   }
   res = mysql_use_result(conn);
   /* output table name */
   printf("MySQL Tables in mysql database:\n");
   while ((row = mysql_fetch_row(res)) != NULL)
      printf("%s \n", row[0]);
   /* close connection */
   mysql_free_result(res);
   mysql_close(conn);
} 

[ninguno]

Well, finally i discover the problem and was an odbc timeout.

I change in ./src/libs/zbxdbhigh/odbc.c the SQL_LOGIN_TIMEOUT parameter from 5 to 15, recompile, and now my database monitor works!

SQLSetConnectAttr(pdbh->hdbc, (SQLINTEGER)SQL_LOGIN_TIMEOUT, (SQLPOINTER)15, (SQLINTEGER)0); 

Shouldn't the login timeout be a parameter for configure? I know my connection between server and host is not the best, but it could be useful to have the option to tweak this without recompiling (there are also two more odbc parameters, SQL_ATTR_QUERY_TIMEOUT, SQL_ATTR_CONNECTION_TIMEOUT but in my case I didn't need to change them).

What do you think?