[RFC] - NDOUtils duplicate lines problem

[Hendrik Baecker]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
Hi List,

a few days ago Øyvind Nordang, Julien Mathis and  Michael Friedrich,
discussed about duplicate rows in the servicecheck table.
Michael dropped me a few lines off-list showing up the problem exists
in some more tables.

The underlying problem:
MySQL queries like
    "insert into... on duplicate key update..."
needs primary and/or unique keys to be hit by the query to trigger the
UPDATE instead of the INSERT.
Most of the relevant keys for those queries are actually primary nor
unique keys, they are just KEYs.

So we have(98%) / might have (2%) duplicate entries in the following
tables:

nagios_servicechecks
nagios_systemcommands
nagios_timedeventqueue
nagios_timedevents

Now to the problem from the point of development: I see no good
solution to handle this.

One part of the solution is to change the keys to define the mandatory
UNIQUE keys, but this can't be done cleanly on existing data since you
can't define some UNIQUE keys on rows that would be duplicates.

Solutions to be discussed:

1. Big-Bang-Solution
Define the user have to truncate above tables to be able to set the
right keys.
This will result in data loss.

2.
It might be possible to write some nifty upgrade scripts to dump the
data out of the database, set the unique keys and try to re-import the
data with some fault tolerance.
Anyone out there with some experience on this level?
Contra:
Depending on the amount of data, such a job might be: a long runner
(time); complicated; not realy error resistant...

What thinks this community about the cost-benefit ratio?

IMO:
nagios_timedevent* table are not so important and data loss is irrelevant.
nagios_systemcommands might be just more interesting for someone
but nagios_servicechecks seems to me like a treasure of gold, data
loss in here might be realy painful

Any comments?
May be someone is able to adress some voodoo data repair query/script
to eliminate those duplicates before key changing?

- -
Hendrik

PS
For the interested one, the following key changes should be done:

ALTER table nagios_servicechecks DROP key instance_id;
ALTER table nagios_servicechecks DROP key service_object_id;
ALTER table nagios_servicechecks DROP key start_time;
ALTER table nagios_servicechecks DROP key start_time_usec;
ALTER table nagios_servicechecks ADD UNIQUE KEY `instance_id`
(`instance_id`,`service_object_id`,`start_time`,`start_time_usec`);


ALTER table nagios_systemcommands DROP KEY instance_id;
ALTER table nagios_systemcommands DROP KEY start_time;
ALTER table nagios_systemcommands DROP KEY start_time_usec;
ALTER table nagios_systemcommands ADD UNIQUE KEY `instance_id`
(`instance_id`,`start_time`,`start_time_usec`);


ALTER table nagios_timedeventqueue DROP KEY instance_id;
ALTER table nagios_timedeventqueue DROP KEY event_type;
ALTER table nagios_timedeventqueue DROP KEY scheduled_time;
ALTER table nagios_timedeventqueue DROP KEY object_id;
ALTER table nagios_timedeventqueue ADD UNIQUE KEY `instance_id`
(`instance_id`,`event_type`,`scheduled_time`, `object_id`);


ALTER table nagios_timedevents DROP KEY instance_id;
ALTER table nagios_timedevents DROP KEY event_type;
ALTER table nagios_timedevents DROP KEY scheduled_time;
ALTER table nagios_timedevents DROP KEY object_id;
ALTER table nagios_timedevents ADD UNIQUE KEY `instance_id`
(`instance_id`,`event_type`,`scheduled_time`,`object_id`);


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (MingW32)
 
iD8DBQFLAXLOlI0PwfxLQjkRAhn5AJ9ouNnmO4PwLzFv0y6AjGhdZP3gdgCggPo1
fXVPEgT2d3UeEg3HNrsdKac=
=fJuZ
-----END PGP SIGNATURE-----


------------------------------------------------------------------------------
Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day 
trial. Simplify your report design, integration and deployment - and focus on 
what you do best, core application coding. Discover what's new with
Crystal Reports now.  http://p.sf.net/sfu/bobj-july