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 */
52 /* Global variables */
54 static bool fix = false;
55 static bool batch = false;
57 static ID_LIST id_list;
58 static NAME_LIST name_list;
59 static char buf[20000];
60 static bool quit = false;
61 static CONFIG *config;
62 static const char *idx_tmp_name;
64 #define MAX_ID_LIST_LEN 10000000
66 /* Forward referenced functions */
67 static int make_id_list(const char *query, ID_LIST *id_list);
68 static int delete_id_list(const char *query, ID_LIST *id_list);
69 static int make_name_list(const char *query, NAME_LIST *name_list);
70 static void print_name_list(NAME_LIST *name_list);
71 static void free_name_list(NAME_LIST *name_list);
72 static char *get_cmd(const char *prompt);
73 static void eliminate_duplicate_filenames();
74 static void eliminate_duplicate_paths();
75 static void eliminate_orphaned_jobmedia_records();
76 static void eliminate_orphaned_file_records();
77 static void eliminate_orphaned_path_records();
78 static void eliminate_orphaned_filename_records();
79 static void eliminate_orphaned_fileset_records();
80 static void eliminate_orphaned_client_records();
81 static void eliminate_orphaned_job_records();
82 static void eliminate_admin_records();
83 static void eliminate_restore_records();
84 static void repair_bad_paths();
85 static void repair_bad_filenames();
86 static void do_interactive_mode();
87 static bool yes_no(const char *prompt);
88 static bool check_idx(const char *col_name);
89 static bool create_tmp_idx(const char *idx_name, const char *table_name,
90 const char *col_name);
91 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
92 static int check_idx_handler(void *ctx, int num_fields, char **row);
98 "\n%sVersion: %s (%s)\n\n"
99 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>] [<dbport>] [<dbsslkey>] [<dbsslcert>] [<dbsslca>]\n"
101 " -C catalog name in the director conf file\n"
102 " -c Director conf filename\n"
103 " -B print catalog configuration and exit\n"
104 " -d <nn> set debug level to <nn>\n"
105 " -dt print a timestamp in debug output\n"
106 " -f fix inconsistencies\n"
107 " -t test if client library is thread-safe\n"
109 " -? print this message\n"
110 "\n", 2002, "", VERSION, BDATE);
115 int main (int argc, char *argv[])
118 const char *user, *password, *db_name, *dbhost;
119 const char *dbsslkey = NULL, *dbsslcert = NULL, *dbsslca = NULL;
120 const char *dbsslcapath = NULL, *dbsslcipher = NULL;
122 bool print_catalog=false;
123 char *configfile = NULL;
124 char *catalogname = NULL;
127 setlocale(LC_ALL, "");
128 bindtextdomain("bacula", LOCALEDIR);
129 textdomain("bacula");
132 my_name_is(argc, argv, "dbcheck");
133 init_msg(NULL, NULL); /* setup message handler */
135 memset(&id_list, 0, sizeof(id_list));
136 memset(&name_list, 0, sizeof(name_list));
138 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
141 print_catalog = true; /* get catalog information from config */
143 case 'b': /* batch */
146 case 'C': /* CatalogName */
147 catalogname = optarg;
149 case 'c': /* configfile */
152 case 'd': /* debug level */
153 if (*optarg == 't') {
154 dbg_timestamp = true;
156 debug_level = atoi(optarg);
157 if (debug_level <= 0) {
162 case 'f': /* fix inconsistencies */
182 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
184 config = new_config_parser();
185 parse_dir_config(config, configfile, M_ERROR_TERM);
187 foreach_res(catalog, R_CATALOG) {
188 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
191 } else if (!catalogname) { // stop on first if no catalogname is given
199 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
201 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
207 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
210 Pmsg0(0, _("Error no Director resource defined.\n"));
213 set_working_directory(director->working_directory);
215 /* Print catalog information and exit (-B) */
218 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
219 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
220 catalog->db_password, catalog->db_address,
221 catalog->db_port, catalog->db_socket,
222 catalog->db_ssl_key, catalog->db_ssl_cert, catalog->db_ssl_ca,
223 catalog->db_ssl_capath, catalog->db_ssl_cipher,
224 catalog->mult_db_connections,
225 catalog->disable_batch_insert);
227 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
228 db_get_engine_name(db), working_directory);
229 db_close_database(NULL, db);
231 free_pool_memory(catalog_details);
235 db_name = catalog->db_name;
236 user = catalog->db_user;
237 password = catalog->db_password;
238 dbhost = catalog->db_address;
239 if (dbhost && dbhost[0] == 0) {
242 dbport = catalog->db_port;
243 dbsslkey = catalog->db_ssl_key;
244 dbsslcert = catalog->db_ssl_cert;
245 dbsslca = catalog->db_ssl_ca;
246 dbsslcapath = catalog->db_ssl_capath;
247 dbsslcipher = catalog->db_ssl_cipher;
251 Pmsg0(0, _("Wrong number of arguments.\n"));
256 Pmsg0(0, _("Working directory not supplied.\n"));
260 /* This is needed by SQLite to find the db */
261 working_directory = argv[0];
278 dbport = strtol(argv[5], &endptr, 10);
279 if (*endptr != '\0') {
280 Pmsg0(0, _("Database port must be a numeric value.\n"));
282 } else if (errno == ERANGE) {
283 Pmsg0(0, _("Database port must be a int value.\n"));
291 } /* if (argc == 9) */
292 } /* if (argc >= 7) */
293 } /* if (argc >= 6) */
294 } /* if (argc >= 5) */
295 } /* if (argc >= 4) */
296 } /* if (argc >= 3) */
297 } /* if (argc >= 2) */
301 db = db_init_database(NULL, NULL, db_name, user, password, dbhost,
302 dbport, NULL, dbsslkey, dbsslcert, dbsslca, dbsslcapath, dbsslcipher, false, false);
303 if (!db || !db_open_database(NULL, db)) {
304 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
308 /* Drop temporary index idx_tmp_name if it already exists */
309 drop_tmp_idx("idxPIchk", "File");
313 repair_bad_filenames();
314 eliminate_duplicate_filenames();
315 eliminate_duplicate_paths();
316 eliminate_orphaned_jobmedia_records();
317 eliminate_orphaned_file_records();
318 eliminate_orphaned_path_records();
319 eliminate_orphaned_filename_records();
320 eliminate_orphaned_fileset_records();
321 eliminate_orphaned_client_records();
322 eliminate_orphaned_job_records();
323 eliminate_admin_records();
324 eliminate_restore_records();
326 do_interactive_mode();
329 /* Drop temporary index idx_tmp_name */
330 drop_tmp_idx("idxPIchk", "File");
332 if (db) db_close_database(NULL, db);
339 static void do_interactive_mode()
343 printf(_("Hello, this is the database check/correct program.\n"));
345 printf(_("Modify database is on."));
347 printf(_("Modify database is off."));
349 printf(_(" Verbose is on.\n"));
351 printf(_(" Verbose is off.\n"));
353 printf(_("Please select the function you want to perform.\n"));
358 " 1) Toggle modify database flag\n"
359 " 2) Toggle verbose flag\n"
360 " 3) Repair bad Filename records\n"
361 " 4) Repair bad Path records\n"
362 " 5) Eliminate duplicate Filename records\n"
363 " 6) Eliminate duplicate Path records\n"
364 " 7) Eliminate orphaned Jobmedia records\n"
365 " 8) Eliminate orphaned File records\n"
366 " 9) Eliminate orphaned Path records\n"
367 " 10) Eliminate orphaned Filename records\n"
368 " 11) Eliminate orphaned FileSet records\n"
369 " 12) Eliminate orphaned Client records\n"
370 " 13) Eliminate orphaned Job records\n"
371 " 14) Eliminate all Admin records\n"
372 " 15) Eliminate all Restore records\n"
377 " 1) Toggle modify database flag\n"
378 " 2) Toggle verbose flag\n"
379 " 3) Check for bad Filename records\n"
380 " 4) Check for bad Path records\n"
381 " 5) Check for duplicate Filename records\n"
382 " 6) Check for duplicate Path records\n"
383 " 7) Check for orphaned Jobmedia records\n"
384 " 8) Check for orphaned File records\n"
385 " 9) Check for orphaned Path records\n"
386 " 10) Check for orphaned Filename records\n"
387 " 11) Check for orphaned FileSet records\n"
388 " 12) Check for orphaned Client records\n"
389 " 13) Check for orphaned Job records\n"
390 " 14) Check for all Admin records\n"
391 " 15) Check for all Restore records\n"
396 cmd = get_cmd(_("Select function number: "));
398 int item = atoi(cmd);
403 printf(_("Database will be modified.\n"));
405 printf(_("Database will NOT be modified.\n"));
408 verbose = verbose?0:1;
410 printf(_(" Verbose is on.\n"));
412 printf(_(" Verbose is off.\n"));
415 repair_bad_filenames();
421 eliminate_duplicate_filenames();
424 eliminate_duplicate_paths();
427 eliminate_orphaned_jobmedia_records();
430 eliminate_orphaned_file_records();
433 eliminate_orphaned_path_records();
436 eliminate_orphaned_filename_records();
439 eliminate_orphaned_fileset_records();
442 eliminate_orphaned_client_records();
445 eliminate_orphaned_job_records();
448 eliminate_admin_records();
451 eliminate_restore_records();
454 repair_bad_filenames();
456 eliminate_duplicate_filenames();
457 eliminate_duplicate_paths();
458 eliminate_orphaned_jobmedia_records();
459 eliminate_orphaned_file_records();
460 eliminate_orphaned_path_records();
461 eliminate_orphaned_filename_records();
462 eliminate_orphaned_fileset_records();
463 eliminate_orphaned_client_records();
464 eliminate_orphaned_job_records();
465 eliminate_admin_records();
466 eliminate_restore_records();
476 static int print_name_handler(void *ctx, int num_fields, char **row)
479 printf("%s\n", row[0]);
484 static int get_name_handler(void *ctx, int num_fields, char **row)
486 POOLMEM *buf = (POOLMEM *)ctx;
488 pm_strcpy(&buf, row[0]);
493 static int print_job_handler(void *ctx, int num_fields, char **row)
495 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
496 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
500 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
502 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
503 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
507 static int print_file_handler(void *ctx, int num_fields, char **row)
509 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
510 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
514 static int print_fileset_handler(void *ctx, int num_fields, char **row)
516 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
517 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
521 static int print_client_handler(void *ctx, int num_fields, char **row)
523 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
524 NPRT(row[0]), NPRT(row[1]));
529 * Called here with each id to be added to the list
531 static int id_list_handler(void *ctx, int num_fields, char **row)
533 ID_LIST *lst = (ID_LIST *)ctx;
535 if (lst->num_ids == MAX_ID_LIST_LEN) {
538 if (lst->num_ids == lst->max_ids) {
539 if (lst->max_ids == 0) {
540 lst->max_ids = 10000;
541 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
543 lst->max_ids = (lst->max_ids * 3) / 2;
544 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
547 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
552 * Construct record id list
554 static int make_id_list(const char *query, ID_LIST *id_list)
556 id_list->num_ids = 0;
557 id_list->num_del = 0;
558 id_list->tot_ids = 0;
560 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
561 printf("%s", db_strerror(db));
568 * Delete all entries in the list
570 static int delete_id_list(const char *query, ID_LIST *id_list)
573 for (int i=0; i < id_list->num_ids; i++) {
574 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
576 printf(_("Deleting: %s\n"), buf);
578 db_sql_query(db, buf, NULL, NULL);
584 * Called here with each name to be added to the list
586 static int name_list_handler(void *ctx, int num_fields, char **row)
588 NAME_LIST *name = (NAME_LIST *)ctx;
590 if (name->num_ids == MAX_ID_LIST_LEN) {
593 if (name->num_ids == name->max_ids) {
594 if (name->max_ids == 0) {
595 name->max_ids = 10000;
596 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
598 name->max_ids = (name->max_ids * 3) / 2;
599 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
602 name->name[name->num_ids++] = bstrdup(row[0]);
607 * Construct name list
609 static int make_name_list(const char *query, NAME_LIST *name_list)
611 name_list->num_ids = 0;
612 name_list->num_del = 0;
613 name_list->tot_ids = 0;
615 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
616 printf("%s", db_strerror(db));
623 * Print names in the list
625 static void print_name_list(NAME_LIST *name_list)
627 for (int i=0; i < name_list->num_ids; i++) {
628 printf("%s\n", name_list->name[i]);
633 * Free names in the list
635 static void free_name_list(NAME_LIST *name_list)
637 for (int i=0; i < name_list->num_ids; i++) {
638 free(name_list->name[i]);
640 name_list->num_ids = 0;
643 static void eliminate_duplicate_filenames()
648 printf(_("Checking for duplicate Filename entries.\n"));
650 /* Make list of duplicated names */
651 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
652 "HAVING count(Name) > 1";
654 if (!make_name_list(query, &name_list)) {
657 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
658 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
659 print_name_list(&name_list);
665 /* Loop through list of duplicate names */
666 for (int i=0; i<name_list.num_ids; i++) {
667 /* Get all the Ids of each name */
668 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
669 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
673 if (!make_id_list(buf, &id_list)) {
677 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
679 /* Force all records to use the first id then delete the other ids */
680 for (int j=1; j<id_list.num_ids; j++) {
681 char ed1[50], ed2[50];
682 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
683 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
687 db_sql_query(db, buf, NULL, NULL);
688 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
693 db_sql_query(db, buf, NULL, NULL);
697 free_name_list(&name_list);
700 static void eliminate_duplicate_paths()
705 printf(_("Checking for duplicate Path entries.\n"));
707 /* Make list of duplicated names */
708 query = "SELECT Path, count(Path) as Count FROM Path "
709 "GROUP BY Path HAVING count(Path) > 1";
711 if (!make_name_list(query, &name_list)) {
714 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
715 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
716 print_name_list(&name_list);
722 /* Loop through list of duplicate names */
723 for (int i=0; i<name_list.num_ids; i++) {
724 /* Get all the Ids of each name */
725 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
726 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
730 if (!make_id_list(buf, &id_list)) {
734 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
736 /* Force all records to use the first id then delete the other ids */
737 for (int j=1; j<id_list.num_ids; j++) {
738 char ed1[50], ed2[50];
739 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
740 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
744 db_sql_query(db, buf, NULL, NULL);
745 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
749 db_sql_query(db, buf, NULL, NULL);
753 free_name_list(&name_list);
756 static void eliminate_orphaned_jobmedia_records()
758 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
759 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
760 "WHERE Job.JobId IS NULL LIMIT 300000";
762 printf(_("Checking for orphaned JobMedia entries.\n"));
763 if (!make_id_list(query, &id_list)) {
766 /* Loop doing 300000 at a time */
767 while (id_list.num_ids != 0) {
768 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
769 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
770 for (int i=0; i < id_list.num_ids; i++) {
772 bsnprintf(buf, sizeof(buf),
773 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
774 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
775 edit_int64(id_list.Id[i], ed1));
776 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
777 printf("%s\n", db_strerror(db));
785 if (fix && id_list.num_ids > 0) {
786 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
787 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
789 break; /* get out if not updating db */
791 if (!make_id_list(query, &id_list)) {
797 static void eliminate_orphaned_file_records()
799 const char *query = "SELECT File.FileId,Job.JobId FROM File "
800 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
801 "WHERE Job.JobId IS NULL LIMIT 300000";
803 printf(_("Checking for orphaned File entries. This may take some time!\n"));
805 printf("%s\n", query);
807 if (!make_id_list(query, &id_list)) {
810 /* Loop doing 300000 at a time */
811 while (id_list.num_ids != 0) {
812 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
813 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
814 for (int i=0; i < id_list.num_ids; i++) {
816 bsnprintf(buf, sizeof(buf),
817 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
818 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
819 edit_int64(id_list.Id[i], ed1));
820 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
821 printf("%s\n", db_strerror(db));
828 if (fix && id_list.num_ids > 0) {
829 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
830 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
832 break; /* get out if not updating db */
834 if (!make_id_list(query, &id_list)) {
840 static void eliminate_orphaned_path_records()
844 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
845 db_int64_handler, &lctx);
847 if (lctx.count == 1) {
848 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
853 /* Check the existence of the required "one column" index */
854 if (!check_idx("PathId")) {
855 if (yes_no(_("Create temporary index? (yes/no): "))) {
856 /* create temporary index PathId */
857 create_tmp_idx("idxPIchk", "File", "PathId");
861 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
862 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
863 "WHERE File.PathId IS NULL LIMIT 300000";
865 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
867 printf("%s\n", query);
869 if (!make_id_list(query, &id_list)) {
872 /* Loop doing 300000 at a time */
873 while (id_list.num_ids != 0) {
874 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
875 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
876 for (int i=0; i < id_list.num_ids; i++) {
878 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
879 edit_int64(id_list.Id[i], ed1));
880 db_sql_query(db, buf, print_name_handler, NULL);
886 if (fix && id_list.num_ids > 0) {
887 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
888 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
890 break; /* get out if not updating db */
892 if (!make_id_list(query, &id_list)) {
896 /* Drop temporary index idx_tmp_name */
897 drop_tmp_idx("idxPIchk", "File");
900 static void eliminate_orphaned_filename_records()
903 /* Check the existence of the required "one column" index */
904 if (!check_idx("FilenameId") ) {
905 if (yes_no(_("Create temporary index? (yes/no): "))) {
906 /* Create temporary index FilenameId */
907 create_tmp_idx("idxFIchk", "File", "FilenameId");
911 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
912 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
913 "WHERE File.FilenameId IS NULL LIMIT 300000";
915 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
917 printf("%s\n", query);
919 if (!make_id_list(query, &id_list)) {
922 /* Loop doing 300000 at a time */
923 while (id_list.num_ids != 0) {
924 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
925 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
926 for (int i=0; i < id_list.num_ids; i++) {
928 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
929 edit_int64(id_list.Id[i], ed1));
930 db_sql_query(db, buf, print_name_handler, NULL);
936 if (fix && id_list.num_ids > 0) {
937 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
938 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
940 break; /* get out if not updating db */
942 if (!make_id_list(query, &id_list)) {
946 /* Drop temporary index idx_tmp_name */
947 drop_tmp_idx("idxFIchk", "File");
951 static void eliminate_orphaned_fileset_records()
955 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
956 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
957 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
958 "WHERE Job.FileSetId IS NULL";
960 printf("%s\n", query);
962 if (!make_id_list(query, &id_list)) {
965 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
966 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
967 for (int i=0; i < id_list.num_ids; i++) {
969 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
970 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
971 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
972 printf("%s\n", db_strerror(db));
979 if (fix && id_list.num_ids > 0) {
980 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
981 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
985 static void eliminate_orphaned_client_records()
989 printf(_("Checking for orphaned Client entries.\n"));
991 * Wiffle through Client for every Client
992 * joining with the Job table including every Client even if
993 * there is not a match in Job (left outer join), then
994 * filter out only those where no Job points to a Client
995 * i.e. Job.Client is NULL
997 query = "SELECT Client.ClientId,Client.Name FROM Client "
998 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
999 "WHERE Job.ClientId IS NULL";
1001 printf("%s\n", query);
1003 if (!make_id_list(query, &id_list)) {
1006 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1007 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1008 for (int i=0; i < id_list.num_ids; i++) {
1010 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1011 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1012 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1013 printf("%s\n", db_strerror(db));
1020 if (fix && id_list.num_ids > 0) {
1021 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1022 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1026 static void eliminate_orphaned_job_records()
1030 printf(_("Checking for orphaned Job entries.\n"));
1032 * Wiffle through Job for every Job
1033 * joining with the Client table including every Job even if
1034 * there is not a match in Client (left outer join), then
1035 * filter out only those where no Client exists
1036 * i.e. Client.Name is NULL
1038 query = "SELECT Job.JobId,Job.Name FROM Job "
1039 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1040 "WHERE Client.Name IS NULL";
1042 printf("%s\n", query);
1044 if (!make_id_list(query, &id_list)) {
1047 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1048 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1049 for (int i=0; i < id_list.num_ids; i++) {
1051 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1052 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1053 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1054 printf("%s\n", db_strerror(db));
1061 if (fix && id_list.num_ids > 0) {
1062 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1063 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1064 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1065 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1066 printf(_("Deleting Log records of orphaned Job records.\n"));
1067 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1071 static void eliminate_admin_records()
1075 printf(_("Checking for Admin Job entries.\n"));
1076 query = "SELECT Job.JobId FROM Job "
1077 "WHERE Job.Type='D'";
1079 printf("%s\n", query);
1081 if (!make_id_list(query, &id_list)) {
1084 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1085 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1086 for (int i=0; i < id_list.num_ids; i++) {
1088 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1089 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1090 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1091 printf("%s\n", db_strerror(db));
1098 if (fix && id_list.num_ids > 0) {
1099 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1100 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1104 static void eliminate_restore_records()
1108 printf(_("Checking for Restore Job entries.\n"));
1109 query = "SELECT Job.JobId FROM Job "
1110 "WHERE Job.Type='R'";
1112 printf("%s\n", query);
1114 if (!make_id_list(query, &id_list)) {
1117 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1118 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1119 for (int i=0; i < id_list.num_ids; i++) {
1121 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1122 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1123 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1124 printf("%s\n", db_strerror(db));
1131 if (fix && id_list.num_ids > 0) {
1132 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1133 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1137 static void repair_bad_filenames()
1142 printf(_("Checking for Filenames with a trailing slash\n"));
1143 query = "SELECT FilenameId,Name from Filename "
1144 "WHERE Name LIKE '%/'";
1146 printf("%s\n", query);
1148 if (!make_id_list(query, &id_list)) {
1151 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1152 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1153 for (i=0; i < id_list.num_ids; i++) {
1155 bsnprintf(buf, sizeof(buf),
1156 "SELECT Name FROM Filename WHERE FilenameId=%s",
1157 edit_int64(id_list.Id[i], ed1));
1158 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1159 printf("%s\n", db_strerror(db));
1166 if (fix && id_list.num_ids > 0) {
1167 POOLMEM *name = get_pool_memory(PM_FNAME);
1168 char esc_name[5000];
1169 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1170 for (i=0; i < id_list.num_ids; i++) {
1173 bsnprintf(buf, sizeof(buf),
1174 "SELECT Name FROM Filename WHERE FilenameId=%s",
1175 edit_int64(id_list.Id[i], ed1));
1176 if (!db_sql_query(db, buf, get_name_handler, name)) {
1177 printf("%s\n", db_strerror(db));
1179 /* Strip trailing slash(es) */
1180 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1188 db_escape_string(NULL, db, esc_name, name, len);
1190 bsnprintf(buf, sizeof(buf),
1191 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1192 esc_name, edit_int64(id_list.Id[i], ed1));
1194 printf("%s\n", buf);
1196 db_sql_query(db, buf, NULL, NULL);
1198 free_pool_memory(name);
1202 static void repair_bad_paths()
1207 printf(_("Checking for Paths without a trailing slash\n"));
1208 query = "SELECT PathId,Path from Path "
1209 "WHERE Path NOT LIKE '%/'";
1211 printf("%s\n", query);
1213 if (!make_id_list(query, &id_list)) {
1216 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1217 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1218 for (i=0; i < id_list.num_ids; i++) {
1220 bsnprintf(buf, sizeof(buf),
1221 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1222 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1223 printf("%s\n", db_strerror(db));
1230 if (fix && id_list.num_ids > 0) {
1231 POOLMEM *name = get_pool_memory(PM_FNAME);
1232 char esc_name[5000];
1233 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1234 for (i=0; i < id_list.num_ids; i++) {
1237 bsnprintf(buf, sizeof(buf),
1238 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1239 if (!db_sql_query(db, buf, get_name_handler, name)) {
1240 printf("%s\n", db_strerror(db));
1242 /* Strip trailing blanks */
1243 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1246 /* Add trailing slash */
1247 len = pm_strcat(&name, "/");
1248 db_escape_string(NULL, db, esc_name, name, len);
1249 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1250 esc_name, edit_int64(id_list.Id[i], ed1));
1252 printf("%s\n", buf);
1254 db_sql_query(db, buf, NULL, NULL);
1256 free_pool_memory(name);
1261 * Gen next input command from the terminal
1263 static char *get_cmd(const char *prompt)
1265 static char cmd[1000];
1267 printf("%s", prompt);
1268 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1273 strip_trailing_junk(cmd);
1277 static bool yes_no(const char *prompt)
1280 cmd = get_cmd(prompt);
1285 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1288 bool python_set_prog(JCR*, char const*) { return false; }
1291 * The code below to add indexes is needed only for MySQL, and
1292 * that to improve the performance.
1296 typedef struct s_idx_list {
1298 int count_key; /* how many times the index meets *key_name */
1299 int count_col; /* how many times meets the desired column name */
1302 static IDX_LIST idx_list[MAXIDX];
1305 * Called here with each table index to be added to the list
1307 static int check_idx_handler(void *ctx, int num_fields, char **row)
1310 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1311 * File | 0 | PRIMARY | 1 | FileId |...
1313 char *name, *key_name, *col_name;
1320 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1321 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1322 idx_list[i].count_key++;
1324 if (strcasecmp(col_name, name) == 0) {
1325 idx_list[i].count_col++;
1330 /* If the new Key_name, add it to the list */
1332 len = strlen(key_name) + 1;
1333 idx_list[i].key_name = (char *)malloc(len);
1334 bstrncpy(idx_list[i].key_name, key_name, len);
1335 idx_list[i].count_key = 1;
1336 if (strcasecmp(col_name, name) == 0) {
1337 idx_list[i].count_col = 1;
1339 idx_list[i].count_col = 0;
1346 * Return TRUE if "one column" index over *col_name exists
1348 static bool check_idx(const char *col_name)
1352 const char *query = "SHOW INDEX FROM File";
1354 if (db_get_type_index(db) != SQL_TYPE_MYSQL) {
1357 /* Continue for MySQL */
1358 memset(&idx_list, 0, sizeof(idx_list));
1359 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1360 printf("%s\n", db_strerror(db));
1362 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1364 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1366 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1367 /* "one column" index over *col_name found */
1373 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1376 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1382 * Create temporary one-column index
1384 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1385 const char *col_name)
1387 idx_tmp_name = NULL;
1388 printf(_("Create temporary index... This may take some time!\n"));
1389 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1391 printf("%s\n", buf);
1393 if (db_sql_query(db, buf, NULL, NULL)) {
1394 idx_tmp_name = idx_name;
1396 printf(_("Temporary index created.\n"));
1399 printf("%s\n", db_strerror(db));
1406 * Drop temporary index
1408 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1410 if (idx_tmp_name != NULL) {
1411 printf(_("Drop temporary index.\n"));
1412 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1414 printf("%s\n", buf);
1416 if (!db_sql_query(db, buf, NULL, NULL)) {
1417 printf("%s\n", db_strerror(db));
1421 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1425 idx_tmp_name = NULL;