Dejal - Forum - Simon - Super Smart Change Detection

Super Smart Change Detection

Tue, 2008-06-03 16:24 — Wade

Background:
I'm trying to monitor MySQL for restarts (to try and locate the cause of the instability). MySQL is running on OS X 10.4 Server and MySQL is dying but being relaunched before I can notice it's down. I can tell it's been down since the Uptime counter is reset, and the open files count, query cache efficiency, etc all drop.

Using the MySQL works service isn't enough to catch the culprit, since it normally jumps back into life before the next check.

So I thought of using the MySQL service and querying for the uptime counter (SHOW global status WHERE variable_name="Uptime"). But this changes every time I query it, so Smart Change Detection isn't quite going to work. And I can't find any way of doing some sort of IF statement in MySQL using the uptime value (IF value < 1000, "restarted", "OK").

What I'm looking for is Super Smart Change Detection. I only want to be notified if the length of the resulting string returned is less than a particular length (say if the uptime counter is less than 4 digits in length, then MySQL restarted less than 16.67 minutes ago), or even if the change detection could be informed that the result includes a counter, and if the counter is less than a particular value or if the counter decreases since last check, then that counts as a failure.

Any ideas on how I could approach this?

Simon
2.4

Tue, 2008-06-03 16:33 — David Sinclair

Re: Super Smart Change Detection

Yes, I want to enhance the Smart Change Detection feature in a future version. I'm thinking of renaming it "Filters", and supporting multiple filters on the test output, that support comparison logic etc as you suggest.

In the meantime, I'm not sure how you could do it. Using the length of the value is a good idea, if you can figure out some way to do logic in a MySQL query.

Another idea could be to use a Script-based notifier. You could write a shell script, AppleScript, PHP, etc, to evaluate the {TestChangeText} or {TestChangeDifferenceWithoutHTML} values, and write to a log file or something if the value is reset.

Tue, 2008-06-03 21:57 — Wade

The logic in MySQL would be

The logic in MySQL would be my preferred method, then I could just do detection for a particular word in the output. But I can't for the life of me get anything to work there.

So instead I've got a ruby script that takes the output via {TestChangeText} grabs the counter value and compares it against the value for last check, saving the current value and sending a notification email as needed.

Took a bit to get it working. 8)

Actually, I'm not actually certain that the MySQL service is working correctly though.
If I issue:

SHOW global status WHERE variable_name="Uptime"

the result in the preview window is:

MCPResult: (encoding : 4, dim 2 x 1)
Variable_name Value
2921 2921

And not
Variable_name Value
Uptime 2921

as the mysql command line returns. The variable name doesn't appear, and the value is repeated twice.

If it returned the variable name, then you could query for a series of status variables and make a more generic notifier than the one I've just made. I'll send you a copy, although since it's pretty specific it might not be of much use to anyone else.

Tue, 2008-06-03 22:13 — David Sinclair

Re: The logic in MySQL

Simon uses a third-party framework for the MySQL queries, so we may not have much control over the format of the output. It does seem not quite right, though.

I'll certainly be interested in seeing and perhaps sharing your notifier script.

Wed, 2008-06-11 09:01 — RightMinds

Have you tried the command

Have you tried the command line mysql tool yet?

Something like:
mysql -h 127.0.0.1 -u user -pPASSWORD -D db_Name -e statement

Capture the output in a shell script, and go from there.

Thu, 2008-06-12 18:57 — Wade

Have you tried the command

Have you tried the command line mysql tool yet?

Thanks for the idea, I had thought of that, and I'm using something similar on another machine. The MySQL server isn't running on the Simon machine (not a big deal), but having to go back to a command line to monitor things just felt wrong, like a betrayal to Simon. 8)

Wed, 2008-06-11 10:42 — David Sinclair

Re: Have you tried the command

That would certainly be an alternative. However, we were able to improve the MySQL plug-in to output the expected text. This change is in Simon 2.4.1, out soon. Wade's notifier script is available now on the Simon Extras page.