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(const char *query, ID_LIST *id_list);
65 static int delete_id_list(const char *query, ID_LIST *id_list);
66 static int make_name_list(const 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(const 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(const 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 const 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);
175 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
178 Pmsg0(0, "Error no Director resource defined.\n");
181 set_working_directory(director->working_directory);
182 db_name = catalog->db_name;
183 user = catalog->db_user;
184 password = catalog->db_password;
185 dbhost = catalog->db_address;
186 if (dbhost && dbhost[0] == 0) {
192 Pmsg0(0, _("Wrong number of arguments.\n"));
197 Pmsg0(0, _("Working directory not supplied.\n"));
201 /* This is needed by SQLite to find the db */
202 working_directory = argv[0];
211 } else if (argc == 3) {
214 } else if (argc == 4) {
218 } else if (argc == 5) {
227 db = db_init_database(NULL, db_name, user, password, dbhost, 0, NULL);
228 if (!db_open_database(NULL, db)) {
229 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
235 repair_bad_filenames();
236 eliminate_duplicate_filenames();
237 eliminate_duplicate_paths();
238 eliminate_orphaned_jobmedia_records();
239 eliminate_orphaned_file_records();
240 eliminate_orphaned_path_records();
241 eliminate_orphaned_filename_records();
242 eliminate_orphaned_fileset_records();
244 do_interactive_mode();
247 db_close_database(NULL, db);
253 static void do_interactive_mode()
258 printf("Hello, this is the database check/correct program.\n\
259 Modify database is %s. Verbose is %s.\n\
260 Please select the fuction you want to perform.\n",
261 fix?"On":"Off", verbose?"On":"Off");
266 1) Toggle modify database flag\n\
267 2) Toggle verbose flag\n\
268 3) Repair bad Filename records\n\
269 4) Repair bad Path records\n\
270 5) Eliminate duplicate Filename records\n\
271 6) Eliminate duplicate Path records\n\
272 7) Eliminate orphaned Jobmedia records\n\
273 8) Eliminate orphaned File records\n\
274 9) Eliminate orphaned Path records\n\
275 10) Eliminate orphaned Filename records\n\
276 11) Eliminate orphaned FileSet records\n\
281 1) Toggle modify database flag\n\
282 2) Toggle verbose flag\n\
283 3) Check for bad Filename records\n\
284 4) Check for bad Path records\n\
285 5) Check for duplicate Filename records\n\
286 6) Check for duplicate Path records\n\
287 7) Check for orphaned Jobmedia records\n\
288 8) Check for orphaned File records\n\
289 9) Check for orphaned Path records\n\
290 10) Check for orphaned Filename records\n\
291 11) Check for orphaned FileSet records\n\
296 cmd = get_cmd(_("Select function number: "));
298 int item = atoi(cmd);
302 printf(_("Database will %sbe modified.\n"), fix?"":_("NOT "));
305 verbose = verbose?0:1;
306 printf(_("Verbose is %s\n"), verbose?_("On"):_("Off"));
309 repair_bad_filenames();
315 eliminate_duplicate_filenames();
318 eliminate_duplicate_paths();
321 eliminate_orphaned_jobmedia_records();
324 eliminate_orphaned_file_records();
327 eliminate_orphaned_path_records();
330 eliminate_orphaned_filename_records();
333 eliminate_orphaned_fileset_records();
336 repair_bad_filenames();
338 eliminate_duplicate_filenames();
339 eliminate_duplicate_paths();
340 eliminate_orphaned_jobmedia_records();
341 eliminate_orphaned_file_records();
342 eliminate_orphaned_path_records();
343 eliminate_orphaned_filename_records();
344 eliminate_orphaned_fileset_records();
354 static int print_name_handler(void *ctx, int num_fields, char **row)
357 printf("%s\n", row[0]);
362 static int get_name_handler(void *ctx, int num_fields, char **row)
364 POOLMEM *buf = (POOLMEM *)ctx;
366 pm_strcpy(&buf, row[0]);
372 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
374 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
375 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
379 static int print_file_handler(void *ctx, int num_fields, char **row)
381 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
382 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
386 static int print_fileset_handler(void *ctx, int num_fields, char **row)
388 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
389 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
395 * Called here with each id to be added to the list
397 static int id_list_handler(void *ctx, int num_fields, char **row)
399 ID_LIST *lst = (ID_LIST *)ctx;
401 if (lst->num_ids == MAX_ID_LIST_LEN) {
404 if (lst->num_ids == lst->max_ids) {
405 if (lst->max_ids == 0) {
407 lst->Id = (uint32_t *)bmalloc(sizeof(uint32_t) * lst->max_ids);
409 lst->max_ids = (lst->max_ids * 3) / 2;
410 lst->Id = (uint32_t *)brealloc(lst->Id, sizeof(uint32_t) * lst->max_ids);
413 lst->Id[lst->num_ids++] = (uint32_t)strtod(row[0], NULL);
418 * Construct record id list
420 static int make_id_list(const char *query, ID_LIST *id_list)
422 id_list->num_ids = 0;
423 id_list->num_del = 0;
424 id_list->tot_ids = 0;
426 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
427 printf("%s", db_strerror(db));
434 * Delete all entries in the list
436 static int delete_id_list(const char *query, ID_LIST *id_list)
438 for (int i=0; i < id_list->num_ids; i++) {
439 sprintf(buf, query, id_list->Id[i]);
441 printf("Deleting: %s\n", buf);
443 db_sql_query(db, buf, NULL, NULL);
449 * Called here with each name to be added to the list
451 static int name_list_handler(void *ctx, int num_fields, char **row)
453 NAME_LIST *name = (NAME_LIST *)ctx;
455 if (name->num_ids == MAX_ID_LIST_LEN) {
458 if (name->num_ids == name->max_ids) {
459 if (name->max_ids == 0) {
460 name->max_ids = 1000;
461 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
463 name->max_ids = (name->max_ids * 3) / 2;
464 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
467 name->name[name->num_ids++] = bstrdup(row[0]);
473 * Construct name list
475 static int make_name_list(const char *query, NAME_LIST *name_list)
477 name_list->num_ids = 0;
478 name_list->num_del = 0;
479 name_list->tot_ids = 0;
481 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
482 printf("%s", db_strerror(db));
489 * Print names in the list
491 static void print_name_list(NAME_LIST *name_list)
493 for (int i=0; i < name_list->num_ids; i++) {
494 printf("%s\n", name_list->name[i]);
500 * Free names in the list
502 static void free_name_list(NAME_LIST *name_list)
504 for (int i=0; i < name_list->num_ids; i++) {
505 free(name_list->name[i]);
507 name_list->num_ids = 0;
510 static void eliminate_duplicate_filenames()
515 printf("Checking for duplicate Filename entries.\n");
517 /* Make list of duplicated names */
518 query = "SELECT Name,count(Name) as Count FROM Filename GROUP BY Name "
521 if (!make_name_list(query, &name_list)) {
524 printf("Found %d duplicate Filename records.\n", name_list.num_ids);
525 if (name_list.num_ids && verbose && yes_no("Print the list? (yes/no): ")) {
526 print_name_list(&name_list);
529 /* Loop through list of duplicate names */
530 for (int i=0; i<name_list.num_ids; i++) {
531 /* Get all the Ids of each name */
532 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
533 sprintf(buf, "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
537 if (!make_id_list(buf, &id_list)) {
541 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
543 /* Force all records to use the first id then delete the other ids */
544 for (int j=1; j<id_list.num_ids; j++) {
545 sprintf(buf, "UPDATE File SET FilenameId=%u WHERE FilenameId=%u",
546 id_list.Id[0], id_list.Id[j]);
550 db_sql_query(db, buf, NULL, NULL);
551 sprintf(buf, "DELETE FROM Filename WHERE FilenameId=%u",
556 db_sql_query(db, buf, NULL, NULL);
560 free_name_list(&name_list);
563 static void eliminate_duplicate_paths()
568 printf(_("Checking for duplicate Path entries.\n"));
570 /* Make list of duplicated names */
572 query = "SELECT Path,count(Path) as Count FROM Path "
573 "GROUP BY Path HAVING Count > 1";
575 if (!make_name_list(query, &name_list)) {
578 printf("Found %d duplicate Path records.\n", name_list.num_ids);
579 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
580 print_name_list(&name_list);
583 /* Loop through list of duplicate names */
584 for (int i=0; i<name_list.num_ids; i++) {
585 /* Get all the Ids of each name */
586 db_escape_string(esc_name, name_list.name[i], strlen(name_list.name[i]));
587 sprintf(buf, "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
591 if (!make_id_list(buf, &id_list)) {
595 printf("Found %d for: %s\n", id_list.num_ids, name_list.name[i]);
597 /* Force all records to use the first id then delete the other ids */
598 for (int j=1; j<id_list.num_ids; j++) {
599 sprintf(buf, "UPDATE File SET PathId=%u WHERE PathId=%u",
600 id_list.Id[0], id_list.Id[j]);
604 db_sql_query(db, buf, NULL, NULL);
605 sprintf(buf, "DELETE FROM Path WHERE PathId=%u",
610 db_sql_query(db, buf, NULL, NULL);
614 free_name_list(&name_list);
617 static void eliminate_orphaned_jobmedia_records()
621 printf("Checking for orphaned JobMedia entries.\n");
622 query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
623 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
624 "WHERE Job.JobId IS NULL";
625 if (!make_id_list(query, &id_list)) {
628 printf("Found %d orphaned JobMedia records.\n", id_list.num_ids);
629 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
630 for (int i=0; i < id_list.num_ids; i++) {
632 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
633 "WHERE JobMedia.JobMediaId=%u AND Media.MediaId=JobMedia.MediaId", id_list.Id[i]);
634 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
635 printf("%s\n", db_strerror(db));
640 if (fix && id_list.num_ids > 0) {
641 printf("Deleting %d orphaned JobMedia records.\n", id_list.num_ids);
642 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%u", &id_list);
646 static void eliminate_orphaned_file_records()
650 printf("Checking for orphaned File entries. This may take some time!\n");
651 query = "SELECT File.FileId,Job.JobId FROM File "
652 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
653 "WHERE Job.JobId IS NULL";
655 printf("%s\n", query);
657 if (!make_id_list(query, &id_list)) {
660 printf("Found %d orphaned File records.\n", id_list.num_ids);
661 if (name_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
662 for (int i=0; i < id_list.num_ids; i++) {
664 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
665 "WHERE File.FileId=%u AND File.FilenameId=Filename.FilenameId", id_list.Id[i]);
666 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
667 printf("%s\n", db_strerror(db));
672 if (fix && id_list.num_ids > 0) {
673 printf("Deleting %d orphaned File records.\n", id_list.num_ids);
674 delete_id_list("DELETE FROM File WHERE FileId=%u", &id_list);
678 static void eliminate_orphaned_path_records()
682 printf("Checking for orphaned Path entries. This may take some time!\n");
683 query = "SELECT Path.PathId,File.PathId FROM Path "
684 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
685 "GROUP BY Path.PathId HAVING File.PathId IS NULL";
687 printf("%s\n", query);
689 if (!make_id_list(query, &id_list)) {
692 printf("Found %d orphaned Path records.\n", id_list.num_ids);
693 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
694 for (int i=0; i < id_list.num_ids; i++) {
695 sprintf(buf, "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
696 db_sql_query(db, buf, print_name_handler, NULL);
700 if (fix && id_list.num_ids > 0) {
701 printf("Deleting %d orphaned Path records.\n", id_list.num_ids);
702 delete_id_list("DELETE FROM Path WHERE PathId=%u", &id_list);
706 static void eliminate_orphaned_filename_records()
710 printf("Checking for orphaned Filename entries. This may take some time!\n");
711 query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
712 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
713 "WHERE File.FilenameId IS NULL";
715 printf("%s\n", query);
717 if (!make_id_list(query, &id_list)) {
720 printf("Found %d orphaned Filename records.\n", id_list.num_ids);
721 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
722 for (int i=0; i < id_list.num_ids; i++) {
723 sprintf(buf, "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
724 db_sql_query(db, buf, print_name_handler, NULL);
728 if (fix && id_list.num_ids > 0) {
729 printf("Deleting %d orphaned Filename records.\n", id_list.num_ids);
730 delete_id_list("DELETE FROM Filename WHERE FilenameId=%u", &id_list);
734 static void eliminate_orphaned_fileset_records()
738 printf("Checking for orphaned FileSet entries. This takes some time!\n");
739 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
740 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
741 "WHERE Job.FileSetId IS NULL";
743 printf("%s\n", query);
745 if (!make_id_list(query, &id_list)) {
748 printf("Found %d orphaned FileSet records.\n", id_list.num_ids);
749 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
750 for (int i=0; i < id_list.num_ids; i++) {
751 sprintf(buf, "SELECT FileSetId,FileSet,MD5 FROM FileSet "
752 "WHERE FileSetId=%u", id_list.Id[i]);
753 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
754 printf("%s\n", db_strerror(db));
759 if (fix && id_list.num_ids > 0) {
760 printf("Deleting %d orphaned FileSet records.\n", id_list.num_ids);
761 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%u", &id_list);
765 static void repair_bad_filenames()
770 printf("Checking for Filenames with a trailing slash\n");
771 query = "SELECT FilenameId,Name from Filename "
772 "WHERE Name LIKE '%/'";
774 printf("%s\n", query);
776 if (!make_id_list(query, &id_list)) {
779 printf("Found %d bad Filename records.\n", id_list.num_ids);
780 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
781 for (i=0; i < id_list.num_ids; i++) {
783 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
784 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
785 printf("%s\n", db_strerror(db));
790 if (fix && id_list.num_ids > 0) {
791 POOLMEM *name = get_pool_memory(PM_FNAME);
793 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
794 for (i=0; i < id_list.num_ids; i++) {
797 "SELECT Name FROM Filename WHERE FilenameId=%u", id_list.Id[i]);
798 if (!db_sql_query(db, buf, get_name_handler, name)) {
799 printf("%s\n", db_strerror(db));
801 /* Strip trailing slash(es) */
802 for (len=strlen(name); len > 0 && name[len-1]=='/'; len--)
810 db_escape_string(esc_name, name, len);
812 bsnprintf(buf, sizeof(buf),
813 "UPDATE Filename SET Name='%s' WHERE FilenameId=%u",
814 esc_name, id_list.Id[i]);
818 db_sql_query(db, buf, NULL, NULL);
823 static void repair_bad_paths()
828 printf("Checking for Paths without a trailing slash\n");
829 query = "SELECT PathId,Path from Path "
830 "WHERE Path NOT LIKE '%/'";
832 printf("%s\n", query);
834 if (!make_id_list(query, &id_list)) {
837 printf("Found %d bad Path records.\n", id_list.num_ids);
838 if (id_list.num_ids && verbose && yes_no("Print them? (yes/no): ")) {
839 for (i=0; i < id_list.num_ids; i++) {
841 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
842 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
843 printf("%s\n", db_strerror(db));
848 if (fix && id_list.num_ids > 0) {
849 POOLMEM *name = get_pool_memory(PM_FNAME);
851 printf("Reparing %d bad Filename records.\n", id_list.num_ids);
852 for (i=0; i < id_list.num_ids; i++) {
855 "SELECT Path FROM Path WHERE PathId=%u", id_list.Id[i]);
856 if (!db_sql_query(db, buf, get_name_handler, name)) {
857 printf("%s\n", db_strerror(db));
859 /* Strip trailing blanks */
860 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
863 /* Add trailing slash */
864 len = pm_strcat(&name, "/");
865 db_escape_string(esc_name, name, len);
866 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%u",
867 esc_name, id_list.Id[i]);
871 db_sql_query(db, buf, NULL, NULL);
880 * Gen next input command from the terminal
882 static char *get_cmd(const char *prompt)
884 static char cmd[1000];
886 printf("%s", prompt);
887 if (fgets(cmd, sizeof(cmd), stdin) == NULL)
890 strip_trailing_junk(cmd);
894 static int yes_no(const char *prompt)
897 cmd = get_cmd(prompt);
898 return strcasecmp(cmd, "yes") == 0;