git.sur5r.net Git - bacula/bacula/blob - bacula/examples/database/dbcheck.sql

   1
   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)
   4 -- then \i dbckeck.sql
   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 ...
   7 --
   8 --  Notes from Marc Cousin, the author of this script: 01Sep08
   9 --  The script version won't work better with mysql without indexes.
  10
  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.
  14
  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).
  20
  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).
  23
  24 --repair_bad_paths():
  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 ...
  29 --
  30 --Proposal :
  31 --    UPDATE Path Set Path=Path || '/' WHERE Path NOT LIKE '%/'; # Should work everywhere
  32 --
  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 ...
  38 --
  39 --Proposal :
  40 --    UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/'; # Works at least with Pg and Mysql
  41 --
  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
  46 --
  47 --Proposal :
  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);
  52 --    DROP TABLE t1;
  53 --    DROP TABLE t2;
  54 --
  55 --eliminate_duplicate_paths():
  56 --    Does exactly the same as above ...
  57 --
  58 --Proposal :
  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);
  63 --    DROP TABLE t1;
  64 --    DROP TABLE t2;
  65 --
  66 --
  67 --All the orphaned below delete records from a table when they are not referenced anymore in the others...
  68 --
  69 --eliminate_orphaned_jobmedia_records():
  70 --Proposal :
  71 --    DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
  72 --
  73 --eliminate_orphaned_file_records():
  74 --Proposal :
  75 --    DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
  76 --
  77 --eliminate_orphaned_path_records():
  78 --Here, the problem is that File is a big table ... we'd better avoid NOT IN on it ...
  79 --Proposal :
  80 --    CREATE TEMP TABLE t1 AS
  81 --        SELECT Path.PathId
  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);
  85 --    DROP TABLE t1;
  86 --
  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 ...
  89 --Proposal :
  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);
  95 --    DROP TABLE t1;
  96 --
  97 --eliminate_orphaned_fileset_records():
  98 --
  99 --Proposal :
 100 --    DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
 101 --
 102 --eliminate_orphaned_client_records():
 103 --Proposal :
 104 --    DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
 105 --
 106 --eliminate_orphaned_job_records():
 107 --Proposal :
 108 --    DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
 109 --
 110 --eliminate_admin_records():
 111 --Proposal :
 112 --    DELETE FROM Job WHERE Job.Type='D';
 113 --
 114 --eliminate_restore_records():
 115 --Proposal :
 116 --    DELETE FROM Job WHERE Job.Type='R';
 117 --
 118 --
 119 --
 120 --One script that does it all :
 121 --
 122 \t
 123 \a
 124 BEGIN;
 125 -- Uncomment to raise to '1GB' or more to get better results
 126 -- SET work_mem TO '1GB';
 127
 128 SELECT('eliminate_admin_records()');
 129 DELETE FROM Job WHERE Job.Type='D';
 130
 131 SELECT('eliminate_restore_records()');
 132 DELETE FROM Job WHERE Job.Type='R';
 133
 134 SELECT('repair_bad_paths()');
 135 UPDATE Path Set Path=Path||'/'  WHERE Path NOT LIKE '%/' AND Path <> '';
 136
 137 SELECT('repair_bad_filenames()');
 138 UPDATE FileName Set Name=substr(Name,1,char_length(Name)-1) WHERE Name LIKE '%/';
 139
 140 SELECT('eliminate_duplicate_filenames()');
 141 CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
 142 ANALYSE t1;
 143 CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
 144 ANALYSE t2;
 145 UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
 146 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
 147 DROP TABLE t1;
 148 DROP TABLE t2;
 149
 150 SELECT('eliminate_duplicate_paths()');
 151 CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
 152 ANALYSE t1;
 153 CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
 154 ANALYSE t2;
 155 UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
 156 DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
 157 DROP TABLE t1;
 158 DROP TABLE t2;
 159
 160 SELECT('eliminate_orphaned_job_records()');
 161 DELETE FROM Job WHERE ClientId NOT IN (SELECT ClientId FROM Client);
 162
 163 SELECT('eliminate_orphaned_jobmedia_records()');
 164 DELETE FROM JobMedia WHERE JobId NOT IN (SELECT JobId FROM Job) OR MediaID NOT IN (SELECT MediaID FROM Media);
 165
 166 SELECT('eliminate_orphaned_file_records()');
 167 DELETE FROM File WHERE JobId NOT IN (SELECT JobId FROM JOB);
 168
 169 SELECT('eliminate_orphaned_path_records()');
 170 CREATE TEMP TABLE t1 AS
 171     SELECT Path.PathId
 172     FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
 173     WHERE File.PathId IS NULL;
 174 ANALYSE t1;
 175 DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
 176 DROP TABLE t1;
 177
 178 SELECT('eliminate_orphaned_filename_records()');
 179 CREATE TEMP TABLE t1 AS
 180     SELECT FileName.FileNameId
 181     FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
 182     WHERE File.FileNameId IS NULL;
 183 ANALYSE t1;
 184 DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
 185 DROP TABLE t1;
 186
 187 SELECT('eliminate_orphaned_fileset_records()');
 188 DELETE FROM FileSet WHERE FileSetId NOT IN (SELECT DISTINCT FileSetId FROM Job);
 189
 190 SELECT('eliminate_orphaned_client_records()');
 191 DELETE FROM Client WHERE ClientId NOT IN (SELECT DISTINCT ClientId FROM Job);
 192
 193
 194 SELECT('Now you should commit,');
 195 SELECT('but check that the amount of deleted or updated data is sane...');
 196 SELECT('If you''re sure, type ''COMMIT;''');
 197 SELECT('THIS SCRIPT IS STILL BETA !');