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 void print_catalog_details(CAT *catalog, const char *working_dir);
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 print_catalog=false;
135 char *configfile = NULL;
136 char *catalogname = NULL;
139 setlocale(LC_ALL, "");
140 bindtextdomain("bacula", LOCALEDIR);
141 textdomain("bacula");
144 my_name_is(argc, argv, "dbcheck");
145 init_msg(NULL, NULL); /* setup message handler */
147 memset(&id_list, 0, sizeof(id_list));
148 memset(&name_list, 0, sizeof(name_list));
150 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
153 print_catalog = true; /* get catalog information from config */
156 case 'b': /* batch */
160 case 'C': /* CatalogName */
161 catalogname = optarg;
164 case 'c': /* configfile */
168 case 'd': /* debug level */
169 if (*optarg == 't') {
170 dbg_timestamp = true;
172 debug_level = atoi(optarg);
173 if (debug_level <= 0) {
179 case 'f': /* fix inconsistencies */
201 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
203 config = new_config_parser();
204 parse_dir_config(config, configfile, M_ERROR_TERM);
206 foreach_res(catalog, R_CATALOG) {
207 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
210 } else if (!catalogname) { // stop on first if no catalogname is given
218 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
220 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
226 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
229 Pmsg0(0, _("Error no Director resource defined.\n"));
232 set_working_directory(director->working_directory);
234 /* Print catalog information and exit (-B) */
236 print_catalog_details(catalog, director->working_directory);
240 db_name = catalog->db_name;
241 user = catalog->db_user;
242 password = catalog->db_password;
243 dbhost = catalog->db_address;
244 if (dbhost && dbhost[0] == 0) {
247 dbport = catalog->db_port;
251 Pmsg0(0, _("Wrong number of arguments.\n"));
256 Pmsg0(0, _("Working directory not supplied.\n"));
260 /* This is needed by SQLite to find the db */
261 working_directory = argv[0];
270 } else if (argc == 3) {
273 } else if (argc == 4) {
277 } else if (argc == 5) {
282 } else if (argc == 6) {
288 dbport = strtol(argv[5], &endptr, 10);
289 if (*endptr != '\0') {
290 Pmsg0(0, _("Database port must be a numeric value.\n"));
292 } else if (errno == ERANGE) {
293 Pmsg0(0, _("Database port must be a int value.\n"));
300 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
301 if (!db_open_database(NULL, db)) {
302 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
306 /* drop temporary index idx_tmp_name if it already exists */
307 drop_tmp_idx("idxPIchk", "File");
311 repair_bad_filenames();
312 eliminate_duplicate_filenames();
313 eliminate_duplicate_paths();
314 eliminate_orphaned_jobmedia_records();
315 eliminate_orphaned_file_records();
316 eliminate_orphaned_path_records();
317 eliminate_orphaned_filename_records();
318 eliminate_orphaned_fileset_records();
319 eliminate_orphaned_client_records();
320 eliminate_orphaned_job_records();
321 eliminate_admin_records();
322 eliminate_restore_records();
324 do_interactive_mode();
327 /* drop temporary index idx_tmp_name */
328 drop_tmp_idx("idxPIchk", "File");
330 db_close_database(NULL, db);
337 static void print_catalog_details(CAT *catalog, const char *working_dir)
339 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
342 * Instantiate a B_DB class and see what db_type gets assigned to it.
344 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
345 catalog->db_password, catalog->db_address,
346 catalog->db_port, catalog->db_socket,
347 catalog->mult_db_connections,
348 catalog->disable_batch_insert);
350 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(buf),
351 db->db_get_type(), working_directory);
352 db_close_database(NULL, db);
354 free_pool_memory(buf);
357 static void do_interactive_mode()
361 printf(_("Hello, this is the database check/correct program.\n"));
363 printf(_("Modify database is on."));
365 printf(_("Modify database is off."));
367 printf(_(" Verbose is on.\n"));
369 printf(_(" Verbose is off.\n"));
371 printf(_("Please select the function you want to perform.\n"));
376 " 1) Toggle modify database flag\n"
377 " 2) Toggle verbose flag\n"
378 " 3) Repair bad Filename records\n"
379 " 4) Repair bad Path records\n"
380 " 5) Eliminate duplicate Filename records\n"
381 " 6) Eliminate duplicate Path records\n"
382 " 7) Eliminate orphaned Jobmedia records\n"
383 " 8) Eliminate orphaned File records\n"
384 " 9) Eliminate orphaned Path records\n"
385 " 10) Eliminate orphaned Filename records\n"
386 " 11) Eliminate orphaned FileSet records\n"
387 " 12) Eliminate orphaned Client records\n"
388 " 13) Eliminate orphaned Job records\n"
389 " 14) Eliminate all Admin records\n"
390 " 15) Eliminate all Restore records\n"
395 " 1) Toggle modify database flag\n"
396 " 2) Toggle verbose flag\n"
397 " 3) Check for bad Filename records\n"
398 " 4) Check for bad Path records\n"
399 " 5) Check for duplicate Filename records\n"
400 " 6) Check for duplicate Path records\n"
401 " 7) Check for orphaned Jobmedia records\n"
402 " 8) Check for orphaned File records\n"
403 " 9) Check for orphaned Path records\n"
404 " 10) Check for orphaned Filename records\n"
405 " 11) Check for orphaned FileSet records\n"
406 " 12) Check for orphaned Client records\n"
407 " 13) Check for orphaned Job records\n"
408 " 14) Check for all Admin records\n"
409 " 15) Check for all Restore records\n"
414 cmd = get_cmd(_("Select function number: "));
416 int item = atoi(cmd);
421 printf(_("Database will be modified.\n"));
423 printf(_("Database will NOT be modified.\n"));
426 verbose = verbose?0:1;
428 printf(_(" Verbose is on.\n"));
430 printf(_(" Verbose is off.\n"));
433 repair_bad_filenames();
439 eliminate_duplicate_filenames();
442 eliminate_duplicate_paths();
445 eliminate_orphaned_jobmedia_records();
448 eliminate_orphaned_file_records();
451 eliminate_orphaned_path_records();
454 eliminate_orphaned_filename_records();
457 eliminate_orphaned_fileset_records();
460 eliminate_orphaned_client_records();
463 eliminate_orphaned_job_records();
466 eliminate_admin_records();
469 eliminate_restore_records();
472 repair_bad_filenames();
474 eliminate_duplicate_filenames();
475 eliminate_duplicate_paths();
476 eliminate_orphaned_jobmedia_records();
477 eliminate_orphaned_file_records();
478 eliminate_orphaned_path_records();
479 eliminate_orphaned_filename_records();
480 eliminate_orphaned_fileset_records();
481 eliminate_orphaned_client_records();
482 eliminate_orphaned_job_records();
483 eliminate_admin_records();
484 eliminate_restore_records();
494 static int print_name_handler(void *ctx, int num_fields, char **row)
497 printf("%s\n", row[0]);
502 static int get_name_handler(void *ctx, int num_fields, char **row)
504 POOLMEM *buf = (POOLMEM *)ctx;
506 pm_strcpy(&buf, row[0]);
511 static int print_job_handler(void *ctx, int num_fields, char **row)
513 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
514 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
519 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
521 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
522 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
526 static int print_file_handler(void *ctx, int num_fields, char **row)
528 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
529 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
533 static int print_fileset_handler(void *ctx, int num_fields, char **row)
535 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
536 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
540 static int print_client_handler(void *ctx, int num_fields, char **row)
542 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
543 NPRT(row[0]), NPRT(row[1]));
549 * Called here with each id to be added to the list
551 static int id_list_handler(void *ctx, int num_fields, char **row)
553 ID_LIST *lst = (ID_LIST *)ctx;
555 if (lst->num_ids == MAX_ID_LIST_LEN) {
558 if (lst->num_ids == lst->max_ids) {
559 if (lst->max_ids == 0) {
560 lst->max_ids = 10000;
561 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
563 lst->max_ids = (lst->max_ids * 3) / 2;
564 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
567 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
572 * Construct record id list
574 static int make_id_list(const char *query, ID_LIST *id_list)
576 id_list->num_ids = 0;
577 id_list->num_del = 0;
578 id_list->tot_ids = 0;
580 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
581 printf("%s", db_strerror(db));
588 * Delete all entries in the list
590 static int delete_id_list(const char *query, ID_LIST *id_list)
593 for (int i=0; i < id_list->num_ids; i++) {
594 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
596 printf(_("Deleting: %s\n"), buf);
598 db_sql_query(db, buf, NULL, NULL);
604 * Called here with each name to be added to the list
606 static int name_list_handler(void *ctx, int num_fields, char **row)
608 NAME_LIST *name = (NAME_LIST *)ctx;
610 if (name->num_ids == MAX_ID_LIST_LEN) {
613 if (name->num_ids == name->max_ids) {
614 if (name->max_ids == 0) {
615 name->max_ids = 10000;
616 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
618 name->max_ids = (name->max_ids * 3) / 2;
619 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
622 name->name[name->num_ids++] = bstrdup(row[0]);
628 * Construct name list
630 static int make_name_list(const char *query, NAME_LIST *name_list)
632 name_list->num_ids = 0;
633 name_list->num_del = 0;
634 name_list->tot_ids = 0;
636 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
637 printf("%s", db_strerror(db));
644 * Print names in the list
646 static void print_name_list(NAME_LIST *name_list)
648 for (int i=0; i < name_list->num_ids; i++) {
649 printf("%s\n", name_list->name[i]);
655 * Free names in the list
657 static void free_name_list(NAME_LIST *name_list)
659 for (int i=0; i < name_list->num_ids; i++) {
660 free(name_list->name[i]);
662 name_list->num_ids = 0;
665 static void eliminate_duplicate_filenames()
670 printf(_("Checking for duplicate Filename entries.\n"));
672 /* Make list of duplicated names */
673 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
674 "HAVING count(Name) > 1";
676 if (!make_name_list(query, &name_list)) {
679 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
680 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
681 print_name_list(&name_list);
687 /* Loop through list of duplicate names */
688 for (int i=0; i<name_list.num_ids; i++) {
689 /* Get all the Ids of each name */
690 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
691 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
695 if (!make_id_list(buf, &id_list)) {
699 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
701 /* Force all records to use the first id then delete the other ids */
702 for (int j=1; j<id_list.num_ids; j++) {
703 char ed1[50], ed2[50];
704 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
705 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
709 db_sql_query(db, buf, NULL, NULL);
710 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
715 db_sql_query(db, buf, NULL, NULL);
719 free_name_list(&name_list);
722 static void eliminate_duplicate_paths()
727 printf(_("Checking for duplicate Path entries.\n"));
729 /* Make list of duplicated names */
731 query = "SELECT Path, count(Path) as Count FROM Path "
732 "GROUP BY Path HAVING count(Path) > 1";
734 if (!make_name_list(query, &name_list)) {
737 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
738 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
739 print_name_list(&name_list);
745 /* Loop through list of duplicate names */
746 for (int i=0; i<name_list.num_ids; i++) {
747 /* Get all the Ids of each name */
748 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
749 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
753 if (!make_id_list(buf, &id_list)) {
757 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
759 /* Force all records to use the first id then delete the other ids */
760 for (int j=1; j<id_list.num_ids; j++) {
761 char ed1[50], ed2[50];
762 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
763 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
767 db_sql_query(db, buf, NULL, NULL);
768 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
772 db_sql_query(db, buf, NULL, NULL);
776 free_name_list(&name_list);
779 static void eliminate_orphaned_jobmedia_records()
781 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
782 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
783 "WHERE Job.JobId IS NULL LIMIT 300000";
785 printf(_("Checking for orphaned JobMedia entries.\n"));
786 if (!make_id_list(query, &id_list)) {
789 /* Loop doing 300000 at a time */
790 while (id_list.num_ids != 0) {
791 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
792 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
793 for (int i=0; i < id_list.num_ids; i++) {
795 bsnprintf(buf, sizeof(buf),
796 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
797 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
798 edit_int64(id_list.Id[i], ed1));
799 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
800 printf("%s\n", db_strerror(db));
808 if (fix && id_list.num_ids > 0) {
809 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
810 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
812 break; /* get out if not updating db */
814 if (!make_id_list(query, &id_list)) {
820 static void eliminate_orphaned_file_records()
822 const char *query = "SELECT File.FileId,Job.JobId FROM File "
823 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
824 "WHERE Job.JobId IS NULL LIMIT 300000";
826 printf(_("Checking for orphaned File entries. This may take some time!\n"));
828 printf("%s\n", query);
830 if (!make_id_list(query, &id_list)) {
833 /* Loop doing 300000 at a time */
834 while (id_list.num_ids != 0) {
835 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
836 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
837 for (int i=0; i < id_list.num_ids; i++) {
839 bsnprintf(buf, sizeof(buf),
840 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
841 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
842 edit_int64(id_list.Id[i], ed1));
843 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
844 printf("%s\n", db_strerror(db));
851 if (fix && id_list.num_ids > 0) {
852 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
853 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
855 break; /* get out if not updating db */
857 if (!make_id_list(query, &id_list)) {
863 static void eliminate_orphaned_path_records()
866 /* check the existence of the required "one column" index */
867 if (!check_idx("PathId")) {
868 if (yes_no(_("Create temporary index? (yes/no): "))) {
869 /* create temporary index PathId */
870 create_tmp_idx("idxPIchk", "File", "PathId");
874 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
875 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
876 "WHERE File.PathId IS NULL LIMIT 300000";
878 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
880 printf("%s\n", query);
882 if (!make_id_list(query, &id_list)) {
885 /* Loop doing 300000 at a time */
886 while (id_list.num_ids != 0) {
887 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
888 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
889 for (int i=0; i < id_list.num_ids; i++) {
891 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
892 edit_int64(id_list.Id[i], ed1));
893 db_sql_query(db, buf, print_name_handler, NULL);
899 if (fix && id_list.num_ids > 0) {
900 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
901 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
903 break; /* get out if not updating db */
905 if (!make_id_list(query, &id_list)) {
909 /* drop temporary index idx_tmp_name */
910 drop_tmp_idx("idxPIchk", "File");
913 static void eliminate_orphaned_filename_records()
916 /* check the existence of the required "one column" index */
917 if (!check_idx("FilenameId") ) {
918 if (yes_no(_("Create temporary index? (yes/no): "))) {
919 /* create temporary index FilenameId */
920 create_tmp_idx("idxFIchk", "File", "FilenameId");
924 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
925 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
926 "WHERE File.FilenameId IS NULL LIMIT 300000";
928 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
930 printf("%s\n", query);
932 if (!make_id_list(query, &id_list)) {
935 /* Loop doing 300000 at a time */
936 while (id_list.num_ids != 0) {
937 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
938 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
939 for (int i=0; i < id_list.num_ids; i++) {
941 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
942 edit_int64(id_list.Id[i], ed1));
943 db_sql_query(db, buf, print_name_handler, NULL);
949 if (fix && id_list.num_ids > 0) {
950 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
951 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
953 break; /* get out if not updating db */
955 if (!make_id_list(query, &id_list)) {
959 /* drop temporary index idx_tmp_name */
960 drop_tmp_idx("idxFIchk", "File");
964 static void eliminate_orphaned_fileset_records()
968 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
969 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
970 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
971 "WHERE Job.FileSetId IS NULL";
973 printf("%s\n", query);
975 if (!make_id_list(query, &id_list)) {
978 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
979 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
980 for (int i=0; i < id_list.num_ids; i++) {
982 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
983 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
984 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
985 printf("%s\n", db_strerror(db));
992 if (fix && id_list.num_ids > 0) {
993 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
994 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
998 static void eliminate_orphaned_client_records()
1002 printf(_("Checking for orphaned Client entries.\n"));
1004 * Wiffle through Client for every Client
1005 * joining with the Job table including every Client even if
1006 * there is not a match in Job (left outer join), then
1007 * filter out only those where no Job points to a Client
1008 * i.e. Job.Client is NULL
1010 query = "SELECT Client.ClientId,Client.Name FROM Client "
1011 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1012 "WHERE Job.ClientId IS NULL";
1014 printf("%s\n", query);
1016 if (!make_id_list(query, &id_list)) {
1019 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1020 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1021 for (int i=0; i < id_list.num_ids; i++) {
1023 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1024 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1025 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1026 printf("%s\n", db_strerror(db));
1033 if (fix && id_list.num_ids > 0) {
1034 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1035 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1039 static void eliminate_orphaned_job_records()
1043 printf(_("Checking for orphaned Job entries.\n"));
1045 * Wiffle through Job for every Job
1046 * joining with the Client table including every Job even if
1047 * there is not a match in Client (left outer join), then
1048 * filter out only those where no Client exists
1049 * i.e. Client.Name is NULL
1051 query = "SELECT Job.JobId,Job.Name FROM Job "
1052 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1053 "WHERE Client.Name IS NULL";
1055 printf("%s\n", query);
1057 if (!make_id_list(query, &id_list)) {
1060 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1061 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1062 for (int i=0; i < id_list.num_ids; i++) {
1064 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1065 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1066 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1067 printf("%s\n", db_strerror(db));
1074 if (fix && id_list.num_ids > 0) {
1075 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1076 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1077 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1078 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1079 printf(_("Deleting Log records of orphaned Job records.\n"));
1080 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1085 static void eliminate_admin_records()
1089 printf(_("Checking for Admin Job entries.\n"));
1090 query = "SELECT Job.JobId FROM Job "
1091 "WHERE Job.Type='D'";
1093 printf("%s\n", query);
1095 if (!make_id_list(query, &id_list)) {
1098 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1099 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1100 for (int i=0; i < id_list.num_ids; i++) {
1102 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1103 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1104 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1105 printf("%s\n", db_strerror(db));
1112 if (fix && id_list.num_ids > 0) {
1113 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1114 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1118 static void eliminate_restore_records()
1122 printf(_("Checking for Restore Job entries.\n"));
1123 query = "SELECT Job.JobId FROM Job "
1124 "WHERE Job.Type='R'";
1126 printf("%s\n", query);
1128 if (!make_id_list(query, &id_list)) {
1131 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1132 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1133 for (int i=0; i < id_list.num_ids; i++) {
1135 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1136 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1137 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1138 printf("%s\n", db_strerror(db));
1145 if (fix && id_list.num_ids > 0) {
1146 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1147 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1154 static void repair_bad_filenames()
1159 printf(_("Checking for Filenames with a trailing slash\n"));
1160 query = "SELECT FilenameId,Name from Filename "
1161 "WHERE Name LIKE '%/'";
1163 printf("%s\n", query);
1165 if (!make_id_list(query, &id_list)) {
1168 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1169 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1170 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, print_name_handler, NULL)) {
1176 printf("%s\n", db_strerror(db));
1183 if (fix && id_list.num_ids > 0) {
1184 POOLMEM *name = get_pool_memory(PM_FNAME);
1185 char esc_name[5000];
1186 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1187 for (i=0; i < id_list.num_ids; i++) {
1190 bsnprintf(buf, sizeof(buf),
1191 "SELECT Name FROM Filename WHERE FilenameId=%s",
1192 edit_int64(id_list.Id[i], ed1));
1193 if (!db_sql_query(db, buf, get_name_handler, name)) {
1194 printf("%s\n", db_strerror(db));
1196 /* Strip trailing slash(es) */
1197 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1205 db_escape_string(NULL, db, esc_name, name, len);
1207 bsnprintf(buf, sizeof(buf),
1208 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1209 esc_name, edit_int64(id_list.Id[i], ed1));
1211 printf("%s\n", buf);
1213 db_sql_query(db, buf, NULL, NULL);
1218 static void repair_bad_paths()
1223 printf(_("Checking for Paths without a trailing slash\n"));
1224 query = "SELECT PathId,Path from Path "
1225 "WHERE Path NOT LIKE '%/'";
1227 printf("%s\n", query);
1229 if (!make_id_list(query, &id_list)) {
1232 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1233 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1234 for (i=0; i < id_list.num_ids; i++) {
1236 bsnprintf(buf, sizeof(buf),
1237 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1238 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1239 printf("%s\n", db_strerror(db));
1246 if (fix && id_list.num_ids > 0) {
1247 POOLMEM *name = get_pool_memory(PM_FNAME);
1248 char esc_name[5000];
1249 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1250 for (i=0; i < id_list.num_ids; i++) {
1253 bsnprintf(buf, sizeof(buf),
1254 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1255 if (!db_sql_query(db, buf, get_name_handler, name)) {
1256 printf("%s\n", db_strerror(db));
1258 /* Strip trailing blanks */
1259 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1262 /* Add trailing slash */
1263 len = pm_strcat(&name, "/");
1264 db_escape_string(NULL, db, esc_name, name, len);
1265 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1266 esc_name, edit_int64(id_list.Id[i], ed1));
1268 printf("%s\n", buf);
1270 db_sql_query(db, buf, NULL, NULL);
1277 * Gen next input command from the terminal
1279 static char *get_cmd(const char *prompt)
1281 static char cmd[1000];
1283 printf("%s", prompt);
1284 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1289 strip_trailing_junk(cmd);
1293 static bool yes_no(const char *prompt)
1296 cmd = get_cmd(prompt);
1301 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1304 bool python_set_prog(JCR*, char const*) { return false; }
1308 * The code below to add indexes is needed only for MySQL, and
1309 * that to improve the performance.
1314 typedef struct s_idx_list {
1316 int count_key; /* how many times the index meets *key_name */
1317 int count_col; /* how many times meets the desired column name */
1320 static IDX_LIST idx_list[MAXIDX];
1323 * Called here with each table index to be added to the list
1325 static int check_idx_handler(void *ctx, int num_fields, char **row)
1327 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1328 * File | 0 | PRIMARY | 1 | FileId |...
1330 char *name, *key_name, *col_name;
1336 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1337 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1338 idx_list[i].count_key++;
1340 if (strcasecmp(col_name, name) == 0) {
1341 idx_list[i].count_col++;
1346 /* if the new Key_name, add it to the list */
1348 len = strlen(key_name) + 1;
1349 idx_list[i].key_name = (char *)malloc(len);
1350 bstrncpy(idx_list[i].key_name, key_name, len);
1351 idx_list[i].count_key = 1;
1352 if (strcasecmp(col_name, name) == 0) {
1353 idx_list[i].count_col = 1;
1355 idx_list[i].count_col = 0;
1363 * Return TRUE if "one column" index over *col_name exists
1365 static bool check_idx(const char *col_name)
1371 memset(&idx_list, 0, sizeof(idx_list));
1372 const char *query = "SHOW INDEX FROM File";
1373 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1374 printf("%s\n", db_strerror(db));
1377 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1378 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1379 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1380 /* "one column" index over *col_name found */
1386 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1389 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1399 * Create temporary one-column index
1401 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1402 const char *col_name)
1404 idx_tmp_name = NULL;
1405 printf(_("Create temporary index... This may take some time!\n"));
1406 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1408 printf("%s\n", buf);
1410 if (db_sql_query(db, buf, NULL, NULL)) {
1411 idx_tmp_name = idx_name;
1413 printf(_("Temporary index created.\n"));
1416 printf("%s\n", db_strerror(db));
1423 * Drop temporary index
1425 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1427 if (idx_tmp_name != NULL) {
1428 printf(_("Drop temporary index.\n"));
1429 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1431 printf("%s\n", buf);
1433 if (!db_sql_query(db, buf, NULL, NULL)) {
1434 printf("%s\n", db_strerror(db));
1438 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1442 idx_tmp_name = NULL;