Join GitHub today
GitHub is home to over 40 million developers working together to host and review code, manage projects, and build software together.Sign up
[dev.icinga.com #181] missing unique constraints in several tables (mysql) #86
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
... 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.
2009-11-11 18:04:04 +00:00 by mfriedrich ec87b68
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.
as you can see
instance_id=1 - both
so the second query should generate an update not an insert!!!
this fail concerns mysql.sql and the following tables:
difference between mysql and postgres/oracle:
depends on the defined unique constraint within the table creation.
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.
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 :-)