Subject: Re: Database Testing

From: Jeff Weisberg <[email-masked]>
Date: Wed, 3 Mar 2004 16:26:58 -0500 (EST)

| I am currently monitoring the health of my two slave MySQL servers via some
| perl:
| my $sql = 'show slave status';
| my $sth = $dbh->prepare($sql);
| $sth->execute;
| my $row=$sth->fetchrow_hashref;
| my $slave_io = $row->{Slave_IO_Running};
| my $slave_sql = $row->{Slave_SQL_Running};
| 
| print "OK" if(($slave_io eq 'Yes') && ($slave_sql eq 'Yes'));
| 
| how can this be monitored from Argus's internal Database Testing Technique ?

I don't know enough about mysql to know what 'show slave status' returns,
but I'd probably do something like:

	Service DB {
		user:		jkister
		pass:		alyssa
		dsn:		dbi:mysql:host=db.example.com;database=mydb
		sql:		show slave status
	}

then I'd start up my web browser, and look at the 'debugging' page
for the service and look at 'db::rbuffer' to figure out what mysql
was returning. then I'd add the appropriate 'expect' to my config.

	--jeff

---

From: "Jeremy Kister" <[email-masked]>
Date: Thu, 4 Mar 2004 14:43:44 -0500

On Wed Mar 3 16:26:58 EST 2004, Jeff Weisberg wrote:

>       user:         kister

great.  now i have to go change my password. ;)

Interestingly, when i did use the incorrect password to connect, the service
showed as down (as expected), but Argus did not show me the reason (ie: bad
credentials) -- even though i have 'showreason: yes' in the config.

> was returning. then I'd add the appropriate 'expect' to my config.

well, the query returns a bunch of stuff (18 key/value pairs, to be exact).
db::rbuffer shows:
mysql.example.net user 3306 60 host1-bin.002 276511 host23-relay-bin.001 30.
. .

which are only some of the values.  since db::rbuffer is displaying these
values in a different order than my perl is, i cooked up a quick patch to
arguscgi that expands the debug info when ". . ." is placed on the end of
the string, when you hover your mouse over the debug text (using MSIE, at
least).
http://www.jeremykister.com/jeremy/code/argus/arguscgi-3.3_RC-20040217.expand_debugging.patch

the entire db::rbuffer contains:
mysql.example.net user 3306 60 host1-bin.002 276511 host23-relay-bin.001
3096735 host1-bin.002 Yes Yes dns,isp  0  0 276511 3096735~x0A

Without having the key/value pairs to test against, and since it is safe to
assume that the values i am looking for will no doubtedly shift around for
one reason or another (such as one of the values being null unless there was
an error at some point), i am weary in using "expect: Yes Yes"... are my
concerns founded?

Jeremy Kister
www.jeremykister.com/jeremy/

---

From: Jeff Weisberg <[email-masked]>
Date: Thu, 4 Mar 2004 16:13:50 -0500 (EST)

| Interestingly, when i did use the incorrect password to connect, the service
| showed as down (as expected), but Argus did not show me the reason (ie: bad
| credentials) -- even though i have 'showreason: yes' in the config.

hmmm. I'll look into that.

[...]
| which are only some of the values.  since db::rbuffer is displaying these
| values in a different order than my perl is, i cooked up a quick patch to
| arguscgi that expands the debug info when ". . ." is placed on the end of
| the string, when you hover your mouse over the debug text (using MSIE, at
| least).
| http://www.jeremykister.com/jeremy/code/argus/arguscgi-3.3_RC-20040217.expand_debugging.patch

if the field is large, you can use argusctl to fetch it
	argusctl getparam object=Top:Foo:Bar param=db::rbuffer
or
	argusctl about object=Top:Foo:Bar

| the entire db::rbuffer contains:
| mysql.example.net user 3306 60 host1-bin.002 276511 host23-relay-bin.001
| 3096735 host1-bin.002 Yes Yes dns,isp  0  0 276511 3096735~x0A
| 
| Without having the key/value pairs to test against, and since it is safe to
| assume that the values i am looking for will no doubtedly shift around for
| one reason or another (such as one of the values being null unless there was
| an error at some point), i am weary in using "expect: Yes Yes"... are my
| concerns founded?

argus should be keeping them in the order that the database
returns them, but I don't know how consistent mysql is.

I'm open to suggestions for argus doing something differently

	--jeff

---

From: Jeff Weisberg <[email-masked]>
Date: Fri, 12 Mar 2004 14:01:25 -0500 (EST)

| According to documentation at
| http://www.mysql.com/doc/en/Replication_Problems.html, admins should check
| that both the Slave_IO_Running and Slave_SQL_Running colums report 'Yes'.

I'm not a mysql user, so forgive my ignorance; does mysql have
any sort way to fetch this data where you can specify a column
list or where clause? perhaps by querying a 'special' table
or sub-select?

some sort of
	select Slave_IO_Running, Slave_SQL_Running from special_table
or
	select 'OK' from (show slave status)
	  where Slave_IO_Running = 'Yes' and Slave_SQL_Running = 'Yes'

	--jeff

---

From: "Jeremy Kister" <[email-masked]>
Date: Mon, 15 Mar 2004 21:32:21 -0500

> list or where clause? perhaps by querying a 'special' table

I tried multiple variants of both suggestions, none of which bore fruit.

I am unable to provide any evidence there is any special_table in existance
for this purpose (documentation, experimentation, and otherwise), and the
sub-select yields sql syntax errors no matter my variation.

i've got a slave mysql server you can test on, if you wish.

Jeremy Kister

---

From: "Jeremy Kister" <[email-masked]>
Date: Mon, 15 Mar 2004 21:39:33 -0500

On Fri Mar 12 13:11:50 EST 2004, Christopher Ferry wrote:
> That must be for MySQL 4(I've never worked with replication on 4)

That's right.. I was assuming we were all taking about 4.

> So we only need to check for slave_running.
> I think this makes sense, right?

As long as none of those fields will give you a 'Yes' when bad things
happen, that makes sense.

Jeremy Kister