2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2011 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
30 * Program to check a Bacula database for consistency and to
33 * Kern E. Sibbald, August 2002
38 #include "cats/cats.h"
39 #include "cats/sql_glue.h"
40 #include "lib/runscript.h"
41 #include "dird/dird_conf.h"
43 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
46 int generate_daemon_event(JCR *jcr, const char *event)
49 typedef struct s_id_ctx {
50 int64_t *Id; /* ids to be modified */
51 int num_ids; /* ids stored */
52 int max_ids; /* size of array */
53 int num_del; /* number deleted */
54 int tot_ids; /* total to process */
57 typedef struct s_name_ctx {
58 char **name; /* list of names */
59 int num_ids; /* ids stored */
60 int max_ids; /* size of array */
61 int num_del; /* number deleted */
62 int tot_ids; /* total to process */
65 /* Global variables */
66 static bool fix = false;
67 static bool batch = false;
69 static ID_LIST id_list;
70 static NAME_LIST name_list;
71 static char buf[20000];
72 static bool quit = false;
73 static CONFIG *config;
75 #define MAX_ID_LIST_LEN 10000000
77 /* Forward referenced functions */
78 static int make_id_list(const char *query, ID_LIST *id_list);
79 static int delete_id_list(const char *query, ID_LIST *id_list);
80 static int make_name_list(const char *query, NAME_LIST *name_list);
81 static void print_name_list(NAME_LIST *name_list);
82 static void free_name_list(NAME_LIST *name_list);
83 static char *get_cmd(const char *prompt);
84 static void eliminate_duplicate_filenames();
85 static void eliminate_duplicate_paths();
86 static void eliminate_orphaned_jobmedia_records();
87 static void eliminate_orphaned_file_records();
88 static void eliminate_orphaned_path_records();
89 static void eliminate_orphaned_filename_records();
90 static void eliminate_orphaned_fileset_records();
91 static void eliminate_orphaned_client_records();
92 static void eliminate_orphaned_job_records();
93 static void eliminate_admin_records();
94 static void eliminate_restore_records();
95 static void repair_bad_paths();
96 static void repair_bad_filenames();
97 static void do_interactive_mode();
98 static bool yes_no(const char *prompt);
99 static bool check_idx(const char *col_name);
100 static bool create_tmp_idx(const char *idx_name, const char *table_name,
101 const char *col_name);
102 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
104 static int check_idx_handler(void *ctx, int num_fields, char **row);
108 /* Global variables */
109 static const char *idx_tmp_name;
114 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
116 " -C catalog name in the director conf file\n"
117 " -c Director conf filename\n"
118 " -B print catalog configuration and exit\n"
119 " -d <nn> set debug level to <nn>\n"
120 " -dt print a timestamp in debug output\n"
121 " -f fix inconsistencies\n"
122 " -t test if client library is thread-safe\n"
124 " -? print this message\n\n");
128 int main (int argc, char *argv[])
131 const char *user, *password, *db_name, *dbhost;
133 bool print_catalog=false;
134 char *configfile = NULL;
135 char *catalogname = NULL;
138 setlocale(LC_ALL, "");
139 bindtextdomain("bacula", LOCALEDIR);
140 textdomain("bacula");
143 my_name_is(argc, argv, "dbcheck");
144 init_msg(NULL, NULL); /* setup message handler */
146 memset(&id_list, 0, sizeof(id_list));
147 memset(&name_list, 0, sizeof(name_list));
149 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
152 print_catalog = true; /* get catalog information from config */
155 case 'b': /* batch */
159 case 'C': /* CatalogName */
160 catalogname = optarg;
163 case 'c': /* configfile */
167 case 'd': /* debug level */
168 if (*optarg == 't') {
169 dbg_timestamp = true;
171 debug_level = atoi(optarg);
172 if (debug_level <= 0) {
178 case 'f': /* fix inconsistencies */
200 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
202 config = new_config_parser();
203 parse_dir_config(config, configfile, M_ERROR_TERM);
205 foreach_res(catalog, R_CATALOG) {
206 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
209 } else if (!catalogname) { // stop on first if no catalogname is given
217 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
219 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
225 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
228 Pmsg0(0, _("Error no Director resource defined.\n"));
231 set_working_directory(director->working_directory);
233 /* Print catalog information and exit (-B) */
235 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
236 printf("%s\nworking_dir=%s\n", catalog->display(buf),
238 free_pool_memory(buf);
242 db_name = catalog->db_name;
243 user = catalog->db_user;
244 password = catalog->db_password;
245 dbhost = catalog->db_address;
246 if (dbhost && dbhost[0] == 0) {
249 dbport = catalog->db_port;
253 Pmsg0(0, _("Wrong number of arguments.\n"));
258 Pmsg0(0, _("Working directory not supplied.\n"));
262 /* This is needed by SQLite to find the db */
263 working_directory = argv[0];
272 } else if (argc == 3) {
275 } else if (argc == 4) {
279 } else if (argc == 5) {
284 } else if (argc == 6) {
290 dbport = strtol(argv[5], &endptr, 10);
291 if (*endptr != '\0') {
292 Pmsg0(0, _("Database port must be a numeric value.\n"));
294 } else if (errno == ERANGE) {
295 Pmsg0(0, _("Database port must be a int value.\n"));
302 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
303 if (!db_open_database(NULL, db)) {
304 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
308 /* drop temporary index idx_tmp_name if it already exists */
309 drop_tmp_idx("idxPIchk", "File");
313 repair_bad_filenames();
314 eliminate_duplicate_filenames();
315 eliminate_duplicate_paths();
316 eliminate_orphaned_jobmedia_records();
317 eliminate_orphaned_file_records();
318 eliminate_orphaned_path_records();
319 eliminate_orphaned_filename_records();
320 eliminate_orphaned_fileset_records();
321 eliminate_orphaned_client_records();
322 eliminate_orphaned_job_records();
323 eliminate_admin_records();
324 eliminate_restore_records();
326 do_interactive_mode();
329 /* drop temporary index idx_tmp_name */
330 drop_tmp_idx("idxPIchk", "File");
332 db_close_database(NULL, db);
339 static void do_interactive_mode()
343 printf(_("Hello, this is the database check/correct program.\n"));
345 printf(_("Modify database is on."));
347 printf(_("Modify database is off."));
349 printf(_(" Verbose is on.\n"));
351 printf(_(" Verbose is off.\n"));
353 printf(_("Please select the function you want to perform.\n"));
358 " 1) Toggle modify database flag\n"
359 " 2) Toggle verbose flag\n"
360 " 3) Repair bad Filename records\n"
361 " 4) Repair bad Path records\n"
362 " 5) Eliminate duplicate Filename records\n"
363 " 6) Eliminate duplicate Path records\n"
364 " 7) Eliminate orphaned Jobmedia records\n"
365 " 8) Eliminate orphaned File records\n"
366 " 9) Eliminate orphaned Path records\n"
367 " 10) Eliminate orphaned Filename records\n"
368 " 11) Eliminate orphaned FileSet records\n"
369 " 12) Eliminate orphaned Client records\n"
370 " 13) Eliminate orphaned Job records\n"
371 " 14) Eliminate all Admin records\n"
372 " 15) Eliminate all Restore records\n"
377 " 1) Toggle modify database flag\n"
378 " 2) Toggle verbose flag\n"
379 " 3) Check for bad Filename records\n"
380 " 4) Check for bad Path records\n"
381 " 5) Check for duplicate Filename records\n"
382 " 6) Check for duplicate Path records\n"
383 " 7) Check for orphaned Jobmedia records\n"
384 " 8) Check for orphaned File records\n"
385 " 9) Check for orphaned Path records\n"
386 " 10) Check for orphaned Filename records\n"
387 " 11) Check for orphaned FileSet records\n"
388 " 12) Check for orphaned Client records\n"
389 " 13) Check for orphaned Job records\n"
390 " 14) Check for all Admin records\n"
391 " 15) Check for all Restore records\n"
396 cmd = get_cmd(_("Select function number: "));
398 int item = atoi(cmd);
403 printf(_("Database will be modified.\n"));
405 printf(_("Database will NOT be modified.\n"));
408 verbose = verbose?0:1;
410 printf(_(" Verbose is on.\n"));
412 printf(_(" Verbose is off.\n"));
415 repair_bad_filenames();
421 eliminate_duplicate_filenames();
424 eliminate_duplicate_paths();
427 eliminate_orphaned_jobmedia_records();
430 eliminate_orphaned_file_records();
433 eliminate_orphaned_path_records();
436 eliminate_orphaned_filename_records();
439 eliminate_orphaned_fileset_records();
442 eliminate_orphaned_client_records();
445 eliminate_orphaned_job_records();
448 eliminate_admin_records();
451 eliminate_restore_records();
454 repair_bad_filenames();
456 eliminate_duplicate_filenames();
457 eliminate_duplicate_paths();
458 eliminate_orphaned_jobmedia_records();
459 eliminate_orphaned_file_records();
460 eliminate_orphaned_path_records();
461 eliminate_orphaned_filename_records();
462 eliminate_orphaned_fileset_records();
463 eliminate_orphaned_client_records();
464 eliminate_orphaned_job_records();
465 eliminate_admin_records();
466 eliminate_restore_records();
476 static int print_name_handler(void *ctx, int num_fields, char **row)
479 printf("%s\n", row[0]);
484 static int get_name_handler(void *ctx, int num_fields, char **row)
486 POOLMEM *buf = (POOLMEM *)ctx;
488 pm_strcpy(&buf, row[0]);
493 static int print_job_handler(void *ctx, int num_fields, char **row)
495 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
496 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
501 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
503 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
504 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
508 static int print_file_handler(void *ctx, int num_fields, char **row)
510 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
511 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
515 static int print_fileset_handler(void *ctx, int num_fields, char **row)
517 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
518 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
522 static int print_client_handler(void *ctx, int num_fields, char **row)
524 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
525 NPRT(row[0]), NPRT(row[1]));
531 * Called here with each id to be added to the list
533 static int id_list_handler(void *ctx, int num_fields, char **row)
535 ID_LIST *lst = (ID_LIST *)ctx;
537 if (lst->num_ids == MAX_ID_LIST_LEN) {
540 if (lst->num_ids == lst->max_ids) {
541 if (lst->max_ids == 0) {
542 lst->max_ids = 10000;
543 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
545 lst->max_ids = (lst->max_ids * 3) / 2;
546 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
549 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
554 * Construct record id list
556 static int make_id_list(const char *query, ID_LIST *id_list)
558 id_list->num_ids = 0;
559 id_list->num_del = 0;
560 id_list->tot_ids = 0;
562 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
563 printf("%s", db_strerror(db));
570 * Delete all entries in the list
572 static int delete_id_list(const char *query, ID_LIST *id_list)
575 for (int i=0; i < id_list->num_ids; i++) {
576 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
578 printf(_("Deleting: %s\n"), buf);
580 db_sql_query(db, buf, NULL, NULL);
586 * Called here with each name to be added to the list
588 static int name_list_handler(void *ctx, int num_fields, char **row)
590 NAME_LIST *name = (NAME_LIST *)ctx;
592 if (name->num_ids == MAX_ID_LIST_LEN) {
595 if (name->num_ids == name->max_ids) {
596 if (name->max_ids == 0) {
597 name->max_ids = 10000;
598 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
600 name->max_ids = (name->max_ids * 3) / 2;
601 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
604 name->name[name->num_ids++] = bstrdup(row[0]);
610 * Construct name list
612 static int make_name_list(const char *query, NAME_LIST *name_list)
614 name_list->num_ids = 0;
615 name_list->num_del = 0;
616 name_list->tot_ids = 0;
618 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
619 printf("%s", db_strerror(db));
626 * Print names in the list
628 static void print_name_list(NAME_LIST *name_list)
630 for (int i=0; i < name_list->num_ids; i++) {
631 printf("%s\n", name_list->name[i]);
637 * Free names in the list
639 static void free_name_list(NAME_LIST *name_list)
641 for (int i=0; i < name_list->num_ids; i++) {
642 free(name_list->name[i]);
644 name_list->num_ids = 0;
647 static void eliminate_duplicate_filenames()
652 printf(_("Checking for duplicate Filename entries.\n"));
654 /* Make list of duplicated names */
655 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
656 "HAVING count(Name) > 1";
658 if (!make_name_list(query, &name_list)) {
661 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
662 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
663 print_name_list(&name_list);
669 /* Loop through list of duplicate names */
670 for (int i=0; i<name_list.num_ids; i++) {
671 /* Get all the Ids of each name */
672 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
673 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
677 if (!make_id_list(buf, &id_list)) {
681 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
683 /* Force all records to use the first id then delete the other ids */
684 for (int j=1; j<id_list.num_ids; j++) {
685 char ed1[50], ed2[50];
686 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
687 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
691 db_sql_query(db, buf, NULL, NULL);
692 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
697 db_sql_query(db, buf, NULL, NULL);
701 free_name_list(&name_list);
704 static void eliminate_duplicate_paths()
709 printf(_("Checking for duplicate Path entries.\n"));
711 /* Make list of duplicated names */
713 query = "SELECT Path, count(Path) as Count FROM Path "
714 "GROUP BY Path HAVING count(Path) > 1";
716 if (!make_name_list(query, &name_list)) {
719 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
720 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
721 print_name_list(&name_list);
727 /* Loop through list of duplicate names */
728 for (int i=0; i<name_list.num_ids; i++) {
729 /* Get all the Ids of each name */
730 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
731 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
735 if (!make_id_list(buf, &id_list)) {
739 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
741 /* Force all records to use the first id then delete the other ids */
742 for (int j=1; j<id_list.num_ids; j++) {
743 char ed1[50], ed2[50];
744 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
745 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
749 db_sql_query(db, buf, NULL, NULL);
750 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
754 db_sql_query(db, buf, NULL, NULL);
758 free_name_list(&name_list);
761 static void eliminate_orphaned_jobmedia_records()
763 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
764 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
765 "WHERE Job.JobId IS NULL LIMIT 300000";
767 printf(_("Checking for orphaned JobMedia entries.\n"));
768 if (!make_id_list(query, &id_list)) {
771 /* Loop doing 300000 at a time */
772 while (id_list.num_ids != 0) {
773 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
774 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
775 for (int i=0; i < id_list.num_ids; i++) {
777 bsnprintf(buf, sizeof(buf),
778 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
779 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
780 edit_int64(id_list.Id[i], ed1));
781 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
782 printf("%s\n", db_strerror(db));
790 if (fix && id_list.num_ids > 0) {
791 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
792 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
794 break; /* get out if not updating db */
796 if (!make_id_list(query, &id_list)) {
802 static void eliminate_orphaned_file_records()
804 const char *query = "SELECT File.FileId,Job.JobId FROM File "
805 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
806 "WHERE Job.JobId IS NULL LIMIT 300000";
808 printf(_("Checking for orphaned File entries. This may take some time!\n"));
810 printf("%s\n", query);
812 if (!make_id_list(query, &id_list)) {
815 /* Loop doing 300000 at a time */
816 while (id_list.num_ids != 0) {
817 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
818 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
819 for (int i=0; i < id_list.num_ids; i++) {
821 bsnprintf(buf, sizeof(buf),
822 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
823 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
824 edit_int64(id_list.Id[i], ed1));
825 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
826 printf("%s\n", db_strerror(db));
833 if (fix && id_list.num_ids > 0) {
834 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
835 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
837 break; /* get out if not updating db */
839 if (!make_id_list(query, &id_list)) {
845 static void eliminate_orphaned_path_records()
848 /* check the existence of the required "one column" index */
849 if (!check_idx("PathId")) {
850 if (yes_no(_("Create temporary index? (yes/no): "))) {
851 /* create temporary index PathId */
852 create_tmp_idx("idxPIchk", "File", "PathId");
856 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
857 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
858 "WHERE File.PathId IS NULL LIMIT 300000";
860 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
862 printf("%s\n", query);
864 if (!make_id_list(query, &id_list)) {
867 /* Loop doing 300000 at a time */
868 while (id_list.num_ids != 0) {
869 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
870 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
871 for (int i=0; i < id_list.num_ids; i++) {
873 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
874 edit_int64(id_list.Id[i], ed1));
875 db_sql_query(db, buf, print_name_handler, NULL);
881 if (fix && id_list.num_ids > 0) {
882 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
883 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
885 break; /* get out if not updating db */
887 if (!make_id_list(query, &id_list)) {
891 /* drop temporary index idx_tmp_name */
892 drop_tmp_idx("idxPIchk", "File");
895 static void eliminate_orphaned_filename_records()
898 /* check the existence of the required "one column" index */
899 if (!check_idx("FilenameId") ) {
900 if (yes_no(_("Create temporary index? (yes/no): "))) {
901 /* create temporary index FilenameId */
902 create_tmp_idx("idxFIchk", "File", "FilenameId");
906 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
907 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
908 "WHERE File.FilenameId IS NULL LIMIT 300000";
910 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
912 printf("%s\n", query);
914 if (!make_id_list(query, &id_list)) {
917 /* Loop doing 300000 at a time */
918 while (id_list.num_ids != 0) {
919 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
920 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
921 for (int i=0; i < id_list.num_ids; i++) {
923 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
924 edit_int64(id_list.Id[i], ed1));
925 db_sql_query(db, buf, print_name_handler, NULL);
931 if (fix && id_list.num_ids > 0) {
932 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
933 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
935 break; /* get out if not updating db */
937 if (!make_id_list(query, &id_list)) {
941 /* drop temporary index idx_tmp_name */
942 drop_tmp_idx("idxFIchk", "File");
946 static void eliminate_orphaned_fileset_records()
950 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
951 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
952 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
953 "WHERE Job.FileSetId IS NULL";
955 printf("%s\n", query);
957 if (!make_id_list(query, &id_list)) {
960 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
961 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
962 for (int i=0; i < id_list.num_ids; i++) {
964 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
965 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
966 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
967 printf("%s\n", db_strerror(db));
974 if (fix && id_list.num_ids > 0) {
975 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
976 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
980 static void eliminate_orphaned_client_records()
984 printf(_("Checking for orphaned Client entries.\n"));
986 * Wiffle through Client for every Client
987 * joining with the Job table including every Client even if
988 * there is not a match in Job (left outer join), then
989 * filter out only those where no Job points to a Client
990 * i.e. Job.Client is NULL
992 query = "SELECT Client.ClientId,Client.Name FROM Client "
993 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
994 "WHERE Job.ClientId IS NULL";
996 printf("%s\n", query);
998 if (!make_id_list(query, &id_list)) {
1001 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1002 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1003 for (int i=0; i < id_list.num_ids; i++) {
1005 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1006 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1007 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1008 printf("%s\n", db_strerror(db));
1015 if (fix && id_list.num_ids > 0) {
1016 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1017 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1021 static void eliminate_orphaned_job_records()
1025 printf(_("Checking for orphaned Job entries.\n"));
1027 * Wiffle through Job for every Job
1028 * joining with the Client table including every Job even if
1029 * there is not a match in Client (left outer join), then
1030 * filter out only those where no Client exists
1031 * i.e. Client.Name is NULL
1033 query = "SELECT Job.JobId,Job.Name FROM Job "
1034 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1035 "WHERE Client.Name IS NULL";
1037 printf("%s\n", query);
1039 if (!make_id_list(query, &id_list)) {
1042 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1043 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1044 for (int i=0; i < id_list.num_ids; i++) {
1046 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1047 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1048 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1049 printf("%s\n", db_strerror(db));
1056 if (fix && id_list.num_ids > 0) {
1057 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1058 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1059 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1060 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1061 printf(_("Deleting Log records of orphaned Job records.\n"));
1062 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1067 static void eliminate_admin_records()
1071 printf(_("Checking for Admin Job entries.\n"));
1072 query = "SELECT Job.JobId FROM Job "
1073 "WHERE Job.Type='D'";
1075 printf("%s\n", query);
1077 if (!make_id_list(query, &id_list)) {
1080 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1081 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1082 for (int i=0; i < id_list.num_ids; i++) {
1084 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1085 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1086 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1087 printf("%s\n", db_strerror(db));
1094 if (fix && id_list.num_ids > 0) {
1095 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1096 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1100 static void eliminate_restore_records()
1104 printf(_("Checking for Restore Job entries.\n"));
1105 query = "SELECT Job.JobId FROM Job "
1106 "WHERE Job.Type='R'";
1108 printf("%s\n", query);
1110 if (!make_id_list(query, &id_list)) {
1113 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1114 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1115 for (int i=0; i < id_list.num_ids; i++) {
1117 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1118 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1119 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1120 printf("%s\n", db_strerror(db));
1127 if (fix && id_list.num_ids > 0) {
1128 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1129 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1136 static void repair_bad_filenames()
1141 printf(_("Checking for Filenames with a trailing slash\n"));
1142 query = "SELECT FilenameId,Name from Filename "
1143 "WHERE Name LIKE '%/'";
1145 printf("%s\n", query);
1147 if (!make_id_list(query, &id_list)) {
1150 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1151 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1152 for (i=0; i < id_list.num_ids; i++) {
1154 bsnprintf(buf, sizeof(buf),
1155 "SELECT Name FROM Filename WHERE FilenameId=%s",
1156 edit_int64(id_list.Id[i], ed1));
1157 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1158 printf("%s\n", db_strerror(db));
1165 if (fix && id_list.num_ids > 0) {
1166 POOLMEM *name = get_pool_memory(PM_FNAME);
1167 char esc_name[5000];
1168 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1169 for (i=0; i < id_list.num_ids; i++) {
1172 bsnprintf(buf, sizeof(buf),
1173 "SELECT Name FROM Filename WHERE FilenameId=%s",
1174 edit_int64(id_list.Id[i], ed1));
1175 if (!db_sql_query(db, buf, get_name_handler, name)) {
1176 printf("%s\n", db_strerror(db));
1178 /* Strip trailing slash(es) */
1179 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1187 db_escape_string(NULL, db, esc_name, name, len);
1189 bsnprintf(buf, sizeof(buf),
1190 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1191 esc_name, edit_int64(id_list.Id[i], ed1));
1193 printf("%s\n", buf);
1195 db_sql_query(db, buf, NULL, NULL);
1200 static void repair_bad_paths()
1205 printf(_("Checking for Paths without a trailing slash\n"));
1206 query = "SELECT PathId,Path from Path "
1207 "WHERE Path NOT LIKE '%/'";
1209 printf("%s\n", query);
1211 if (!make_id_list(query, &id_list)) {
1214 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1215 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1216 for (i=0; i < id_list.num_ids; i++) {
1218 bsnprintf(buf, sizeof(buf),
1219 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1220 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1221 printf("%s\n", db_strerror(db));
1228 if (fix && id_list.num_ids > 0) {
1229 POOLMEM *name = get_pool_memory(PM_FNAME);
1230 char esc_name[5000];
1231 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1232 for (i=0; i < id_list.num_ids; i++) {
1235 bsnprintf(buf, sizeof(buf),
1236 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1237 if (!db_sql_query(db, buf, get_name_handler, name)) {
1238 printf("%s\n", db_strerror(db));
1240 /* Strip trailing blanks */
1241 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1244 /* Add trailing slash */
1245 len = pm_strcat(&name, "/");
1246 db_escape_string(NULL, db, esc_name, name, len);
1247 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1248 esc_name, edit_int64(id_list.Id[i], ed1));
1250 printf("%s\n", buf);
1252 db_sql_query(db, buf, NULL, NULL);
1259 * Gen next input command from the terminal
1261 static char *get_cmd(const char *prompt)
1263 static char cmd[1000];
1265 printf("%s", prompt);
1266 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1271 strip_trailing_junk(cmd);
1275 static bool yes_no(const char *prompt)
1278 cmd = get_cmd(prompt);
1283 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1286 bool python_set_prog(JCR*, char const*) { return false; }
1290 * The code below to add indexes is needed only for MySQL, and
1291 * that to improve the performance.
1296 typedef struct s_idx_list {
1298 int count_key; /* how many times the index meets *key_name */
1299 int count_col; /* how many times meets the desired column name */
1302 static IDX_LIST idx_list[MAXIDX];
1305 * Called here with each table index to be added to the list
1307 static int check_idx_handler(void *ctx, int num_fields, char **row)
1309 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1310 * File | 0 | PRIMARY | 1 | FileId |...
1312 char *name, *key_name, *col_name;
1318 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1319 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1320 idx_list[i].count_key++;
1322 if (strcasecmp(col_name, name) == 0) {
1323 idx_list[i].count_col++;
1328 /* if the new Key_name, add it to the list */
1330 len = strlen(key_name) + 1;
1331 idx_list[i].key_name = (char *)malloc(len);
1332 bstrncpy(idx_list[i].key_name, key_name, len);
1333 idx_list[i].count_key = 1;
1334 if (strcasecmp(col_name, name) == 0) {
1335 idx_list[i].count_col = 1;
1337 idx_list[i].count_col = 0;
1345 * Return TRUE if "one column" index over *col_name exists
1347 static bool check_idx(const char *col_name)
1353 memset(&idx_list, 0, sizeof(idx_list));
1354 const char *query = "SHOW INDEX FROM File";
1355 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1356 printf("%s\n", db_strerror(db));
1359 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1360 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1361 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1362 /* "one column" index over *col_name found */
1368 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1371 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1381 * Create temporary one-column index
1383 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1384 const char *col_name)
1386 idx_tmp_name = NULL;
1387 printf(_("Create temporary index... This may take some time!\n"));
1388 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1390 printf("%s\n", buf);
1392 if (db_sql_query(db, buf, NULL, NULL)) {
1393 idx_tmp_name = idx_name;
1395 printf(_("Temporary index created.\n"));
1398 printf("%s\n", db_strerror(db));
1405 * Drop temporary index
1407 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1409 if (idx_tmp_name != NULL) {
1410 printf(_("Drop temporary index.\n"));
1411 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1413 printf("%s\n", buf);
1415 if (!db_sql_query(db, buf, NULL, NULL)) {
1416 printf("%s\n", db_strerror(db));
1420 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1424 idx_tmp_name = NULL;