3 * Program to check a Bacula database for consistency and to
6 * Kern E. Sibbald, August 2002
12 Copyright (C) 2000-2003 Kern Sibbald and John Walker
14 This program is free software; you can redistribute it and/or
15 modify it under the terms of the GNU General Public License as
16 published by the Free Software Foundation; either version 2 of
17 the License, or (at your option) any later version.
19 This program is distributed in the hope that it will be useful,
20 but WITHOUT ANY WARRANTY; without even the implied warranty of
21 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
22 General Public License for more details.
24 You should have received a copy of the GNU General Public
25 License along with this program; if not, write to the Free
26 Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
32 #include "cats/cats.h"
34 typedef struct s_id_ctx {
35 uint32_t *Id; /* ids to be modified */
36 int num_ids; /* ids stored */
37 int max_ids; /* size of array */
38 int num_del; /* number deleted */
39 int tot_ids; /* total to process */
42 typedef struct s_name_ctx {
43 char **name; /* list of names */
44 int num_ids; /* ids stored */
45 int max_ids; /* size of array */
46 int num_del; /* number deleted */
47 int tot_ids; /* total to process */
52 /* Global variables */
53 static bool fix = false;
54 static bool batch = false;
56 static ID_LIST id_list;
57 static NAME_LIST name_list;
58 static char buf[2000];
60 #define MAX_ID_LIST_LEN 1000000
62 /* Forward referenced functions */
63 static int make_id_list(char *query, ID_LIST *id_list);
64 static int delete_id_list(char *query, ID_LIST *id_list);
65 static int make_name_list(char *query, NAME_LIST *name_list);
66 static void print_name_list(NAME_LIST *name_list);
67 static void free_name_list(NAME_LIST *name_list);
68 static char *get_cmd(char *prompt);
69 static void eliminate_duplicate_filenames();
70 static void eliminate_duplicate_paths();
71 static void eliminate_orphaned_jobmedia_records();
72 static void eliminate_orphaned_file_records();
73 static void eliminate_orphaned_path_records();
74 static void eliminate_orphaned_filename_records();
75 static void eliminate_orphaned_fileset_records();
76 static void repair_bad_paths();
77 static void repair_bad_filenames();
78 static void do_interactive_mode();
79 static int yes_no(char *prompt);
85 "Usage: dbcheck [-d debug_level] <working-directory> <bacula-databse> <user> <password>\n"
87 " -dnn set debug level to nn\n"
88 " -f fix inconsistencies\n"
90 " -? print this message\n\n");
94 int main (int argc, char *argv[])
97 char *user, *password, *db_name;
99 my_name_is(argc, argv, "dbcheck");
100 init_msg(NULL, NULL); /* setup message handler */
102 memset(&id_list, 0, sizeof(id_list));
103 memset(&name_list, 0, sizeof(name_list));
106 while ((ch = getopt(argc, argv, "bd:fv?")) != -1) {
108 case 'b': /* batch */
112 case 'd': /* debug level */
113 debug_level = atoi(optarg);
114 if (debug_level <= 0)
118 case 'f': /* fix inconsistencies */
135 Pmsg0(0, _("Wrong number of arguments.\n"));
140 Pmsg0(0, _("Working directory not supplied.\n"));
144 /* This is needed by SQLite to find the db */
145 working_directory = argv[0];
153 } else if (argc == 3) {
156 } else if (argc == 4) {
163 db = db_init_database(NULL, db_name, user, password, NULL, 0, NULL);
164 if (!db_open_database(NULL, db)) {
165 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
170 repair_bad_filenames();
171 eliminate_duplicate_filenames();
172 eliminate_duplicate_paths();
173 eliminate_orphaned_jobmedia_records();
174 eliminate_orphaned_file_records();
175 eliminate_orphaned_path_records();
176 eliminate_orphaned_filename_records();
177 eliminate_orphaned_fileset_records();
179 do_interactive_mode();
182 db_close_database(NULL, db);
188 static void do_interactive_mode()
193 printf("Hello, this is the database check/correct program.\n\
194 Modify database is %s. Verbose is %s.\n\
195 Please select the fuction you want to perform.\n",
196 fix?"On":"Off", verbose?"On":"Off");
201 1) Toggle modify database flag\n\
202 2) Toggle verbose flag\n\
203 3) Repair bad Filename records\n\
204 4) Repair bad Path records\n\
205 5) Eliminate duplicate Filename records\n\
206 6) Eliminate duplicate Path records\n\
207 7) Eliminate orphaned Jobmedia records\n\
208 8) Eliminate orphaned File records\n\
209 9) Eliminate orphaned Path records\n\
210 10) Eliminate orphaned Filename records\n\
211 11) Eliminate orphaned FileSet records\n\
216 1) Toggle modify database flag\n\
217 2) Toggle verbose flag\n\
218 3) Check for bad Filename records\n\
219 4) Check for bad Path records\n\
220 5) Check for duplicate Filename records\n\
221 6) Check for duplicate Path records\n\
222 7) Check for orphaned Jobmedia records\n\
223 8) Check for orphaned File records\n\
224 9) Check for orphaned Path records\n\
225 10) Check for orphaned Filename records\n\
226 11) Check for orphaned FileSet records\n\
231 cmd = get_cmd(_("Select function number: "));
233 int item = atoi(cmd);
237 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
240 verbose = verbose?0:1;
241 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
244 repair_bad_filenames();
250 eliminate_duplicate_filenames();
253 eliminate_duplicate_paths();
256 eliminate_orphaned_jobmedia_records();
259 eliminate_orphaned_file_records();
262 eliminate_orphaned_path_records();
265 eliminate_orphaned_filename_records();
268 eliminate_orphaned_fileset_records();
271 repair_bad_filenames();
273 eliminate_duplicate_filenames();
274 eliminate_duplicate_paths();
275 eliminate_orphaned_jobmedia_records();
276 eliminate_orphaned_file_records();
277 eliminate_orphaned_path_records();
278 eliminate_orphaned_filename_records();
279 eliminate_orphaned_fileset_records();
289 static int print_name_handler(void *ctx, int num_fields, char **row)
292 printf("%s\n", row[0]);
297 static int get_name_handler(void *ctx, int num_fields, char **row)
299 POOLMEM *buf = (POOLMEM *)ctx;
301 pm_strcpy(&buf, row[0]);
307 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
309 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
310 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
314 static int print_file_handler(void *ctx, int num_fields, char **row)
316 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
317 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
321 static int print_fileset_handler(void *ctx, int num_fields, char **row)
323 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
324 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
330 * Called here with each id to be added to the list
332 static int id_list_handler(void *ctx, int num_fields, char **row)
334 ID_LIST *lst = (ID_LIST *)ctx;
336 if (lst->num_ids == MAX_ID_LIST_LEN) {
339 if (lst->num_ids == lst->max_ids) {
340 if (lst->max_ids == 0) {
342 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
344 lst->max_ids = (lst->max_ids * 3) / 2;
345 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
348 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
353 * Construct record id list
355 static int make_id_list(char *query, ID_LIST *id_list)
357 id_list->num_ids = 0;
358 id_list->num_del = 0;
359 id_list->tot_ids = 0;
361 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
362 printf("%s", db_strerror(db));
369 * Delete all entries in the list
371 static int delete_id_list(char *query, ID_LIST *id_list)
373 for (int i=0; i < id_list->num_ids; i++) {
374 sprintf(buf, query, id_list->Id[i]);
376 printf("Deleting: %s\n", buf);
378 db_sql_query(db, buf, NULL, NULL);
384 * Called here with each name to be added to the list
386 static int name_list_handler(void *ctx, int num_fields, char **row)
388 NAME_LIST *name = (NAME_LIST *)ctx;
390 if (name->num_ids == MAX_ID_LIST_LEN) {
393 if (name->num_ids == name->max_ids) {
394 if (name->max_ids == 0) {
395 name->max_ids = 1000;
396 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
398 name->max_ids = (name->max_ids * 3) / 2;
399 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
402 name->name[name->num_ids++] = bstrdup(row[0]);
408 * Construct name list
410 static int make_name_list(char *query, NAME_LIST *name_list)
412 name_list->num_ids = 0;
413 name_list->num_del = 0;
414 name_list->tot_ids = 0;
416 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
417 printf("%s", db_strerror(db));
424 * Print names in the list
426 static void print_name_list(NAME_LIST *name_list)
428 for (int i=0; i < name_list->num_ids; i++) {
429 printf("%s\n", name_list->name[i]);
435 * Free names in the list
437 static void free_name_list(NAME_LIST *name_list)
439 for (int i=0; i < name_list->num_ids; i++) {
440 free(name_list->name[i]);
442 name_list->num_ids = 0;
445 static void eliminate_duplicate_filenames()
450 printf("Checking for duplicate Filename entries.\n");
452 /* Make list of duplicated names */
453 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
456 if (!make_name_list(query, &name_list)) {
459 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
460 if (name_list.num_ids && verbose && yes_no("Print the list? (yes/no): ")) {
461 print_name_list(&name_list);
464 /* Loop through list of duplicate names */
465 for (int i=0; i<name_list.num_ids; i++) {
466 /* Get all the Ids of each name */
467 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
468 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
472 if (!make_id_list(buf, &id_list)) {
476 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
478 /* Force all records to use the first id then delete the other ids */
479 for (int j=1; j<id_list.num_ids; j++) {
480 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
481 id_list.Id[0], id_list.Id[j]);
485 db_sql_query(db, buf, NULL, NULL);
486 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
491 db_sql_query(db, buf, NULL, NULL);
495 free_name_list(&name_list);
498 static void eliminate_duplicate_paths()
503 printf(_("Checking for duplicate Path entries.\n"));
505 /* Make list of duplicated names */
507 query = "SELECT Path,count(Path) as Count FROM Path "
508 "GROUP BY Path HAVING Count > 1";
510 if (!make_name_list(query, &name_list)) {
513 printf("Found %d duplicate Path records.\n", name_list.num_ids);
514 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
515 print_name_list(&name_list);
518 /* Loop through list of duplicate names */
519 for (int i=0; i<name_list.num_ids; i++) {
520 /* Get all the Ids of each name */
521 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
522 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
526 if (!make_id_list(buf, &id_list)) {
530 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
532 /* Force all records to use the first id then delete the other ids */
533 for (int j=1; j<id_list.num_ids; j++) {
534 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
535 id_list.Id[0], id_list.Id[j]);
539 db_sql_query(db, buf, NULL, NULL);
540 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
545 db_sql_query(db, buf, NULL, NULL);
549 free_name_list(&name_list);
552 static void eliminate_orphaned_jobmedia_records()
556 printf("Checking for orphaned JobMedia entries.\n");
557 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
558 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
559 "WHERE Job.JobId IS NULL";
560 if (!make_id_list(query, &id_list)) {
563 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
564 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
565 for (int i=0; i < id_list.num_ids; i++) {
567 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
568 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
569 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
570 printf("%s\n", db_strerror(db));
575 if (fix && id_list.num_ids > 0) {
576 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
577 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
581 static void eliminate_orphaned_file_records()
585 printf("Checking for orphaned File entries. This may take some time!\n");
586 query = "SELECT File.FileId,Job.JobId FROM File "
587 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
588 "WHERE Job.JobId IS NULL";
590 printf("%s\n", query);
592 if (!make_id_list(query, &id_list)) {
595 printf("Found %d orphaned File records.\n", id_list.num_ids);
596 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
597 for (int i=0; i < id_list.num_ids; i++) {
599 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
600 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
601 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
602 printf("%s\n", db_strerror(db));
607 if (fix && id_list.num_ids > 0) {
608 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
609 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
613 static void eliminate_orphaned_path_records()
617 printf("Checking for orphaned Path entries. This may take some time!\n");
618 query = "SELECT Path.PathId,File.PathId FROM Path "
619 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
620 "GROUP BY Path.PathId HAVING File.PathId IS NULL";
622 printf("%s\n", query);
624 if (!make_id_list(query, &id_list)) {
627 printf("Found %d orphaned Path records.\n", id_list.num_ids);
628 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
629 for (int i=0; i < id_list.num_ids; i++) {
630 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
631 db_sql_query(db, buf, print_name_handler, NULL);
635 if (fix && id_list.num_ids > 0) {
636 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
637 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
641 static void eliminate_orphaned_filename_records()
645 printf("Checking for orphaned Filename entries. This may take some time!\n");
646 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
647 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
648 "WHERE File.FilenameId IS NULL";
650 printf("%s\n", query);
652 if (!make_id_list(query, &id_list)) {
655 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
656 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
657 for (int i=0; i < id_list.num_ids; i++) {
658 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
659 db_sql_query(db, buf, print_name_handler, NULL);
663 if (fix && id_list.num_ids > 0) {
664 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
665 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
669 static void eliminate_orphaned_fileset_records()
673 printf("Checking for orphaned FileSet entries. This takes some time!\n");
674 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
675 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
676 "WHERE Job.FileSetId IS NULL";
678 printf("%s\n", query);
680 if (!make_id_list(query, &id_list)) {
683 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
684 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
685 for (int i=0; i < id_list.num_ids; i++) {
686 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
687 "WHERE FileSetId=%u", id_list.Id[i]);
688 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
689 printf("%s\n", db_strerror(db));
694 if (fix && id_list.num_ids > 0) {
695 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
696 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
700 static void repair_bad_filenames()
705 printf("Checking for Filenames with a trailing slash\n");
706 query = "SELECT FilenameId,Name from Filename "
707 "WHERE Name LIKE '%/'";
709 printf("%s\n", query);
711 if (!make_id_list(query, &id_list)) {
714 printf("Found %d bad Filename records.\n", id_list.num_ids);
715 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
716 for (i=0; i < id_list.num_ids; i++) {
718 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
719 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
720 printf("%s\n", db_strerror(db));
725 if (fix && id_list.num_ids > 0) {
726 POOLMEM *name = get_pool_memory(PM_FNAME);
728 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
729 for (i=0; i < id_list.num_ids; i++) {
732 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
733 if (!db_sql_query(db, buf, get_name_handler, name)) {
734 printf("%s\n", db_strerror(db));
736 /* Strip trailing slash(es) */
737 for (len=strlen(name); len > 0 && name[len-1]=='/'; len--)
745 db_escape_string(esc_name, name, len);
747 bsnprintf(buf, sizeof(buf),
748 "UPDATE Filename SET Name='%s' WHERE FilenameId=%u",
749 esc_name, id_list.Id[i]);
753 db_sql_query(db, buf, NULL, NULL);
758 static void repair_bad_paths()
763 printf("Checking for Paths without a trailing slash\n");
764 query = "SELECT PathId,Path from Path "
765 "WHERE Path NOT LIKE '%/'";
767 printf("%s\n", query);
769 if (!make_id_list(query, &id_list)) {
772 printf("Found %d bad Path records.\n", id_list.num_ids);
773 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
774 for (i=0; i < id_list.num_ids; i++) {
776 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
777 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
778 printf("%s\n", db_strerror(db));
783 if (fix && id_list.num_ids > 0) {
784 POOLMEM *name = get_pool_memory(PM_FNAME);
786 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
787 for (i=0; i < id_list.num_ids; i++) {
790 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
791 if (!db_sql_query(db, buf, get_name_handler, name)) {
792 printf("%s\n", db_strerror(db));
794 /* Strip trailing blanks */
795 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
798 /* Add trailing slash */
799 len = pm_strcat(&name, "/");
800 db_escape_string(esc_name, name, len);
801 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%u",
802 esc_name, id_list.Id[i]);
806 db_sql_query(db, buf, NULL, NULL);
815 * Gen next input command from the terminal
817 static char *get_cmd(char *prompt)
819 static char cmd[1000];
821 printf("%s", prompt);
822 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
825 strip_trailing_junk(cmd);
829 static int yes_no(char *prompt)
832 cmd = get_cmd(prompt);
833 return strcasecmp(cmd, "yes") == 0;