From c30f2d03692a73c85b6e76aeab506e5848fad7b7 Mon Sep 17 00:00:00 2001 From: Kern Sibbald Date: Sat, 10 Feb 2007 20:15:58 +0000 Subject: [PATCH] Correct VolCatBytes bug from media patch git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/trunk@4159 91ce42f0-d328-0410-95d8-f526ca767f89 --- bacula/src/cats/sql_cmds.c | 63 ++++++++++++++ bacula/src/dird/ua_prune.c | 10 +-- bacula/src/stored/dev.c | 2 - bacula/src/tools/dbcheck.c | 168 +++++++++++++++++++++---------------- bacula/src/tools/testls.c | 0 bacula/technotes-2.1 | 8 +- 6 files changed, 169 insertions(+), 82 deletions(-) mode change 100755 => 100644 bacula/src/tools/testls.c diff --git a/bacula/src/cats/sql_cmds.c b/bacula/src/cats/sql_cmds.c index a92316db5a..2dc9501480 100644 --- a/bacula/src/cats/sql_cmds.c +++ b/bacula/src/cats/sql_cmds.c @@ -120,6 +120,7 @@ const char *insert_delcand = * At the same time, we select "orphanned" jobs * (i.e. no files, ...) for deletion. */ +#ifdef old_way const char *select_backup_del = "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " "FROM Job,DelCandidates " @@ -178,6 +179,68 @@ const char *select_migrate_del = "AND Job.ClientId=%s " "AND Job.Type='g')"; +#else +/* Faster way */ +const char *select_backup_del = + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " + "FROM Job,DelCandidates " + "WHERE (Job.JobId=DelCandidates.JobId AND ((DelCandidates.JobFiles=0) OR " + "(DelCandidates.JobStatus!='T'))) OR " + "(Job.JobTDate>%s " + "AND Job.ClientId=%s " + "AND Job.Level='F' AND Job.JobStatus='T' AND Job.Type IN ('B','M') " + "AND Job.FileSetId=DelCandidates.FileSetId)"; + +/* Select Jobs from the DelCandidates table that have a + * more recent InitCatalog -- i.e. are not the only InitCatalog + * This is the list of Jobs to delete for a Verify Job. + */ +const char *select_verify_del = + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " + "FROM Job,DelCandidates " + "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus!='T') OR " + "(Job.JobTDate>%s " + "AND Job.ClientId=%s " + "AND Job.Type='V' AND Job.Level='V' AND Job.JobStatus='T' " + "AND Job.FileSetId=DelCandidates.FileSetId)"; + + +/* Select Jobs from the DelCandidates table. + * This is the list of Jobs to delete for a Restore Job. + */ +const char *select_restore_del = + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " + "FROM Job,DelCandidates " + "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus!='T') OR " + "(Job.JobTDate>%s " + "AND Job.ClientId=%s " + "AND Job.Type='R')"; + +/* Select Jobs from the DelCandidates table. + * This is the list of Jobs to delete for an Admin Job. + */ +const char *select_admin_del = + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " + "FROM Job,DelCandidates " + "WHERE (Job.JobId=DelCandidates.JobId AND DelCandidates.JobStatus!='T') OR " + "(Job.JobTDate>%s " + "AND Job.ClientId=%s " + "AND Job.Type='D')"; + +/* + * Select Jobs from the DelCandidates table. + * This is the list of Jobs to delete for an Migrate Job. + */ +const char *select_migrate_del = + "SELECT DISTINCT DelCandidates.JobId,DelCandidates.PurgedFiles " + "FROM Job,DelCandidates " + "WHERE (Job.JobId=DelCanditates.JobId AND DelCandidates.JobStatus!='T') OR " + "(Job.JobTDate>%s " + "AND Job.ClientId=%s " + "AND Job.Type='g')"; + +#endif + /* ======= ua_restore.c */ const char *uar_count_files = "SELECT JobFiles FROM Job WHERE JobId=%s"; diff --git a/bacula/src/dird/ua_prune.c b/bacula/src/dird/ua_prune.c index 52fb760520..30e8dab2b2 100644 --- a/bacula/src/dird/ua_prune.c +++ b/bacula/src/dird/ua_prune.c @@ -393,19 +393,19 @@ int prune_jobs(UAContext *ua, CLIENT *client, int JobType) edit_int64(cr.ClientId, ed2); switch (JobType) { case JT_BACKUP: - Mmsg(query, select_backup_del, ed1, ed1, ed2); + Mmsg(query, select_backup_del, ed1, ed2); break; case JT_RESTORE: - Mmsg(query, select_restore_del, ed1, ed1, ed2); + Mmsg(query, select_restore_del, ed1, ed2); break; case JT_VERIFY: - Mmsg(query, select_verify_del, ed1, ed1, ed2); + Mmsg(query, select_verify_del, ed1, ed2); break; case JT_ADMIN: - Mmsg(query, select_admin_del, ed1, ed1, ed2); + Mmsg(query, select_admin_del, ed1, ed2); break; case JT_MIGRATE: - Mmsg(query, select_migrate_del, ed1, ed1, ed2); + Mmsg(query, select_migrate_del, ed1, ed2); break; } if (!db_sql_query(ua->db, query, job_delete_handler, (void *)&del)) { diff --git a/bacula/src/stored/dev.c b/bacula/src/stored/dev.c index 8ff9b6108c..a07fb361a4 100644 --- a/bacula/src/stored/dev.c +++ b/bacula/src/stored/dev.c @@ -2223,7 +2223,6 @@ ssize_t DEVICE::read(void *buf, size_t len) if (read_len > 0) { /* skip error */ DevReadBytes += read_len; - VolCatInfo.VolCatRBytes += read_len; } return read_len; @@ -2249,7 +2248,6 @@ ssize_t DEVICE::write(const void *buf, size_t len) if (write_len > 0) { /* skip error */ DevWriteBytes += write_len; - VolCatInfo.VolCatBytes += write_len; } return write_len; diff --git a/bacula/src/tools/dbcheck.c b/bacula/src/tools/dbcheck.c index e51c72c403..4e5eee8aab 100644 --- a/bacula/src/tools/dbcheck.c +++ b/bacula/src/tools/dbcheck.c @@ -702,135 +702,155 @@ static void eliminate_duplicate_paths() static void eliminate_orphaned_jobmedia_records() { - const char *query; + const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia " + "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) " + "WHERE Job.JobId IS NULL LIMIT 300000"; printf(_("Checking for orphaned JobMedia entries.\n")); - query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia " - "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) " - "WHERE Job.JobId IS NULL"; if (!make_id_list(query, &id_list)) { exit(1); } - printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids); - if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { - for (int i=0; i < id_list.num_ids; i++) { - char ed1[50]; - bsnprintf(buf, sizeof(buf), + /* Loop doing 300000 at a time */ + while (id_list.num_ids != 0) { + printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids); + if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { + for (int i=0; i < id_list.num_ids; i++) { + char ed1[50]; + bsnprintf(buf, sizeof(buf), "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media " "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId", - edit_int64(id_list.Id[i], ed1)); - if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) { - printf("%s\n", db_strerror(db)); + edit_int64(id_list.Id[i], ed1)); + if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) { + printf("%s\n", db_strerror(db)); + } } } - } - if (quit) { - return; - } + if (quit) { + return; + } - if (fix && id_list.num_ids > 0) { - printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids); - delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list); + if (fix && id_list.num_ids > 0) { + printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids); + delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list); + } + if (!make_id_list(query, &id_list)) { + exit(1); + } } } static void eliminate_orphaned_file_records() { - const char *query; + const char *query = "SELECT File.FileId,Job.JobId FROM File " + "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) " + "WHERE Job.JobId IS NULL LIMIT 300000"; printf(_("Checking for orphaned File entries. This may take some time!\n")); - query = "SELECT File.FileId,Job.JobId FROM File " - "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) " - "WHERE Job.JobId IS NULL"; if (verbose > 1) { printf("%s\n", query); } if (!make_id_list(query, &id_list)) { exit(1); } - printf(_("Found %d orphaned File records.\n"), id_list.num_ids); - if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { - for (int i=0; i < id_list.num_ids; i++) { - char ed1[50]; - bsnprintf(buf, sizeof(buf), + /* Loop doing 300000 at a time */ + while (id_list.num_ids != 0) { + printf(_("Found %d orphaned File records.\n"), id_list.num_ids); + if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { + for (int i=0; i < id_list.num_ids; i++) { + char ed1[50]; + bsnprintf(buf, sizeof(buf), "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename " "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId", - edit_int64(id_list.Id[i], ed1)); - if (!db_sql_query(db, buf, print_file_handler, NULL)) { - printf("%s\n", db_strerror(db)); + edit_int64(id_list.Id[i], ed1)); + if (!db_sql_query(db, buf, print_file_handler, NULL)) { + printf("%s\n", db_strerror(db)); + } } } - } - if (quit) { - return; - } - if (fix && id_list.num_ids > 0) { - printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids); - delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list); + if (quit) { + return; + } + if (fix && id_list.num_ids > 0) { + printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids); + delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list); + } + if (!make_id_list(query, &id_list)) { + exit(1); + } } } static void eliminate_orphaned_path_records() { - const char *query; + const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path " + "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) " + "WHERE File.PathId IS NULL LIMIT 300000"; printf(_("Checking for orphaned Path entries. This may take some time!\n")); - query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path " - "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) " - "WHERE File.PathId IS NULL"; if (verbose > 1) { printf("%s\n", query); } if (!make_id_list(query, &id_list)) { exit(1); } - printf(_("Found %d orphaned Path records.\n"), id_list.num_ids); - if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { - for (int i=0; i < id_list.num_ids; i++) { - char ed1[50]; - bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s", - edit_int64(id_list.Id[i], ed1)); - db_sql_query(db, buf, print_name_handler, NULL); + /* Loop doing 300000 at a time */ + while (id_list.num_ids != 0) { + printf(_("Found %d orphaned Path records.\n"), id_list.num_ids); + if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { + for (int i=0; i < id_list.num_ids; i++) { + char ed1[50]; + bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s", + edit_int64(id_list.Id[i], ed1)); + db_sql_query(db, buf, print_name_handler, NULL); + } + } + if (quit) { + return; + } + if (fix && id_list.num_ids > 0) { + printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids); + delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list); + } + if (!make_id_list(query, &id_list)) { + exit(1); } - } - if (quit) { - return; - } - if (fix && id_list.num_ids > 0) { - printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids); - delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list); } } static void eliminate_orphaned_filename_records() { - const char *query; + const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename " + "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) " + "WHERE File.FilenameId IS NULL LIMIT 300000"; printf(_("Checking for orphaned Filename entries. This may take some time!\n")); - query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename " - "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) " - "WHERE File.FilenameId IS NULL"; if (verbose > 1) { printf("%s\n", query); } if (!make_id_list(query, &id_list)) { exit(1); } - printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids); - if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { - for (int i=0; i < id_list.num_ids; i++) { - char ed1[50]; - bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s", - edit_int64(id_list.Id[i], ed1)); - db_sql_query(db, buf, print_name_handler, NULL); + /* Loop doing 300000 at a time */ + while (id_list.num_ids != 0) { + printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids); + if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) { + for (int i=0; i < id_list.num_ids; i++) { + char ed1[50]; + bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s", + edit_int64(id_list.Id[i], ed1)); + db_sql_query(db, buf, print_name_handler, NULL); + } + } + if (quit) { + return; + } + if (fix && id_list.num_ids > 0) { + printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids); + delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list); + } + if (!make_id_list(query, &id_list)) { + exit(1); } - } - if (quit) { - return; - } - if (fix && id_list.num_ids > 0) { - printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids); - delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list); } } diff --git a/bacula/src/tools/testls.c b/bacula/src/tools/testls.c old mode 100755 new mode 100644 diff --git a/bacula/technotes-2.1 b/bacula/technotes-2.1 index a70ce27f2a..ad5f0bde1f 100644 --- a/bacula/technotes-2.1 +++ b/bacula/technotes-2.1 @@ -1,7 +1,13 @@ Technical notes on version 2.1 -General: a +General: 10Feb07 +kes Correct VolCatBytes bug from media patch. +kes Apply patch from bug #612 by Rudolf Cejka to speedup pruning + in the case of orphaned records. +kes Modify dbcheck to handle orphaned JobMedia, Path, Filename, + and File records in 300K chunks to be more efficient. This + idea came from Alan Brown (if I remember right). kes Apply Eric's scratch patch that moves a purged Volume to the RecyclePool. Question: how is RecyclePool set? what happens to the ScratchPool? -- 2.39.5