2 -- This script does the same as dbcheck, but in full SQL in order to be faster
3 -- To run it, exec it like this : psql -U bacula bacula (YOUR username and database)
5 -- It will tell you what it does. At the end you'll have to commit yourself.
6 -- Check the numbers of altered records before ...
8 -- Notes from Marc Cousin, the author of this script: 01Sep08
9 -- The script version won't work better with mysql without indexes.
11 -- The reason is that the script works with global queries instead of many small
12 -- queries like dbcheck. So PostgreSQL can optimise the query by building hash
13 -- joins or merge joins.
15 -- Mysql can't do that (last time I checked, at least ...), and will do nested
16 -- loops between job and file for instance. And without the missing indexes,
17 -- mysql will still be as slow as with dbcheck, as you'll more or less have
18 ----thousands of full scans on the job table (where postgresql will do only a few
19 -- to build its hash).
21 -- So for dbcheck with mysql, there is no other solution than adding the missing
22 -- indexes (but adding and dropping them just for the dbcheck is a good option).
25 -- - SELECT PathId,Path from Path "
26 -- "WHERE Path NOT LIKE '%/'
27 -- - ask for confirmation
28 -- - add a slash, doing one update for each record to be updated ...
31 -- UPDATE Path Set Path=Path || '/' WHERE Path NOT LIKE '%/'; # Should work everywhere
33 --repair_bad_filenames():
34 -- - SELECT FileNameId,Name from FileName
35 -- WHERE Name LIKE '%/'
36 -- - ask for confirmation
37 -- - remove the slash, one update per row ...
40 -- UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/'; # Works at least with Pg and Mysql
42 --eliminate_duplicate_filenames():
43 -- - Lists all min filenameids from filename where there are duplicates on name
44 -- - Updates filetable to this entry instead of one of its duplicates
45 -- - Deletes filenameids from filename which are duplicate and not the min filenameids
48 -- CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
49 -- CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
50 -- UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
51 -- DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
55 --eliminate_duplicate_paths():
56 -- Does exactly the same as above ...
59 -- CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
60 -- CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
61 -- UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
62 -- DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
67 --All the orphaned below delete records from a table when they are not referenced anymore in the others...
69 --eliminate_orphaned_jobmedia_records():
71 -- DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
73 --eliminate_orphaned_file_records():
75 -- DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
77 --eliminate_orphaned_path_records():
78 --Here, the problem is that File is a big table ... we'd better avoid NOT IN on it ...
80 -- CREATE TEMP TABLE t1 AS
82 -- FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
83 -- WHERE File.PathId IS NULL;
84 -- DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
87 --eliminate_orphaned_filename_records():
88 --Here, again, the problem is that File is a big table ... we'd better avoid NOT IN on it ...
90 -- CREATE TEMP TABLE t1 AS
91 -- SELECT FileName.FileNameId
92 -- FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
93 -- WHERE File.FileNameId IS NULL;
94 -- DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
97 --eliminate_orphaned_fileset_records():
100 -- DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
102 --eliminate_orphaned_client_records():
104 -- DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
106 --eliminate_orphaned_job_records():
108 -- DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
110 --eliminate_admin_records():
112 -- DELETE FROM Job WHERE Job.Type='D';
114 --eliminate_restore_records():
116 -- DELETE FROM Job WHERE Job.Type='R';
120 --One script that does it all :
126 SELECT('eliminate_admin_records()');
127 DELETE FROM Job WHERE Job.Type='D';
129 SELECT('eliminate_restore_records()');
130 DELETE FROM Job WHERE Job.Type='R';
132 SELECT('repair_bad_paths()');
133 UPDATE Path Set Path=Path||'/' WHERE Path NOT LIKE '%/' AND Path <> '';
135 SELECT('repair_bad_filenames()');
136 UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/';
138 SELECT('eliminate_duplicate_filenames()');
139 CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
140 CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
141 UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
142 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
146 SELECT('eliminate_duplicate_paths()');
147 CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
148 CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
149 UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
150 DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
154 SELECT('eliminate_orphaned_job_records()');
155 DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
157 SELECT('eliminate_orphaned_jobmedia_records()');
158 DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
160 SELECT('eliminate_orphaned_file_records()');
161 DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
163 SELECT('eliminate_orphaned_path_records()');
164 CREATE TEMP TABLE t1 AS
166 FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
167 WHERE File.PathId IS NULL;
168 DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
171 SELECT('eliminate_orphaned_filename_records()');
172 CREATE TEMP TABLE t1 AS
173 SELECT FileName.FileNameId
174 FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
175 WHERE File.FileNameId IS NULL;
176 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
179 SELECT('eliminate_orphaned_fileset_records()');
180 DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
182 SELECT('eliminate_orphaned_client_records()');
183 DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
186 SELECT('Now you should commit,');
187 SELECT('but check that the amount of deleted or updated data is sane...');
188 SELECT('If you''re sure, type ''COMMIT;''');
189 SELECT('THIS SCRIPT IS STILL BETA !');