[dev.icinga.com #181] missing unique constraints in several tables (mysql)

[icinga-migration]

This issue has been migrated from Redmine: https://dev.icinga.com/issues/181

Created by mfriedrich on 2009-11-11 08:25:41 +00:00

Assignee: mfriedrich
Status: Resolved (closed on 2009-11-11 18:05:06 +00:00)
Target Version: 1.0 Stable
Last Update: 2009-11-11 18:07:33 +00:00 (in Redmine)

---

... which causes duplicate rows.

INSERT ... ON DUPLICATE KEY UPDATE

If there's no duplicate key because of missing unique constraints (keys), then always an insert will be issued. this causes the database to grow and generate doubled up stuff, which is kind of useless.

Changesets

2009-11-11 18:04:04 +00:00 by mfriedrich ec87b68

fix missing unique keys in several tables

* causing duplicate rows because ON DUPLICATE KEY never matches
* KEY != UNIQUE KEY, keys dropped

at least for timedevent* this will create half of the rows

fixes #181

[icinga-migration]

Updated by mfriedrich on 2009-11-11 18:05:06 +00:00

• Status changed from New to Resolved
• Done % changed from 0 to 100

Applied in changeset commit:"ec87b6891fbf4b9b79bc51ae875635b23fcf03e0".

[icinga-migration]

Updated by mfriedrich on 2009-11-11 18:07:33 +00:00

rofl... looking at timedevents table ... there also is no unique constraint. unique key != key

the unique keys should be

UNIQUE KEY `instance_id` (`instance_id`,`event_type`,`scheduled_time`,`object_id`),

now it looks like this.

+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| timedevent_id | instance_id | event_type | queued_time         | queued_time_usec | event_time          | event_time_usec | scheduled_time      | recurring_event | object_id | deletion_time       | deletion_time_usec |
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
|        346416 |           1 |          0 | 2009-11-11 16:16:06 |           448613 | 0000-00-00 00:00:00 |               0 | 2009-11-11 16:20:58 |               0 |     19918 | 0000-00-00 00:00:00 |                  0 |
|        346941 |           1 |          0 | 0000-00-00 00:00:00 |                0 | 2009-11-11 16:20:58 |           49905 | 2009-11-11 16:20:58 |               0 |     19918 | 0000-00-00 00:00:00 |                  0 |

as you can see

instance_id=1 - both
event_type=0 - both
scheduled_time='2009-11-11 16:20:58' - both
object_id=19918 - both

so the second query should generate an update not an insert!!!

this fail concerns mysql.sql and the following tables:

systemcommands
timedeventqueue
timedevents

difference between mysql and postgres/oracle:

MySQL:
INSERT INTO table () VALUES () ON DUPLICATE KEY UPDATE SET foo=bar;

depends on the defined unique constraint within the table creation.

Oracle
MERGE INTO table USING DUAL ON (unique constraint) WHEN MATCHED THEN UPDATE SET foo=bar WHEN NOT MATCHED INSERT () VALUES ();

Postgres
UPDATE table SET foo=bar WHERE (unique constraint);
if nothing affected
INSERT INTO table () VALUES ();

both have defined unique constraints within the queries already. the table created unique constraints are just a doubled check.

They have been deeply debugged by myself, since I have implemented their support.

But MySQL is missing some constraints and cannot recheck that within the query.

---

So my fix will attempt to recreate those unique keys within the table creation and the upgrade ALTER TABLE statements.

Looks nice indeed, no more duplicates.

mysql> select * from icinga_timedevents where object_id=20260;

+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
| timedevent_id | instance_id | event_type | queued_time         | queued_time_usec | event_time          | event_time_usec | scheduled_time      | recurring_event | object_id | deletion_time       | deletion_time_usec |
+---------------+-------------+------------+---------------------+------------------+---------------------+-----------------+---------------------+-----------------+-----------+---------------------+--------------------+
|        362362 |           1 |          0 | 2009-11-11 18:25:56 |           255593 | 2009-11-11 18:30:44 |          240715 | 2009-11-11 18:30:44 |               0 |     20260 | 0000-00-00 00:00:00 |                  0 |

mysql> select * from icinga_systemcommands where start_time='2009-11-11 18:25:46' and start_time_usec=178164;

+------------------+-------------+---------------------+-----------------+---------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+-------------+--------+-------------+
| systemcommand_id | instance_id | start_time          | start_time_usec | end_time            | end_time_usec | command_line                                                                                                                                      | timeout | early_timeout | execution_time | return_code | output | long_output |
+------------------+-------------+---------------------+-----------------+---------------------+---------------+---------------------------------------------------------------------------------------------------------------------------------------------------+---------+---------------+----------------+-------------+--------+-------------+
|           126918 |           1 | 2009-11-11 18:25:46 |          178164 | 2009-11-11 18:25:46 |        207814 | /usr/bin/printf "%b" "1257960336\thostname\tUP\t1\tHARD\t0.030\tPING OK - Packet loss = 0%, RTA = 0.69 ms\t\n" >> /opt/icinga/var/host-perfdata.out |       5 |             0 |          0.029 |           0 |        |             | 

People are wondering why timedevents are that many rows and exploding the db... well with this patch you'll get probably the half of them!

tested, applied to GIT amster :-)