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);
469 if (!make_id_list(buf, &id_list)) {
473 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
475 /* Force all records to use the first id then delete the other ids */
476 for (int j=1; j<id_list.num_ids; j++) {
477 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
478 id_list.Id[0], id_list.Id[j]);
479 db_sql_query(db, buf, NULL, NULL);
480 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
482 db_sql_query(db, buf, NULL, NULL);
486 free_name_list(&name_list);
489 static void eliminate_duplicate_paths()
494 printf(_("Checking for duplicate Path entries.\n"));
496 /* Make list of duplicated names */
498 query = "SELECT Path,count(Path) as Count FROM Path "
499 "GROUP BY Path HAVING Count > 1";
501 if (!make_name_list(query, &name_list)) {
504 printf("Found %d duplicate Path records.\n", name_list.num_ids);
505 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
506 print_name_list(&name_list);
509 /* Loop through list of duplicate names */
510 for (int i=0; i<name_list.num_ids; i++) {
511 /* Get all the Ids of each name */
512 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
513 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
515 if (!make_id_list(buf, &id_list)) {
519 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
521 /* Force all records to use the first id then delete the other ids */
522 for (int j=1; j<id_list.num_ids; j++) {
523 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
524 id_list.Id[0], id_list.Id[j]);
525 db_sql_query(db, buf, NULL, NULL);
526 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
528 db_sql_query(db, buf, NULL, NULL);
532 free_name_list(&name_list);
535 static void eliminate_orphaned_jobmedia_records()
539 printf("Checking for orphaned JobMedia entries.\n");
540 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
541 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
542 "WHERE Job.JobId IS NULL";
543 if (!make_id_list(query, &id_list)) {
546 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
547 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
548 for (int i=0; i < id_list.num_ids; i++) {
550 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
551 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
552 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
553 printf("%s\n", db_strerror(db));
558 if (fix && id_list.num_ids > 0) {
559 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
560 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
564 static void eliminate_orphaned_file_records()
568 printf("Checking for orphaned File entries. This may take some time!\n");
569 query = "SELECT File.FileId,Job.JobId FROM File "
570 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
571 "WHERE Job.JobId IS NULL";
572 if (!make_id_list(query, &id_list)) {
575 printf("Found %d orphaned File records.\n", id_list.num_ids);
576 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
577 for (int i=0; i < id_list.num_ids; i++) {
579 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
580 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
581 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
582 printf("%s\n", db_strerror(db));
587 if (fix && id_list.num_ids > 0) {
588 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
589 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
593 static void eliminate_orphaned_path_records()
597 printf("Checking for orphaned Path entries. This may take some time!\n");
598 query = "SELECT Path.PathId,File.PathId FROM Path "
599 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
600 "GROUP BY Path.PathId HAVING File.PathId IS NULL";
601 if (!make_id_list(query, &id_list)) {
604 printf("Found %d orphaned Path records.\n", id_list.num_ids);
605 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
606 for (int i=0; i < id_list.num_ids; i++) {
607 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
608 db_sql_query(db, buf, print_name_handler, NULL);
612 if (fix && id_list.num_ids > 0) {
613 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
614 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
618 static void eliminate_orphaned_filename_records()
622 printf("Checking for orphaned Filename entries. This may take some time!\n");
623 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
624 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
625 "WHERE File.FilenameId IS NULL";
627 if (!make_id_list(query, &id_list)) {
630 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
631 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
632 for (int i=0; i < id_list.num_ids; i++) {
633 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
634 db_sql_query(db, buf, print_name_handler, NULL);
638 if (fix && id_list.num_ids > 0) {
639 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
640 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
644 static void eliminate_orphaned_fileset_records()
648 printf("Checking for orphaned FileSet entries. This takes some time!\n");
649 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
650 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
651 "WHERE Job.FileSetId IS NULL";
652 if (!make_id_list(query, &id_list)) {
655 printf("Found %d orphaned FileSet 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 FileSetId,FileSet,MD5 FROM FileSet "
659 "WHERE FileSetId=%u", id_list.Id[i]);
660 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
661 printf("%s\n", db_strerror(db));
666 if (fix && id_list.num_ids > 0) {
667 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
668 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
672 static void repair_bad_filenames()
677 printf("Checking for Filenames with a trailing slash\n");
678 query = "SELECT FilenameId,Name from Filename "
679 "WHERE Name LIKE '%/'";
680 if (!make_id_list(query, &id_list)) {
683 printf("Found %d bad Filename records.\n", id_list.num_ids);
684 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
685 for (i=0; i < id_list.num_ids; i++) {
687 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
688 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
689 printf("%s\n", db_strerror(db));
694 if (fix && id_list.num_ids > 0) {
695 POOLMEM *name = get_pool_memory(PM_FNAME);
697 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
698 for (i=0; i < id_list.num_ids; i++) {
701 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
702 if (!db_sql_query(db, buf, get_name_handler, name)) {
703 printf("%s\n", db_strerror(db));
705 /* Strip trailing slash(es) */
706 for (len=strlen(name); len > 1 && name[len-1]; len--)
708 db_escape_string(esc_name, name, len);
709 bsnprintf(buf, sizeof(buf),
710 "UPDATE Filename SET Name='%s' WHERE FilenameId=%u", id_list.Id[i]);
711 db_sql_query(db, buf, NULL, NULL);
716 static void repair_bad_paths()
721 printf("Checking for Paths without a trailing slash\n");
722 query = "SELECT PathId,Path from Path "
723 "WHERE Path IS NOT LIKE '%/'";
724 if (!make_id_list(query, &id_list)) {
727 printf("Found %d bad Path records.\n", id_list.num_ids);
728 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
729 for (i=0; i < id_list.num_ids; i++) {
731 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
732 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
733 printf("%s\n", db_strerror(db));
738 if (fix && id_list.num_ids > 0) {
739 POOLMEM *name = get_pool_memory(PM_FNAME);
741 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
742 for (i=0; i < id_list.num_ids; i++) {
744 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
745 if (!db_sql_query(db, buf, get_name_handler, name)) {
746 printf("%s\n", db_strerror(db));
748 /* Add trailing slash */
749 int len = pm_strcat(&name, "/");
750 db_escape_string(esc_name, name, len);
751 bsnprintf(buf, sizeof(buf),
752 "UPDATE Path SET Path='%s' WHERE PathId=%u", id_list.Id[i]);
753 db_sql_query(db, buf, NULL, NULL);
762 * Gen next input command from the terminal
764 static char *get_cmd(char *prompt)
766 static char cmd[1000];
768 printf("%s", prompt);
769 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
772 strip_trailing_junk(cmd);
776 static int yes_no(char *prompt)
779 cmd = get_cmd(prompt);
780 return strcasecmp(cmd, "yes") == 0;