2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2009 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
40 #include "cats/cats.h"
41 #include "lib/runscript.h"
42 #include "dird/dird_conf.h"
44 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
47 int generate_daemon_event(JCR *jcr, const char *event)
50 typedef struct s_id_ctx {
51 int64_t *Id; /* ids to be modified */
52 int num_ids; /* ids stored */
53 int max_ids; /* size of array */
54 int num_del; /* number deleted */
55 int tot_ids; /* total to process */
58 typedef struct s_name_ctx {
59 char **name; /* list of names */
60 int num_ids; /* ids stored */
61 int max_ids; /* size of array */
62 int num_del; /* number deleted */
63 int tot_ids; /* total to process */
66 /* Global variables */
67 static bool fix = false;
68 static bool batch = false;
70 static ID_LIST id_list;
71 static NAME_LIST name_list;
72 static char buf[20000];
73 static bool quit = false;
74 static CONFIG *config;
76 #define MAX_ID_LIST_LEN 10000000
78 /* Forward referenced functions */
79 static int make_id_list(const char *query, ID_LIST *id_list);
80 static int delete_id_list(const char *query, ID_LIST *id_list);
81 static int make_name_list(const char *query, NAME_LIST *name_list);
82 static void print_name_list(NAME_LIST *name_list);
83 static void free_name_list(NAME_LIST *name_list);
84 static char *get_cmd(const char *prompt);
85 static void eliminate_duplicate_filenames();
86 static void eliminate_duplicate_paths();
87 static void eliminate_orphaned_jobmedia_records();
88 static void eliminate_orphaned_file_records();
89 static void eliminate_orphaned_path_records();
90 static void eliminate_orphaned_filename_records();
91 static void eliminate_orphaned_fileset_records();
92 static void eliminate_orphaned_client_records();
93 static void eliminate_orphaned_job_records();
94 static void eliminate_admin_records();
95 static void eliminate_restore_records();
96 static void repair_bad_paths();
97 static void repair_bad_filenames();
98 static void do_interactive_mode();
99 static bool yes_no(const char *prompt);
100 static bool check_idx(const char *col_name);
101 static bool create_tmp_idx(const char *idx_name, const char *table_name,
102 const char *col_name);
103 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
105 static int check_idx_handler(void *ctx, int num_fields, char **row);
109 /* Global variables */
110 static const char *idx_tmp_name;
115 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
117 " -C catalog name in the director conf file\n"
118 " -c Director conf filename\n"
119 " -B print catalog configuration and exit\n"
120 " -d <nn> set debug level to <nn>\n"
121 " -dt print a timestamp in debug output\n"
122 " -f fix inconsistencies\n"
123 " -t test if client library is thread-safe\n"
125 " -? print this message\n\n");
129 int main (int argc, char *argv[])
132 const char *user, *password, *db_name, *dbhost;
134 bool test_thread=false;
135 bool print_catalog=false;
136 char *configfile = NULL;
137 char *catalogname = NULL;
140 setlocale(LC_ALL, "");
141 bindtextdomain("bacula", LOCALEDIR);
142 textdomain("bacula");
145 my_name_is(argc, argv, "dbcheck");
146 init_msg(NULL, NULL); /* setup message handler */
148 memset(&id_list, 0, sizeof(id_list));
149 memset(&name_list, 0, sizeof(name_list));
151 while ((ch = getopt(argc, argv, "bc:C:d:fvBt?")) != -1) {
154 print_catalog = true; /* get catalog information from config */
157 case 'b': /* batch */
161 case 'C': /* CatalogName */
162 catalogname = optarg;
165 case 'c': /* configfile */
169 case 'd': /* debug level */
170 if (*optarg == 't') {
171 dbg_timestamp = true;
173 debug_level = atoi(optarg);
174 if (debug_level <= 0) {
180 case 'f': /* fix inconsistencies */
202 /* When we will load the SQL backend with ldopen, this check would be
203 * moved after the database initialization. It will need a valid config
206 db_check_backend_thread_safe();
207 Pmsg0(0, _("OK - DB backend seems to be thread-safe.\n"));
215 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
217 config = new_config_parser();
218 parse_dir_config(config, configfile, M_ERROR_TERM);
220 foreach_res(catalog, R_CATALOG) {
221 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
224 } else if (!catalogname) { // stop on first if no catalogname is given
232 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
234 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
240 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
243 Pmsg0(0, _("Error no Director resource defined.\n"));
246 set_working_directory(director->working_directory);
248 /* Print catalog information and exit (-B) */
250 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
251 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(buf),
252 db_get_type(), working_directory);
253 free_pool_memory(buf);
257 db_name = catalog->db_name;
258 user = catalog->db_user;
259 password = catalog->db_password;
260 dbhost = catalog->db_address;
261 if (dbhost && dbhost[0] == 0) {
264 dbport = catalog->db_port;
268 Pmsg0(0, _("Wrong number of arguments.\n"));
273 Pmsg0(0, _("Working directory not supplied.\n"));
277 /* This is needed by SQLite to find the db */
278 working_directory = argv[0];
287 } else if (argc == 3) {
290 } else if (argc == 4) {
294 } else if (argc == 5) {
299 } else if (argc == 6) {
305 dbport = strtol(argv[5], &endptr, 10);
306 if (*endptr != '\0') {
307 Pmsg0(0, _("Database port must be a numeric value.\n"));
309 } else if (errno == ERANGE) {
310 Pmsg0(0, _("Database port must be a int value.\n"));
317 db = db_init_database(NULL, db_name, user, password, dbhost, dbport, NULL, 0);
318 if (!db_open_database(NULL, db)) {
319 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
325 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();
333 eliminate_orphaned_client_records();
334 eliminate_orphaned_job_records();
335 eliminate_admin_records();
336 eliminate_restore_records();
338 do_interactive_mode();
341 db_close_database(NULL, db);
348 static void do_interactive_mode()
352 printf(_("Hello, this is the database check/correct program.\n"));
354 printf(_("Modify database is on."));
356 printf(_("Modify database is off."));
358 printf(_(" Verbose is on.\n"));
360 printf(_(" Verbose is off.\n"));
362 printf(_("Please select the function you want to perform.\n"));
367 " 1) Toggle modify database flag\n"
368 " 2) Toggle verbose flag\n"
369 " 3) Repair bad Filename records\n"
370 " 4) Repair bad Path records\n"
371 " 5) Eliminate duplicate Filename records\n"
372 " 6) Eliminate duplicate Path records\n"
373 " 7) Eliminate orphaned Jobmedia records\n"
374 " 8) Eliminate orphaned File records\n"
375 " 9) Eliminate orphaned Path records\n"
376 " 10) Eliminate orphaned Filename records\n"
377 " 11) Eliminate orphaned FileSet records\n"
378 " 12) Eliminate orphaned Client records\n"
379 " 13) Eliminate orphaned Job records\n"
380 " 14) Eliminate all Admin records\n"
381 " 15) Eliminate all Restore records\n"
386 " 1) Toggle modify database flag\n"
387 " 2) Toggle verbose flag\n"
388 " 3) Check for bad Filename records\n"
389 " 4) Check for bad Path records\n"
390 " 5) Check for duplicate Filename records\n"
391 " 6) Check for duplicate Path records\n"
392 " 7) Check for orphaned Jobmedia records\n"
393 " 8) Check for orphaned File records\n"
394 " 9) Check for orphaned Path records\n"
395 " 10) Check for orphaned Filename records\n"
396 " 11) Check for orphaned FileSet records\n"
397 " 12) Check for orphaned Client records\n"
398 " 13) Check for orphaned Job records\n"
399 " 14) Check for all Admin records\n"
400 " 15) Check for all Restore records\n"
405 cmd = get_cmd(_("Select function number: "));
407 int item = atoi(cmd);
412 printf(_("Database will be modified.\n"));
414 printf(_("Database will NOT be modified.\n"));
417 verbose = verbose?0:1;
419 printf(_(" Verbose is on.\n"));
421 printf(_(" Verbose is off.\n"));
424 repair_bad_filenames();
430 eliminate_duplicate_filenames();
433 eliminate_duplicate_paths();
436 eliminate_orphaned_jobmedia_records();
439 eliminate_orphaned_file_records();
442 eliminate_orphaned_path_records();
445 eliminate_orphaned_filename_records();
448 eliminate_orphaned_fileset_records();
451 eliminate_orphaned_client_records();
454 eliminate_orphaned_job_records();
457 eliminate_admin_records();
460 eliminate_restore_records();
463 repair_bad_filenames();
465 eliminate_duplicate_filenames();
466 eliminate_duplicate_paths();
467 eliminate_orphaned_jobmedia_records();
468 eliminate_orphaned_file_records();
469 eliminate_orphaned_path_records();
470 eliminate_orphaned_filename_records();
471 eliminate_orphaned_fileset_records();
472 eliminate_orphaned_client_records();
473 eliminate_orphaned_job_records();
474 eliminate_admin_records();
475 eliminate_restore_records();
485 static int print_name_handler(void *ctx, int num_fields, char **row)
488 printf("%s\n", row[0]);
493 static int get_name_handler(void *ctx, int num_fields, char **row)
495 POOLMEM *buf = (POOLMEM *)ctx;
497 pm_strcpy(&buf, row[0]);
502 static int print_job_handler(void *ctx, int num_fields, char **row)
504 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
505 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
510 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
512 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
513 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
517 static int print_file_handler(void *ctx, int num_fields, char **row)
519 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
520 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
524 static int print_fileset_handler(void *ctx, int num_fields, char **row)
526 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
527 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
531 static int print_client_handler(void *ctx, int num_fields, char **row)
533 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
534 NPRT(row[0]), NPRT(row[1]));
540 * Called here with each id to be added to the list
542 static int id_list_handler(void *ctx, int num_fields, char **row)
544 ID_LIST *lst = (ID_LIST *)ctx;
546 if (lst->num_ids == MAX_ID_LIST_LEN) {
549 if (lst->num_ids == lst->max_ids) {
550 if (lst->max_ids == 0) {
551 lst->max_ids = 10000;
552 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
554 lst->max_ids = (lst->max_ids * 3) / 2;
555 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
558 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
563 * Construct record id list
565 static int make_id_list(const char *query, ID_LIST *id_list)
567 id_list->num_ids = 0;
568 id_list->num_del = 0;
569 id_list->tot_ids = 0;
571 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
572 printf("%s", db_strerror(db));
579 * Delete all entries in the list
581 static int delete_id_list(const char *query, ID_LIST *id_list)
584 for (int i=0; i < id_list->num_ids; i++) {
585 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
587 printf(_("Deleting: %s\n"), buf);
589 db_sql_query(db, buf, NULL, NULL);
595 * Called here with each name to be added to the list
597 static int name_list_handler(void *ctx, int num_fields, char **row)
599 NAME_LIST *name = (NAME_LIST *)ctx;
601 if (name->num_ids == MAX_ID_LIST_LEN) {
604 if (name->num_ids == name->max_ids) {
605 if (name->max_ids == 0) {
606 name->max_ids = 10000;
607 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
609 name->max_ids = (name->max_ids * 3) / 2;
610 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
613 name->name[name->num_ids++] = bstrdup(row[0]);
619 * Construct name list
621 static int make_name_list(const char *query, NAME_LIST *name_list)
623 name_list->num_ids = 0;
624 name_list->num_del = 0;
625 name_list->tot_ids = 0;
627 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
628 printf("%s", db_strerror(db));
635 * Print names in the list
637 static void print_name_list(NAME_LIST *name_list)
639 for (int i=0; i < name_list->num_ids; i++) {
640 printf("%s\n", name_list->name[i]);
646 * Free names in the list
648 static void free_name_list(NAME_LIST *name_list)
650 for (int i=0; i < name_list->num_ids; i++) {
651 free(name_list->name[i]);
653 name_list->num_ids = 0;
656 static void eliminate_duplicate_filenames()
661 printf(_("Checking for duplicate Filename entries.\n"));
663 /* Make list of duplicated names */
664 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
665 "HAVING count(Name) > 1";
667 if (!make_name_list(query, &name_list)) {
670 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
671 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
672 print_name_list(&name_list);
678 /* Loop through list of duplicate names */
679 for (int i=0; i<name_list.num_ids; i++) {
680 /* Get all the Ids of each name */
681 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
682 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
686 if (!make_id_list(buf, &id_list)) {
690 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
692 /* Force all records to use the first id then delete the other ids */
693 for (int j=1; j<id_list.num_ids; j++) {
694 char ed1[50], ed2[50];
695 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
696 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
700 db_sql_query(db, buf, NULL, NULL);
701 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
706 db_sql_query(db, buf, NULL, NULL);
710 free_name_list(&name_list);
713 static void eliminate_duplicate_paths()
718 printf(_("Checking for duplicate Path entries.\n"));
720 /* Make list of duplicated names */
722 query = "SELECT Path, count(Path) as Count FROM Path "
723 "GROUP BY Path HAVING count(Path) > 1";
725 if (!make_name_list(query, &name_list)) {
728 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
729 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
730 print_name_list(&name_list);
736 /* Loop through list of duplicate names */
737 for (int i=0; i<name_list.num_ids; i++) {
738 /* Get all the Ids of each name */
739 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
740 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
744 if (!make_id_list(buf, &id_list)) {
748 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
750 /* Force all records to use the first id then delete the other ids */
751 for (int j=1; j<id_list.num_ids; j++) {
752 char ed1[50], ed2[50];
753 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
754 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
758 db_sql_query(db, buf, NULL, NULL);
759 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
763 db_sql_query(db, buf, NULL, NULL);
767 free_name_list(&name_list);
770 static void eliminate_orphaned_jobmedia_records()
772 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
773 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
774 "WHERE Job.JobId IS NULL LIMIT 300000";
776 printf(_("Checking for orphaned JobMedia entries.\n"));
777 if (!make_id_list(query, &id_list)) {
780 /* Loop doing 300000 at a time */
781 while (id_list.num_ids != 0) {
782 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
783 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
784 for (int i=0; i < id_list.num_ids; i++) {
786 bsnprintf(buf, sizeof(buf),
787 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
788 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
789 edit_int64(id_list.Id[i], ed1));
790 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
791 printf("%s\n", db_strerror(db));
799 if (fix && id_list.num_ids > 0) {
800 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
801 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
803 break; /* get out if not updating db */
805 if (!make_id_list(query, &id_list)) {
811 static void eliminate_orphaned_file_records()
813 const char *query = "SELECT File.FileId,Job.JobId FROM File "
814 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
815 "WHERE Job.JobId IS NULL LIMIT 300000";
817 printf(_("Checking for orphaned File entries. This may take some time!\n"));
819 printf("%s\n", query);
821 if (!make_id_list(query, &id_list)) {
824 /* Loop doing 300000 at a time */
825 while (id_list.num_ids != 0) {
826 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
827 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
828 for (int i=0; i < id_list.num_ids; i++) {
830 bsnprintf(buf, sizeof(buf),
831 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
832 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
833 edit_int64(id_list.Id[i], ed1));
834 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
835 printf("%s\n", db_strerror(db));
842 if (fix && id_list.num_ids > 0) {
843 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
844 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
846 break; /* get out if not updating db */
848 if (!make_id_list(query, &id_list)) {
854 static void eliminate_orphaned_path_records()
857 /* check the existence of the required "one column" index */
858 if (!check_idx("PathId")) {
859 if (yes_no(_("Create temporary index? (yes/no): "))) {
860 /* create temporary index PathId */
861 create_tmp_idx("idxPIchk", "File", "PathId");
865 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
866 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
867 "WHERE File.PathId IS NULL LIMIT 300000";
869 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
871 printf("%s\n", query);
873 if (!make_id_list(query, &id_list)) {
876 /* Loop doing 300000 at a time */
877 while (id_list.num_ids != 0) {
878 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
879 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
880 for (int i=0; i < id_list.num_ids; i++) {
882 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
883 edit_int64(id_list.Id[i], ed1));
884 db_sql_query(db, buf, print_name_handler, NULL);
890 if (fix && id_list.num_ids > 0) {
891 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
892 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
894 break; /* get out if not updating db */
896 if (!make_id_list(query, &id_list)) {
900 /* drop temporary index idx_tmp_name */
901 drop_tmp_idx("idxPIchk", "File");
904 static void eliminate_orphaned_filename_records()
907 /* check the existence of the required "one column" index */
908 if (!check_idx("FilenameId") ) {
909 if (yes_no(_("Create temporary index? (yes/no): "))) {
910 /* create temporary index FilenameId */
911 create_tmp_idx("idxFIchk", "File", "FilenameId");
915 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
916 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
917 "WHERE File.FilenameId IS NULL LIMIT 300000";
919 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
921 printf("%s\n", query);
923 if (!make_id_list(query, &id_list)) {
926 /* Loop doing 300000 at a time */
927 while (id_list.num_ids != 0) {
928 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
929 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
930 for (int i=0; i < id_list.num_ids; i++) {
932 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
933 edit_int64(id_list.Id[i], ed1));
934 db_sql_query(db, buf, print_name_handler, NULL);
940 if (fix && id_list.num_ids > 0) {
941 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
942 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
944 break; /* get out if not updating db */
946 if (!make_id_list(query, &id_list)) {
950 /* drop temporary index idx_tmp_name */
951 drop_tmp_idx("idxFIchk", "File");
955 static void eliminate_orphaned_fileset_records()
959 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
960 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
961 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
962 "WHERE Job.FileSetId IS NULL";
964 printf("%s\n", query);
966 if (!make_id_list(query, &id_list)) {
969 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
970 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
971 for (int i=0; i < id_list.num_ids; i++) {
973 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
974 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
975 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
976 printf("%s\n", db_strerror(db));
983 if (fix && id_list.num_ids > 0) {
984 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
985 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
989 static void eliminate_orphaned_client_records()
993 printf(_("Checking for orphaned Client entries.\n"));
995 * Wiffle through Client for every Client
996 * joining with the Job table including every Client even if
997 * there is not a match in Job (left outer join), then
998 * filter out only those where no Job points to a Client
999 * i.e. Job.Client is NULL
1001 query = "SELECT Client.ClientId,Client.Name FROM Client "
1002 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1003 "WHERE Job.ClientId IS NULL";
1005 printf("%s\n", query);
1007 if (!make_id_list(query, &id_list)) {
1010 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1011 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1012 for (int i=0; i < id_list.num_ids; i++) {
1014 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1015 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1016 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1017 printf("%s\n", db_strerror(db));
1024 if (fix && id_list.num_ids > 0) {
1025 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1026 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1030 static void eliminate_orphaned_job_records()
1034 printf(_("Checking for orphaned Job entries.\n"));
1036 * Wiffle through Job for every Job
1037 * joining with the Client table including every Job even if
1038 * there is not a match in Client (left outer join), then
1039 * filter out only those where no Client exists
1040 * i.e. Client.Name is NULL
1042 query = "SELECT Job.JobId,Job.Name FROM Job "
1043 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1044 "WHERE Client.Name IS NULL";
1046 printf("%s\n", query);
1048 if (!make_id_list(query, &id_list)) {
1051 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1052 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1053 for (int i=0; i < id_list.num_ids; i++) {
1055 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1056 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1057 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1058 printf("%s\n", db_strerror(db));
1065 if (fix && id_list.num_ids > 0) {
1066 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1067 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1068 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1069 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1070 printf(_("Deleting Log records of orphaned Job records.\n"));
1071 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1076 static void eliminate_admin_records()
1080 printf(_("Checking for Admin Job entries.\n"));
1081 query = "SELECT Job.JobId FROM Job "
1082 "WHERE Job.Type='D'";
1084 printf("%s\n", query);
1086 if (!make_id_list(query, &id_list)) {
1089 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1090 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1091 for (int i=0; i < id_list.num_ids; i++) {
1093 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1094 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1095 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1096 printf("%s\n", db_strerror(db));
1103 if (fix && id_list.num_ids > 0) {
1104 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1105 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1109 static void eliminate_restore_records()
1113 printf(_("Checking for Restore Job entries.\n"));
1114 query = "SELECT Job.JobId FROM Job "
1115 "WHERE Job.Type='R'";
1117 printf("%s\n", query);
1119 if (!make_id_list(query, &id_list)) {
1122 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1123 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1124 for (int i=0; i < id_list.num_ids; i++) {
1126 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1127 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1128 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1129 printf("%s\n", db_strerror(db));
1136 if (fix && id_list.num_ids > 0) {
1137 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1138 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1145 static void repair_bad_filenames()
1150 printf(_("Checking for Filenames with a trailing slash\n"));
1151 query = "SELECT FilenameId,Name from Filename "
1152 "WHERE Name LIKE '%/'";
1154 printf("%s\n", query);
1156 if (!make_id_list(query, &id_list)) {
1159 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1160 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1161 for (i=0; i < id_list.num_ids; i++) {
1163 bsnprintf(buf, sizeof(buf),
1164 "SELECT Name FROM Filename WHERE FilenameId=%s",
1165 edit_int64(id_list.Id[i], ed1));
1166 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1167 printf("%s\n", db_strerror(db));
1174 if (fix && id_list.num_ids > 0) {
1175 POOLMEM *name = get_pool_memory(PM_FNAME);
1176 char esc_name[5000];
1177 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1178 for (i=0; i < id_list.num_ids; i++) {
1181 bsnprintf(buf, sizeof(buf),
1182 "SELECT Name FROM Filename WHERE FilenameId=%s",
1183 edit_int64(id_list.Id[i], ed1));
1184 if (!db_sql_query(db, buf, get_name_handler, name)) {
1185 printf("%s\n", db_strerror(db));
1187 /* Strip trailing slash(es) */
1188 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1196 db_escape_string(NULL, db, esc_name, name, len);
1198 bsnprintf(buf, sizeof(buf),
1199 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1200 esc_name, edit_int64(id_list.Id[i], ed1));
1202 printf("%s\n", buf);
1204 db_sql_query(db, buf, NULL, NULL);
1209 static void repair_bad_paths()
1214 printf(_("Checking for Paths without a trailing slash\n"));
1215 query = "SELECT PathId,Path from Path "
1216 "WHERE Path NOT LIKE '%/'";
1218 printf("%s\n", query);
1220 if (!make_id_list(query, &id_list)) {
1223 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1224 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1225 for (i=0; i < id_list.num_ids; i++) {
1227 bsnprintf(buf, sizeof(buf),
1228 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1229 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1230 printf("%s\n", db_strerror(db));
1237 if (fix && id_list.num_ids > 0) {
1238 POOLMEM *name = get_pool_memory(PM_FNAME);
1239 char esc_name[5000];
1240 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1241 for (i=0; i < id_list.num_ids; i++) {
1244 bsnprintf(buf, sizeof(buf),
1245 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1246 if (!db_sql_query(db, buf, get_name_handler, name)) {
1247 printf("%s\n", db_strerror(db));
1249 /* Strip trailing blanks */
1250 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1253 /* Add trailing slash */
1254 len = pm_strcat(&name, "/");
1255 db_escape_string(NULL, db, esc_name, name, len);
1256 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1257 esc_name, edit_int64(id_list.Id[i], ed1));
1259 printf("%s\n", buf);
1261 db_sql_query(db, buf, NULL, NULL);
1268 * Gen next input command from the terminal
1270 static char *get_cmd(const char *prompt)
1272 static char cmd[1000];
1274 printf("%s", prompt);
1275 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1280 strip_trailing_junk(cmd);
1284 static bool yes_no(const char *prompt)
1287 cmd = get_cmd(prompt);
1292 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1295 bool python_set_prog(JCR*, char const*) { return false; }
1299 * The code below to add indexes is needed only for MySQL, and
1300 * that to improve the performance.
1305 typedef struct s_idx_list {
1307 int count_key; /* how many times the index meets *key_name */
1308 int count_col; /* how many times meets the desired column name */
1311 static IDX_LIST idx_list[MAXIDX];
1314 * Called here with each table index to be added to the list
1316 static int check_idx_handler(void *ctx, int num_fields, char **row)
1318 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1319 * File | 0 | PRIMARY | 1 | FileId |...
1321 char *name, *key_name, *col_name;
1327 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1328 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1329 idx_list[i].count_key++;
1331 if (strcasecmp(col_name, name) == 0) {
1332 idx_list[i].count_col++;
1337 /* if the new Key_name, add it to the list */
1339 len = strlen(key_name) + 1;
1340 idx_list[i].key_name = (char *)malloc(len);
1341 bstrncpy(idx_list[i].key_name, key_name, len);
1342 idx_list[i].count_key = 1;
1343 if (strcasecmp(col_name, name) == 0) {
1344 idx_list[i].count_col = 1;
1346 idx_list[i].count_col = 0;
1354 * Return TRUE if "one column" index over *col_name exists
1356 static bool check_idx(const char *col_name)
1362 memset(&idx_list, 0, sizeof(idx_list));
1363 const char *query = "SHOW INDEX FROM File";
1364 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1365 printf("%s\n", db_strerror(db));
1368 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1369 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1370 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1371 /* "one column" index over *col_name found */
1377 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1380 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1390 * Create temporary one-column index
1392 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1393 const char *col_name)
1395 idx_tmp_name = NULL;
1396 printf(_("Create temporary index... This may take some time!\n"));
1397 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1399 printf("%s\n", buf);
1401 if (db_sql_query(db, buf, NULL, NULL)) {
1402 idx_tmp_name = idx_name;
1404 printf(_("Temporary index created.\n"));
1407 printf("%s\n", db_strerror(db));
1414 * Drop temporary index
1416 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1418 if (idx_tmp_name != NULL) {
1419 printf(_("Drop temporary index.\n"));
1420 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1422 printf("%s\n", buf);
1424 if (!db_sql_query(db, buf, NULL, NULL)) {
1425 printf("%s\n", db_strerror(db));
1429 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1433 idx_tmp_name = NULL;