[RFC] - NDOUtils duplicate lines problem

[Michael Friedrich]

Hi,

any updates on that? Or no more complaints about duplicate rows?

Depending on demand I could provide the fixed version from Icinga 
IDOUtils (and also a clean upgrading path without data loss) for 
NDOUtils usage...

Kind regards,
Michael

Hendrik Baecker wrote:
> -----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
> _______________________________________________
> Nagios-devel mailing list
> Nagios-devel at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/nagios-devel
>


------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev