From 8c010e0ccc8b007f8657a1025d2de36e2310e550 Mon Sep 17 00:00:00 2001 From: James Turnbull Date: Thu, 26 Feb 2009 11:08:23 +1100 Subject: Fixed #1910 - updated logcheck --- ext/logcheck/puppet | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'ext') diff --git a/ext/logcheck/puppet b/ext/logcheck/puppet index 449ec70f5..8f2a7ee70 100644 --- a/ext/logcheck/puppet +++ b/ext/logcheck/puppet @@ -1,5 +1,6 @@ +^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: (Handled resources in|Resource comparison took|Searched for [host|resources|resource params and tags] in) [0-9.]+ seconds ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Starting Puppet server version [.0-9]+$ -^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Compiled configuration for [._[:alnum:]-]+ in [.0-9]+ seconds$ +^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Compiled catalog for [._[:alnum:]-]+ in [.0-9]+ seconds$ ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Caught TERM; shutting down$ ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Shutting down$ ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetd\[[0-9]+\]: Starting Puppet client version [.0-9]+$ -- cgit From ec56ddf8f5b58f16d0067055346889be79b29186 Mon Sep 17 00:00:00 2001 From: James Turnbull Date: Sat, 28 Feb 2009 10:30:23 +1100 Subject: This script fixes the most common issues with inconsistent storeconfigs database (including duplicate resources record, duplicate param_values records, dangling records...). Usage: stop all puppetmasters backup your database! % cat ext/dbfix.sql | mysql puppet relaunch all puppetmasters Signed-off-by: Brice Figureau --- --- ext/dbfix.sql | 98 +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 98 insertions(+) create mode 100644 ext/dbfix.sql (limited to 'ext') 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; -- cgit From a790ee3b487a7eac6668e45747e338d44d75da9e Mon Sep 17 00:00:00 2001 From: James Turnbull Date: Wed, 4 Mar 2009 19:28:45 +1100 Subject: Further fix to #1910 --- ext/logcheck/puppet | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'ext') diff --git a/ext/logcheck/puppet b/ext/logcheck/puppet index 8f2a7ee70..d1b1b27bd 100644 --- a/ext/logcheck/puppet +++ b/ext/logcheck/puppet @@ -1,4 +1,4 @@ -^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: (Handled resources in|Resource comparison took|Searched for [host|resources|resource params and tags] in) [0-9.]+ seconds +^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: (Handled resources in|Resource comparison took|Searched for (host|resources|resource params and tags) in) [0-9.]+ seconds ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Starting Puppet server version [.0-9]+$ ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Compiled catalog for [._[:alnum:]-]+ in [.0-9]+ seconds$ ^\w{3} [ :0-9]{11} [._[:alnum:]-]+ puppetmasterd\[[0-9]+\]: Caught TERM; shutting down$ -- cgit From 1c7c8fe7ae813d4344005c2b353e9a292ecf68c7 Mon Sep 17 00:00:00 2001 From: Brice Figureau Date: Mon, 2 Mar 2009 20:48:44 +0100 Subject: dbfix - fix typo and close another possible inconsistency This patch fixes a typo in one request producing a SQL error. It also close an issue where param_values could point to inexistant param_names after the deduplications of those. It does this by rewriting the param_values to point to the highest id of the param_names of the same name. The same operation is performed on the tags. Signed-off-by: Brice Figureau --- ext/dbfix.sql | 40 +++++++++++++++++++++++++++++++++++++--- 1 file changed, 37 insertions(+), 3 deletions(-) (limited to 'ext') diff --git a/ext/dbfix.sql b/ext/dbfix.sql index d22914adf..960369117 100644 --- a/ext/dbfix.sql +++ b/ext/dbfix.sql @@ -35,6 +35,23 @@ FROM param_values AS bad_rows 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 @@ -48,12 +65,12 @@ FROM param_names AS bad_rows good_rows.name = bad_rows.name AND good_rows.max_id <> bad_rows.id; --- Remove duplicate resource_tags, and keep the latest one +-- 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 param_names + FROM resource_tags GROUP BY resource_id,puppet_tag_id HAVING count(*) > 1 ) AS good_rows @@ -62,7 +79,24 @@ FROM resource_tags AS bad_rows 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 +-- 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 ( -- cgit