[RFC] - NDOUtils duplicate lines problem

[Hendrik Baecker]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

Take care to backup your Database before doing this hack [as for any
unqualified tipps, tricks and hacks on OSS ;)]

It would be a good thing to first test the following test on a seperate
database copy of your live system.

The idea behind is to first dump the table in question, drop all single
keys except the primary key for the table, set the new and hopefully
'better' keyset carefully ignoring duplicate key errors and re-import
previously dumped data.

Step 0:
Stop NDOUtils and perhaps even nagios, they won't cache all the data
captured during the next operations. May be you can think of a better
way to not loose the data from NOW to workaround END. (ndomod.cfg and
output type file may be usefull here)

Step 1:
Make a good backup of your ndoutils database including all structures
and all data

Step 2: Dumping table data
(Tested with: MySQL dump 10.13  Distrib 5.1.37, x86_64)

In the case of 'nagios_servicechecks':
mysqldump \
- -u ndouser -p \
- --lock-table=false \
- -c --disable-keys=false \
- --replace=true \
- --no-create-info \
- --tables nagios_servicechecks \
nagios > /tmp/nagios_servicechecks_data.sql


Step 3: Dropping the wrong keys

Login to your database:

(following output from my local development workstation)

mysql> ALTER table nagios_servicechecks DROP key instance_id;
Query OK, 95 rows affected (0,12 sec)
Records: 95  Duplicates: 0  Warnings: 0

mysql> ALTER table nagios_servicechecks DROP key service_object_id;
Query OK, 95 rows affected (0,09 sec)
Records: 95  Duplicates: 0  Warnings: 0

mysql> ALTER table nagios_servicechecks DROP key start_time;
Query OK, 95 rows affected (0,07 sec)
Records: 95  Duplicates: 0  Warnings: 0

mysql> ALTER table nagios_servicechecks DROP key start_time_usec;
ERROR 1091 (42000): Can't DROP 'start_time_usec'; check that column/key
exists

^^--- Now all keys should be deleted, please re-check if there are
duplicate key definitions. It might be to see some keys called
'start_time_X' where X is an integer.


mysql> ALTER table nagios_servicechecks ADD UNIQUE KEY `instance_id`
    -> (`instance_id`,`service_object_id`,`start_time`,`start_time_usec`);
ERROR 1062 (23000): Duplicate entry '1-9-2010-03-02 09:50:42-21538' for
key 'instance_id'

^^--- Things are supposed to happen, cause there ARE duplicates...


mysql> ALTER ignore table nagios_servicechecks ADD UNIQUE KEY
`instance_id`
(`instance_id`,`service_object_id`,`start_time`,`start_time_usec`);
Query OK, 95 rows affected (0,04 sec)
Records: 95  Duplicates: 47  Warnings: 0

mysql> quit


The "ignore" will drop those duplicate line with a 'heavy guessing'
mysql voodoo. Remember why we dumped our data in Step 2? ^^


Step 4: Re-import data
Our dumbfile /tmp/nagios_servicechecks_data.sql should begin with s.th.
like:
LOCK TABLES `nagios_servicechecks` WRITE;
REPLACE INTO `nagios_servicechecks` .......

REPLACE INTO is similar to INSERT INTO but with the major feature to
update datarows which felt against the unique keys.

Command:
mysql -u ndouser -p --database=nagios < /tmp/nagios_servicecheck_data.sql

Workaround completed!

In my test this works so far so good. I can't say anything about
durations and I am only able to give you some hope to not loose data.

Please let me know if this works for you.

- -
Hendrik
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkuNIHgACgkQlI0PwfxLQjlX6ACfW/YBp33vdBCAS+3+S5st5QgG
hI4An19SPe/xCH52aHFfvIwCOxyfFyt1
=62Ot
-----END PGP SIGNATURE-----

------------------------------------------------------------------------------
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