From: Michael F. <mic...@un...> - 2010-01-08 13:58:36
|
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 > Nag...@li... > https://lists.sourceforge.net/lists/listinfo/nagios-devel > |