summaryrefslogtreecommitdiffstats
path: root/ext/dbfix.sql
blob: 3720289b944135d7f4db6bd77e65846e96d78873 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
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;