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>] [<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 *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_parser();
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_key, catalog->db_ssl_cert, catalog->db_ssl_ca,
222 catalog->db_ssl_capath, catalog->db_ssl_cipher,
223 catalog->mult_db_connections,
224 catalog->disable_batch_insert);
226 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
227 db_get_engine_name(db), working_directory);
228 db_close_database(NULL, db);
230 free_pool_memory(catalog_details);
234 db_name = catalog->db_name;
235 user = catalog->db_user;
236 password = catalog->db_password;
237 dbhost = catalog->db_address;
238 if (dbhost && dbhost[0] == 0) {
241 dbport = catalog->db_port;
242 dbsslkey = catalog->db_ssl_key;
243 dbsslcert = catalog->db_ssl_cert;
244 dbsslca = catalog->db_ssl_ca;
245 dbsslcapath = catalog->db_ssl_capath;
246 dbsslcipher = catalog->db_ssl_cipher;
250 Pmsg0(0, _("Wrong number of arguments.\n"));
255 Pmsg0(0, _("Working directory not supplied.\n"));
259 /* This is needed by SQLite to find the db */
260 working_directory = argv[0];
277 dbport = strtol(argv[5], &endptr, 10);
278 if (*endptr != '\0') {
279 Pmsg0(0, _("Database port must be a numeric value.\n"));
281 } else if (errno == ERANGE) {
282 Pmsg0(0, _("Database port must be a int value.\n"));
290 } /* if (argc == 9) */
291 } /* if (argc >= 7) */
292 } /* if (argc >= 6) */
293 } /* if (argc >= 5) */
294 } /* if (argc >= 4) */
295 } /* if (argc >= 3) */
296 } /* if (argc >= 2) */
300 db = db_init_database(NULL, NULL, db_name, user, password, dbhost,
301 dbport, NULL, dbsslkey, dbsslcert, dbsslca, dbsslcapath, dbsslcipher, false, false);
302 if (!db || !db_open_database(NULL, db)) {
303 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
307 /* Drop temporary index idx_tmp_name if it already exists */
308 drop_tmp_idx("idxPIchk", "File");
312 repair_bad_filenames();
313 eliminate_duplicate_filenames();
314 eliminate_duplicate_paths();
315 eliminate_orphaned_jobmedia_records();
316 eliminate_orphaned_file_records();
317 eliminate_orphaned_path_records();
318 eliminate_orphaned_filename_records();
319 eliminate_orphaned_fileset_records();
320 eliminate_orphaned_client_records();
321 eliminate_orphaned_job_records();
322 eliminate_admin_records();
323 eliminate_restore_records();
325 do_interactive_mode();
328 /* Drop temporary index idx_tmp_name */
329 drop_tmp_idx("idxPIchk", "File");
331 if (db) db_close_database(NULL, db);
338 static void do_interactive_mode()
342 printf(_("Hello, this is the database check/correct program.\n"));
344 printf(_("Modify database is on."));
346 printf(_("Modify database is off."));
348 printf(_(" Verbose is on.\n"));
350 printf(_(" Verbose is off.\n"));
352 printf(_("Please select the function you want to perform.\n"));
357 " 1) Toggle modify database flag\n"
358 " 2) Toggle verbose flag\n"
359 " 3) Repair bad Filename records\n"
360 " 4) Repair bad Path records\n"
361 " 5) Eliminate duplicate Filename records\n"
362 " 6) Eliminate duplicate Path records\n"
363 " 7) Eliminate orphaned Jobmedia records\n"
364 " 8) Eliminate orphaned File records\n"
365 " 9) Eliminate orphaned Path records\n"
366 " 10) Eliminate orphaned Filename records\n"
367 " 11) Eliminate orphaned FileSet records\n"
368 " 12) Eliminate orphaned Client records\n"
369 " 13) Eliminate orphaned Job records\n"
370 " 14) Eliminate all Admin records\n"
371 " 15) Eliminate all Restore records\n"
376 " 1) Toggle modify database flag\n"
377 " 2) Toggle verbose flag\n"
378 " 3) Check for bad Filename records\n"
379 " 4) Check for bad Path records\n"
380 " 5) Check for duplicate Filename records\n"
381 " 6) Check for duplicate Path records\n"
382 " 7) Check for orphaned Jobmedia records\n"
383 " 8) Check for orphaned File records\n"
384 " 9) Check for orphaned Path records\n"
385 " 10) Check for orphaned Filename records\n"
386 " 11) Check for orphaned FileSet records\n"
387 " 12) Check for orphaned Client records\n"
388 " 13) Check for orphaned Job records\n"
389 " 14) Check for all Admin records\n"
390 " 15) Check for all Restore records\n"
395 cmd = get_cmd(_("Select function number: "));
397 int item = atoi(cmd);
402 printf(_("Database will be modified.\n"));
404 printf(_("Database will NOT be modified.\n"));
407 verbose = verbose?0:1;
409 printf(_(" Verbose is on.\n"));
411 printf(_(" Verbose is off.\n"));
414 repair_bad_filenames();
420 eliminate_duplicate_filenames();
423 eliminate_duplicate_paths();
426 eliminate_orphaned_jobmedia_records();
429 eliminate_orphaned_file_records();
432 eliminate_orphaned_path_records();
435 eliminate_orphaned_filename_records();
438 eliminate_orphaned_fileset_records();
441 eliminate_orphaned_client_records();
444 eliminate_orphaned_job_records();
447 eliminate_admin_records();
450 eliminate_restore_records();
453 repair_bad_filenames();
455 eliminate_duplicate_filenames();
456 eliminate_duplicate_paths();
457 eliminate_orphaned_jobmedia_records();
458 eliminate_orphaned_file_records();
459 eliminate_orphaned_path_records();
460 eliminate_orphaned_filename_records();
461 eliminate_orphaned_fileset_records();
462 eliminate_orphaned_client_records();
463 eliminate_orphaned_job_records();
464 eliminate_admin_records();
465 eliminate_restore_records();
475 static int print_name_handler(void *ctx, int num_fields, char **row)
478 printf("%s\n", row[0]);
483 static int get_name_handler(void *ctx, int num_fields, char **row)
485 POOLMEM *buf = (POOLMEM *)ctx;
487 pm_strcpy(&buf, row[0]);
492 static int print_job_handler(void *ctx, int num_fields, char **row)
494 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
495 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
499 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
501 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
502 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
506 static int print_file_handler(void *ctx, int num_fields, char **row)
508 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
509 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
513 static int print_fileset_handler(void *ctx, int num_fields, char **row)
515 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
516 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
520 static int print_client_handler(void *ctx, int num_fields, char **row)
522 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
523 NPRT(row[0]), NPRT(row[1]));
528 * Called here with each id to be added to the list
530 static int id_list_handler(void *ctx, int num_fields, char **row)
532 ID_LIST *lst = (ID_LIST *)ctx;
534 if (lst->num_ids == MAX_ID_LIST_LEN) {
537 if (lst->num_ids == lst->max_ids) {
538 if (lst->max_ids == 0) {
539 lst->max_ids = 10000;
540 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
542 lst->max_ids = (lst->max_ids * 3) / 2;
543 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
546 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
551 * Construct record id list
553 static int make_id_list(const char *query, ID_LIST *id_list)
555 id_list->num_ids = 0;
556 id_list->num_del = 0;
557 id_list->tot_ids = 0;
559 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
560 printf("%s", db_strerror(db));
567 * Delete all entries in the list
569 static int delete_id_list(const char *query, ID_LIST *id_list)
572 for (int i=0; i < id_list->num_ids; i++) {
573 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
575 printf(_("Deleting: %s\n"), buf);
577 db_sql_query(db, buf, NULL, NULL);
583 * Called here with each name to be added to the list
585 static int name_list_handler(void *ctx, int num_fields, char **row)
587 NAME_LIST *name = (NAME_LIST *)ctx;
589 if (name->num_ids == MAX_ID_LIST_LEN) {
592 if (name->num_ids == name->max_ids) {
593 if (name->max_ids == 0) {
594 name->max_ids = 10000;
595 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
597 name->max_ids = (name->max_ids * 3) / 2;
598 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
601 name->name[name->num_ids++] = bstrdup(row[0]);
606 * Construct name list
608 static int make_name_list(const char *query, NAME_LIST *name_list)
610 name_list->num_ids = 0;
611 name_list->num_del = 0;
612 name_list->tot_ids = 0;
614 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
615 printf("%s", db_strerror(db));
622 * Print names in the list
624 static void print_name_list(NAME_LIST *name_list)
626 for (int i=0; i < name_list->num_ids; i++) {
627 printf("%s\n", name_list->name[i]);
632 * Free names in the list
634 static void free_name_list(NAME_LIST *name_list)
636 for (int i=0; i < name_list->num_ids; i++) {
637 free(name_list->name[i]);
639 name_list->num_ids = 0;
642 static void eliminate_duplicate_filenames()
647 printf(_("Checking for duplicate Filename entries.\n"));
649 /* Make list of duplicated names */
650 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
651 "HAVING count(Name) > 1";
653 if (!make_name_list(query, &name_list)) {
656 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
657 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
658 print_name_list(&name_list);
664 /* Loop through list of duplicate names */
665 for (int i=0; i<name_list.num_ids; i++) {
666 /* Get all the Ids of each name */
667 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
668 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
672 if (!make_id_list(buf, &id_list)) {
676 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
678 /* Force all records to use the first id then delete the other ids */
679 for (int j=1; j<id_list.num_ids; j++) {
680 char ed1[50], ed2[50];
681 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
682 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
686 db_sql_query(db, buf, NULL, NULL);
687 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
692 db_sql_query(db, buf, NULL, NULL);
696 free_name_list(&name_list);
699 static void eliminate_duplicate_paths()
704 printf(_("Checking for duplicate Path entries.\n"));
706 /* Make list of duplicated names */
707 query = "SELECT Path, count(Path) as Count FROM Path "
708 "GROUP BY Path HAVING count(Path) > 1";
710 if (!make_name_list(query, &name_list)) {
713 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
714 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
715 print_name_list(&name_list);
721 /* Loop through list of duplicate names */
722 for (int i=0; i<name_list.num_ids; i++) {
723 /* Get all the Ids of each name */
724 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
725 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
729 if (!make_id_list(buf, &id_list)) {
733 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
735 /* Force all records to use the first id then delete the other ids */
736 for (int j=1; j<id_list.num_ids; j++) {
737 char ed1[50], ed2[50];
738 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
739 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
743 db_sql_query(db, buf, NULL, NULL);
744 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
748 db_sql_query(db, buf, NULL, NULL);
752 free_name_list(&name_list);
755 static void eliminate_orphaned_jobmedia_records()
757 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
758 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
759 "WHERE Job.JobId IS NULL LIMIT 300000";
761 printf(_("Checking for orphaned JobMedia entries.\n"));
762 if (!make_id_list(query, &id_list)) {
765 /* Loop doing 300000 at a time */
766 while (id_list.num_ids != 0) {
767 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
768 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
769 for (int i=0; i < id_list.num_ids; i++) {
771 bsnprintf(buf, sizeof(buf),
772 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
773 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
774 edit_int64(id_list.Id[i], ed1));
775 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
776 printf("%s\n", db_strerror(db));
784 if (fix && id_list.num_ids > 0) {
785 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
786 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
788 break; /* get out if not updating db */
790 if (!make_id_list(query, &id_list)) {
796 static void eliminate_orphaned_file_records()
798 const char *query = "SELECT File.FileId,Job.JobId FROM File "
799 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
800 "WHERE Job.JobId IS NULL LIMIT 300000";
802 printf(_("Checking for orphaned File entries. This may take some time!\n"));
804 printf("%s\n", query);
806 if (!make_id_list(query, &id_list)) {
809 /* Loop doing 300000 at a time */
810 while (id_list.num_ids != 0) {
811 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
812 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
813 for (int i=0; i < id_list.num_ids; i++) {
815 bsnprintf(buf, sizeof(buf),
816 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
817 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
818 edit_int64(id_list.Id[i], ed1));
819 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
820 printf("%s\n", db_strerror(db));
827 if (fix && id_list.num_ids > 0) {
828 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
829 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
831 break; /* get out if not updating db */
833 if (!make_id_list(query, &id_list)) {
839 static void eliminate_orphaned_path_records()
843 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
844 db_int64_handler, &lctx);
846 if (lctx.count == 1) {
847 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
852 /* Check the existence of the required "one column" index */
853 if (!check_idx("PathId")) {
854 if (yes_no(_("Create temporary index? (yes/no): "))) {
855 /* create temporary index PathId */
856 create_tmp_idx("idxPIchk", "File", "PathId");
860 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
861 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
862 "WHERE File.PathId IS NULL LIMIT 300000";
864 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
866 printf("%s\n", query);
868 if (!make_id_list(query, &id_list)) {
871 /* Loop doing 300000 at a time */
872 while (id_list.num_ids != 0) {
873 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
874 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
875 for (int i=0; i < id_list.num_ids; i++) {
877 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
878 edit_int64(id_list.Id[i], ed1));
879 db_sql_query(db, buf, print_name_handler, NULL);
885 if (fix && id_list.num_ids > 0) {
886 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
887 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
889 break; /* get out if not updating db */
891 if (!make_id_list(query, &id_list)) {
895 /* Drop temporary index idx_tmp_name */
896 drop_tmp_idx("idxPIchk", "File");
899 static void eliminate_orphaned_filename_records()
902 /* Check the existence of the required "one column" index */
903 if (!check_idx("FilenameId") ) {
904 if (yes_no(_("Create temporary index? (yes/no): "))) {
905 /* Create temporary index FilenameId */
906 create_tmp_idx("idxFIchk", "File", "FilenameId");
910 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
911 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
912 "WHERE File.FilenameId IS NULL LIMIT 300000";
914 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
916 printf("%s\n", query);
918 if (!make_id_list(query, &id_list)) {
921 /* Loop doing 300000 at a time */
922 while (id_list.num_ids != 0) {
923 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
924 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
925 for (int i=0; i < id_list.num_ids; i++) {
927 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
928 edit_int64(id_list.Id[i], ed1));
929 db_sql_query(db, buf, print_name_handler, NULL);
935 if (fix && id_list.num_ids > 0) {
936 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
937 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
939 break; /* get out if not updating db */
941 if (!make_id_list(query, &id_list)) {
945 /* Drop temporary index idx_tmp_name */
946 drop_tmp_idx("idxFIchk", "File");
950 static void eliminate_orphaned_fileset_records()
954 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
955 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
956 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
957 "WHERE Job.FileSetId IS NULL";
959 printf("%s\n", query);
961 if (!make_id_list(query, &id_list)) {
964 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
965 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
966 for (int i=0; i < id_list.num_ids; i++) {
968 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
969 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
970 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
971 printf("%s\n", db_strerror(db));
978 if (fix && id_list.num_ids > 0) {
979 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
980 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
984 static void eliminate_orphaned_client_records()
988 printf(_("Checking for orphaned Client entries.\n"));
990 * Wiffle through Client for every Client
991 * joining with the Job table including every Client even if
992 * there is not a match in Job (left outer join), then
993 * filter out only those where no Job points to a Client
994 * i.e. Job.Client is NULL
996 query = "SELECT Client.ClientId,Client.Name FROM Client "
997 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
998 "WHERE Job.ClientId IS NULL";
1000 printf("%s\n", query);
1002 if (!make_id_list(query, &id_list)) {
1005 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1006 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1007 for (int i=0; i < id_list.num_ids; i++) {
1009 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1010 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1011 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1012 printf("%s\n", db_strerror(db));
1019 if (fix && id_list.num_ids > 0) {
1020 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1021 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1025 static void eliminate_orphaned_job_records()
1029 printf(_("Checking for orphaned Job entries.\n"));
1031 * Wiffle through Job for every Job
1032 * joining with the Client table including every Job even if
1033 * there is not a match in Client (left outer join), then
1034 * filter out only those where no Client exists
1035 * i.e. Client.Name is NULL
1037 query = "SELECT Job.JobId,Job.Name FROM Job "
1038 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1039 "WHERE Client.Name IS NULL";
1041 printf("%s\n", query);
1043 if (!make_id_list(query, &id_list)) {
1046 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1047 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1048 for (int i=0; i < id_list.num_ids; i++) {
1050 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1051 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1052 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1053 printf("%s\n", db_strerror(db));
1060 if (fix && id_list.num_ids > 0) {
1061 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1062 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1063 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1064 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1065 printf(_("Deleting Log records of orphaned Job records.\n"));
1066 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1070 static void eliminate_admin_records()
1074 printf(_("Checking for Admin Job entries.\n"));
1075 query = "SELECT Job.JobId FROM Job "
1076 "WHERE Job.Type='D'";
1078 printf("%s\n", query);
1080 if (!make_id_list(query, &id_list)) {
1083 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1084 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1085 for (int i=0; i < id_list.num_ids; i++) {
1087 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1088 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1089 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1090 printf("%s\n", db_strerror(db));
1097 if (fix && id_list.num_ids > 0) {
1098 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1099 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1103 static void eliminate_restore_records()
1107 printf(_("Checking for Restore Job entries.\n"));
1108 query = "SELECT Job.JobId FROM Job "
1109 "WHERE Job.Type='R'";
1111 printf("%s\n", query);
1113 if (!make_id_list(query, &id_list)) {
1116 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1117 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1118 for (int i=0; i < id_list.num_ids; i++) {
1120 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1121 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1122 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1123 printf("%s\n", db_strerror(db));
1130 if (fix && id_list.num_ids > 0) {
1131 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1132 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1136 static void repair_bad_filenames()
1141 printf(_("Checking for Filenames with a trailing slash\n"));
1142 query = "SELECT FilenameId,Name from Filename "
1143 "WHERE Name LIKE '%/'";
1145 printf("%s\n", query);
1147 if (!make_id_list(query, &id_list)) {
1150 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1151 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1152 for (i=0; i < id_list.num_ids; i++) {
1154 bsnprintf(buf, sizeof(buf),
1155 "SELECT Name FROM Filename WHERE FilenameId=%s",
1156 edit_int64(id_list.Id[i], ed1));
1157 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1158 printf("%s\n", db_strerror(db));
1165 if (fix && id_list.num_ids > 0) {
1166 POOLMEM *name = get_pool_memory(PM_FNAME);
1167 char esc_name[5000];
1168 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1169 for (i=0; i < id_list.num_ids; i++) {
1172 bsnprintf(buf, sizeof(buf),
1173 "SELECT Name FROM Filename WHERE FilenameId=%s",
1174 edit_int64(id_list.Id[i], ed1));
1175 if (!db_sql_query(db, buf, get_name_handler, name)) {
1176 printf("%s\n", db_strerror(db));
1178 /* Strip trailing slash(es) */
1179 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1187 db_escape_string(NULL, db, esc_name, name, len);
1189 bsnprintf(buf, sizeof(buf),
1190 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1191 esc_name, edit_int64(id_list.Id[i], ed1));
1193 printf("%s\n", buf);
1195 db_sql_query(db, buf, NULL, NULL);
1197 free_pool_memory(name);
1201 static void repair_bad_paths()
1206 printf(_("Checking for Paths without a trailing slash\n"));
1207 query = "SELECT PathId,Path from Path "
1208 "WHERE Path NOT LIKE '%/'";
1210 printf("%s\n", query);
1212 if (!make_id_list(query, &id_list)) {
1215 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1216 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1217 for (i=0; i < id_list.num_ids; i++) {
1219 bsnprintf(buf, sizeof(buf),
1220 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1221 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1222 printf("%s\n", db_strerror(db));
1229 if (fix && id_list.num_ids > 0) {
1230 POOLMEM *name = get_pool_memory(PM_FNAME);
1231 char esc_name[5000];
1232 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1233 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, get_name_handler, name)) {
1239 printf("%s\n", db_strerror(db));
1241 /* Strip trailing blanks */
1242 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1245 /* Add trailing slash */
1246 len = pm_strcat(&name, "/");
1247 db_escape_string(NULL, db, esc_name, name, len);
1248 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1249 esc_name, edit_int64(id_list.Id[i], ed1));
1251 printf("%s\n", buf);
1253 db_sql_query(db, buf, NULL, NULL);
1255 free_pool_memory(name);
1260 * Gen next input command from the terminal
1262 static char *get_cmd(const char *prompt)
1264 static char cmd[1000];
1266 printf("%s", prompt);
1267 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1272 strip_trailing_junk(cmd);
1276 static bool yes_no(const char *prompt)
1279 cmd = get_cmd(prompt);
1284 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1287 bool python_set_prog(JCR*, char const*) { return false; }
1290 * The code below to add indexes is needed only for MySQL, and
1291 * that to improve the performance.
1295 typedef struct s_idx_list {
1297 int count_key; /* how many times the index meets *key_name */
1298 int count_col; /* how many times meets the desired column name */
1301 static IDX_LIST idx_list[MAXIDX];
1304 * Called here with each table index to be added to the list
1306 static int check_idx_handler(void *ctx, int num_fields, char **row)
1309 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1310 * File | 0 | PRIMARY | 1 | FileId |...
1312 char *name, *key_name, *col_name;
1319 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1320 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1321 idx_list[i].count_key++;
1323 if (strcasecmp(col_name, name) == 0) {
1324 idx_list[i].count_col++;
1329 /* If the new Key_name, add it to the list */
1331 len = strlen(key_name) + 1;
1332 idx_list[i].key_name = (char *)malloc(len);
1333 bstrncpy(idx_list[i].key_name, key_name, len);
1334 idx_list[i].count_key = 1;
1335 if (strcasecmp(col_name, name) == 0) {
1336 idx_list[i].count_col = 1;
1338 idx_list[i].count_col = 0;
1345 * Return TRUE if "one column" index over *col_name exists
1347 static bool check_idx(const char *col_name)
1351 const char *query = "SHOW INDEX FROM File";
1353 if (db_get_type_index(db) != SQL_TYPE_MYSQL) {
1356 /* Continue for MySQL */
1357 memset(&idx_list, 0, sizeof(idx_list));
1358 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1359 printf("%s\n", db_strerror(db));
1361 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1363 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1365 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1366 /* "one column" index over *col_name found */
1372 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1375 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1381 * Create temporary one-column index
1383 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1384 const char *col_name)
1386 idx_tmp_name = NULL;
1387 printf(_("Create temporary index... This may take some time!\n"));
1388 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1390 printf("%s\n", buf);
1392 if (db_sql_query(db, buf, NULL, NULL)) {
1393 idx_tmp_name = idx_name;
1395 printf(_("Temporary index created.\n"));
1398 printf("%s\n", db_strerror(db));
1405 * Drop temporary index
1407 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1409 if (idx_tmp_name != NULL) {
1410 printf(_("Drop temporary index.\n"));
1411 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1413 printf("%s\n", buf);
1415 if (!db_sql_query(db, buf, NULL, NULL)) {
1416 printf("%s\n", db_strerror(db));
1420 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1424 idx_tmp_name = NULL;