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>] [<dbport>] [<dbsslkey>] [<dbsslcert>] [<dbsslca>]\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;
123 const char *dbsslkey = NULL, *dbsslcert = NULL, *dbsslca = NULL;
124 const char *dbsslcapath = NULL, *dbsslcipher = NULL;
126 bool print_catalog=false;
127 char *configfile = NULL;
128 char *catalogname = NULL;
131 setlocale(LC_ALL, "");
132 bindtextdomain("bacula", LOCALEDIR);
133 textdomain("bacula");
136 my_name_is(argc, argv, "dbcheck");
137 init_msg(NULL, NULL); /* setup message handler */
139 memset(&id_list, 0, sizeof(id_list));
140 memset(&name_list, 0, sizeof(name_list));
142 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
145 print_catalog = true; /* get catalog information from config */
147 case 'b': /* batch */
150 case 'C': /* CatalogName */
151 catalogname = optarg;
153 case 'c': /* configfile */
156 case 'd': /* debug level */
157 if (*optarg == 't') {
158 dbg_timestamp = true;
160 debug_level = atoi(optarg);
161 if (debug_level <= 0) {
166 case 'f': /* fix inconsistencies */
186 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
188 config = new_config_parser();
189 parse_dir_config(config, configfile, M_ERROR_TERM);
191 foreach_res(catalog, R_CATALOG) {
192 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
195 } else if (!catalogname) { // stop on first if no catalogname is given
203 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
205 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
211 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
214 Pmsg0(0, _("Error no Director resource defined.\n"));
217 set_working_directory(director->working_directory);
220 * Print catalog information and exit (-B)
223 print_catalog_details(catalog, director->working_directory);
227 db_name = catalog->db_name;
228 user = catalog->db_user;
229 password = catalog->db_password;
230 dbhost = catalog->db_address;
231 if (dbhost && dbhost[0] == 0) {
234 dbport = catalog->db_port;
235 dbsslkey = catalog->db_ssl_key;
236 dbsslcert = catalog->db_ssl_cert;
237 dbsslca = catalog->db_ssl_ca;
238 dbsslcapath = catalog->db_ssl_capath;
239 dbsslcipher = catalog->db_ssl_cipher;
243 Pmsg0(0, _("Wrong number of arguments.\n"));
248 Pmsg0(0, _("Working directory not supplied.\n"));
253 * This is needed by SQLite to find the db
255 working_directory = argv[0];
272 dbport = strtol(argv[5], &endptr, 10);
273 if (*endptr != '\0') {
274 Pmsg0(0, _("Database port must be a numeric value.\n"));
276 } else if (errno == ERANGE) {
277 Pmsg0(0, _("Database port must be a int value.\n"));
285 } /* if (argc == 9) */
286 } /* if (argc >= 7) */
287 } /* if (argc >= 6) */
288 } /* if (argc >= 5) */
289 } /* if (argc >= 4) */
290 } /* if (argc >= 3) */
291 } /* if (argc >= 2) */
297 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, dbsslkey, dbsslcert, dbsslca, dbsslcapath, dbsslcipher, false, false);
298 if (!db || !db_open_database(NULL, db)) {
299 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
304 * Drop temporary index idx_tmp_name if it already exists
306 drop_tmp_idx("idxPIchk", "File");
310 repair_bad_filenames();
311 eliminate_duplicate_filenames();
312 eliminate_duplicate_paths();
313 eliminate_orphaned_jobmedia_records();
314 eliminate_orphaned_file_records();
315 eliminate_orphaned_path_records();
316 eliminate_orphaned_filename_records();
317 eliminate_orphaned_fileset_records();
318 eliminate_orphaned_client_records();
319 eliminate_orphaned_job_records();
320 eliminate_admin_records();
321 eliminate_restore_records();
323 do_interactive_mode();
327 * Drop temporary index idx_tmp_name
329 drop_tmp_idx("idxPIchk", "File");
331 if (db) db_close_database(NULL, db);
338 static void print_catalog_details(CAT *catalog, const char *working_dir)
340 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
343 * Instantiate a BDB class and see what db_type gets assigned to it.
345 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
346 catalog->db_password, catalog->db_address,
347 catalog->db_port, catalog->db_socket,
348 catalog->db_ssl_key, catalog->db_ssl_cert, catalog->db_ssl_ca,
349 catalog->db_ssl_capath, catalog->db_ssl_cipher,
350 catalog->mult_db_connections,
351 catalog->disable_batch_insert);
353 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
354 db_get_engine_name(db), working_directory);
355 db_close_database(NULL, db);
357 free_pool_memory(catalog_details);
360 static void do_interactive_mode()
364 printf(_("Hello, this is the database check/correct program.\n"));
366 printf(_("Modify database is on."));
368 printf(_("Modify database is off."));
370 printf(_(" Verbose is on.\n"));
372 printf(_(" Verbose is off.\n"));
374 printf(_("Please select the function you want to perform.\n"));
379 " 1) Toggle modify database flag\n"
380 " 2) Toggle verbose flag\n"
381 " 3) Repair bad Filename records\n"
382 " 4) Repair bad Path records\n"
383 " 5) Eliminate duplicate Filename records\n"
384 " 6) Eliminate duplicate Path records\n"
385 " 7) Eliminate orphaned Jobmedia records\n"
386 " 8) Eliminate orphaned File records\n"
387 " 9) Eliminate orphaned Path records\n"
388 " 10) Eliminate orphaned Filename records\n"
389 " 11) Eliminate orphaned FileSet records\n"
390 " 12) Eliminate orphaned Client records\n"
391 " 13) Eliminate orphaned Job records\n"
392 " 14) Eliminate all Admin records\n"
393 " 15) Eliminate all Restore records\n"
398 " 1) Toggle modify database flag\n"
399 " 2) Toggle verbose flag\n"
400 " 3) Check for bad Filename records\n"
401 " 4) Check for bad Path records\n"
402 " 5) Check for duplicate Filename records\n"
403 " 6) Check for duplicate Path records\n"
404 " 7) Check for orphaned Jobmedia records\n"
405 " 8) Check for orphaned File records\n"
406 " 9) Check for orphaned Path records\n"
407 " 10) Check for orphaned Filename records\n"
408 " 11) Check for orphaned FileSet records\n"
409 " 12) Check for orphaned Client records\n"
410 " 13) Check for orphaned Job records\n"
411 " 14) Check for all Admin records\n"
412 " 15) Check for all Restore records\n"
417 cmd = get_cmd(_("Select function number: "));
419 int item = atoi(cmd);
424 printf(_("Database will be modified.\n"));
426 printf(_("Database will NOT be modified.\n"));
429 verbose = verbose?0:1;
431 printf(_(" Verbose is on.\n"));
433 printf(_(" Verbose is off.\n"));
436 repair_bad_filenames();
442 eliminate_duplicate_filenames();
445 eliminate_duplicate_paths();
448 eliminate_orphaned_jobmedia_records();
451 eliminate_orphaned_file_records();
454 eliminate_orphaned_path_records();
457 eliminate_orphaned_filename_records();
460 eliminate_orphaned_fileset_records();
463 eliminate_orphaned_client_records();
466 eliminate_orphaned_job_records();
469 eliminate_admin_records();
472 eliminate_restore_records();
475 repair_bad_filenames();
477 eliminate_duplicate_filenames();
478 eliminate_duplicate_paths();
479 eliminate_orphaned_jobmedia_records();
480 eliminate_orphaned_file_records();
481 eliminate_orphaned_path_records();
482 eliminate_orphaned_filename_records();
483 eliminate_orphaned_fileset_records();
484 eliminate_orphaned_client_records();
485 eliminate_orphaned_job_records();
486 eliminate_admin_records();
487 eliminate_restore_records();
497 static int print_name_handler(void *ctx, int num_fields, char **row)
500 printf("%s\n", row[0]);
505 static int get_name_handler(void *ctx, int num_fields, char **row)
507 POOLMEM *name = (POOLMEM *)ctx;
510 pm_strcpy(&name, row[0]);
515 static int print_job_handler(void *ctx, int num_fields, char **row)
517 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
518 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
522 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
524 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
525 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
529 static int print_file_handler(void *ctx, int num_fields, char **row)
531 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
532 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
536 static int print_fileset_handler(void *ctx, int num_fields, char **row)
538 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
539 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
543 static int print_client_handler(void *ctx, int num_fields, char **row)
545 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
546 NPRT(row[0]), NPRT(row[1]));
551 * Called here with each id to be added to the list
553 static int id_list_handler(void *ctx, int num_fields, char **row)
555 ID_LIST *lst = (ID_LIST *)ctx;
557 if (lst->num_ids == MAX_ID_LIST_LEN) {
560 if (lst->num_ids == lst->max_ids) {
561 if (lst->max_ids == 0) {
562 lst->max_ids = 10000;
563 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
565 lst->max_ids = (lst->max_ids * 3) / 2;
566 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
569 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
574 * Construct record id list
576 static int make_id_list(const char *query, ID_LIST *id_list)
578 id_list->num_ids = 0;
579 id_list->num_del = 0;
580 id_list->tot_ids = 0;
582 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
583 printf("%s", db_strerror(db));
590 * Delete all entries in the list
592 static int delete_id_list(const char *query, ID_LIST *id_list)
595 for (int i=0; i < id_list->num_ids; i++) {
596 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
598 printf(_("Deleting: %s\n"), buf);
600 db_sql_query(db, buf, NULL, NULL);
606 * Called here with each name to be added to the list
608 static int name_list_handler(void *ctx, int num_fields, char **row)
610 NAME_LIST *name = (NAME_LIST *)ctx;
612 if (name->num_ids == MAX_ID_LIST_LEN) {
615 if (name->num_ids == name->max_ids) {
616 if (name->max_ids == 0) {
617 name->max_ids = 10000;
618 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
620 name->max_ids = (name->max_ids * 3) / 2;
621 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
624 name->name[name->num_ids++] = bstrdup(row[0]);
629 * Construct name list
631 static int make_name_list(const char *query, NAME_LIST *name_list)
633 name_list->num_ids = 0;
634 name_list->num_del = 0;
635 name_list->tot_ids = 0;
637 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
638 printf("%s", db_strerror(db));
645 * Print names in the list
647 static void print_name_list(NAME_LIST *name_list)
649 for (int i=0; i < name_list->num_ids; i++) {
650 printf("%s\n", name_list->name[i]);
655 * Free names in the list
657 static void free_name_list(NAME_LIST *name_list)
659 for (int i=0; i < name_list->num_ids; i++) {
660 free(name_list->name[i]);
662 name_list->num_ids = 0;
665 static void eliminate_duplicate_filenames()
670 printf(_("Checking for duplicate Filename entries.\n"));
673 * Make list of duplicated names
675 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
676 "HAVING count(Name) > 1";
678 if (!make_name_list(query, &name_list)) {
681 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
682 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
683 print_name_list(&name_list);
690 * Loop through list of duplicate names
692 for (int i=0; i<name_list.num_ids; i++) {
694 * Get all the Ids of each name
696 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
697 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
701 if (!make_id_list(buf, &id_list)) {
705 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
708 * 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"));
738 * Make list of duplicated names
740 query = "SELECT Path, count(Path) as Count FROM Path "
741 "GROUP BY Path HAVING count(Path) > 1";
743 if (!make_name_list(query, &name_list)) {
746 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
747 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
748 print_name_list(&name_list);
755 * Loop through list of duplicate names
757 for (int i=0; i<name_list.num_ids; i++) {
759 * Get all the Ids of each name
761 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
762 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
766 if (!make_id_list(buf, &id_list)) {
770 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
773 * Force all records to use the first id then delete the other ids
775 for (int j=1; j<id_list.num_ids; j++) {
776 char ed1[50], ed2[50];
777 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
778 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
782 db_sql_query(db, buf, NULL, NULL);
783 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
787 db_sql_query(db, buf, NULL, NULL);
791 free_name_list(&name_list);
794 static void eliminate_orphaned_jobmedia_records()
796 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
797 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
798 "WHERE Job.JobId IS NULL LIMIT 300000";
800 printf(_("Checking for orphaned JobMedia entries.\n"));
801 if (!make_id_list(query, &id_list)) {
805 * Loop doing 300000 at a time
807 while (id_list.num_ids != 0) {
808 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
809 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
810 for (int i=0; i < id_list.num_ids; i++) {
812 bsnprintf(buf, sizeof(buf),
813 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
814 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
815 edit_int64(id_list.Id[i], ed1));
816 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
817 printf("%s\n", db_strerror(db));
825 if (fix && id_list.num_ids > 0) {
826 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
827 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
829 break; /* get out if not updating db */
831 if (!make_id_list(query, &id_list)) {
837 static void eliminate_orphaned_file_records()
839 const char *query = "SELECT File.FileId,Job.JobId FROM File "
840 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
841 "WHERE Job.JobId IS NULL LIMIT 300000";
843 printf(_("Checking for orphaned File entries. This may take some time!\n"));
845 printf("%s\n", query);
847 if (!make_id_list(query, &id_list)) {
851 * Loop doing 300000 at a time
853 while (id_list.num_ids != 0) {
854 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
855 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
856 for (int i=0; i < id_list.num_ids; i++) {
858 bsnprintf(buf, sizeof(buf),
859 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
860 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
861 edit_int64(id_list.Id[i], ed1));
862 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
863 printf("%s\n", db_strerror(db));
870 if (fix && id_list.num_ids > 0) {
871 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
872 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
874 break; /* get out if not updating db */
876 if (!make_id_list(query, &id_list)) {
882 static void eliminate_orphaned_path_records()
886 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
887 db_int64_handler, &lctx);
889 if (lctx.count == 1) {
890 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
896 * Check the existence of the required "one column" index
898 if (!check_idx("PathId")) {
899 if (yes_no(_("Create temporary index? (yes/no): "))) {
901 * create temporary index PathId
903 create_tmp_idx("idxPIchk", "File", "PathId");
907 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
908 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
909 "WHERE File.PathId IS NULL LIMIT 300000";
911 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
913 printf("%s\n", query);
915 if (!make_id_list(query, &id_list)) {
919 * Loop doing 300000 at a time
921 while (id_list.num_ids != 0) {
922 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
923 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
924 for (int i=0; i < id_list.num_ids; i++) {
926 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
927 edit_int64(id_list.Id[i], ed1));
928 db_sql_query(db, buf, print_name_handler, NULL);
934 if (fix && id_list.num_ids > 0) {
935 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
936 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
938 break; /* get out if not updating db */
940 if (!make_id_list(query, &id_list)) {
945 * Drop temporary index idx_tmp_name
947 drop_tmp_idx("idxPIchk", "File");
950 static void eliminate_orphaned_filename_records()
954 * Check the existence of the required "one column" index
956 if (!check_idx("FilenameId") ) {
957 if (yes_no(_("Create temporary index? (yes/no): "))) {
959 * Create temporary index FilenameId
961 create_tmp_idx("idxFIchk", "File", "FilenameId");
965 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
966 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
967 "WHERE File.FilenameId IS NULL LIMIT 300000";
969 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
971 printf("%s\n", query);
973 if (!make_id_list(query, &id_list)) {
977 * Loop doing 300000 at a time
979 while (id_list.num_ids != 0) {
980 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
981 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
982 for (int i=0; i < id_list.num_ids; i++) {
984 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
985 edit_int64(id_list.Id[i], ed1));
986 db_sql_query(db, buf, print_name_handler, NULL);
992 if (fix && id_list.num_ids > 0) {
993 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
994 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
996 break; /* get out if not updating db */
998 if (!make_id_list(query, &id_list)) {
1003 * Drop temporary index idx_tmp_name
1005 drop_tmp_idx("idxFIchk", "File");
1009 static void eliminate_orphaned_fileset_records()
1013 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1014 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1015 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1016 "WHERE Job.FileSetId IS NULL";
1018 printf("%s\n", query);
1020 if (!make_id_list(query, &id_list)) {
1023 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1024 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1025 for (int i=0; i < id_list.num_ids; i++) {
1027 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1028 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1029 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1030 printf("%s\n", db_strerror(db));
1037 if (fix && id_list.num_ids > 0) {
1038 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1039 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1043 static void eliminate_orphaned_client_records()
1047 printf(_("Checking for orphaned Client entries.\n"));
1050 * Wiffle through Client for every Client
1051 * joining with the Job table including every Client even if
1052 * there is not a match in Job (left outer join), then
1053 * filter out only those where no Job points to a Client
1054 * i.e. Job.Client is NULL
1056 query = "SELECT Client.ClientId,Client.Name FROM Client "
1057 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1058 "WHERE Job.ClientId IS NULL";
1060 printf("%s\n", query);
1062 if (!make_id_list(query, &id_list)) {
1065 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1066 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1067 for (int i=0; i < id_list.num_ids; i++) {
1069 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1070 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1071 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1072 printf("%s\n", db_strerror(db));
1079 if (fix && id_list.num_ids > 0) {
1080 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1081 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1085 static void eliminate_orphaned_job_records()
1089 printf(_("Checking for orphaned Job entries.\n"));
1092 * Wiffle through Job for every Job
1093 * joining with the Client table including every Job even if
1094 * there is not a match in Client (left outer join), then
1095 * filter out only those where no Client exists
1096 * i.e. Client.Name is NULL
1098 query = "SELECT Job.JobId,Job.Name FROM Job "
1099 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1100 "WHERE Client.Name IS NULL";
1102 printf("%s\n", query);
1104 if (!make_id_list(query, &id_list)) {
1107 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1108 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1109 for (int i=0; i < id_list.num_ids; i++) {
1111 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1112 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1113 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1114 printf("%s\n", db_strerror(db));
1121 if (fix && id_list.num_ids > 0) {
1122 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1123 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1124 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1125 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1126 printf(_("Deleting Log records of orphaned Job records.\n"));
1127 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1131 static void eliminate_admin_records()
1135 printf(_("Checking for Admin Job entries.\n"));
1136 query = "SELECT Job.JobId FROM Job "
1137 "WHERE Job.Type='D'";
1139 printf("%s\n", query);
1141 if (!make_id_list(query, &id_list)) {
1144 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1145 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1146 for (int i=0; i < id_list.num_ids; i++) {
1148 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1149 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1150 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1151 printf("%s\n", db_strerror(db));
1158 if (fix && id_list.num_ids > 0) {
1159 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1160 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1164 static void eliminate_restore_records()
1168 printf(_("Checking for Restore Job entries.\n"));
1169 query = "SELECT Job.JobId FROM Job "
1170 "WHERE Job.Type='R'";
1172 printf("%s\n", query);
1174 if (!make_id_list(query, &id_list)) {
1177 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1178 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1179 for (int i=0; i < id_list.num_ids; i++) {
1181 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1182 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1183 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1184 printf("%s\n", db_strerror(db));
1191 if (fix && id_list.num_ids > 0) {
1192 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1193 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1197 static void repair_bad_filenames()
1202 printf(_("Checking for Filenames with a trailing slash\n"));
1203 query = "SELECT FilenameId,Name from Filename "
1204 "WHERE Name LIKE '%/'";
1206 printf("%s\n", query);
1208 if (!make_id_list(query, &id_list)) {
1211 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1212 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1213 for (i=0; i < id_list.num_ids; i++) {
1215 bsnprintf(buf, sizeof(buf),
1216 "SELECT Name FROM Filename WHERE FilenameId=%s",
1217 edit_int64(id_list.Id[i], ed1));
1218 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1219 printf("%s\n", db_strerror(db));
1226 if (fix && id_list.num_ids > 0) {
1227 POOLMEM *name = get_pool_memory(PM_FNAME);
1228 char esc_name[5000];
1229 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1230 for (i=0; i < id_list.num_ids; i++) {
1233 bsnprintf(buf, sizeof(buf),
1234 "SELECT Name FROM Filename WHERE FilenameId=%s",
1235 edit_int64(id_list.Id[i], ed1));
1236 if (!db_sql_query(db, buf, get_name_handler, name)) {
1237 printf("%s\n", db_strerror(db));
1240 * Strip trailing slash(es)
1242 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1250 db_escape_string(NULL, db, esc_name, name, len);
1252 bsnprintf(buf, sizeof(buf),
1253 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1254 esc_name, edit_int64(id_list.Id[i], ed1));
1256 printf("%s\n", buf);
1258 db_sql_query(db, buf, NULL, NULL);
1260 free_pool_memory(name);
1264 static void repair_bad_paths()
1269 printf(_("Checking for Paths without a trailing slash\n"));
1270 query = "SELECT PathId,Path from Path "
1271 "WHERE Path NOT LIKE '%/'";
1273 printf("%s\n", query);
1275 if (!make_id_list(query, &id_list)) {
1278 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1279 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1280 for (i=0; i < id_list.num_ids; i++) {
1282 bsnprintf(buf, sizeof(buf),
1283 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1284 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1285 printf("%s\n", db_strerror(db));
1292 if (fix && id_list.num_ids > 0) {
1293 POOLMEM *name = get_pool_memory(PM_FNAME);
1294 char esc_name[5000];
1295 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1296 for (i=0; i < id_list.num_ids; i++) {
1299 bsnprintf(buf, sizeof(buf),
1300 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1301 if (!db_sql_query(db, buf, get_name_handler, name)) {
1302 printf("%s\n", db_strerror(db));
1305 * Strip trailing blanks
1307 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1311 * Add trailing slash
1313 len = pm_strcat(&name, "/");
1314 db_escape_string(NULL, db, esc_name, name, len);
1315 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1316 esc_name, edit_int64(id_list.Id[i], ed1));
1318 printf("%s\n", buf);
1320 db_sql_query(db, buf, NULL, NULL);
1322 free_pool_memory(name);
1327 * Gen next input command from the terminal
1329 static char *get_cmd(const char *prompt)
1331 static char cmd[1000];
1333 printf("%s", prompt);
1334 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1339 strip_trailing_junk(cmd);
1343 static bool yes_no(const char *prompt)
1346 cmd = get_cmd(prompt);
1351 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1354 bool python_set_prog(JCR*, char const*) { return false; }
1357 * The code below to add indexes is needed only for MySQL, and
1358 * that to improve the performance.
1362 typedef struct s_idx_list {
1364 int count_key; /* how many times the index meets *key_name */
1365 int count_col; /* how many times meets the desired column name */
1368 static IDX_LIST idx_list[MAXIDX];
1371 * Called here with each table index to be added to the list
1373 static int check_idx_handler(void *ctx, int num_fields, char **row)
1376 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1377 * File | 0 | PRIMARY | 1 | FileId |...
1379 char *name, *key_name, *col_name;
1386 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1387 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1388 idx_list[i].count_key++;
1390 if (strcasecmp(col_name, name) == 0) {
1391 idx_list[i].count_col++;
1397 * If the new Key_name, add it to the list
1400 len = strlen(key_name) + 1;
1401 idx_list[i].key_name = (char *)malloc(len);
1402 bstrncpy(idx_list[i].key_name, key_name, len);
1403 idx_list[i].count_key = 1;
1404 if (strcasecmp(col_name, name) == 0) {
1405 idx_list[i].count_col = 1;
1407 idx_list[i].count_col = 0;
1414 * Return TRUE if "one column" index over *col_name exists
1416 static bool check_idx(const char *col_name)
1420 const char *query = "SHOW INDEX FROM File";
1422 switch (db_get_type_index(db)) {
1423 case SQL_TYPE_MYSQL:
1424 memset(&idx_list, 0, sizeof(idx_list));
1425 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1426 printf("%s\n", db_strerror(db));
1428 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1430 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1432 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1434 * "one column" index over *col_name found
1441 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1444 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1453 * Create temporary one-column index
1455 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1456 const char *col_name)
1458 idx_tmp_name = NULL;
1459 printf(_("Create temporary index... This may take some time!\n"));
1460 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1462 printf("%s\n", buf);
1464 if (db_sql_query(db, buf, NULL, NULL)) {
1465 idx_tmp_name = idx_name;
1467 printf(_("Temporary index created.\n"));
1470 printf("%s\n", db_strerror(db));
1477 * Drop temporary index
1479 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1481 if (idx_tmp_name != NULL) {
1482 printf(_("Drop temporary index.\n"));
1483 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1485 printf("%s\n", buf);
1487 if (!db_sql_query(db, buf, NULL, NULL)) {
1488 printf("%s\n", db_strerror(db));
1492 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1496 idx_tmp_name = NULL;