2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2016 Kern Sibbald
6 The original author of Bacula is Kern Sibbald, with contributions
7 from many others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
21 * Program to check a Bacula database for consistency and to
24 * Kern E. Sibbald, August 2002
29 #include "cats/cats.h"
30 #include "lib/runscript.h"
31 #include "dird/dird_conf.h"
33 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
35 typedef struct s_id_ctx {
36 int64_t *Id; /* ids to be modified */
37 int num_ids; /* ids stored */
38 int max_ids; /* size of array */
39 int num_del; /* number deleted */
40 int tot_ids; /* total to process */
43 typedef struct s_name_ctx {
44 char **name; /* list of names */
45 int num_ids; /* ids stored */
46 int max_ids; /* size of array */
47 int num_del; /* number deleted */
48 int tot_ids; /* total to process */
51 /* Global variables */
53 static bool fix = false;
54 static bool batch = false;
56 static ID_LIST id_list;
57 static NAME_LIST name_list;
58 static char buf[20000];
59 static bool quit = false;
60 static CONFIG *config;
61 static const char *idx_tmp_name;
63 #define MAX_ID_LIST_LEN 10000000
65 /* Forward referenced functions */
66 static int make_id_list(const char *query, ID_LIST *id_list);
67 static int delete_id_list(const char *query, ID_LIST *id_list);
68 static int make_name_list(const char *query, NAME_LIST *name_list);
69 static void print_name_list(NAME_LIST *name_list);
70 static void free_name_list(NAME_LIST *name_list);
71 static char *get_cmd(const char *prompt);
72 static void eliminate_duplicate_filenames();
73 static void eliminate_duplicate_paths();
74 static void eliminate_orphaned_jobmedia_records();
75 static void eliminate_orphaned_file_records();
76 static void eliminate_orphaned_path_records();
77 static void eliminate_orphaned_filename_records();
78 static void eliminate_orphaned_fileset_records();
79 static void eliminate_orphaned_client_records();
80 static void eliminate_orphaned_job_records();
81 static void eliminate_admin_records();
82 static void eliminate_restore_records();
83 static void repair_bad_paths();
84 static void repair_bad_filenames();
85 static void do_interactive_mode();
86 static bool yes_no(const char *prompt);
87 static bool check_idx(const char *col_name);
88 static bool create_tmp_idx(const char *idx_name, const char *table_name,
89 const char *col_name);
90 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
91 static int check_idx_handler(void *ctx, int num_fields, char **row);
97 "\n%sVersion: %s (%s)\n\n"
98 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>] [<dbport>] [<dbsslmode>] [<dbsslkey>] [<dbsslcert>] [<dbsslca>]\n"
100 " -C catalog name in the director conf file\n"
101 " -c Director conf filename\n"
102 " -B print catalog configuration and exit\n"
103 " -d <nn> set debug level to <nn>\n"
104 " -dt print a timestamp in debug output\n"
105 " -f fix inconsistencies\n"
106 " -t test if client library is thread-safe\n"
108 " -? print this message\n"
109 "\n", 2002, "", VERSION, BDATE);
114 int main (int argc, char *argv[])
117 const char *user, *password, *db_name, *dbhost;
118 const char *dbsslmode = NULL, *dbsslkey = NULL, *dbsslcert = NULL, *dbsslca = NULL;
119 const char *dbsslcapath = NULL, *dbsslcipher = NULL;
121 bool print_catalog=false;
122 char *configfile = NULL;
123 char *catalogname = NULL;
126 setlocale(LC_ALL, "");
127 bindtextdomain("bacula", LOCALEDIR);
128 textdomain("bacula");
131 my_name_is(argc, argv, "dbcheck");
132 init_msg(NULL, NULL); /* setup message handler */
134 memset(&id_list, 0, sizeof(id_list));
135 memset(&name_list, 0, sizeof(name_list));
137 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
140 print_catalog = true; /* get catalog information from config */
142 case 'b': /* batch */
145 case 'C': /* CatalogName */
146 catalogname = optarg;
148 case 'c': /* configfile */
151 case 'd': /* debug level */
152 if (*optarg == 't') {
153 dbg_timestamp = true;
155 debug_level = atoi(optarg);
156 if (debug_level <= 0) {
161 case 'f': /* fix inconsistencies */
181 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
183 config = New(CONFIG());
184 parse_dir_config(config, configfile, M_ERROR_TERM);
186 foreach_res(catalog, R_CATALOG) {
187 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
190 } else if (!catalogname) { // stop on first if no catalogname is given
198 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
200 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
206 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
209 Pmsg0(0, _("Error no Director resource defined.\n"));
212 set_working_directory(director->working_directory);
214 /* Print catalog information and exit (-B) */
217 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
218 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
219 catalog->db_password, catalog->db_address,
220 catalog->db_port, catalog->db_socket,
221 catalog->db_ssl_mode,
222 catalog->db_ssl_key, catalog->db_ssl_cert,
224 catalog->db_ssl_capath, catalog->db_ssl_cipher,
225 catalog->mult_db_connections,
226 catalog->disable_batch_insert);
228 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
229 db_get_engine_name(db), working_directory);
230 db_close_database(NULL, db);
232 free_pool_memory(catalog_details);
236 db_name = catalog->db_name;
237 user = catalog->db_user;
238 password = catalog->db_password;
239 dbhost = catalog->db_address;
240 if (dbhost && dbhost[0] == 0) {
243 dbport = catalog->db_port;
244 dbsslmode = catalog->db_ssl_mode;
245 dbsslkey = catalog->db_ssl_key;
246 dbsslcert = catalog->db_ssl_cert;
247 dbsslca = catalog->db_ssl_ca;
248 dbsslcapath = catalog->db_ssl_capath;
249 dbsslcipher = catalog->db_ssl_cipher;
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];
280 dbport = strtol(argv[5], &endptr, 10);
281 if (*endptr != '\0') {
282 Pmsg0(0, _("Database port must be a numeric value.\n"));
284 } else if (errno == ERANGE) {
285 Pmsg0(0, _("Database port must be a int value.\n"));
295 } /* if (argc == 10) */
296 } /* if (argc >= 8) */
297 } /* if (argc >= 7) */
298 } /* if (argc >= 6) */
299 } /* if (argc >= 5) */
300 } /* if (argc >= 4) */
301 } /* if (argc >= 3) */
302 } /* if (argc >= 2) */
306 db = db_init_database(NULL, NULL, db_name, user, password, dbhost,
307 dbport, NULL, dbsslmode, dbsslkey, dbsslcert, dbsslca,
308 dbsslcapath, dbsslcipher, false, false);
310 if (!db || !db_open_database(NULL, db)) {
311 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
315 /* Drop temporary index idx_tmp_name if it already exists */
316 drop_tmp_idx("idxPIchk", "File");
320 repair_bad_filenames();
321 eliminate_duplicate_filenames();
322 eliminate_duplicate_paths();
323 eliminate_orphaned_jobmedia_records();
324 eliminate_orphaned_file_records();
325 eliminate_orphaned_path_records();
326 eliminate_orphaned_filename_records();
327 eliminate_orphaned_fileset_records();
328 eliminate_orphaned_client_records();
329 eliminate_orphaned_job_records();
330 eliminate_admin_records();
331 eliminate_restore_records();
333 do_interactive_mode();
336 /* Drop temporary index idx_tmp_name */
337 drop_tmp_idx("idxPIchk", "File");
339 if (db) db_close_database(NULL, db);
346 void print_catalog_details(CAT *catalog, const char *working_dir)
348 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
351 * Instantiate a BDB class and see what db_type gets assigned to it.
353 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
354 catalog->db_password, catalog->db_address,
355 catalog->db_port, catalog->db_socket,
356 catalog->db_ssl_mode, catalog->db_ssl_key,
357 catalog->db_ssl_cert, catalog->db_ssl_ca,
358 catalog->db_ssl_capath, catalog->db_ssl_cipher,
359 catalog->mult_db_connections,
360 catalog->disable_batch_insert);
362 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
363 db_get_engine_name(db), working_directory);
364 db_close_database(NULL, db);
366 free_pool_memory(catalog_details);
369 static void do_interactive_mode()
373 printf(_("Hello, this is the database check/correct program.\n"));
375 printf(_("Modify database is on."));
377 printf(_("Modify database is off."));
379 printf(_(" Verbose is on.\n"));
381 printf(_(" Verbose is off.\n"));
383 printf(_("Please select the function you want to perform.\n"));
388 " 1) Toggle modify database flag\n"
389 " 2) Toggle verbose flag\n"
390 " 3) Repair bad Filename records\n"
391 " 4) Repair bad Path records\n"
392 " 5) Eliminate duplicate Filename records\n"
393 " 6) Eliminate duplicate Path records\n"
394 " 7) Eliminate orphaned Jobmedia records\n"
395 " 8) Eliminate orphaned File records\n"
396 " 9) Eliminate orphaned Path records\n"
397 " 10) Eliminate orphaned Filename records\n"
398 " 11) Eliminate orphaned FileSet records\n"
399 " 12) Eliminate orphaned Client records\n"
400 " 13) Eliminate orphaned Job records\n"
401 " 14) Eliminate all Admin records\n"
402 " 15) Eliminate all Restore records\n"
407 " 1) Toggle modify database flag\n"
408 " 2) Toggle verbose flag\n"
409 " 3) Check for bad Filename records\n"
410 " 4) Check for bad Path records\n"
411 " 5) Check for duplicate Filename records\n"
412 " 6) Check for duplicate Path records\n"
413 " 7) Check for orphaned Jobmedia records\n"
414 " 8) Check for orphaned File records\n"
415 " 9) Check for orphaned Path records\n"
416 " 10) Check for orphaned Filename records\n"
417 " 11) Check for orphaned FileSet records\n"
418 " 12) Check for orphaned Client records\n"
419 " 13) Check for orphaned Job records\n"
420 " 14) Check for all Admin records\n"
421 " 15) Check for all Restore records\n"
426 cmd = get_cmd(_("Select function number: "));
428 int item = atoi(cmd);
433 printf(_("Database will be modified.\n"));
435 printf(_("Database will NOT be modified.\n"));
438 verbose = verbose?0:1;
440 printf(_(" Verbose is on.\n"));
442 printf(_(" Verbose is off.\n"));
445 repair_bad_filenames();
451 eliminate_duplicate_filenames();
454 eliminate_duplicate_paths();
457 eliminate_orphaned_jobmedia_records();
460 eliminate_orphaned_file_records();
463 eliminate_orphaned_path_records();
466 eliminate_orphaned_filename_records();
469 eliminate_orphaned_fileset_records();
472 eliminate_orphaned_client_records();
475 eliminate_orphaned_job_records();
478 eliminate_admin_records();
481 eliminate_restore_records();
484 repair_bad_filenames();
486 eliminate_duplicate_filenames();
487 eliminate_duplicate_paths();
488 eliminate_orphaned_jobmedia_records();
489 eliminate_orphaned_file_records();
490 eliminate_orphaned_path_records();
491 eliminate_orphaned_filename_records();
492 eliminate_orphaned_fileset_records();
493 eliminate_orphaned_client_records();
494 eliminate_orphaned_job_records();
495 eliminate_admin_records();
496 eliminate_restore_records();
506 static int print_name_handler(void *ctx, int num_fields, char **row)
509 printf("%s\n", row[0]);
514 static int get_name_handler(void *ctx, int num_fields, char **row)
516 POOLMEM *buf = (POOLMEM *)ctx;
518 pm_strcpy(&buf, row[0]);
523 static int print_job_handler(void *ctx, int num_fields, char **row)
525 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
526 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
530 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
532 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
533 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
537 static int print_file_handler(void *ctx, int num_fields, char **row)
539 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
540 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
544 static int print_fileset_handler(void *ctx, int num_fields, char **row)
546 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
547 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
551 static int print_client_handler(void *ctx, int num_fields, char **row)
553 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
554 NPRT(row[0]), NPRT(row[1]));
559 * Called here with each id to be added to the list
561 static int id_list_handler(void *ctx, int num_fields, char **row)
563 ID_LIST *lst = (ID_LIST *)ctx;
565 if (lst->num_ids == MAX_ID_LIST_LEN) {
568 if (lst->num_ids == lst->max_ids) {
569 if (lst->max_ids == 0) {
570 lst->max_ids = 10000;
571 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
573 lst->max_ids = (lst->max_ids * 3) / 2;
574 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
577 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
582 * Construct record id list
584 static int make_id_list(const char *query, ID_LIST *id_list)
586 id_list->num_ids = 0;
587 id_list->num_del = 0;
588 id_list->tot_ids = 0;
590 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
591 printf("%s", db_strerror(db));
598 * Delete all entries in the list
600 static int delete_id_list(const char *query, ID_LIST *id_list)
603 for (int i=0; i < id_list->num_ids; i++) {
604 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
606 printf(_("Deleting: %s\n"), buf);
608 db_sql_query(db, buf, NULL, NULL);
614 * Called here with each name to be added to the list
616 static int name_list_handler(void *ctx, int num_fields, char **row)
618 NAME_LIST *name = (NAME_LIST *)ctx;
620 if (name->num_ids == MAX_ID_LIST_LEN) {
623 if (name->num_ids == name->max_ids) {
624 if (name->max_ids == 0) {
625 name->max_ids = 10000;
626 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
628 name->max_ids = (name->max_ids * 3) / 2;
629 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
632 name->name[name->num_ids++] = bstrdup(row[0]);
637 * Construct name list
639 static int make_name_list(const char *query, NAME_LIST *name_list)
641 name_list->num_ids = 0;
642 name_list->num_del = 0;
643 name_list->tot_ids = 0;
645 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
646 printf("%s", db_strerror(db));
653 * Print names in the list
655 static void print_name_list(NAME_LIST *name_list)
657 for (int i=0; i < name_list->num_ids; i++) {
658 printf("%s\n", name_list->name[i]);
663 * Free names in the list
665 static void free_name_list(NAME_LIST *name_list)
667 for (int i=0; i < name_list->num_ids; i++) {
668 free(name_list->name[i]);
670 name_list->num_ids = 0;
673 static void eliminate_duplicate_filenames()
678 printf(_("Checking for duplicate Filename entries.\n"));
680 /* Make list of duplicated names */
681 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
682 "HAVING count(Name) > 1";
684 if (!make_name_list(query, &name_list)) {
687 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
688 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
689 print_name_list(&name_list);
695 /* Loop through list of duplicate names */
696 for (int i=0; i<name_list.num_ids; i++) {
697 /* Get all the Ids of each name */
698 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
699 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
703 if (!make_id_list(buf, &id_list)) {
707 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
709 /* Force all records to use the first id then delete the other ids */
710 for (int j=1; j<id_list.num_ids; j++) {
711 char ed1[50], ed2[50];
712 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
713 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
717 db_sql_query(db, buf, NULL, NULL);
718 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
723 db_sql_query(db, buf, NULL, NULL);
727 free_name_list(&name_list);
730 static void eliminate_duplicate_paths()
735 printf(_("Checking for duplicate Path entries.\n"));
737 /* Make list of duplicated names */
738 query = "SELECT Path, count(Path) as Count FROM Path "
739 "GROUP BY Path HAVING count(Path) > 1";
741 if (!make_name_list(query, &name_list)) {
744 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
745 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
746 print_name_list(&name_list);
752 /* Loop through list of duplicate names */
753 for (int i=0; i<name_list.num_ids; i++) {
754 /* Get all the Ids of each name */
755 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
756 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
760 if (!make_id_list(buf, &id_list)) {
764 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
766 /* Force all records to use the first id then delete the other ids */
767 for (int j=1; j<id_list.num_ids; j++) {
768 char ed1[50], ed2[50];
769 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
770 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
774 db_sql_query(db, buf, NULL, NULL);
775 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
779 db_sql_query(db, buf, NULL, NULL);
783 free_name_list(&name_list);
786 static void eliminate_orphaned_jobmedia_records()
788 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
789 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
790 "WHERE Job.JobId IS NULL LIMIT 300000";
792 printf(_("Checking for orphaned JobMedia entries.\n"));
793 if (!make_id_list(query, &id_list)) {
796 /* Loop doing 300000 at a time */
797 while (id_list.num_ids != 0) {
798 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
799 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
800 for (int i=0; i < id_list.num_ids; i++) {
802 bsnprintf(buf, sizeof(buf),
803 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
804 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
805 edit_int64(id_list.Id[i], ed1));
806 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
807 printf("%s\n", db_strerror(db));
815 if (fix && id_list.num_ids > 0) {
816 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
817 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
819 break; /* get out if not updating db */
821 if (!make_id_list(query, &id_list)) {
827 static void eliminate_orphaned_file_records()
829 const char *query = "SELECT File.FileId,Job.JobId FROM File "
830 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
831 "WHERE Job.JobId IS NULL LIMIT 300000";
833 printf(_("Checking for orphaned File entries. This may take some time!\n"));
835 printf("%s\n", query);
837 if (!make_id_list(query, &id_list)) {
840 /* Loop doing 300000 at a time */
841 while (id_list.num_ids != 0) {
842 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
843 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
844 for (int i=0; i < id_list.num_ids; i++) {
846 bsnprintf(buf, sizeof(buf),
847 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
848 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
849 edit_int64(id_list.Id[i], ed1));
850 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
851 printf("%s\n", db_strerror(db));
858 if (fix && id_list.num_ids > 0) {
859 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
860 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
862 break; /* get out if not updating db */
864 if (!make_id_list(query, &id_list)) {
870 static void eliminate_orphaned_path_records()
874 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
875 db_int64_handler, &lctx);
877 if (lctx.count == 1) {
878 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
883 /* Check the existence of the required "one column" index */
884 if (!check_idx("PathId")) {
885 if (yes_no(_("Create temporary index? (yes/no): "))) {
886 /* create temporary index PathId */
887 create_tmp_idx("idxPIchk", "File", "PathId");
891 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
892 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
893 "WHERE File.PathId IS NULL LIMIT 300000";
895 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
897 printf("%s\n", query);
899 if (!make_id_list(query, &id_list)) {
902 /* Loop doing 300000 at a time */
903 while (id_list.num_ids != 0) {
904 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
905 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
906 for (int i=0; i < id_list.num_ids; i++) {
908 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
909 edit_int64(id_list.Id[i], ed1));
910 db_sql_query(db, buf, print_name_handler, NULL);
916 if (fix && id_list.num_ids > 0) {
917 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
918 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
920 break; /* get out if not updating db */
922 if (!make_id_list(query, &id_list)) {
926 /* Drop temporary index idx_tmp_name */
927 drop_tmp_idx("idxPIchk", "File");
930 static void eliminate_orphaned_filename_records()
933 /* Check the existence of the required "one column" index */
934 if (!check_idx("FilenameId") ) {
935 if (yes_no(_("Create temporary index? (yes/no): "))) {
936 /* Create temporary index FilenameId */
937 create_tmp_idx("idxFIchk", "File", "FilenameId");
941 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
942 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
943 "WHERE File.FilenameId IS NULL LIMIT 300000";
945 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
947 printf("%s\n", query);
949 if (!make_id_list(query, &id_list)) {
952 /* Loop doing 300000 at a time */
953 while (id_list.num_ids != 0) {
954 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
955 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
956 for (int i=0; i < id_list.num_ids; i++) {
958 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
959 edit_int64(id_list.Id[i], ed1));
960 db_sql_query(db, buf, print_name_handler, NULL);
966 if (fix && id_list.num_ids > 0) {
967 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
968 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
970 break; /* get out if not updating db */
972 if (!make_id_list(query, &id_list)) {
976 /* Drop temporary index idx_tmp_name */
977 drop_tmp_idx("idxFIchk", "File");
981 static void eliminate_orphaned_fileset_records()
985 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
986 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
987 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
988 "WHERE Job.FileSetId IS NULL";
990 printf("%s\n", query);
992 if (!make_id_list(query, &id_list)) {
995 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
996 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
997 for (int i=0; i < id_list.num_ids; i++) {
999 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1000 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1001 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1002 printf("%s\n", db_strerror(db));
1009 if (fix && id_list.num_ids > 0) {
1010 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1011 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1015 static void eliminate_orphaned_client_records()
1019 printf(_("Checking for orphaned Client entries.\n"));
1021 * Wiffle through Client for every Client
1022 * joining with the Job table including every Client even if
1023 * there is not a match in Job (left outer join), then
1024 * filter out only those where no Job points to a Client
1025 * i.e. Job.Client is NULL
1027 query = "SELECT Client.ClientId,Client.Name FROM Client "
1028 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1029 "WHERE Job.ClientId IS NULL";
1031 printf("%s\n", query);
1033 if (!make_id_list(query, &id_list)) {
1036 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1037 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1038 for (int i=0; i < id_list.num_ids; i++) {
1040 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1041 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1042 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1043 printf("%s\n", db_strerror(db));
1050 if (fix && id_list.num_ids > 0) {
1051 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1052 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1056 static void eliminate_orphaned_job_records()
1060 printf(_("Checking for orphaned Job entries.\n"));
1062 * Wiffle through Job for every Job
1063 * joining with the Client table including every Job even if
1064 * there is not a match in Client (left outer join), then
1065 * filter out only those where no Client exists
1066 * i.e. Client.Name is NULL
1068 query = "SELECT Job.JobId,Job.Name FROM Job "
1069 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1070 "WHERE Client.Name IS NULL";
1072 printf("%s\n", query);
1074 if (!make_id_list(query, &id_list)) {
1077 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1078 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1079 for (int i=0; i < id_list.num_ids; i++) {
1081 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1082 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1083 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1084 printf("%s\n", db_strerror(db));
1091 if (fix && id_list.num_ids > 0) {
1092 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1093 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1094 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1095 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1096 printf(_("Deleting Log records of orphaned Job records.\n"));
1097 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1101 static void eliminate_admin_records()
1105 printf(_("Checking for Admin Job entries.\n"));
1106 query = "SELECT Job.JobId FROM Job "
1107 "WHERE Job.Type='D'";
1109 printf("%s\n", query);
1111 if (!make_id_list(query, &id_list)) {
1114 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1115 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1116 for (int i=0; i < id_list.num_ids; i++) {
1118 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1119 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1120 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1121 printf("%s\n", db_strerror(db));
1128 if (fix && id_list.num_ids > 0) {
1129 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1130 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1134 static void eliminate_restore_records()
1138 printf(_("Checking for Restore Job entries.\n"));
1139 query = "SELECT Job.JobId FROM Job "
1140 "WHERE Job.Type='R'";
1142 printf("%s\n", query);
1144 if (!make_id_list(query, &id_list)) {
1147 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1148 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1149 for (int i=0; i < id_list.num_ids; i++) {
1151 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1152 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1153 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1154 printf("%s\n", db_strerror(db));
1161 if (fix && id_list.num_ids > 0) {
1162 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1163 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1167 static void repair_bad_filenames()
1172 printf(_("Checking for Filenames with a trailing slash\n"));
1173 query = "SELECT FilenameId,Name from Filename "
1174 "WHERE Name LIKE '%/'";
1176 printf("%s\n", query);
1178 if (!make_id_list(query, &id_list)) {
1181 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1182 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1183 for (i=0; i < id_list.num_ids; i++) {
1185 bsnprintf(buf, sizeof(buf),
1186 "SELECT Name FROM Filename WHERE FilenameId=%s",
1187 edit_int64(id_list.Id[i], ed1));
1188 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1189 printf("%s\n", db_strerror(db));
1196 if (fix && id_list.num_ids > 0) {
1197 POOLMEM *name = get_pool_memory(PM_FNAME);
1198 char esc_name[5000];
1199 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1200 for (i=0; i < id_list.num_ids; i++) {
1203 bsnprintf(buf, sizeof(buf),
1204 "SELECT Name FROM Filename WHERE FilenameId=%s",
1205 edit_int64(id_list.Id[i], ed1));
1206 if (!db_sql_query(db, buf, get_name_handler, name)) {
1207 printf("%s\n", db_strerror(db));
1209 /* Strip trailing slash(es) */
1210 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1218 db_escape_string(NULL, db, esc_name, name, len);
1220 bsnprintf(buf, sizeof(buf),
1221 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1222 esc_name, edit_int64(id_list.Id[i], ed1));
1224 printf("%s\n", buf);
1226 db_sql_query(db, buf, NULL, NULL);
1228 free_pool_memory(name);
1232 static void repair_bad_paths()
1237 printf(_("Checking for Paths without a trailing slash\n"));
1238 query = "SELECT PathId,Path from Path "
1239 "WHERE Path NOT LIKE '%/'";
1241 printf("%s\n", query);
1243 if (!make_id_list(query, &id_list)) {
1246 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1247 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1248 for (i=0; i < id_list.num_ids; i++) {
1250 bsnprintf(buf, sizeof(buf),
1251 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1252 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1253 printf("%s\n", db_strerror(db));
1260 if (fix && id_list.num_ids > 0) {
1261 POOLMEM *name = get_pool_memory(PM_FNAME);
1262 char esc_name[5000];
1263 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1264 for (i=0; i < id_list.num_ids; i++) {
1267 bsnprintf(buf, sizeof(buf),
1268 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1269 if (!db_sql_query(db, buf, get_name_handler, name)) {
1270 printf("%s\n", db_strerror(db));
1272 /* Strip trailing blanks */
1273 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1276 /* Add trailing slash */
1277 len = pm_strcat(&name, "/");
1278 db_escape_string(NULL, db, esc_name, name, len);
1279 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1280 esc_name, edit_int64(id_list.Id[i], ed1));
1282 printf("%s\n", buf);
1284 db_sql_query(db, buf, NULL, NULL);
1286 free_pool_memory(name);
1291 * Gen next input command from the terminal
1293 static char *get_cmd(const char *prompt)
1295 static char cmd[1000];
1297 printf("%s", prompt);
1298 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1303 strip_trailing_junk(cmd);
1307 static bool yes_no(const char *prompt)
1310 cmd = get_cmd(prompt);
1315 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1318 bool python_set_prog(JCR*, char const*) { return false; }
1321 * The code below to add indexes is needed only for MySQL, and
1322 * that to improve the performance.
1326 typedef struct s_idx_list {
1328 int count_key; /* how many times the index meets *key_name */
1329 int count_col; /* how many times meets the desired column name */
1332 static IDX_LIST idx_list[MAXIDX];
1335 * Called here with each table index to be added to the list
1337 static int check_idx_handler(void *ctx, int num_fields, char **row)
1340 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1341 * File | 0 | PRIMARY | 1 | FileId |...
1343 char *name, *key_name, *col_name;
1350 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1351 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1352 idx_list[i].count_key++;
1354 if (strcasecmp(col_name, name) == 0) {
1355 idx_list[i].count_col++;
1360 /* If the new Key_name, add it to the list */
1362 len = strlen(key_name) + 1;
1363 idx_list[i].key_name = (char *)malloc(len);
1364 bstrncpy(idx_list[i].key_name, key_name, len);
1365 idx_list[i].count_key = 1;
1366 if (strcasecmp(col_name, name) == 0) {
1367 idx_list[i].count_col = 1;
1369 idx_list[i].count_col = 0;
1376 * Return TRUE if "one column" index over *col_name exists
1378 static bool check_idx(const char *col_name)
1382 const char *query = "SHOW INDEX FROM File";
1384 if (db_get_type_index(db) != SQL_TYPE_MYSQL) {
1387 /* Continue for MySQL */
1388 memset(&idx_list, 0, sizeof(idx_list));
1389 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1390 printf("%s\n", db_strerror(db));
1392 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1394 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1396 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1397 /* "one column" index over *col_name found */
1403 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1406 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1412 * Create temporary one-column index
1414 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1415 const char *col_name)
1417 idx_tmp_name = NULL;
1418 printf(_("Create temporary index... This may take some time!\n"));
1419 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1421 printf("%s\n", buf);
1423 if (db_sql_query(db, buf, NULL, NULL)) {
1424 idx_tmp_name = idx_name;
1426 printf(_("Temporary index created.\n"));
1429 printf("%s\n", db_strerror(db));
1436 * Drop temporary index
1438 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1440 if (idx_tmp_name != NULL) {
1441 printf(_("Drop temporary index.\n"));
1442 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1444 printf("%s\n", buf);
1446 if (!db_sql_query(db, buf, NULL, NULL)) {
1447 printf("%s\n", db_strerror(db));
1451 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1455 idx_tmp_name = NULL;