I just discovered this. I have a huge database... close to 46 gig to be exact. Can you provide some help with this. Thanks <div class="gmail_quote">On Fri, Jan 8, 2010 at 8:55 AM, Michael Friedrich <<a href="mailto:michael.friedrich@univie.ac.at">michael.friedrich@univie.ac.at</a>> wrote: <blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">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 <div><div></div><div class="h5"> 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. <a href="http://p.sf.net/sfu/bobj-july" target="_blank">http://p.sf.net/sfu/bobj-july</a> > _______________________________________________ > Nagios-devel mailing list > <a href="mailto:Nagios-devel@lists.sourceforge.net">Nagios-devel@lists.sourceforge.net</a> > <a href="https://lists.sourceforge.net/lists/listinfo/nagios-devel" target="_blank">https://lists.sourceforge.net/lists/listinfo/nagios-devel</a> > </div></div>------------------------------------------------------------------------------ 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 <a href="http://p.sf.net/sfu/verizon-dev2dev" target="_blank">http://p.sf.net/sfu/verizon-dev2dev</a> <div><div></div><div class="h5">_______________________________________________ Nagios-devel mailing list <a href="mailto:Nagios-devel@lists.sourceforge.net">Nagios-devel@lists.sourceforge.net</a> <a href="https://lists.sourceforge.net/lists/listinfo/nagios-devel" target="_blank">https://lists.sourceforge.net/lists/listinfo/nagios-devel</a> </div></div></blockquote></div> -- Cordially, Shadhin Rahman