1 files changed, 132 insertions, 0 deletions
diff --git a/ext/dbfix.sql b/ext/dbfix.sql
new file mode 100644
index 000000000..960369117
--- /dev/null
+++ b/ext/dbfix.sql
@@ -0,0 +1,132 @@
+-- MySQL DB consistency check/fix
+-- 
+-- Usage:
+-- cat dbfix.sql | mysql -u user -p puppet
+--
+-- WARNING: perform a database backup before running this script
+
+-- Remove duplicate resources, and keep the latest one
+DELETE bad_rows.*
+FROM resources AS bad_rows
+  INNER JOIN (
+    SELECT title,restype,host_id, MAX(id) as max_id
+    FROM resources
+    GROUP BY title,restype,host_id
+    HAVING count(*) > 1
+  ) AS good_rows 
+  ON 
+    good_rows.title = bad_rows.title AND 
+    good_rows.restype = bad_rows.restype AND 
+    good_rows.host_id = bad_rows.host_id AND 
+    good_rows.max_id <> bad_rows.id;
+
+-- Remove duplicate param_values, and keep the latest one
+DELETE bad_rows.*
+FROM param_values AS bad_rows
+  INNER JOIN (
+    SELECT value,param_name_id,resource_id, MAX(id) as max_id
+    FROM param_values
+    GROUP BY value,param_name_id,resource_id
+    HAVING count(*) > 1
+  ) AS good_rows 
+  ON 
+    good_rows.value = bad_rows.value AND 
+    good_rows.param_name_id = bad_rows.param_name_id AND 
+    good_rows.resource_id = bad_rows.resource_id AND 
+    good_rows.max_id <> bad_rows.id;
+
+-- rewrite param_values that points to duplicated param_names
+-- to point to the highest param_name id.
+UPDATE 
+  param_values v 
+  INNER JOIN
+  param_names n
+  ON n.id = v.param_name_id
+  INNER JOIN
+  (
+    SELECT name, MAX(id) as max_id
+    FROM param_names
+    GROUP BY name
+    HAVING count(*) > 1
+  ) nmax ON n.name = nmax.name
+SET
+  v.param_name_id = nmax.max_id;
+
+-- Remove duplicate param_names, and keep the latest one
+DELETE bad_rows.*
+FROM param_names AS bad_rows
+  INNER JOIN (
+    SELECT name, MAX(id) as max_id
+    FROM param_names
+    GROUP BY name
+    HAVING count(*) > 1
+  ) AS good_rows 
+  ON 
+    good_rows.name = bad_rows.name AND
+    good_rows.max_id <> bad_rows.id;
+
+-- Remove duplicate resource_tags, and keep the highest one
+DELETE bad_rows.*
+FROM resource_tags AS bad_rows
+  INNER JOIN (
+    SELECT resource_id,puppet_tag_id, MAX(id) as max_id
+    FROM resource_tags
+    GROUP BY resource_id,puppet_tag_id
+    HAVING count(*) > 1
+  ) AS good_rows 
+  ON 
+    good_rows.resource_id = bad_rows.resource_id AND
+    good_rows.puppet_tag_id = bad_rows.puppet_tag_id AND
+    good_rows.max_id <> bad_rows.id;
+
+-- rewrite resource_tags that points to duplicated puppet_tags
+-- to point to the highest puppet_tags id.
+UPDATE 
+  resource_tags v 
+  INNER JOIN
+  puppet_tags n
+  ON n.id = v.puppet_tag_id
+  INNER JOIN
+  (
+    SELECT name, MAX(id) as max_id
+    FROM puppet_tags
+    GROUP BY name
+    HAVING count(*) > 1
+  ) nmax ON n.name = nmax.name
+SET
+  v.puppet_tag_id = nmax.max_id;
+
+-- Remove duplicate puppet_tags, and keep the highest one
+DELETE bad_rows.*
+FROM puppet_tags AS bad_rows
+  INNER JOIN (
+    SELECT name, MAX(id) as max_id
+    FROM puppet_tags
+    GROUP BY name
+    HAVING count(*) > 1
+  ) AS good_rows 
+  ON 
+    good_rows.name = bad_rows.name AND
+    good_rows.max_id <> bad_rows.id;
+
+-- Fix dangling resources
+-- note: we use a table to not exceed the number of InnoDB locks if there are two much
+-- rows to delete.
+-- this is an alternative to: DELETE resources FROM resources r LEFT JOIN hosts h ON h.id=r.host_id WHERE h.id IS NULL;
+--
+CREATE TABLE resources_c LIKE resources;
+INSERT INTO resources_c SELECT r.* FROM resources r INNER JOIN hosts h ON h.id=r.host_id;
+RENAME TABLE resources TO resources_old, resources_c TO resources;
+DROP TABLE resources_old;
+
+-- Fix dangling param_values
+CREATE TABLE param_values_c LIKE param_values;
+INSERT INTO param_values_c SELECT v.* FROM param_values v INNER JOIN resources r ON r.id=v.resource_id;
+RENAME TABLE param_values TO param_values_old, param_values_c TO param_values;
+DROP TABLE param_values_old;
+
+-- Fix dangling resource_tags
+CREATE TABLE resource_tags_c LIKE resource_tags;
+INSERT INTO resource_tags_c SELECT t.* FROM resource_tags t INNER JOIN resources r ON r.id=t.resource_id;
+RENAME TABLE resource_tags TO resource_tags_old, resource_tags_c TO resource_tags;
+DROP TABLE resource_tags_old;