2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2014 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from many
7 others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 Bacula® is a registered trademark of Kern Sibbald.
18 * Program to check a Bacula database for consistency and to
21 * Kern E. Sibbald, August 2002
26 #include "cats/cats.h"
27 #include "cats/sql_glue.h"
28 #include "lib/runscript.h"
29 #include "dird/dird_conf.h"
31 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
36 int generate_daemon_event(JCR *jcr, const char *event)
39 typedef struct s_id_ctx {
40 int64_t *Id; /* ids to be modified */
41 int num_ids; /* ids stored */
42 int max_ids; /* size of array */
43 int num_del; /* number deleted */
44 int tot_ids; /* total to process */
47 typedef struct s_name_ctx {
48 char **name; /* list of names */
49 int num_ids; /* ids stored */
50 int max_ids; /* size of array */
51 int num_del; /* number deleted */
52 int tot_ids; /* total to process */
58 static bool fix = false;
59 static bool batch = false;
61 static ID_LIST id_list;
62 static NAME_LIST name_list;
63 static char buf[20000];
64 static bool quit = false;
65 static CONFIG *config;
66 static const char *idx_tmp_name;
68 #define MAX_ID_LIST_LEN 10000000
71 * Forward referenced functions
73 static void print_catalog_details(CAT *catalog, const char *working_dir);
74 static int make_id_list(const char *query, ID_LIST *id_list);
75 static int delete_id_list(const char *query, ID_LIST *id_list);
76 static int make_name_list(const char *query, NAME_LIST *name_list);
77 static void print_name_list(NAME_LIST *name_list);
78 static void free_name_list(NAME_LIST *name_list);
79 static char *get_cmd(const char *prompt);
80 static void eliminate_duplicate_filenames();
81 static void eliminate_duplicate_paths();
82 static void eliminate_orphaned_jobmedia_records();
83 static void eliminate_orphaned_file_records();
84 static void eliminate_orphaned_path_records();
85 static void eliminate_orphaned_filename_records();
86 static void eliminate_orphaned_fileset_records();
87 static void eliminate_orphaned_client_records();
88 static void eliminate_orphaned_job_records();
89 static void eliminate_admin_records();
90 static void eliminate_restore_records();
91 static void repair_bad_paths();
92 static void repair_bad_filenames();
93 static void do_interactive_mode();
94 static bool yes_no(const char *prompt);
95 static bool check_idx(const char *col_name);
96 static bool create_tmp_idx(const char *idx_name, const char *table_name,
97 const char *col_name);
98 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
99 static int check_idx_handler(void *ctx, int num_fields, char **row);
104 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
106 " -C catalog name in the director conf file\n"
107 " -c Director conf filename\n"
108 " -B print catalog configuration and exit\n"
109 " -d <nn> set debug level to <nn>\n"
110 " -dt print a timestamp in debug output\n"
111 " -f fix inconsistencies\n"
112 " -t test if client library is thread-safe\n"
114 " -? print this message\n\n");
118 int main (int argc, char *argv[])
121 const char *user, *password, *db_name, *dbhost;
123 bool print_catalog=false;
124 char *configfile = NULL;
125 char *catalogname = NULL;
128 setlocale(LC_ALL, "");
129 bindtextdomain("bacula", LOCALEDIR);
130 textdomain("bacula");
133 my_name_is(argc, argv, "dbcheck");
134 init_msg(NULL, NULL); /* setup message handler */
136 memset(&id_list, 0, sizeof(id_list));
137 memset(&name_list, 0, sizeof(name_list));
139 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
142 print_catalog = true; /* get catalog information from config */
144 case 'b': /* batch */
147 case 'C': /* CatalogName */
148 catalogname = optarg;
150 case 'c': /* configfile */
153 case 'd': /* debug level */
154 if (*optarg == 't') {
155 dbg_timestamp = true;
157 debug_level = atoi(optarg);
158 if (debug_level <= 0) {
163 case 'f': /* fix inconsistencies */
183 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
185 config = new_config_parser();
186 parse_dir_config(config, configfile, M_ERROR_TERM);
188 foreach_res(catalog, R_CATALOG) {
189 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
192 } else if (!catalogname) { // stop on first if no catalogname is given
200 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
202 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
208 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
211 Pmsg0(0, _("Error no Director resource defined.\n"));
214 set_working_directory(director->working_directory);
217 * Print catalog information and exit (-B)
220 print_catalog_details(catalog, director->working_directory);
224 db_name = catalog->db_name;
225 user = catalog->db_user;
226 password = catalog->db_password;
227 dbhost = catalog->db_address;
228 if (dbhost && dbhost[0] == 0) {
231 dbport = catalog->db_port;
235 Pmsg0(0, _("Wrong number of arguments.\n"));
240 Pmsg0(0, _("Working directory not supplied.\n"));
245 * This is needed by SQLite to find the db
247 working_directory = argv[0];
256 } else if (argc == 3) {
259 } else if (argc == 4) {
263 } else if (argc == 5) {
268 } else if (argc == 6) {
274 dbport = strtol(argv[5], &endptr, 10);
275 if (*endptr != '\0') {
276 Pmsg0(0, _("Database port must be a numeric value.\n"));
278 } else if (errno == ERANGE) {
279 Pmsg0(0, _("Database port must be a int value.\n"));
288 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
289 if (!db_open_database(NULL, db)) {
290 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
295 * Drop temporary index idx_tmp_name if it already exists
297 drop_tmp_idx("idxPIchk", "File");
301 repair_bad_filenames();
302 eliminate_duplicate_filenames();
303 eliminate_duplicate_paths();
304 eliminate_orphaned_jobmedia_records();
305 eliminate_orphaned_file_records();
306 eliminate_orphaned_path_records();
307 eliminate_orphaned_filename_records();
308 eliminate_orphaned_fileset_records();
309 eliminate_orphaned_client_records();
310 eliminate_orphaned_job_records();
311 eliminate_admin_records();
312 eliminate_restore_records();
314 do_interactive_mode();
318 * Drop temporary index idx_tmp_name
320 drop_tmp_idx("idxPIchk", "File");
322 db_close_database(NULL, db);
329 static void print_catalog_details(CAT *catalog, const char *working_dir)
331 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
334 * Instantiate a B_DB class and see what db_type gets assigned to it.
336 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
337 catalog->db_password, catalog->db_address,
338 catalog->db_port, catalog->db_socket,
339 catalog->mult_db_connections,
340 catalog->disable_batch_insert);
342 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
343 db->db_get_type(), working_directory);
344 db_close_database(NULL, db);
346 free_pool_memory(catalog_details);
349 static void do_interactive_mode()
353 printf(_("Hello, this is the database check/correct program.\n"));
355 printf(_("Modify database is on."));
357 printf(_("Modify database is off."));
359 printf(_(" Verbose is on.\n"));
361 printf(_(" Verbose is off.\n"));
363 printf(_("Please select the function you want to perform.\n"));
368 " 1) Toggle modify database flag\n"
369 " 2) Toggle verbose flag\n"
370 " 3) Repair bad Filename records\n"
371 " 4) Repair bad Path records\n"
372 " 5) Eliminate duplicate Filename records\n"
373 " 6) Eliminate duplicate Path records\n"
374 " 7) Eliminate orphaned Jobmedia records\n"
375 " 8) Eliminate orphaned File records\n"
376 " 9) Eliminate orphaned Path records\n"
377 " 10) Eliminate orphaned Filename records\n"
378 " 11) Eliminate orphaned FileSet records\n"
379 " 12) Eliminate orphaned Client records\n"
380 " 13) Eliminate orphaned Job records\n"
381 " 14) Eliminate all Admin records\n"
382 " 15) Eliminate all Restore records\n"
387 " 1) Toggle modify database flag\n"
388 " 2) Toggle verbose flag\n"
389 " 3) Check for bad Filename records\n"
390 " 4) Check for bad Path records\n"
391 " 5) Check for duplicate Filename records\n"
392 " 6) Check for duplicate Path records\n"
393 " 7) Check for orphaned Jobmedia records\n"
394 " 8) Check for orphaned File records\n"
395 " 9) Check for orphaned Path records\n"
396 " 10) Check for orphaned Filename records\n"
397 " 11) Check for orphaned FileSet records\n"
398 " 12) Check for orphaned Client records\n"
399 " 13) Check for orphaned Job records\n"
400 " 14) Check for all Admin records\n"
401 " 15) Check for all Restore records\n"
406 cmd = get_cmd(_("Select function number: "));
408 int item = atoi(cmd);
413 printf(_("Database will be modified.\n"));
415 printf(_("Database will NOT be modified.\n"));
418 verbose = verbose?0:1;
420 printf(_(" Verbose is on.\n"));
422 printf(_(" Verbose is off.\n"));
425 repair_bad_filenames();
431 eliminate_duplicate_filenames();
434 eliminate_duplicate_paths();
437 eliminate_orphaned_jobmedia_records();
440 eliminate_orphaned_file_records();
443 eliminate_orphaned_path_records();
446 eliminate_orphaned_filename_records();
449 eliminate_orphaned_fileset_records();
452 eliminate_orphaned_client_records();
455 eliminate_orphaned_job_records();
458 eliminate_admin_records();
461 eliminate_restore_records();
464 repair_bad_filenames();
466 eliminate_duplicate_filenames();
467 eliminate_duplicate_paths();
468 eliminate_orphaned_jobmedia_records();
469 eliminate_orphaned_file_records();
470 eliminate_orphaned_path_records();
471 eliminate_orphaned_filename_records();
472 eliminate_orphaned_fileset_records();
473 eliminate_orphaned_client_records();
474 eliminate_orphaned_job_records();
475 eliminate_admin_records();
476 eliminate_restore_records();
486 static int print_name_handler(void *ctx, int num_fields, char **row)
489 printf("%s\n", row[0]);
494 static int get_name_handler(void *ctx, int num_fields, char **row)
496 POOLMEM *name = (POOLMEM *)ctx;
499 pm_strcpy(&name, row[0]);
504 static int print_job_handler(void *ctx, int num_fields, char **row)
506 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
507 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
511 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
513 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
514 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
518 static int print_file_handler(void *ctx, int num_fields, char **row)
520 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
521 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
525 static int print_fileset_handler(void *ctx, int num_fields, char **row)
527 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
528 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
532 static int print_client_handler(void *ctx, int num_fields, char **row)
534 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
535 NPRT(row[0]), NPRT(row[1]));
540 * Called here with each id to be added to the list
542 static int id_list_handler(void *ctx, int num_fields, char **row)
544 ID_LIST *lst = (ID_LIST *)ctx;
546 if (lst->num_ids == MAX_ID_LIST_LEN) {
549 if (lst->num_ids == lst->max_ids) {
550 if (lst->max_ids == 0) {
551 lst->max_ids = 10000;
552 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
554 lst->max_ids = (lst->max_ids * 3) / 2;
555 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
558 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
563 * Construct record id list
565 static int make_id_list(const char *query, ID_LIST *id_list)
567 id_list->num_ids = 0;
568 id_list->num_del = 0;
569 id_list->tot_ids = 0;
571 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
572 printf("%s", db_strerror(db));
579 * Delete all entries in the list
581 static int delete_id_list(const char *query, ID_LIST *id_list)
584 for (int i=0; i < id_list->num_ids; i++) {
585 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
587 printf(_("Deleting: %s\n"), buf);
589 db_sql_query(db, buf, NULL, NULL);
595 * Called here with each name to be added to the list
597 static int name_list_handler(void *ctx, int num_fields, char **row)
599 NAME_LIST *name = (NAME_LIST *)ctx;
601 if (name->num_ids == MAX_ID_LIST_LEN) {
604 if (name->num_ids == name->max_ids) {
605 if (name->max_ids == 0) {
606 name->max_ids = 10000;
607 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
609 name->max_ids = (name->max_ids * 3) / 2;
610 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
613 name->name[name->num_ids++] = bstrdup(row[0]);
618 * Construct name list
620 static int make_name_list(const char *query, NAME_LIST *name_list)
622 name_list->num_ids = 0;
623 name_list->num_del = 0;
624 name_list->tot_ids = 0;
626 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
627 printf("%s", db_strerror(db));
634 * Print names in the list
636 static void print_name_list(NAME_LIST *name_list)
638 for (int i=0; i < name_list->num_ids; i++) {
639 printf("%s\n", name_list->name[i]);
644 * Free names in the list
646 static void free_name_list(NAME_LIST *name_list)
648 for (int i=0; i < name_list->num_ids; i++) {
649 free(name_list->name[i]);
651 name_list->num_ids = 0;
654 static void eliminate_duplicate_filenames()
659 printf(_("Checking for duplicate Filename entries.\n"));
662 * Make list of duplicated names
664 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
665 "HAVING count(Name) > 1";
667 if (!make_name_list(query, &name_list)) {
670 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
671 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
672 print_name_list(&name_list);
679 * Loop through list of duplicate names
681 for (int i=0; i<name_list.num_ids; i++) {
683 * Get all the Ids of each name
685 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
686 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
690 if (!make_id_list(buf, &id_list)) {
694 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
697 * Force all records to use the first id then delete the other ids
699 for (int j=1; j<id_list.num_ids; j++) {
700 char ed1[50], ed2[50];
701 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
702 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
706 db_sql_query(db, buf, NULL, NULL);
707 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
712 db_sql_query(db, buf, NULL, NULL);
716 free_name_list(&name_list);
719 static void eliminate_duplicate_paths()
724 printf(_("Checking for duplicate Path entries.\n"));
727 * Make list of duplicated names
729 query = "SELECT Path, count(Path) as Count FROM Path "
730 "GROUP BY Path HAVING count(Path) > 1";
732 if (!make_name_list(query, &name_list)) {
735 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
736 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
737 print_name_list(&name_list);
744 * Loop through list of duplicate names
746 for (int i=0; i<name_list.num_ids; i++) {
748 * Get all the Ids of each name
750 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
751 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
755 if (!make_id_list(buf, &id_list)) {
759 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
762 * Force all records to use the first id then delete the other ids
764 for (int j=1; j<id_list.num_ids; j++) {
765 char ed1[50], ed2[50];
766 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
767 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
771 db_sql_query(db, buf, NULL, NULL);
772 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
776 db_sql_query(db, buf, NULL, NULL);
780 free_name_list(&name_list);
783 static void eliminate_orphaned_jobmedia_records()
785 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
786 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
787 "WHERE Job.JobId IS NULL LIMIT 300000";
789 printf(_("Checking for orphaned JobMedia entries.\n"));
790 if (!make_id_list(query, &id_list)) {
794 * Loop doing 300000 at a time
796 while (id_list.num_ids != 0) {
797 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
798 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
799 for (int i=0; i < id_list.num_ids; i++) {
801 bsnprintf(buf, sizeof(buf),
802 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
803 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
804 edit_int64(id_list.Id[i], ed1));
805 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
806 printf("%s\n", db_strerror(db));
814 if (fix && id_list.num_ids > 0) {
815 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
816 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
818 break; /* get out if not updating db */
820 if (!make_id_list(query, &id_list)) {
826 static void eliminate_orphaned_file_records()
828 const char *query = "SELECT File.FileId,Job.JobId FROM File "
829 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
830 "WHERE Job.JobId IS NULL LIMIT 300000";
832 printf(_("Checking for orphaned File entries. This may take some time!\n"));
834 printf("%s\n", query);
836 if (!make_id_list(query, &id_list)) {
840 * Loop doing 300000 at a time
842 while (id_list.num_ids != 0) {
843 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
844 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
845 for (int i=0; i < id_list.num_ids; i++) {
847 bsnprintf(buf, sizeof(buf),
848 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
849 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
850 edit_int64(id_list.Id[i], ed1));
851 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
852 printf("%s\n", db_strerror(db));
859 if (fix && id_list.num_ids > 0) {
860 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
861 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
863 break; /* get out if not updating db */
865 if (!make_id_list(query, &id_list)) {
871 static void eliminate_orphaned_path_records()
875 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
876 db_int64_handler, &lctx);
878 if (lctx.count == 1) {
879 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
885 * Check the existence of the required "one column" index
887 if (!check_idx("PathId")) {
888 if (yes_no(_("Create temporary index? (yes/no): "))) {
890 * create temporary index PathId
892 create_tmp_idx("idxPIchk", "File", "PathId");
896 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
897 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
898 "WHERE File.PathId IS NULL LIMIT 300000";
900 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
902 printf("%s\n", query);
904 if (!make_id_list(query, &id_list)) {
908 * Loop doing 300000 at a time
910 while (id_list.num_ids != 0) {
911 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
912 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
913 for (int i=0; i < id_list.num_ids; i++) {
915 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
916 edit_int64(id_list.Id[i], ed1));
917 db_sql_query(db, buf, print_name_handler, NULL);
923 if (fix && id_list.num_ids > 0) {
924 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
925 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
927 break; /* get out if not updating db */
929 if (!make_id_list(query, &id_list)) {
934 * Drop temporary index idx_tmp_name
936 drop_tmp_idx("idxPIchk", "File");
939 static void eliminate_orphaned_filename_records()
943 * Check the existence of the required "one column" index
945 if (!check_idx("FilenameId") ) {
946 if (yes_no(_("Create temporary index? (yes/no): "))) {
948 * Create temporary index FilenameId
950 create_tmp_idx("idxFIchk", "File", "FilenameId");
954 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
955 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
956 "WHERE File.FilenameId IS NULL LIMIT 300000";
958 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
960 printf("%s\n", query);
962 if (!make_id_list(query, &id_list)) {
966 * Loop doing 300000 at a time
968 while (id_list.num_ids != 0) {
969 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
970 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
971 for (int i=0; i < id_list.num_ids; i++) {
973 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
974 edit_int64(id_list.Id[i], ed1));
975 db_sql_query(db, buf, print_name_handler, NULL);
981 if (fix && id_list.num_ids > 0) {
982 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
983 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
985 break; /* get out if not updating db */
987 if (!make_id_list(query, &id_list)) {
992 * Drop temporary index idx_tmp_name
994 drop_tmp_idx("idxFIchk", "File");
998 static void eliminate_orphaned_fileset_records()
1002 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1003 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1004 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1005 "WHERE Job.FileSetId IS NULL";
1007 printf("%s\n", query);
1009 if (!make_id_list(query, &id_list)) {
1012 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1013 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1014 for (int i=0; i < id_list.num_ids; i++) {
1016 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1017 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1018 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1019 printf("%s\n", db_strerror(db));
1026 if (fix && id_list.num_ids > 0) {
1027 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1028 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1032 static void eliminate_orphaned_client_records()
1036 printf(_("Checking for orphaned Client entries.\n"));
1039 * Wiffle through Client for every Client
1040 * joining with the Job table including every Client even if
1041 * there is not a match in Job (left outer join), then
1042 * filter out only those where no Job points to a Client
1043 * i.e. Job.Client is NULL
1045 query = "SELECT Client.ClientId,Client.Name FROM Client "
1046 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1047 "WHERE Job.ClientId IS NULL";
1049 printf("%s\n", query);
1051 if (!make_id_list(query, &id_list)) {
1054 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1055 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1056 for (int i=0; i < id_list.num_ids; i++) {
1058 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1059 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1060 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1061 printf("%s\n", db_strerror(db));
1068 if (fix && id_list.num_ids > 0) {
1069 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1070 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1074 static void eliminate_orphaned_job_records()
1078 printf(_("Checking for orphaned Job entries.\n"));
1081 * Wiffle through Job for every Job
1082 * joining with the Client table including every Job even if
1083 * there is not a match in Client (left outer join), then
1084 * filter out only those where no Client exists
1085 * i.e. Client.Name is NULL
1087 query = "SELECT Job.JobId,Job.Name FROM Job "
1088 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1089 "WHERE Client.Name IS NULL";
1091 printf("%s\n", query);
1093 if (!make_id_list(query, &id_list)) {
1096 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1097 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1098 for (int i=0; i < id_list.num_ids; i++) {
1100 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1101 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1102 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1103 printf("%s\n", db_strerror(db));
1110 if (fix && id_list.num_ids > 0) {
1111 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1112 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1113 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1114 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1115 printf(_("Deleting Log records of orphaned Job records.\n"));
1116 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1120 static void eliminate_admin_records()
1124 printf(_("Checking for Admin Job entries.\n"));
1125 query = "SELECT Job.JobId FROM Job "
1126 "WHERE Job.Type='D'";
1128 printf("%s\n", query);
1130 if (!make_id_list(query, &id_list)) {
1133 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1134 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1135 for (int i=0; i < id_list.num_ids; i++) {
1137 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1138 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1139 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1140 printf("%s\n", db_strerror(db));
1147 if (fix && id_list.num_ids > 0) {
1148 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1149 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1153 static void eliminate_restore_records()
1157 printf(_("Checking for Restore Job entries.\n"));
1158 query = "SELECT Job.JobId FROM Job "
1159 "WHERE Job.Type='R'";
1161 printf("%s\n", query);
1163 if (!make_id_list(query, &id_list)) {
1166 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1167 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1168 for (int i=0; i < id_list.num_ids; i++) {
1170 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1171 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1172 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1173 printf("%s\n", db_strerror(db));
1180 if (fix && id_list.num_ids > 0) {
1181 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1182 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1186 static void repair_bad_filenames()
1191 printf(_("Checking for Filenames with a trailing slash\n"));
1192 query = "SELECT FilenameId,Name from Filename "
1193 "WHERE Name LIKE '%/'";
1195 printf("%s\n", query);
1197 if (!make_id_list(query, &id_list)) {
1200 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1201 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1202 for (i=0; i < id_list.num_ids; i++) {
1204 bsnprintf(buf, sizeof(buf),
1205 "SELECT Name FROM Filename WHERE FilenameId=%s",
1206 edit_int64(id_list.Id[i], ed1));
1207 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1208 printf("%s\n", db_strerror(db));
1215 if (fix && id_list.num_ids > 0) {
1216 POOLMEM *name = get_pool_memory(PM_FNAME);
1217 char esc_name[5000];
1218 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1219 for (i=0; i < id_list.num_ids; i++) {
1222 bsnprintf(buf, sizeof(buf),
1223 "SELECT Name FROM Filename WHERE FilenameId=%s",
1224 edit_int64(id_list.Id[i], ed1));
1225 if (!db_sql_query(db, buf, get_name_handler, name)) {
1226 printf("%s\n", db_strerror(db));
1229 * Strip trailing slash(es)
1231 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1239 db_escape_string(NULL, db, esc_name, name, len);
1241 bsnprintf(buf, sizeof(buf),
1242 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1243 esc_name, edit_int64(id_list.Id[i], ed1));
1245 printf("%s\n", buf);
1247 db_sql_query(db, buf, NULL, NULL);
1249 free_pool_memory(name);
1253 static void repair_bad_paths()
1258 printf(_("Checking for Paths without a trailing slash\n"));
1259 query = "SELECT PathId,Path from Path "
1260 "WHERE Path NOT LIKE '%/'";
1262 printf("%s\n", query);
1264 if (!make_id_list(query, &id_list)) {
1267 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1268 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1269 for (i=0; i < id_list.num_ids; i++) {
1271 bsnprintf(buf, sizeof(buf),
1272 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1273 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1274 printf("%s\n", db_strerror(db));
1281 if (fix && id_list.num_ids > 0) {
1282 POOLMEM *name = get_pool_memory(PM_FNAME);
1283 char esc_name[5000];
1284 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1285 for (i=0; i < id_list.num_ids; i++) {
1288 bsnprintf(buf, sizeof(buf),
1289 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1290 if (!db_sql_query(db, buf, get_name_handler, name)) {
1291 printf("%s\n", db_strerror(db));
1294 * Strip trailing blanks
1296 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1300 * Add trailing slash
1302 len = pm_strcat(&name, "/");
1303 db_escape_string(NULL, db, esc_name, name, len);
1304 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1305 esc_name, edit_int64(id_list.Id[i], ed1));
1307 printf("%s\n", buf);
1309 db_sql_query(db, buf, NULL, NULL);
1311 free_pool_memory(name);
1316 * Gen next input command from the terminal
1318 static char *get_cmd(const char *prompt)
1320 static char cmd[1000];
1322 printf("%s", prompt);
1323 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1328 strip_trailing_junk(cmd);
1332 static bool yes_no(const char *prompt)
1335 cmd = get_cmd(prompt);
1340 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1343 bool python_set_prog(JCR*, char const*) { return false; }
1346 * The code below to add indexes is needed only for MySQL, and
1347 * that to improve the performance.
1351 typedef struct s_idx_list {
1353 int count_key; /* how many times the index meets *key_name */
1354 int count_col; /* how many times meets the desired column name */
1357 static IDX_LIST idx_list[MAXIDX];
1360 * Called here with each table index to be added to the list
1362 static int check_idx_handler(void *ctx, int num_fields, char **row)
1365 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1366 * File | 0 | PRIMARY | 1 | FileId |...
1368 char *name, *key_name, *col_name;
1375 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1376 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1377 idx_list[i].count_key++;
1379 if (strcasecmp(col_name, name) == 0) {
1380 idx_list[i].count_col++;
1386 * If the new Key_name, add it to the list
1389 len = strlen(key_name) + 1;
1390 idx_list[i].key_name = (char *)malloc(len);
1391 bstrncpy(idx_list[i].key_name, key_name, len);
1392 idx_list[i].count_key = 1;
1393 if (strcasecmp(col_name, name) == 0) {
1394 idx_list[i].count_col = 1;
1396 idx_list[i].count_col = 0;
1403 * Return TRUE if "one column" index over *col_name exists
1405 static bool check_idx(const char *col_name)
1409 const char *query = "SHOW INDEX FROM File";
1411 switch (db_get_type_index(db)) {
1412 case SQL_TYPE_MYSQL:
1413 memset(&idx_list, 0, sizeof(idx_list));
1414 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1415 printf("%s\n", db_strerror(db));
1417 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1419 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1421 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1423 * "one column" index over *col_name found
1430 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1433 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1442 * Create temporary one-column index
1444 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1445 const char *col_name)
1447 idx_tmp_name = NULL;
1448 printf(_("Create temporary index... This may take some time!\n"));
1449 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1451 printf("%s\n", buf);
1453 if (db_sql_query(db, buf, NULL, NULL)) {
1454 idx_tmp_name = idx_name;
1456 printf(_("Temporary index created.\n"));
1459 printf("%s\n", db_strerror(db));
1466 * Drop temporary index
1468 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1470 if (idx_tmp_name != NULL) {
1471 printf(_("Drop temporary index.\n"));
1472 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1474 printf("%s\n", buf);
1476 if (!db_sql_query(db, buf, NULL, NULL)) {
1477 printf("%s\n", db_strerror(db));
1481 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1485 idx_tmp_name = NULL;