2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2011 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
30 * Program to check a Bacula database for consistency and to
33 * Kern E. Sibbald, August 2002
38 #include "cats/cats.h"
39 #include "cats/sql_glue.h"
40 #include "lib/runscript.h"
41 #include "dird/dird_conf.h"
43 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
48 int generate_daemon_event(JCR *jcr, const char *event)
51 typedef struct s_id_ctx {
52 int64_t *Id; /* ids to be modified */
53 int num_ids; /* ids stored */
54 int max_ids; /* size of array */
55 int num_del; /* number deleted */
56 int tot_ids; /* total to process */
59 typedef struct s_name_ctx {
60 char **name; /* list of names */
61 int num_ids; /* ids stored */
62 int max_ids; /* size of array */
63 int num_del; /* number deleted */
64 int tot_ids; /* total to process */
70 static bool fix = false;
71 static bool batch = false;
73 static ID_LIST id_list;
74 static NAME_LIST name_list;
75 static char buf[20000];
76 static bool quit = false;
77 static CONFIG *config;
78 static const char *idx_tmp_name;
80 #define MAX_ID_LIST_LEN 10000000
83 * Forward referenced functions
85 static void print_catalog_details(CAT *catalog, const char *working_dir);
86 static int make_id_list(const char *query, ID_LIST *id_list);
87 static int delete_id_list(const char *query, ID_LIST *id_list);
88 static int make_name_list(const char *query, NAME_LIST *name_list);
89 static void print_name_list(NAME_LIST *name_list);
90 static void free_name_list(NAME_LIST *name_list);
91 static char *get_cmd(const char *prompt);
92 static void eliminate_duplicate_filenames();
93 static void eliminate_duplicate_paths();
94 static void eliminate_orphaned_jobmedia_records();
95 static void eliminate_orphaned_file_records();
96 static void eliminate_orphaned_path_records();
97 static void eliminate_orphaned_filename_records();
98 static void eliminate_orphaned_fileset_records();
99 static void eliminate_orphaned_client_records();
100 static void eliminate_orphaned_job_records();
101 static void eliminate_admin_records();
102 static void eliminate_restore_records();
103 static void repair_bad_paths();
104 static void repair_bad_filenames();
105 static void do_interactive_mode();
106 static bool yes_no(const char *prompt);
107 static bool check_idx(const char *col_name);
108 static bool create_tmp_idx(const char *idx_name, const char *table_name,
109 const char *col_name);
110 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
112 static int check_idx_handler(void *ctx, int num_fields, char **row);
118 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
120 " -C catalog name in the director conf file\n"
121 " -c Director conf filename\n"
122 " -B print catalog configuration and exit\n"
123 " -d <nn> set debug level to <nn>\n"
124 " -dt print a timestamp in debug output\n"
125 " -f fix inconsistencies\n"
126 " -t test if client library is thread-safe\n"
128 " -? print this message\n\n");
132 int main (int argc, char *argv[])
135 const char *user, *password, *db_name, *dbhost;
137 bool print_catalog=false;
138 char *configfile = NULL;
139 char *catalogname = NULL;
142 setlocale(LC_ALL, "");
143 bindtextdomain("bacula", LOCALEDIR);
144 textdomain("bacula");
147 my_name_is(argc, argv, "dbcheck");
148 init_msg(NULL, NULL); /* setup message handler */
150 memset(&id_list, 0, sizeof(id_list));
151 memset(&name_list, 0, sizeof(name_list));
153 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
156 print_catalog = true; /* get catalog information from config */
158 case 'b': /* batch */
161 case 'C': /* CatalogName */
162 catalogname = optarg;
164 case 'c': /* configfile */
167 case 'd': /* debug level */
168 if (*optarg == 't') {
169 dbg_timestamp = true;
171 debug_level = atoi(optarg);
172 if (debug_level <= 0) {
177 case 'f': /* fix inconsistencies */
197 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
199 config = new_config_parser();
200 parse_dir_config(config, configfile, M_ERROR_TERM);
202 foreach_res(catalog, R_CATALOG) {
203 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
206 } else if (!catalogname) { // stop on first if no catalogname is given
214 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
216 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
222 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
225 Pmsg0(0, _("Error no Director resource defined.\n"));
228 set_working_directory(director->working_directory);
231 * Print catalog information and exit (-B)
234 print_catalog_details(catalog, director->working_directory);
238 db_name = catalog->db_name;
239 user = catalog->db_user;
240 password = catalog->db_password;
241 dbhost = catalog->db_address;
242 if (dbhost && dbhost[0] == 0) {
245 dbport = catalog->db_port;
249 Pmsg0(0, _("Wrong number of arguments.\n"));
254 Pmsg0(0, _("Working directory not supplied.\n"));
259 * This is needed by SQLite to find the db
261 working_directory = argv[0];
270 } else if (argc == 3) {
273 } else if (argc == 4) {
277 } else if (argc == 5) {
282 } else if (argc == 6) {
288 dbport = strtol(argv[5], &endptr, 10);
289 if (*endptr != '\0') {
290 Pmsg0(0, _("Database port must be a numeric value.\n"));
292 } else if (errno == ERANGE) {
293 Pmsg0(0, _("Database port must be a int value.\n"));
302 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
303 if (!db_open_database(NULL, db)) {
304 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
309 * Drop temporary index idx_tmp_name if it already exists
311 drop_tmp_idx("idxPIchk", "File");
315 repair_bad_filenames();
316 eliminate_duplicate_filenames();
317 eliminate_duplicate_paths();
318 eliminate_orphaned_jobmedia_records();
319 eliminate_orphaned_file_records();
320 eliminate_orphaned_path_records();
321 eliminate_orphaned_filename_records();
322 eliminate_orphaned_fileset_records();
323 eliminate_orphaned_client_records();
324 eliminate_orphaned_job_records();
325 eliminate_admin_records();
326 eliminate_restore_records();
328 do_interactive_mode();
332 * Drop temporary index idx_tmp_name
334 drop_tmp_idx("idxPIchk", "File");
336 db_close_database(NULL, db);
343 static void print_catalog_details(CAT *catalog, const char *working_dir)
345 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
348 * Instantiate a B_DB class and see what db_type gets assigned to it.
350 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
351 catalog->db_password, catalog->db_address,
352 catalog->db_port, catalog->db_socket,
353 catalog->mult_db_connections,
354 catalog->disable_batch_insert);
356 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(buf),
357 db->db_get_type(), working_directory);
358 db_close_database(NULL, db);
360 free_pool_memory(buf);
363 static void do_interactive_mode()
367 printf(_("Hello, this is the database check/correct program.\n"));
369 printf(_("Modify database is on."));
371 printf(_("Modify database is off."));
373 printf(_(" Verbose is on.\n"));
375 printf(_(" Verbose is off.\n"));
377 printf(_("Please select the function you want to perform.\n"));
382 " 1) Toggle modify database flag\n"
383 " 2) Toggle verbose flag\n"
384 " 3) Repair bad Filename records\n"
385 " 4) Repair bad Path records\n"
386 " 5) Eliminate duplicate Filename records\n"
387 " 6) Eliminate duplicate Path records\n"
388 " 7) Eliminate orphaned Jobmedia records\n"
389 " 8) Eliminate orphaned File records\n"
390 " 9) Eliminate orphaned Path records\n"
391 " 10) Eliminate orphaned Filename records\n"
392 " 11) Eliminate orphaned FileSet records\n"
393 " 12) Eliminate orphaned Client records\n"
394 " 13) Eliminate orphaned Job records\n"
395 " 14) Eliminate all Admin records\n"
396 " 15) Eliminate all Restore records\n"
401 " 1) Toggle modify database flag\n"
402 " 2) Toggle verbose flag\n"
403 " 3) Check for bad Filename records\n"
404 " 4) Check for bad Path records\n"
405 " 5) Check for duplicate Filename records\n"
406 " 6) Check for duplicate Path records\n"
407 " 7) Check for orphaned Jobmedia records\n"
408 " 8) Check for orphaned File records\n"
409 " 9) Check for orphaned Path records\n"
410 " 10) Check for orphaned Filename records\n"
411 " 11) Check for orphaned FileSet records\n"
412 " 12) Check for orphaned Client records\n"
413 " 13) Check for orphaned Job records\n"
414 " 14) Check for all Admin records\n"
415 " 15) Check for all Restore records\n"
420 cmd = get_cmd(_("Select function number: "));
422 int item = atoi(cmd);
427 printf(_("Database will be modified.\n"));
429 printf(_("Database will NOT be modified.\n"));
432 verbose = verbose?0:1;
434 printf(_(" Verbose is on.\n"));
436 printf(_(" Verbose is off.\n"));
439 repair_bad_filenames();
445 eliminate_duplicate_filenames();
448 eliminate_duplicate_paths();
451 eliminate_orphaned_jobmedia_records();
454 eliminate_orphaned_file_records();
457 eliminate_orphaned_path_records();
460 eliminate_orphaned_filename_records();
463 eliminate_orphaned_fileset_records();
466 eliminate_orphaned_client_records();
469 eliminate_orphaned_job_records();
472 eliminate_admin_records();
475 eliminate_restore_records();
478 repair_bad_filenames();
480 eliminate_duplicate_filenames();
481 eliminate_duplicate_paths();
482 eliminate_orphaned_jobmedia_records();
483 eliminate_orphaned_file_records();
484 eliminate_orphaned_path_records();
485 eliminate_orphaned_filename_records();
486 eliminate_orphaned_fileset_records();
487 eliminate_orphaned_client_records();
488 eliminate_orphaned_job_records();
489 eliminate_admin_records();
490 eliminate_restore_records();
500 static int print_name_handler(void *ctx, int num_fields, char **row)
503 printf("%s\n", row[0]);
508 static int get_name_handler(void *ctx, int num_fields, char **row)
510 POOLMEM *buf = (POOLMEM *)ctx;
512 pm_strcpy(&buf, row[0]);
517 static int print_job_handler(void *ctx, int num_fields, char **row)
519 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
520 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
524 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
526 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
527 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
531 static int print_file_handler(void *ctx, int num_fields, char **row)
533 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
534 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
538 static int print_fileset_handler(void *ctx, int num_fields, char **row)
540 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
541 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
545 static int print_client_handler(void *ctx, int num_fields, char **row)
547 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
548 NPRT(row[0]), NPRT(row[1]));
553 * Called here with each id to be added to the list
555 static int id_list_handler(void *ctx, int num_fields, char **row)
557 ID_LIST *lst = (ID_LIST *)ctx;
559 if (lst->num_ids == MAX_ID_LIST_LEN) {
562 if (lst->num_ids == lst->max_ids) {
563 if (lst->max_ids == 0) {
564 lst->max_ids = 10000;
565 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
567 lst->max_ids = (lst->max_ids * 3) / 2;
568 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
571 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
576 * Construct record id list
578 static int make_id_list(const char *query, ID_LIST *id_list)
580 id_list->num_ids = 0;
581 id_list->num_del = 0;
582 id_list->tot_ids = 0;
584 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
585 printf("%s", db_strerror(db));
592 * Delete all entries in the list
594 static int delete_id_list(const char *query, ID_LIST *id_list)
597 for (int i=0; i < id_list->num_ids; i++) {
598 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
600 printf(_("Deleting: %s\n"), buf);
602 db_sql_query(db, buf, NULL, NULL);
608 * Called here with each name to be added to the list
610 static int name_list_handler(void *ctx, int num_fields, char **row)
612 NAME_LIST *name = (NAME_LIST *)ctx;
614 if (name->num_ids == MAX_ID_LIST_LEN) {
617 if (name->num_ids == name->max_ids) {
618 if (name->max_ids == 0) {
619 name->max_ids = 10000;
620 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
622 name->max_ids = (name->max_ids * 3) / 2;
623 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
626 name->name[name->num_ids++] = bstrdup(row[0]);
631 * Construct name list
633 static int make_name_list(const char *query, NAME_LIST *name_list)
635 name_list->num_ids = 0;
636 name_list->num_del = 0;
637 name_list->tot_ids = 0;
639 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
640 printf("%s", db_strerror(db));
647 * Print names in the list
649 static void print_name_list(NAME_LIST *name_list)
651 for (int i=0; i < name_list->num_ids; i++) {
652 printf("%s\n", name_list->name[i]);
657 * Free names in the list
659 static void free_name_list(NAME_LIST *name_list)
661 for (int i=0; i < name_list->num_ids; i++) {
662 free(name_list->name[i]);
664 name_list->num_ids = 0;
667 static void eliminate_duplicate_filenames()
672 printf(_("Checking for duplicate Filename entries.\n"));
675 * Make list of duplicated names
677 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
678 "HAVING count(Name) > 1";
680 if (!make_name_list(query, &name_list)) {
683 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
684 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
685 print_name_list(&name_list);
692 * Loop through list of duplicate names
694 for (int i=0; i<name_list.num_ids; i++) {
696 * Get all the Ids of each name
698 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
699 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
703 if (!make_id_list(buf, &id_list)) {
707 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
710 * Force all records to use the first id then delete the other ids
712 for (int j=1; j<id_list.num_ids; j++) {
713 char ed1[50], ed2[50];
714 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
715 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
719 db_sql_query(db, buf, NULL, NULL);
720 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
725 db_sql_query(db, buf, NULL, NULL);
729 free_name_list(&name_list);
732 static void eliminate_duplicate_paths()
737 printf(_("Checking for duplicate Path entries.\n"));
740 * Make list of duplicated names
742 query = "SELECT Path, count(Path) as Count FROM Path "
743 "GROUP BY Path HAVING count(Path) > 1";
745 if (!make_name_list(query, &name_list)) {
748 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
749 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
750 print_name_list(&name_list);
757 * Loop through list of duplicate names
759 for (int i=0; i<name_list.num_ids; i++) {
761 * Get all the Ids of each name
763 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
764 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
768 if (!make_id_list(buf, &id_list)) {
772 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
775 * Force all records to use the first id then delete the other ids
777 for (int j=1; j<id_list.num_ids; j++) {
778 char ed1[50], ed2[50];
779 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
780 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
784 db_sql_query(db, buf, NULL, NULL);
785 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
789 db_sql_query(db, buf, NULL, NULL);
793 free_name_list(&name_list);
796 static void eliminate_orphaned_jobmedia_records()
798 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
799 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
800 "WHERE Job.JobId IS NULL LIMIT 300000";
802 printf(_("Checking for orphaned JobMedia entries.\n"));
803 if (!make_id_list(query, &id_list)) {
807 * Loop doing 300000 at a time
809 while (id_list.num_ids != 0) {
810 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
811 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
812 for (int i=0; i < id_list.num_ids; i++) {
814 bsnprintf(buf, sizeof(buf),
815 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
816 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
817 edit_int64(id_list.Id[i], ed1));
818 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
819 printf("%s\n", db_strerror(db));
827 if (fix && id_list.num_ids > 0) {
828 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
829 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
831 break; /* get out if not updating db */
833 if (!make_id_list(query, &id_list)) {
839 static void eliminate_orphaned_file_records()
841 const char *query = "SELECT File.FileId,Job.JobId FROM File "
842 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
843 "WHERE Job.JobId IS NULL LIMIT 300000";
845 printf(_("Checking for orphaned File entries. This may take some time!\n"));
847 printf("%s\n", query);
849 if (!make_id_list(query, &id_list)) {
853 * Loop doing 300000 at a time
855 while (id_list.num_ids != 0) {
856 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
857 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
858 for (int i=0; i < id_list.num_ids; i++) {
860 bsnprintf(buf, sizeof(buf),
861 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
862 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
863 edit_int64(id_list.Id[i], ed1));
864 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
865 printf("%s\n", db_strerror(db));
872 if (fix && id_list.num_ids > 0) {
873 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
874 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
876 break; /* get out if not updating db */
878 if (!make_id_list(query, &id_list)) {
884 static void eliminate_orphaned_path_records()
888 * Check the existence of the required "one column" index
890 if (!check_idx("PathId")) {
891 if (yes_no(_("Create temporary index? (yes/no): "))) {
893 * create temporary index PathId
895 create_tmp_idx("idxPIchk", "File", "PathId");
899 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
900 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
901 "WHERE File.PathId IS NULL LIMIT 300000";
903 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
905 printf("%s\n", query);
907 if (!make_id_list(query, &id_list)) {
911 * Loop doing 300000 at a time
913 while (id_list.num_ids != 0) {
914 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
915 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
916 for (int i=0; i < id_list.num_ids; i++) {
918 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
919 edit_int64(id_list.Id[i], ed1));
920 db_sql_query(db, buf, print_name_handler, NULL);
926 if (fix && id_list.num_ids > 0) {
927 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
928 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
930 break; /* get out if not updating db */
932 if (!make_id_list(query, &id_list)) {
937 * Drop temporary index idx_tmp_name
939 drop_tmp_idx("idxPIchk", "File");
942 static void eliminate_orphaned_filename_records()
946 * Check the existence of the required "one column" index
948 if (!check_idx("FilenameId") ) {
949 if (yes_no(_("Create temporary index? (yes/no): "))) {
951 * Create temporary index FilenameId
953 create_tmp_idx("idxFIchk", "File", "FilenameId");
957 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
958 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
959 "WHERE File.FilenameId IS NULL LIMIT 300000";
961 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
963 printf("%s\n", query);
965 if (!make_id_list(query, &id_list)) {
969 * Loop doing 300000 at a time
971 while (id_list.num_ids != 0) {
972 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
973 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
974 for (int i=0; i < id_list.num_ids; i++) {
976 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
977 edit_int64(id_list.Id[i], ed1));
978 db_sql_query(db, buf, print_name_handler, NULL);
984 if (fix && id_list.num_ids > 0) {
985 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
986 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
988 break; /* get out if not updating db */
990 if (!make_id_list(query, &id_list)) {
995 * Drop temporary index idx_tmp_name
997 drop_tmp_idx("idxFIchk", "File");
1001 static void eliminate_orphaned_fileset_records()
1005 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1006 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1007 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1008 "WHERE Job.FileSetId IS NULL";
1010 printf("%s\n", query);
1012 if (!make_id_list(query, &id_list)) {
1015 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1016 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1017 for (int i=0; i < id_list.num_ids; i++) {
1019 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1020 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1021 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1022 printf("%s\n", db_strerror(db));
1029 if (fix && id_list.num_ids > 0) {
1030 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1031 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1035 static void eliminate_orphaned_client_records()
1039 printf(_("Checking for orphaned Client entries.\n"));
1042 * Wiffle through Client for every Client
1043 * joining with the Job table including every Client even if
1044 * there is not a match in Job (left outer join), then
1045 * filter out only those where no Job points to a Client
1046 * i.e. Job.Client is NULL
1048 query = "SELECT Client.ClientId,Client.Name FROM Client "
1049 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1050 "WHERE Job.ClientId IS NULL";
1052 printf("%s\n", query);
1054 if (!make_id_list(query, &id_list)) {
1057 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1058 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1059 for (int i=0; i < id_list.num_ids; i++) {
1061 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1062 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1063 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1064 printf("%s\n", db_strerror(db));
1071 if (fix && id_list.num_ids > 0) {
1072 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1073 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1077 static void eliminate_orphaned_job_records()
1081 printf(_("Checking for orphaned Job entries.\n"));
1084 * Wiffle through Job for every Job
1085 * joining with the Client table including every Job even if
1086 * there is not a match in Client (left outer join), then
1087 * filter out only those where no Client exists
1088 * i.e. Client.Name is NULL
1090 query = "SELECT Job.JobId,Job.Name FROM Job "
1091 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1092 "WHERE Client.Name IS NULL";
1094 printf("%s\n", query);
1096 if (!make_id_list(query, &id_list)) {
1099 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1100 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1101 for (int i=0; i < id_list.num_ids; i++) {
1103 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1104 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1105 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1106 printf("%s\n", db_strerror(db));
1113 if (fix && id_list.num_ids > 0) {
1114 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1115 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1116 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1117 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1118 printf(_("Deleting Log records of orphaned Job records.\n"));
1119 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1123 static void eliminate_admin_records()
1127 printf(_("Checking for Admin Job entries.\n"));
1128 query = "SELECT Job.JobId FROM Job "
1129 "WHERE Job.Type='D'";
1131 printf("%s\n", query);
1133 if (!make_id_list(query, &id_list)) {
1136 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1137 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1138 for (int i=0; i < id_list.num_ids; i++) {
1140 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1141 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1142 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1143 printf("%s\n", db_strerror(db));
1150 if (fix && id_list.num_ids > 0) {
1151 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1152 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1156 static void eliminate_restore_records()
1160 printf(_("Checking for Restore Job entries.\n"));
1161 query = "SELECT Job.JobId FROM Job "
1162 "WHERE Job.Type='R'";
1164 printf("%s\n", query);
1166 if (!make_id_list(query, &id_list)) {
1169 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1170 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1171 for (int i=0; i < id_list.num_ids; i++) {
1173 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1174 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1175 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1176 printf("%s\n", db_strerror(db));
1183 if (fix && id_list.num_ids > 0) {
1184 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1185 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1189 static void repair_bad_filenames()
1194 printf(_("Checking for Filenames with a trailing slash\n"));
1195 query = "SELECT FilenameId,Name from Filename "
1196 "WHERE Name LIKE '%/'";
1198 printf("%s\n", query);
1200 if (!make_id_list(query, &id_list)) {
1203 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1204 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1205 for (i=0; i < id_list.num_ids; i++) {
1207 bsnprintf(buf, sizeof(buf),
1208 "SELECT Name FROM Filename WHERE FilenameId=%s",
1209 edit_int64(id_list.Id[i], ed1));
1210 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1211 printf("%s\n", db_strerror(db));
1218 if (fix && id_list.num_ids > 0) {
1219 POOLMEM *name = get_pool_memory(PM_FNAME);
1220 char esc_name[5000];
1221 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1222 for (i=0; i < id_list.num_ids; i++) {
1225 bsnprintf(buf, sizeof(buf),
1226 "SELECT Name FROM Filename WHERE FilenameId=%s",
1227 edit_int64(id_list.Id[i], ed1));
1228 if (!db_sql_query(db, buf, get_name_handler, name)) {
1229 printf("%s\n", db_strerror(db));
1232 * Strip trailing slash(es)
1234 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1242 db_escape_string(NULL, db, esc_name, name, len);
1244 bsnprintf(buf, sizeof(buf),
1245 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1246 esc_name, edit_int64(id_list.Id[i], ed1));
1248 printf("%s\n", buf);
1250 db_sql_query(db, buf, NULL, NULL);
1255 static void repair_bad_paths()
1260 printf(_("Checking for Paths without a trailing slash\n"));
1261 query = "SELECT PathId,Path from Path "
1262 "WHERE Path NOT LIKE '%/'";
1264 printf("%s\n", query);
1266 if (!make_id_list(query, &id_list)) {
1269 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1270 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1271 for (i=0; i < id_list.num_ids; i++) {
1273 bsnprintf(buf, sizeof(buf),
1274 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1275 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1276 printf("%s\n", db_strerror(db));
1283 if (fix && id_list.num_ids > 0) {
1284 POOLMEM *name = get_pool_memory(PM_FNAME);
1285 char esc_name[5000];
1286 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1287 for (i=0; i < id_list.num_ids; i++) {
1290 bsnprintf(buf, sizeof(buf),
1291 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1292 if (!db_sql_query(db, buf, get_name_handler, name)) {
1293 printf("%s\n", db_strerror(db));
1296 * Strip trailing blanks
1298 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1302 * Add trailing slash
1304 len = pm_strcat(&name, "/");
1305 db_escape_string(NULL, db, esc_name, name, len);
1306 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1307 esc_name, edit_int64(id_list.Id[i], ed1));
1309 printf("%s\n", buf);
1311 db_sql_query(db, buf, NULL, NULL);
1317 * Gen next input command from the terminal
1319 static char *get_cmd(const char *prompt)
1321 static char cmd[1000];
1323 printf("%s", prompt);
1324 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1329 strip_trailing_junk(cmd);
1333 static bool yes_no(const char *prompt)
1336 cmd = get_cmd(prompt);
1341 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1344 bool python_set_prog(JCR*, char const*) { return false; }
1347 * The code below to add indexes is needed only for MySQL, and
1348 * that to improve the performance.
1353 typedef struct s_idx_list {
1355 int count_key; /* how many times the index meets *key_name */
1356 int count_col; /* how many times meets the desired column name */
1359 static IDX_LIST idx_list[MAXIDX];
1362 * Called here with each table index to be added to the list
1364 static int check_idx_handler(void *ctx, int num_fields, char **row)
1367 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1368 * File | 0 | PRIMARY | 1 | FileId |...
1370 char *name, *key_name, *col_name;
1376 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1377 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1378 idx_list[i].count_key++;
1380 if (strcasecmp(col_name, name) == 0) {
1381 idx_list[i].count_col++;
1387 * If the new Key_name, add it to the list
1390 len = strlen(key_name) + 1;
1391 idx_list[i].key_name = (char *)malloc(len);
1392 bstrncpy(idx_list[i].key_name, key_name, len);
1393 idx_list[i].count_key = 1;
1394 if (strcasecmp(col_name, name) == 0) {
1395 idx_list[i].count_col = 1;
1397 idx_list[i].count_col = 0;
1405 * Return TRUE if "one column" index over *col_name exists
1407 static bool check_idx(const char *col_name)
1413 memset(&idx_list, 0, sizeof(idx_list));
1414 const char *query = "SHOW INDEX FROM File";
1415 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1416 printf("%s\n", db_strerror(db));
1419 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1421 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1423 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1425 * "one column" index over *col_name found
1432 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1435 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1445 * Create temporary one-column index
1447 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1448 const char *col_name)
1450 idx_tmp_name = NULL;
1451 printf(_("Create temporary index... This may take some time!\n"));
1452 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1454 printf("%s\n", buf);
1456 if (db_sql_query(db, buf, NULL, NULL)) {
1457 idx_tmp_name = idx_name;
1459 printf(_("Temporary index created.\n"));
1462 printf("%s\n", db_strerror(db));
1469 * Drop temporary index
1471 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1473 if (idx_tmp_name != NULL) {
1474 printf(_("Drop temporary index.\n"));
1475 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1477 printf("%s\n", buf);
1479 if (!db_sql_query(db, buf, NULL, NULL)) {
1480 printf("%s\n", db_strerror(db));
1484 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1488 idx_tmp_name = NULL;