3 * Program to check a Bacula database for consistency and to
6 * Kern E. Sibbald, August 2002
12 Copyright (C) 2000-2004 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"
33 #include "dird/dird_conf.h"
35 typedef struct s_id_ctx {
36 uint32_t *Id; /* ids to be modified */
37 int num_ids; /* ids stored */
38 int max_ids; /* size of array */
39 int num_del; /* number deleted */
40 int tot_ids; /* total to process */
43 typedef struct s_name_ctx {
44 char **name; /* list of names */
45 int num_ids; /* ids stored */
46 int max_ids; /* size of array */
47 int num_del; /* number deleted */
48 int tot_ids; /* total to process */
53 /* Global variables */
54 static bool fix = false;
55 static bool batch = false;
57 static ID_LIST id_list;
58 static NAME_LIST name_list;
59 static char buf[2000];
61 #define MAX_ID_LIST_LEN 1000000
63 /* Forward referenced functions */
64 static int make_id_list(char *query, ID_LIST *id_list);
65 static int delete_id_list(char *query, ID_LIST *id_list);
66 static int make_name_list(char *query, NAME_LIST *name_list);
67 static void print_name_list(NAME_LIST *name_list);
68 static void free_name_list(NAME_LIST *name_list);
69 static char *get_cmd(char *prompt);
70 static void eliminate_duplicate_filenames();
71 static void eliminate_duplicate_paths();
72 static void eliminate_orphaned_jobmedia_records();
73 static void eliminate_orphaned_file_records();
74 static void eliminate_orphaned_path_records();
75 static void eliminate_orphaned_filename_records();
76 static void eliminate_orphaned_fileset_records();
77 static void repair_bad_paths();
78 static void repair_bad_filenames();
79 static void do_interactive_mode();
80 static int yes_no(char *prompt);
86 "Usage: dbcheck [-c config] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>]\n"
88 " -C catalog name in the director conf file\n"
89 " -c director conf filename\n"
90 " -dnn set debug level to nn\n"
91 " -f fix inconsistencies\n"
93 " -? print this message\n\n");
97 int main (int argc, char *argv[])
100 char *user, *password, *db_name, *dbhost;
101 char *configfile = NULL;
102 char *catalogname = NULL;
104 my_name_is(argc, argv, "dbcheck");
105 init_msg(NULL, NULL); /* setup message handler */
107 memset(&id_list, 0, sizeof(id_list));
108 memset(&name_list, 0, sizeof(name_list));
111 while ((ch = getopt(argc, argv, "bc:C:d:fv?")) != -1) {
113 case 'b': /* batch */
117 case 'C': /* CatalogName */
118 catalogname = optarg;
121 case 'c': /* configfile */
125 case 'd': /* debug level */
126 debug_level = atoi(optarg);
127 if (debug_level <= 0)
131 case 'f': /* fix inconsistencies */
151 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
153 parse_config(configfile);
155 foreach_res(catalog, R_CATALOG) {
156 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
159 } else if (!catalogname) { // stop on first if no catalogname is given
167 Pmsg2(0, "Error can not find the Catalog name[%s] in the given config file [%s]\n", catalogname, configfile);
169 Pmsg1(0, "Error there is no Catalog section in the given config file [%s]\n", configfile);
173 db_name = catalog->db_name;
174 user = catalog->db_user;
175 password = catalog->db_password;
176 dbhost = (catalog->db_address[0] == '\0') ? NULL : catalog->db_address;
180 Pmsg0(0, _("Wrong number of arguments.\n"));
185 Pmsg0(0, _("Working directory not supplied.\n"));
189 /* This is needed by SQLite to find the db */
190 working_directory = argv[0];
199 } else if (argc == 3) {
202 } else if (argc == 4) {
206 } else if (argc == 5) {
215 db = db_init_database(NULL, db_name, user, password, dbhost, 0, NULL);
216 if (!db_open_database(NULL, db)) {
217 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
223 repair_bad_filenames();
224 eliminate_duplicate_filenames();
225 eliminate_duplicate_paths();
226 eliminate_orphaned_jobmedia_records();
227 eliminate_orphaned_file_records();
228 eliminate_orphaned_path_records();
229 eliminate_orphaned_filename_records();
230 eliminate_orphaned_fileset_records();
232 do_interactive_mode();
235 db_close_database(NULL, db);
241 static void do_interactive_mode()
246 printf("Hello, this is the database check/correct program.\n\
247 Modify database is %s. Verbose is %s.\n\
248 Please select the fuction you want to perform.\n",
249 fix?"On":"Off", verbose?"On":"Off");
254 1) Toggle modify database flag\n\
255 2) Toggle verbose flag\n\
256 3) Repair bad Filename records\n\
257 4) Repair bad Path records\n\
258 5) Eliminate duplicate Filename records\n\
259 6) Eliminate duplicate Path records\n\
260 7) Eliminate orphaned Jobmedia records\n\
261 8) Eliminate orphaned File records\n\
262 9) Eliminate orphaned Path records\n\
263 10) Eliminate orphaned Filename records\n\
264 11) Eliminate orphaned FileSet records\n\
269 1) Toggle modify database flag\n\
270 2) Toggle verbose flag\n\
271 3) Check for bad Filename records\n\
272 4) Check for bad Path records\n\
273 5) Check for duplicate Filename records\n\
274 6) Check for duplicate Path records\n\
275 7) Check for orphaned Jobmedia records\n\
276 8) Check for orphaned File records\n\
277 9) Check for orphaned Path records\n\
278 10) Check for orphaned Filename records\n\
279 11) Check for orphaned FileSet records\n\
284 cmd = get_cmd(_("Select function number: "));
286 int item = atoi(cmd);
290 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
293 verbose = verbose?0:1;
294 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
297 repair_bad_filenames();
303 eliminate_duplicate_filenames();
306 eliminate_duplicate_paths();
309 eliminate_orphaned_jobmedia_records();
312 eliminate_orphaned_file_records();
315 eliminate_orphaned_path_records();
318 eliminate_orphaned_filename_records();
321 eliminate_orphaned_fileset_records();
324 repair_bad_filenames();
326 eliminate_duplicate_filenames();
327 eliminate_duplicate_paths();
328 eliminate_orphaned_jobmedia_records();
329 eliminate_orphaned_file_records();
330 eliminate_orphaned_path_records();
331 eliminate_orphaned_filename_records();
332 eliminate_orphaned_fileset_records();
342 static int print_name_handler(void *ctx, int num_fields, char **row)
345 printf("%s\n", row[0]);
350 static int get_name_handler(void *ctx, int num_fields, char **row)
352 POOLMEM *buf = (POOLMEM *)ctx;
354 pm_strcpy(&buf, row[0]);
360 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
362 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
363 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
367 static int print_file_handler(void *ctx, int num_fields, char **row)
369 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
370 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
374 static int print_fileset_handler(void *ctx, int num_fields, char **row)
376 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
377 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
383 * Called here with each id to be added to the list
385 static int id_list_handler(void *ctx, int num_fields, char **row)
387 ID_LIST *lst = (ID_LIST *)ctx;
389 if (lst->num_ids == MAX_ID_LIST_LEN) {
392 if (lst->num_ids == lst->max_ids) {
393 if (lst->max_ids == 0) {
395 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
397 lst->max_ids = (lst->max_ids * 3) / 2;
398 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
401 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
406 * Construct record id list
408 static int make_id_list(char *query, ID_LIST *id_list)
410 id_list->num_ids = 0;
411 id_list->num_del = 0;
412 id_list->tot_ids = 0;
414 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
415 printf("%s", db_strerror(db));
422 * Delete all entries in the list
424 static int delete_id_list(char *query, ID_LIST *id_list)
426 for (int i=0; i < id_list->num_ids; i++) {
427 sprintf(buf, query, id_list->Id[i]);
429 printf("Deleting: %s\n", buf);
431 db_sql_query(db, buf, NULL, NULL);
437 * Called here with each name to be added to the list
439 static int name_list_handler(void *ctx, int num_fields, char **row)
441 NAME_LIST *name = (NAME_LIST *)ctx;
443 if (name->num_ids == MAX_ID_LIST_LEN) {
446 if (name->num_ids == name->max_ids) {
447 if (name->max_ids == 0) {
448 name->max_ids = 1000;
449 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
451 name->max_ids = (name->max_ids * 3) / 2;
452 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
455 name->name[name->num_ids++] = bstrdup(row[0]);
461 * Construct name list
463 static int make_name_list(char *query, NAME_LIST *name_list)
465 name_list->num_ids = 0;
466 name_list->num_del = 0;
467 name_list->tot_ids = 0;
469 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
470 printf("%s", db_strerror(db));
477 * Print names in the list
479 static void print_name_list(NAME_LIST *name_list)
481 for (int i=0; i < name_list->num_ids; i++) {
482 printf("%s\n", name_list->name[i]);
488 * Free names in the list
490 static void free_name_list(NAME_LIST *name_list)
492 for (int i=0; i < name_list->num_ids; i++) {
493 free(name_list->name[i]);
495 name_list->num_ids = 0;
498 static void eliminate_duplicate_filenames()
503 printf("Checking for duplicate Filename entries.\n");
505 /* Make list of duplicated names */
506 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
509 if (!make_name_list(query, &name_list)) {
512 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
513 if (name_list.num_ids && verbose && yes_no("Print the list? (yes/no): ")) {
514 print_name_list(&name_list);
517 /* Loop through list of duplicate names */
518 for (int i=0; i<name_list.num_ids; i++) {
519 /* Get all the Ids of each name */
520 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
521 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
525 if (!make_id_list(buf, &id_list)) {
529 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
531 /* Force all records to use the first id then delete the other ids */
532 for (int j=1; j<id_list.num_ids; j++) {
533 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
534 id_list.Id[0], id_list.Id[j]);
538 db_sql_query(db, buf, NULL, NULL);
539 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
544 db_sql_query(db, buf, NULL, NULL);
548 free_name_list(&name_list);
551 static void eliminate_duplicate_paths()
556 printf(_("Checking for duplicate Path entries.\n"));
558 /* Make list of duplicated names */
560 query = "SELECT Path,count(Path) as Count FROM Path "
561 "GROUP BY Path HAVING Count > 1";
563 if (!make_name_list(query, &name_list)) {
566 printf("Found %d duplicate Path records.\n", name_list.num_ids);
567 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
568 print_name_list(&name_list);
571 /* Loop through list of duplicate names */
572 for (int i=0; i<name_list.num_ids; i++) {
573 /* Get all the Ids of each name */
574 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
575 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
579 if (!make_id_list(buf, &id_list)) {
583 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
585 /* Force all records to use the first id then delete the other ids */
586 for (int j=1; j<id_list.num_ids; j++) {
587 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
588 id_list.Id[0], id_list.Id[j]);
592 db_sql_query(db, buf, NULL, NULL);
593 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
598 db_sql_query(db, buf, NULL, NULL);
602 free_name_list(&name_list);
605 static void eliminate_orphaned_jobmedia_records()
609 printf("Checking for orphaned JobMedia entries.\n");
610 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
611 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
612 "WHERE Job.JobId IS NULL";
613 if (!make_id_list(query, &id_list)) {
616 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
617 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
618 for (int i=0; i < id_list.num_ids; i++) {
620 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
621 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
622 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
623 printf("%s\n", db_strerror(db));
628 if (fix && id_list.num_ids > 0) {
629 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
630 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
634 static void eliminate_orphaned_file_records()
638 printf("Checking for orphaned File entries. This may take some time!\n");
639 query = "SELECT File.FileId,Job.JobId FROM File "
640 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
641 "WHERE Job.JobId IS NULL";
643 printf("%s\n", query);
645 if (!make_id_list(query, &id_list)) {
648 printf("Found %d orphaned File records.\n", id_list.num_ids);
649 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
650 for (int i=0; i < id_list.num_ids; i++) {
652 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
653 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
654 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
655 printf("%s\n", db_strerror(db));
660 if (fix && id_list.num_ids > 0) {
661 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
662 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
666 static void eliminate_orphaned_path_records()
670 printf("Checking for orphaned Path entries. This may take some time!\n");
671 query = "SELECT Path.PathId,File.PathId FROM Path "
672 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
673 "GROUP BY Path.PathId HAVING File.PathId IS NULL";
675 printf("%s\n", query);
677 if (!make_id_list(query, &id_list)) {
680 printf("Found %d orphaned Path records.\n", id_list.num_ids);
681 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
682 for (int i=0; i < id_list.num_ids; i++) {
683 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
684 db_sql_query(db, buf, print_name_handler, NULL);
688 if (fix && id_list.num_ids > 0) {
689 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
690 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
694 static void eliminate_orphaned_filename_records()
698 printf("Checking for orphaned Filename entries. This may take some time!\n");
699 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
700 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
701 "WHERE File.FilenameId IS NULL";
703 printf("%s\n", query);
705 if (!make_id_list(query, &id_list)) {
708 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
709 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
710 for (int i=0; i < id_list.num_ids; i++) {
711 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
712 db_sql_query(db, buf, print_name_handler, NULL);
716 if (fix && id_list.num_ids > 0) {
717 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
718 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
722 static void eliminate_orphaned_fileset_records()
726 printf("Checking for orphaned FileSet entries. This takes some time!\n");
727 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
728 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
729 "WHERE Job.FileSetId IS NULL";
731 printf("%s\n", query);
733 if (!make_id_list(query, &id_list)) {
736 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
737 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
738 for (int i=0; i < id_list.num_ids; i++) {
739 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
740 "WHERE FileSetId=%u", id_list.Id[i]);
741 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
742 printf("%s\n", db_strerror(db));
747 if (fix && id_list.num_ids > 0) {
748 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
749 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
753 static void repair_bad_filenames()
758 printf("Checking for Filenames with a trailing slash\n");
759 query = "SELECT FilenameId,Name from Filename "
760 "WHERE Name LIKE '%/'";
762 printf("%s\n", query);
764 if (!make_id_list(query, &id_list)) {
767 printf("Found %d bad Filename records.\n", id_list.num_ids);
768 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
769 for (i=0; i < id_list.num_ids; i++) {
771 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
772 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
773 printf("%s\n", db_strerror(db));
778 if (fix && id_list.num_ids > 0) {
779 POOLMEM *name = get_pool_memory(PM_FNAME);
781 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
782 for (i=0; i < id_list.num_ids; i++) {
785 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
786 if (!db_sql_query(db, buf, get_name_handler, name)) {
787 printf("%s\n", db_strerror(db));
789 /* Strip trailing slash(es) */
790 for (len=strlen(name); len > 0 && name[len-1]=='/'; len--)
798 db_escape_string(esc_name, name, len);
800 bsnprintf(buf, sizeof(buf),
801 "UPDATE Filename SET Name='%s' WHERE FilenameId=%u",
802 esc_name, id_list.Id[i]);
806 db_sql_query(db, buf, NULL, NULL);
811 static void repair_bad_paths()
816 printf("Checking for Paths without a trailing slash\n");
817 query = "SELECT PathId,Path from Path "
818 "WHERE Path NOT LIKE '%/'";
820 printf("%s\n", query);
822 if (!make_id_list(query, &id_list)) {
825 printf("Found %d bad Path records.\n", id_list.num_ids);
826 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
827 for (i=0; i < id_list.num_ids; i++) {
829 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
830 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
831 printf("%s\n", db_strerror(db));
836 if (fix && id_list.num_ids > 0) {
837 POOLMEM *name = get_pool_memory(PM_FNAME);
839 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
840 for (i=0; i < id_list.num_ids; i++) {
843 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
844 if (!db_sql_query(db, buf, get_name_handler, name)) {
845 printf("%s\n", db_strerror(db));
847 /* Strip trailing blanks */
848 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
851 /* Add trailing slash */
852 len = pm_strcat(&name, "/");
853 db_escape_string(esc_name, name, len);
854 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%u",
855 esc_name, id_list.Id[i]);
859 db_sql_query(db, buf, NULL, NULL);
868 * Gen next input command from the terminal
870 static char *get_cmd(char *prompt)
872 static char cmd[1000];
874 printf("%s", prompt);
875 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
878 strip_trailing_junk(cmd);
882 static int yes_no(char *prompt)
885 cmd = get_cmd(prompt);
886 return strcasecmp(cmd, "yes") == 0;