2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 Kern Sibbald
5 Copyright (C) 2002-2014 Free Software Foundation Europe e.V.
7 The original author of Bacula is Kern Sibbald, with contributions
8 from many others, a complete list can be found in the file AUTHORS.
10 You may use this file and others of this release according to the
11 license defined in the LICENSE file, which includes the Affero General
12 Public License, v3.0 ("AGPLv3") and some additional permissions and
13 terms pursuant to its AGPLv3 Section 7.
15 This notice must be preserved when any source code is
16 conveyed and/or propagated.
18 Bacula(R) is a registered trademark of Kern Sibbald.
22 * Program to check a Bacula database for consistency and to
25 * Kern E. Sibbald, August 2002
30 #include "cats/cats.h"
31 #include "lib/runscript.h"
32 #include "dird/dird_conf.h"
34 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
36 typedef struct s_id_ctx {
37 int64_t *Id; /* ids to be modified */
38 int num_ids; /* ids stored */
39 int max_ids; /* size of array */
40 int num_del; /* number deleted */
41 int tot_ids; /* total to process */
44 typedef struct s_name_ctx {
45 char **name; /* list of names */
46 int num_ids; /* ids stored */
47 int max_ids; /* size of array */
48 int num_del; /* number deleted */
49 int tot_ids; /* total to process */
55 static bool fix = false;
56 static bool batch = false;
58 static ID_LIST id_list;
59 static NAME_LIST name_list;
60 static char buf[20000];
61 static bool quit = false;
62 static CONFIG *config;
63 static const char *idx_tmp_name;
65 #define MAX_ID_LIST_LEN 10000000
68 * Forward referenced functions
70 static void print_catalog_details(CAT *catalog, const char *working_dir);
71 static int make_id_list(const char *query, ID_LIST *id_list);
72 static int delete_id_list(const char *query, ID_LIST *id_list);
73 static int make_name_list(const char *query, NAME_LIST *name_list);
74 static void print_name_list(NAME_LIST *name_list);
75 static void free_name_list(NAME_LIST *name_list);
76 static char *get_cmd(const char *prompt);
77 static void eliminate_duplicate_filenames();
78 static void eliminate_duplicate_paths();
79 static void eliminate_orphaned_jobmedia_records();
80 static void eliminate_orphaned_file_records();
81 static void eliminate_orphaned_path_records();
82 static void eliminate_orphaned_filename_records();
83 static void eliminate_orphaned_fileset_records();
84 static void eliminate_orphaned_client_records();
85 static void eliminate_orphaned_job_records();
86 static void eliminate_admin_records();
87 static void eliminate_restore_records();
88 static void repair_bad_paths();
89 static void repair_bad_filenames();
90 static void do_interactive_mode();
91 static bool yes_no(const char *prompt);
92 static bool check_idx(const char *col_name);
93 static bool create_tmp_idx(const char *idx_name, const char *table_name,
94 const char *col_name);
95 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
96 static int check_idx_handler(void *ctx, int num_fields, char **row);
102 "\n%sVersion: %s (%s)\n\n"
103 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
105 " -C catalog name in the director conf file\n"
106 " -c Director conf filename\n"
107 " -B print catalog configuration and exit\n"
108 " -d <nn> set debug level to <nn>\n"
109 " -dt print a timestamp in debug output\n"
110 " -f fix inconsistencies\n"
111 " -t test if client library is thread-safe\n"
113 " -? print this message\n"
114 "\n", 2002, "", VERSION, BDATE);
119 int main (int argc, char *argv[])
122 const char *user, *password, *db_name, *dbhost;
124 bool print_catalog=false;
125 char *configfile = NULL;
126 char *catalogname = NULL;
129 setlocale(LC_ALL, "");
130 bindtextdomain("bacula", LOCALEDIR);
131 textdomain("bacula");
134 my_name_is(argc, argv, "dbcheck");
135 init_msg(NULL, NULL); /* setup message handler */
137 memset(&id_list, 0, sizeof(id_list));
138 memset(&name_list, 0, sizeof(name_list));
140 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
143 print_catalog = true; /* get catalog information from config */
145 case 'b': /* batch */
148 case 'C': /* CatalogName */
149 catalogname = optarg;
151 case 'c': /* configfile */
154 case 'd': /* debug level */
155 if (*optarg == 't') {
156 dbg_timestamp = true;
158 debug_level = atoi(optarg);
159 if (debug_level <= 0) {
164 case 'f': /* fix inconsistencies */
184 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
186 config = new_config_parser();
187 parse_dir_config(config, configfile, M_ERROR_TERM);
189 foreach_res(catalog, R_CATALOG) {
190 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
193 } else if (!catalogname) { // stop on first if no catalogname is given
201 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
203 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
209 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
212 Pmsg0(0, _("Error no Director resource defined.\n"));
215 set_working_directory(director->working_directory);
218 * Print catalog information and exit (-B)
221 print_catalog_details(catalog, director->working_directory);
225 db_name = catalog->db_name;
226 user = catalog->db_user;
227 password = catalog->db_password;
228 dbhost = catalog->db_address;
229 if (dbhost && dbhost[0] == 0) {
232 dbport = catalog->db_port;
236 Pmsg0(0, _("Wrong number of arguments.\n"));
241 Pmsg0(0, _("Working directory not supplied.\n"));
246 * This is needed by SQLite to find the db
248 working_directory = argv[0];
257 } else if (argc == 3) {
260 } else if (argc == 4) {
264 } else if (argc == 5) {
269 } else if (argc == 6) {
275 dbport = strtol(argv[5], &endptr, 10);
276 if (*endptr != '\0') {
277 Pmsg0(0, _("Database port must be a numeric value.\n"));
279 } else if (errno == ERANGE) {
280 Pmsg0(0, _("Database port must be a int value.\n"));
289 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
290 if (!db || !db_open_database(NULL, db)) {
291 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
296 * Drop temporary index idx_tmp_name if it already exists
298 drop_tmp_idx("idxPIchk", "File");
302 repair_bad_filenames();
303 eliminate_duplicate_filenames();
304 eliminate_duplicate_paths();
305 eliminate_orphaned_jobmedia_records();
306 eliminate_orphaned_file_records();
307 eliminate_orphaned_path_records();
308 eliminate_orphaned_filename_records();
309 eliminate_orphaned_fileset_records();
310 eliminate_orphaned_client_records();
311 eliminate_orphaned_job_records();
312 eliminate_admin_records();
313 eliminate_restore_records();
315 do_interactive_mode();
319 * Drop temporary index idx_tmp_name
321 drop_tmp_idx("idxPIchk", "File");
323 if (db) db_close_database(NULL, db);
330 static void print_catalog_details(CAT *catalog, const char *working_dir)
332 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
335 * Instantiate a BDB class and see what db_type gets assigned to it.
337 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
338 catalog->db_password, catalog->db_address,
339 catalog->db_port, catalog->db_socket,
340 catalog->mult_db_connections,
341 catalog->disable_batch_insert);
343 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
344 db_get_engine_name(db), working_directory);
345 db_close_database(NULL, db);
347 free_pool_memory(catalog_details);
350 static void do_interactive_mode()
354 printf(_("Hello, this is the database check/correct program.\n"));
356 printf(_("Modify database is on."));
358 printf(_("Modify database is off."));
360 printf(_(" Verbose is on.\n"));
362 printf(_(" Verbose is off.\n"));
364 printf(_("Please select the function you want to perform.\n"));
369 " 1) Toggle modify database flag\n"
370 " 2) Toggle verbose flag\n"
371 " 3) Repair bad Filename records\n"
372 " 4) Repair bad Path records\n"
373 " 5) Eliminate duplicate Filename records\n"
374 " 6) Eliminate duplicate Path records\n"
375 " 7) Eliminate orphaned Jobmedia records\n"
376 " 8) Eliminate orphaned File records\n"
377 " 9) Eliminate orphaned Path records\n"
378 " 10) Eliminate orphaned Filename records\n"
379 " 11) Eliminate orphaned FileSet records\n"
380 " 12) Eliminate orphaned Client records\n"
381 " 13) Eliminate orphaned Job records\n"
382 " 14) Eliminate all Admin records\n"
383 " 15) Eliminate all Restore records\n"
388 " 1) Toggle modify database flag\n"
389 " 2) Toggle verbose flag\n"
390 " 3) Check for bad Filename records\n"
391 " 4) Check for bad Path records\n"
392 " 5) Check for duplicate Filename records\n"
393 " 6) Check for duplicate Path records\n"
394 " 7) Check for orphaned Jobmedia records\n"
395 " 8) Check for orphaned File records\n"
396 " 9) Check for orphaned Path records\n"
397 " 10) Check for orphaned Filename records\n"
398 " 11) Check for orphaned FileSet records\n"
399 " 12) Check for orphaned Client records\n"
400 " 13) Check for orphaned Job records\n"
401 " 14) Check for all Admin records\n"
402 " 15) Check for all Restore records\n"
407 cmd = get_cmd(_("Select function number: "));
409 int item = atoi(cmd);
414 printf(_("Database will be modified.\n"));
416 printf(_("Database will NOT be modified.\n"));
419 verbose = verbose?0:1;
421 printf(_(" Verbose is on.\n"));
423 printf(_(" Verbose is off.\n"));
426 repair_bad_filenames();
432 eliminate_duplicate_filenames();
435 eliminate_duplicate_paths();
438 eliminate_orphaned_jobmedia_records();
441 eliminate_orphaned_file_records();
444 eliminate_orphaned_path_records();
447 eliminate_orphaned_filename_records();
450 eliminate_orphaned_fileset_records();
453 eliminate_orphaned_client_records();
456 eliminate_orphaned_job_records();
459 eliminate_admin_records();
462 eliminate_restore_records();
465 repair_bad_filenames();
467 eliminate_duplicate_filenames();
468 eliminate_duplicate_paths();
469 eliminate_orphaned_jobmedia_records();
470 eliminate_orphaned_file_records();
471 eliminate_orphaned_path_records();
472 eliminate_orphaned_filename_records();
473 eliminate_orphaned_fileset_records();
474 eliminate_orphaned_client_records();
475 eliminate_orphaned_job_records();
476 eliminate_admin_records();
477 eliminate_restore_records();
487 static int print_name_handler(void *ctx, int num_fields, char **row)
490 printf("%s\n", row[0]);
495 static int get_name_handler(void *ctx, int num_fields, char **row)
497 POOLMEM *name = (POOLMEM *)ctx;
500 pm_strcpy(&name, row[0]);
505 static int print_job_handler(void *ctx, int num_fields, char **row)
507 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
508 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
512 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
514 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
515 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
519 static int print_file_handler(void *ctx, int num_fields, char **row)
521 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
522 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
526 static int print_fileset_handler(void *ctx, int num_fields, char **row)
528 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
529 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
533 static int print_client_handler(void *ctx, int num_fields, char **row)
535 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
536 NPRT(row[0]), NPRT(row[1]));
541 * Called here with each id to be added to the list
543 static int id_list_handler(void *ctx, int num_fields, char **row)
545 ID_LIST *lst = (ID_LIST *)ctx;
547 if (lst->num_ids == MAX_ID_LIST_LEN) {
550 if (lst->num_ids == lst->max_ids) {
551 if (lst->max_ids == 0) {
552 lst->max_ids = 10000;
553 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
555 lst->max_ids = (lst->max_ids * 3) / 2;
556 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
559 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
564 * Construct record id list
566 static int make_id_list(const char *query, ID_LIST *id_list)
568 id_list->num_ids = 0;
569 id_list->num_del = 0;
570 id_list->tot_ids = 0;
572 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
573 printf("%s", db_strerror(db));
580 * Delete all entries in the list
582 static int delete_id_list(const char *query, ID_LIST *id_list)
585 for (int i=0; i < id_list->num_ids; i++) {
586 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
588 printf(_("Deleting: %s\n"), buf);
590 db_sql_query(db, buf, NULL, NULL);
596 * Called here with each name to be added to the list
598 static int name_list_handler(void *ctx, int num_fields, char **row)
600 NAME_LIST *name = (NAME_LIST *)ctx;
602 if (name->num_ids == MAX_ID_LIST_LEN) {
605 if (name->num_ids == name->max_ids) {
606 if (name->max_ids == 0) {
607 name->max_ids = 10000;
608 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
610 name->max_ids = (name->max_ids * 3) / 2;
611 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
614 name->name[name->num_ids++] = bstrdup(row[0]);
619 * Construct name list
621 static int make_name_list(const char *query, NAME_LIST *name_list)
623 name_list->num_ids = 0;
624 name_list->num_del = 0;
625 name_list->tot_ids = 0;
627 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
628 printf("%s", db_strerror(db));
635 * Print names in the list
637 static void print_name_list(NAME_LIST *name_list)
639 for (int i=0; i < name_list->num_ids; i++) {
640 printf("%s\n", name_list->name[i]);
645 * Free names in the list
647 static void free_name_list(NAME_LIST *name_list)
649 for (int i=0; i < name_list->num_ids; i++) {
650 free(name_list->name[i]);
652 name_list->num_ids = 0;
655 static void eliminate_duplicate_filenames()
660 printf(_("Checking for duplicate Filename entries.\n"));
663 * Make list of duplicated names
665 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
666 "HAVING count(Name) > 1";
668 if (!make_name_list(query, &name_list)) {
671 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
672 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
673 print_name_list(&name_list);
680 * Loop through list of duplicate names
682 for (int i=0; i<name_list.num_ids; i++) {
684 * Get all the Ids of each name
686 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
687 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
691 if (!make_id_list(buf, &id_list)) {
695 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
698 * Force all records to use the first id then delete the other ids
700 for (int j=1; j<id_list.num_ids; j++) {
701 char ed1[50], ed2[50];
702 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
703 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
707 db_sql_query(db, buf, NULL, NULL);
708 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
713 db_sql_query(db, buf, NULL, NULL);
717 free_name_list(&name_list);
720 static void eliminate_duplicate_paths()
725 printf(_("Checking for duplicate Path entries.\n"));
728 * Make list of duplicated names
730 query = "SELECT Path, count(Path) as Count FROM Path "
731 "GROUP BY Path HAVING count(Path) > 1";
733 if (!make_name_list(query, &name_list)) {
736 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
737 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
738 print_name_list(&name_list);
745 * Loop through list of duplicate names
747 for (int i=0; i<name_list.num_ids; i++) {
749 * Get all the Ids of each name
751 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
752 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
756 if (!make_id_list(buf, &id_list)) {
760 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
763 * Force all records to use the first id then delete the other ids
765 for (int j=1; j<id_list.num_ids; j++) {
766 char ed1[50], ed2[50];
767 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
768 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
772 db_sql_query(db, buf, NULL, NULL);
773 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
777 db_sql_query(db, buf, NULL, NULL);
781 free_name_list(&name_list);
784 static void eliminate_orphaned_jobmedia_records()
786 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
787 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
788 "WHERE Job.JobId IS NULL LIMIT 300000";
790 printf(_("Checking for orphaned JobMedia entries.\n"));
791 if (!make_id_list(query, &id_list)) {
795 * 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)) {
841 * Loop doing 300000 at a time
843 while (id_list.num_ids != 0) {
844 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
845 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
846 for (int i=0; i < id_list.num_ids; i++) {
848 bsnprintf(buf, sizeof(buf),
849 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
850 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
851 edit_int64(id_list.Id[i], ed1));
852 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
853 printf("%s\n", db_strerror(db));
860 if (fix && id_list.num_ids > 0) {
861 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
862 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
864 break; /* get out if not updating db */
866 if (!make_id_list(query, &id_list)) {
872 static void eliminate_orphaned_path_records()
876 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
877 db_int64_handler, &lctx);
879 if (lctx.count == 1) {
880 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
886 * Check the existence of the required "one column" index
888 if (!check_idx("PathId")) {
889 if (yes_no(_("Create temporary index? (yes/no): "))) {
891 * create temporary index PathId
893 create_tmp_idx("idxPIchk", "File", "PathId");
897 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
898 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
899 "WHERE File.PathId IS NULL LIMIT 300000";
901 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
903 printf("%s\n", query);
905 if (!make_id_list(query, &id_list)) {
909 * Loop doing 300000 at a time
911 while (id_list.num_ids != 0) {
912 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
913 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
914 for (int i=0; i < id_list.num_ids; i++) {
916 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
917 edit_int64(id_list.Id[i], ed1));
918 db_sql_query(db, buf, print_name_handler, NULL);
924 if (fix && id_list.num_ids > 0) {
925 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
926 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
928 break; /* get out if not updating db */
930 if (!make_id_list(query, &id_list)) {
935 * Drop temporary index idx_tmp_name
937 drop_tmp_idx("idxPIchk", "File");
940 static void eliminate_orphaned_filename_records()
944 * Check the existence of the required "one column" index
946 if (!check_idx("FilenameId") ) {
947 if (yes_no(_("Create temporary index? (yes/no): "))) {
949 * Create temporary index FilenameId
951 create_tmp_idx("idxFIchk", "File", "FilenameId");
955 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
956 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
957 "WHERE File.FilenameId IS NULL LIMIT 300000";
959 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
961 printf("%s\n", query);
963 if (!make_id_list(query, &id_list)) {
967 * Loop doing 300000 at a time
969 while (id_list.num_ids != 0) {
970 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
971 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
972 for (int i=0; i < id_list.num_ids; i++) {
974 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
975 edit_int64(id_list.Id[i], ed1));
976 db_sql_query(db, buf, print_name_handler, NULL);
982 if (fix && id_list.num_ids > 0) {
983 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
984 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
986 break; /* get out if not updating db */
988 if (!make_id_list(query, &id_list)) {
993 * Drop temporary index idx_tmp_name
995 drop_tmp_idx("idxFIchk", "File");
999 static void eliminate_orphaned_fileset_records()
1003 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1004 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1005 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1006 "WHERE Job.FileSetId IS NULL";
1008 printf("%s\n", query);
1010 if (!make_id_list(query, &id_list)) {
1013 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1014 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1015 for (int i=0; i < id_list.num_ids; i++) {
1017 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1018 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1019 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1020 printf("%s\n", db_strerror(db));
1027 if (fix && id_list.num_ids > 0) {
1028 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1029 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1033 static void eliminate_orphaned_client_records()
1037 printf(_("Checking for orphaned Client entries.\n"));
1040 * Wiffle through Client for every Client
1041 * joining with the Job table including every Client even if
1042 * there is not a match in Job (left outer join), then
1043 * filter out only those where no Job points to a Client
1044 * i.e. Job.Client is NULL
1046 query = "SELECT Client.ClientId,Client.Name FROM Client "
1047 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1048 "WHERE Job.ClientId IS NULL";
1050 printf("%s\n", query);
1052 if (!make_id_list(query, &id_list)) {
1055 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1056 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1057 for (int i=0; i < id_list.num_ids; i++) {
1059 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1060 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1061 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1062 printf("%s\n", db_strerror(db));
1069 if (fix && id_list.num_ids > 0) {
1070 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1071 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1075 static void eliminate_orphaned_job_records()
1079 printf(_("Checking for orphaned Job entries.\n"));
1082 * Wiffle through Job for every Job
1083 * joining with the Client table including every Job even if
1084 * there is not a match in Client (left outer join), then
1085 * filter out only those where no Client exists
1086 * i.e. Client.Name is NULL
1088 query = "SELECT Job.JobId,Job.Name FROM Job "
1089 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1090 "WHERE Client.Name IS NULL";
1092 printf("%s\n", query);
1094 if (!make_id_list(query, &id_list)) {
1097 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1098 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1099 for (int i=0; i < id_list.num_ids; i++) {
1101 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1102 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1103 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1104 printf("%s\n", db_strerror(db));
1111 if (fix && id_list.num_ids > 0) {
1112 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1113 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1114 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1115 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1116 printf(_("Deleting Log records of orphaned Job records.\n"));
1117 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1121 static void eliminate_admin_records()
1125 printf(_("Checking for Admin Job entries.\n"));
1126 query = "SELECT Job.JobId FROM Job "
1127 "WHERE Job.Type='D'";
1129 printf("%s\n", query);
1131 if (!make_id_list(query, &id_list)) {
1134 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1135 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1136 for (int i=0; i < id_list.num_ids; i++) {
1138 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1139 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1140 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1141 printf("%s\n", db_strerror(db));
1148 if (fix && id_list.num_ids > 0) {
1149 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1150 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1154 static void eliminate_restore_records()
1158 printf(_("Checking for Restore Job entries.\n"));
1159 query = "SELECT Job.JobId FROM Job "
1160 "WHERE Job.Type='R'";
1162 printf("%s\n", query);
1164 if (!make_id_list(query, &id_list)) {
1167 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1168 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1169 for (int i=0; i < id_list.num_ids; i++) {
1171 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1172 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1173 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1174 printf("%s\n", db_strerror(db));
1181 if (fix && id_list.num_ids > 0) {
1182 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1183 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1187 static void repair_bad_filenames()
1192 printf(_("Checking for Filenames with a trailing slash\n"));
1193 query = "SELECT FilenameId,Name from Filename "
1194 "WHERE Name LIKE '%/'";
1196 printf("%s\n", query);
1198 if (!make_id_list(query, &id_list)) {
1201 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1202 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1203 for (i=0; i < id_list.num_ids; i++) {
1205 bsnprintf(buf, sizeof(buf),
1206 "SELECT Name FROM Filename WHERE FilenameId=%s",
1207 edit_int64(id_list.Id[i], ed1));
1208 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1209 printf("%s\n", db_strerror(db));
1216 if (fix && id_list.num_ids > 0) {
1217 POOLMEM *name = get_pool_memory(PM_FNAME);
1218 char esc_name[5000];
1219 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1220 for (i=0; i < id_list.num_ids; i++) {
1223 bsnprintf(buf, sizeof(buf),
1224 "SELECT Name FROM Filename WHERE FilenameId=%s",
1225 edit_int64(id_list.Id[i], ed1));
1226 if (!db_sql_query(db, buf, get_name_handler, name)) {
1227 printf("%s\n", db_strerror(db));
1230 * Strip trailing slash(es)
1232 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1240 db_escape_string(NULL, db, esc_name, name, len);
1242 bsnprintf(buf, sizeof(buf),
1243 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1244 esc_name, edit_int64(id_list.Id[i], ed1));
1246 printf("%s\n", buf);
1248 db_sql_query(db, buf, NULL, NULL);
1250 free_pool_memory(name);
1254 static void repair_bad_paths()
1259 printf(_("Checking for Paths without a trailing slash\n"));
1260 query = "SELECT PathId,Path from Path "
1261 "WHERE Path NOT LIKE '%/'";
1263 printf("%s\n", query);
1265 if (!make_id_list(query, &id_list)) {
1268 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1269 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1270 for (i=0; i < id_list.num_ids; i++) {
1272 bsnprintf(buf, sizeof(buf),
1273 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1274 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1275 printf("%s\n", db_strerror(db));
1282 if (fix && id_list.num_ids > 0) {
1283 POOLMEM *name = get_pool_memory(PM_FNAME);
1284 char esc_name[5000];
1285 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1286 for (i=0; i < id_list.num_ids; i++) {
1289 bsnprintf(buf, sizeof(buf),
1290 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1291 if (!db_sql_query(db, buf, get_name_handler, name)) {
1292 printf("%s\n", db_strerror(db));
1295 * Strip trailing blanks
1297 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1301 * Add trailing slash
1303 len = pm_strcat(&name, "/");
1304 db_escape_string(NULL, db, esc_name, name, len);
1305 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1306 esc_name, edit_int64(id_list.Id[i], ed1));
1308 printf("%s\n", buf);
1310 db_sql_query(db, buf, NULL, NULL);
1312 free_pool_memory(name);
1317 * Gen next input command from the terminal
1319 static char *get_cmd(const char *prompt)
1321 static char cmd[1000];
1323 printf("%s", prompt);
1324 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1329 strip_trailing_junk(cmd);
1333 static bool yes_no(const char *prompt)
1336 cmd = get_cmd(prompt);
1341 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1344 bool python_set_prog(JCR*, char const*) { return false; }
1347 * The code below to add indexes is needed only for MySQL, and
1348 * that to improve the performance.
1352 typedef struct s_idx_list {
1354 int count_key; /* how many times the index meets *key_name */
1355 int count_col; /* how many times meets the desired column name */
1358 static IDX_LIST idx_list[MAXIDX];
1361 * Called here with each table index to be added to the list
1363 static int check_idx_handler(void *ctx, int num_fields, char **row)
1366 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1367 * File | 0 | PRIMARY | 1 | FileId |...
1369 char *name, *key_name, *col_name;
1376 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1377 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1378 idx_list[i].count_key++;
1380 if (strcasecmp(col_name, name) == 0) {
1381 idx_list[i].count_col++;
1387 * If the new Key_name, add it to the list
1390 len = strlen(key_name) + 1;
1391 idx_list[i].key_name = (char *)malloc(len);
1392 bstrncpy(idx_list[i].key_name, key_name, len);
1393 idx_list[i].count_key = 1;
1394 if (strcasecmp(col_name, name) == 0) {
1395 idx_list[i].count_col = 1;
1397 idx_list[i].count_col = 0;
1404 * Return TRUE if "one column" index over *col_name exists
1406 static bool check_idx(const char *col_name)
1410 const char *query = "SHOW INDEX FROM File";
1412 switch (db_get_type_index(db)) {
1413 case SQL_TYPE_MYSQL:
1414 memset(&idx_list, 0, sizeof(idx_list));
1415 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1416 printf("%s\n", db_strerror(db));
1418 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1420 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1422 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1424 * "one column" index over *col_name found
1431 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1434 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1443 * Create temporary one-column index
1445 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1446 const char *col_name)
1448 idx_tmp_name = NULL;
1449 printf(_("Create temporary index... This may take some time!\n"));
1450 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1452 printf("%s\n", buf);
1454 if (db_sql_query(db, buf, NULL, NULL)) {
1455 idx_tmp_name = idx_name;
1457 printf(_("Temporary index created.\n"));
1460 printf("%s\n", db_strerror(db));
1467 * Drop temporary index
1469 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1471 if (idx_tmp_name != NULL) {
1472 printf(_("Drop temporary index.\n"));
1473 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1475 printf("%s\n", buf);
1477 if (!db_sql_query(db, buf, NULL, NULL)) {
1478 printf("%s\n", db_strerror(db));
1482 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1486 idx_tmp_name = NULL;