diff options
| -rw-r--r-- | CHANGELOG | 2 | ||||
| -rw-r--r-- | ext/dbfix.sql | 98 |
2 files changed, 100 insertions, 0 deletions
@@ -1,4 +1,6 @@ 0.24.8 + Added ext/dbfix.sql script - fixes common errors in stored configuration databases + Fixed #1963 - Failing to read /proc/mounts for selinux kills file downloads Fixed #2025 - gentoo service provider handle only default init level diff --git a/ext/dbfix.sql b/ext/dbfix.sql new file mode 100644 index 000000000..d22914adf --- /dev/null +++ b/ext/dbfix.sql @@ -0,0 +1,98 @@ +-- 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; + +-- 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 latest one +DELETE bad_rows.* +FROM resource_tags AS bad_rows + INNER JOIN ( + SELECT resource_id,puppet_tag_id, MAX(id) as max_id + FROM param_names + 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; + +-- Remove duplicate puppet_tags, and keep the latest 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; |