[rtg] yrtg not reconnecting after mysql loss

[bill fumerola]

On Fri, May 02, 2008 at 03:15:33PM -0500, Bryan Wann wrote:
> I've noticed with 0.8.1+yrtg, it's not reconnecting after loss of the 
> mysql server.  All the threads report back with "mysql unavailable (2006), 
> buffering", over and over again after the server was back up.  I checked 
> my build and verified the yrtg patch for this did apply.
> 
> I'm running mysql 5.0.58 and I know from snort and a few other apps that 
> in 5.0.3 reconnect was turned off by default.  I tried adding in a 
> "mysql->reconnect = 1" in librtgmysql.c in __db_connect() after 
> mysql_real_connection(), but this didn't seem to help.  I noticed it's 
> also using mysql_options(), so I tried adding a [rtg] section to my.cnf 
> with 'reconnect 1'.  No luck.  Does this sound like the right avenue of 
> attack for this problem?

yrtg was done around rtg 7.x, which was before librtg{mysql,pgsql,*}.c
was introduced, so just because the patch applies doesn't mean that the
codepath is intended.

in other words, yrtg had no __db_connect(). that it works is interesting.

you could try and shove a __db_connect() or whatever the call is in a
libtool'd db layer world into rtgsqlbuf.c:

 		case CR_SERVER_GONE_ERROR:
 		case CR_SERVER_LOST:
 		case CR_CONN_HOST_ERROR:
 			debug(LOW, "*** mysql unavailable (%u), buffering\n",
 			    myerrno);
			[ insert a db_connect() here .. ]
			/*
			 */

and it will get it on the next round of flushing.

or play with mysql_options():

%%%
mysql_init(...);
[....]
my_bool reconnect = 1;
mysql_options(dbhandle, MYSQL_OPT_RECONNECT, &reconnect);
[....]
mysql_real_connect(...);
%%%

it should be called after mysql_init but before mysql_real_connect().

%%%%%%%%%%%%
MYSQL_OPT_RECONNECT (argument type: my_bool *)

Enable or disable automatic reconnection to the server if the connection
is found to have been lost. Reconnect is off by default; this option
provides a way to set reconnection behavior explicitly.

Note: mysql_real_connect() incorrectly reset the MYSQL_OPT_RECONNECT
option to its default value before MySQL 5.1.6. Therefore, prior to that
version, if you want reconnect to be enabled for each connection, you
must call mysql_options() with the MYSQL_OPT_RECONNECT option after each
call to mysql_real_connect(). This is not necessary as of 5.1.6: Call
mysql_options() only before mysql_real_connect() as usual.
%%%%%%%%%%%%


i always just tested it by killing the local or remote mysqld and
restarting it a few poll rounds later. this worked for me. that being
said, the libtooled version can/will behave differently than the old
rtgmysql.c database access layer.

good luck,
-- bill