sv_diff returns zero rows

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Set up mycheckpoint to monitor a live server remotely, as follows:
/etc/mycheckpoint.cnf:

[mycheckpoint]
#smtp_host = mail.my-server-company.com
#smtp_from = monitor@my-server-company.com
#smtp_to = dba@my-server-company.com
monitored_port=3306
monitored_host=livedbserver.com
monitored_user=monitoring_user
monitored_password=[*****]
purge_days = 60

[client]
user=monitoring_user
password=[*****]
socket=/var/run/mysqld/mysqld.sock
port=3306
host=localhost
database=mycheckpoint
skip_check_replication=True

2. Deploy and run:

user@monitoringhost$ mycheckpoint deploy
Add the following row to crontab:
*/5 * * * * mycheckpoint

What is the expected output? What do you see instead?

While mycheckpoint is logging rows into the status_variables table, I'm not 
getting any rows from the sv_diff view:

mysql> SELECT questions, uptime, warning_count, ts FROM status_variables;
+-----------+---------+---------------+---------------------+
| questions | uptime  | warning_count | ts                  |
+-----------+---------+---------------+---------------------+
| 241039414 | 1281504 |             0 | 2012-11-02 16:20:01 |
| 241101176 | 1281804 |             0 | 2012-11-02 16:25:01 |
| 241152461 | 1282105 |             0 | 2012-11-02 16:30:01 |
| 241205392 | 1282405 |             0 | 2012-11-02 16:35:01 |
| 241258591 | 1282705 |             0 | 2012-11-02 16:40:01 |
| 241307251 | 1283005 |             0 | 2012-11-02 16:45:02 |
| 241354124 | 1283305 |             0 | 2012-11-02 16:50:01 |
| 241409544 | 1283605 |             0 | 2012-11-02 16:55:01 |
| 241459330 | 1283905 |             0 | 2012-11-02 17:00:01 |
| 241501222 | 1284205 |             0 | 2012-11-02 17:05:01 |
+-----------+---------+---------------+---------------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM sv_diff;
Empty set (0.00 sec)

mysql>

What is the output when running with the "--verbose --debug" options?

user@monitoringhost$ mycheckpoint --verbose --debug
-- Using /etc/mycheckpoint.cnf as defaults file
-- mycheckpoint rev 208, build 201011041330. Copyright (c) 2009-2010 by Shlomi 
Noach
-- database is mycheckpoint
-- monitored host is: livedbserver.com
-- Global status & variables recorded
-- Non-local monitoring; will not read OS data
-- New entry added: id=22; ts=2012-11-02 17:08:32
-- Collecting custom data
-- No alert conditions defined
-- Status variables checkpoint complete

What version of the mycheckpoint are you using?
Revision 208

What version of the MySQL are you using? (SELECT VERSION())
livedbserver.com:
mysql> SELECT VERSION();
+----------------+
| VERSION()      |
+----------------+
| 5.1.65-ius-log |
+----------------+
1 row in set (0.00 sec)

Monitoring host:
mysql> SELECT VERSION();
+-----------------------------+
| VERSION()                   |
+-----------------------------+
| 5.1.61-0ubuntu0.10.10.1-log |
+-----------------------------+
1 row in set (0.00 sec)

What version of Python are you using? (python --version)

user@monitoringhost$ python --version
Python 2.6.6

On what operating system?
livedbserver.com:
CentOS

Monitoring host:
Ubuntu

What is your sql_mode? (SELECT @@global.sql_mode)
livedbserver.com:

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

Monitoring host:

mysql> SELECT @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

Original issue reported on code.google.com by ale...@viovet.co.uk on 2 Nov 2012 at 5:16

[GoogleCodeExporter]

On further investigation, I've noticed that the id column values in the 
status_variables table are going up in increments of 2:

mysql> SELECT id, questions, uptime, warning_count, ts FROM status_variables;
+----+-----------+---------+---------------+---------------------+
| id | questions | uptime  | warning_count | ts                  |
+----+-----------+---------+---------------+---------------------+
|  2 | 241039414 | 1281504 |             0 | 2012-11-02 16:20:01 |
|  4 | 241101176 | 1281804 |             0 | 2012-11-02 16:25:01 |
|  6 | 241152461 | 1282105 |             0 | 2012-11-02 16:30:01 |
|  8 | 241205392 | 1282405 |             0 | 2012-11-02 16:35:01 |
| 10 | 241258591 | 1282705 |             0 | 2012-11-02 16:40:01 |
| 12 | 241307251 | 1283005 |             0 | 2012-11-02 16:45:02 |
| 14 | 241354124 | 1283305 |             0 | 2012-11-02 16:50:01 |
| 16 | 241409544 | 1283605 |             0 | 2012-11-02 16:55:01 |
| 18 | 241459330 | 1283905 |             0 | 2012-11-02 17:00:01 |
| 20 | 241501222 | 1284205 |             0 | 2012-11-02 17:05:01 |
| 22 | 241533573 | 1284416 |             0 | 2012-11-02 17:08:32 |
| 24 | 241546994 | 1284505 |             0 | 2012-11-02 17:10:02 |
| 26 | 241587962 | 1284805 |             0 | 2012-11-02 17:15:01 |
| 28 | 241625703 | 1285105 |             0 | 2012-11-02 17:20:01 |
| 30 | 241737556 | 1285405 |             0 | 2012-11-02 17:25:01 |
| 32 | 241957674 | 1285463 |             0 | 2012-11-02 17:25:59 |
| 34 | 242606395 | 1285705 |             0 | 2012-11-02 17:30:01 |
| 36 | 242641726 | 1286005 |             0 | 2012-11-02 17:35:01 |
| 38 | 242684817 | 1286305 |             0 | 2012-11-02 17:40:01 |
| 40 | 242728618 | 1286605 |             0 | 2012-11-02 17:45:01 |
| 42 | 242768757 | 1286905 |             0 | 2012-11-02 17:50:01 |
| 44 | 242812363 | 1287205 |             0 | 2012-11-02 17:55:01 |
| 46 | 242865670 | 1287506 |             0 | 2012-11-02 18:00:02 |
| 48 | 242930967 | 1287805 |             0 | 2012-11-02 18:05:01 |
| 50 | 242987217 | 1288105 |             0 | 2012-11-02 18:10:01 |
| 52 | 243038891 | 1288405 |             0 | 2012-11-02 18:15:01 |
| 54 | 243224104 | 1288706 |             0 | 2012-11-02 18:20:01 |
+----+-----------+---------+---------------+---------------------+
27 rows in set (0.00 sec)

I'm assuming this is why sv_diff is returning no results, as I believe it works 
by joining the table onto itself, with a rule something like id=(id-1)?

Original comment by ale...@viovet.co.uk on 2 Nov 2012 at 6:24

[GoogleCodeExporter]

Okay. I've found the cause, and it's obviously not a bug with mycheckpoint - 
the monitoring server had the auto_increment_offset and 
auto_increment_increment variables set to 2, from a time when the server was 
previously being used for replication.

It could be a good feature to alert the user if this is the case, as any 
auto_increment value over 1 is a showstopper as far as reporting goes. Either 
that, or perhaps avoid relying on auto_increment?

Luckily fixing my logged history was as simple as:

UPDATE status_variables SET id=id/2 ORDER BY id ASC;

... and then resetting the auto_increment counter for the table to the next 
value.

Original comment by ale...@viovet.co.uk on 2 Nov 2012 at 6:51