[RFC] - NDOUtils duplicate lines problem

[shadih rahman]

I just discovered this.  I have a huge database... close to 46 gig to be
exact.  Can you provide some help with this.  Thanks

On Fri, Jan 8, 2010 at 8:55 AM, Michael Friedrich <
michael.friedrich at univie.ac.at> wrote:

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



-- 
Cordially,
Shadhin Rahman
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/developers/attachments/20100301/fa115096/attachment.html>
-------------- next part --------------
------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
-------------- next part --------------
_______________________________________________
Nagios-devel mailing list
Nagios-devel at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-devel