2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2009 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
40 #include "cats/cats.h"
41 #include "cats/sql_glue.h"
42 #include "lib/runscript.h"
43 #include "dird/dird_conf.h"
45 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 */
67 /* Global variables */
68 static bool fix = false;
69 static bool batch = false;
71 static ID_LIST id_list;
72 static NAME_LIST name_list;
73 static char buf[20000];
74 static bool quit = false;
75 static CONFIG *config;
77 #define MAX_ID_LIST_LEN 10000000
79 /* Forward referenced functions */
80 static int make_id_list(const char *query, ID_LIST *id_list);
81 static int delete_id_list(const char *query, ID_LIST *id_list);
82 static int make_name_list(const char *query, NAME_LIST *name_list);
83 static void print_name_list(NAME_LIST *name_list);
84 static void free_name_list(NAME_LIST *name_list);
85 static char *get_cmd(const char *prompt);
86 static void eliminate_duplicate_filenames();
87 static void eliminate_duplicate_paths();
88 static void eliminate_orphaned_jobmedia_records();
89 static void eliminate_orphaned_file_records();
90 static void eliminate_orphaned_path_records();
91 static void eliminate_orphaned_filename_records();
92 static void eliminate_orphaned_fileset_records();
93 static void eliminate_orphaned_client_records();
94 static void eliminate_orphaned_job_records();
95 static void eliminate_admin_records();
96 static void eliminate_restore_records();
97 static void repair_bad_paths();
98 static void repair_bad_filenames();
99 static void do_interactive_mode();
100 static bool yes_no(const char *prompt);
101 static bool check_idx(const char *col_name);
102 static bool create_tmp_idx(const char *idx_name, const char *table_name,
103 const char *col_name);
104 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
106 static int check_idx_handler(void *ctx, int num_fields, char **row);
110 /* Global variables */
111 static const char *idx_tmp_name;
116 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
118 " -C catalog name in the director conf file\n"
119 " -c Director conf filename\n"
120 " -B print catalog configuration and exit\n"
121 " -d <nn> set debug level to <nn>\n"
122 " -dt print a timestamp in debug output\n"
123 " -f fix inconsistencies\n"
124 " -t test if client library is thread-safe\n"
126 " -? print this message\n\n");
130 int main (int argc, char *argv[])
133 const char *user, *password, *db_name, *dbhost;
135 bool print_catalog=false;
136 char *configfile = NULL;
137 char *catalogname = NULL;
140 setlocale(LC_ALL, "");
141 bindtextdomain("bacula", LOCALEDIR);
142 textdomain("bacula");
145 my_name_is(argc, argv, "dbcheck");
146 init_msg(NULL, NULL); /* setup message handler */
148 memset(&id_list, 0, sizeof(id_list));
149 memset(&name_list, 0, sizeof(name_list));
151 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
154 print_catalog = true; /* get catalog information from config */
157 case 'b': /* batch */
161 case 'C': /* CatalogName */
162 catalogname = optarg;
165 case 'c': /* configfile */
169 case 'd': /* debug level */
170 if (*optarg == 't') {
171 dbg_timestamp = true;
173 debug_level = atoi(optarg);
174 if (debug_level <= 0) {
180 case 'f': /* fix inconsistencies */
202 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
204 config = new_config_parser();
205 parse_dir_config(config, configfile, M_ERROR_TERM);
207 foreach_res(catalog, R_CATALOG) {
208 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
211 } else if (!catalogname) { // stop on first if no catalogname is given
219 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
221 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
227 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
230 Pmsg0(0, _("Error no Director resource defined.\n"));
233 set_working_directory(director->working_directory);
235 /* Print catalog information and exit (-B) */
237 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
238 printf("%s\nworking_dir=%s\n", catalog->display(buf),
240 free_pool_memory(buf);
244 db_name = catalog->db_name;
245 user = catalog->db_user;
246 password = catalog->db_password;
247 dbhost = catalog->db_address;
248 if (dbhost && dbhost[0] == 0) {
251 dbport = catalog->db_port;
255 Pmsg0(0, _("Wrong number of arguments.\n"));
260 Pmsg0(0, _("Working directory not supplied.\n"));
264 /* This is needed by SQLite to find the db */
265 working_directory = argv[0];
274 } else if (argc == 3) {
277 } else if (argc == 4) {
281 } else if (argc == 5) {
286 } else if (argc == 6) {
292 dbport = strtol(argv[5], &endptr, 10);
293 if (*endptr != '\0') {
294 Pmsg0(0, _("Database port must be a numeric value.\n"));
296 } else if (errno == ERANGE) {
297 Pmsg0(0, _("Database port must be a int value.\n"));
304 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
305 if (!db_open_database(NULL, db)) {
306 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
312 repair_bad_filenames();
313 eliminate_duplicate_filenames();
314 eliminate_duplicate_paths();
315 eliminate_orphaned_jobmedia_records();
316 eliminate_orphaned_file_records();
317 eliminate_orphaned_path_records();
318 eliminate_orphaned_filename_records();
319 eliminate_orphaned_fileset_records();
320 eliminate_orphaned_client_records();
321 eliminate_orphaned_job_records();
322 eliminate_admin_records();
323 eliminate_restore_records();
325 do_interactive_mode();
328 db_close_database(NULL, db);
335 static void do_interactive_mode()
339 printf(_("Hello, this is the database check/correct program.\n"));
341 printf(_("Modify database is on."));
343 printf(_("Modify database is off."));
345 printf(_(" Verbose is on.\n"));
347 printf(_(" Verbose is off.\n"));
349 printf(_("Please select the function you want to perform.\n"));
354 " 1) Toggle modify database flag\n"
355 " 2) Toggle verbose flag\n"
356 " 3) Repair bad Filename records\n"
357 " 4) Repair bad Path records\n"
358 " 5) Eliminate duplicate Filename records\n"
359 " 6) Eliminate duplicate Path records\n"
360 " 7) Eliminate orphaned Jobmedia records\n"
361 " 8) Eliminate orphaned File records\n"
362 " 9) Eliminate orphaned Path records\n"
363 " 10) Eliminate orphaned Filename records\n"
364 " 11) Eliminate orphaned FileSet records\n"
365 " 12) Eliminate orphaned Client records\n"
366 " 13) Eliminate orphaned Job records\n"
367 " 14) Eliminate all Admin records\n"
368 " 15) Eliminate all Restore records\n"
373 " 1) Toggle modify database flag\n"
374 " 2) Toggle verbose flag\n"
375 " 3) Check for bad Filename records\n"
376 " 4) Check for bad Path records\n"
377 " 5) Check for duplicate Filename records\n"
378 " 6) Check for duplicate Path records\n"
379 " 7) Check for orphaned Jobmedia records\n"
380 " 8) Check for orphaned File records\n"
381 " 9) Check for orphaned Path records\n"
382 " 10) Check for orphaned Filename records\n"
383 " 11) Check for orphaned FileSet records\n"
384 " 12) Check for orphaned Client records\n"
385 " 13) Check for orphaned Job records\n"
386 " 14) Check for all Admin records\n"
387 " 15) Check for all Restore records\n"
392 cmd = get_cmd(_("Select function number: "));
394 int item = atoi(cmd);
399 printf(_("Database will be modified.\n"));
401 printf(_("Database will NOT be modified.\n"));
404 verbose = verbose?0:1;
406 printf(_(" Verbose is on.\n"));
408 printf(_(" Verbose is off.\n"));
411 repair_bad_filenames();
417 eliminate_duplicate_filenames();
420 eliminate_duplicate_paths();
423 eliminate_orphaned_jobmedia_records();
426 eliminate_orphaned_file_records();
429 eliminate_orphaned_path_records();
432 eliminate_orphaned_filename_records();
435 eliminate_orphaned_fileset_records();
438 eliminate_orphaned_client_records();
441 eliminate_orphaned_job_records();
444 eliminate_admin_records();
447 eliminate_restore_records();
450 repair_bad_filenames();
452 eliminate_duplicate_filenames();
453 eliminate_duplicate_paths();
454 eliminate_orphaned_jobmedia_records();
455 eliminate_orphaned_file_records();
456 eliminate_orphaned_path_records();
457 eliminate_orphaned_filename_records();
458 eliminate_orphaned_fileset_records();
459 eliminate_orphaned_client_records();
460 eliminate_orphaned_job_records();
461 eliminate_admin_records();
462 eliminate_restore_records();
472 static int print_name_handler(void *ctx, int num_fields, char **row)
475 printf("%s\n", row[0]);
480 static int get_name_handler(void *ctx, int num_fields, char **row)
482 POOLMEM *buf = (POOLMEM *)ctx;
484 pm_strcpy(&buf, row[0]);
489 static int print_job_handler(void *ctx, int num_fields, char **row)
491 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
492 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
497 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
499 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
500 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
504 static int print_file_handler(void *ctx, int num_fields, char **row)
506 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
507 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
511 static int print_fileset_handler(void *ctx, int num_fields, char **row)
513 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
514 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
518 static int print_client_handler(void *ctx, int num_fields, char **row)
520 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
521 NPRT(row[0]), NPRT(row[1]));
527 * Called here with each id to be added to the list
529 static int id_list_handler(void *ctx, int num_fields, char **row)
531 ID_LIST *lst = (ID_LIST *)ctx;
533 if (lst->num_ids == MAX_ID_LIST_LEN) {
536 if (lst->num_ids == lst->max_ids) {
537 if (lst->max_ids == 0) {
538 lst->max_ids = 10000;
539 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
541 lst->max_ids = (lst->max_ids * 3) / 2;
542 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
545 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
550 * Construct record id list
552 static int make_id_list(const char *query, ID_LIST *id_list)
554 id_list->num_ids = 0;
555 id_list->num_del = 0;
556 id_list->tot_ids = 0;
558 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
559 printf("%s", db_strerror(db));
566 * Delete all entries in the list
568 static int delete_id_list(const char *query, ID_LIST *id_list)
571 for (int i=0; i < id_list->num_ids; i++) {
572 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
574 printf(_("Deleting: %s\n"), buf);
576 db_sql_query(db, buf, NULL, NULL);
582 * Called here with each name to be added to the list
584 static int name_list_handler(void *ctx, int num_fields, char **row)
586 NAME_LIST *name = (NAME_LIST *)ctx;
588 if (name->num_ids == MAX_ID_LIST_LEN) {
591 if (name->num_ids == name->max_ids) {
592 if (name->max_ids == 0) {
593 name->max_ids = 10000;
594 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
596 name->max_ids = (name->max_ids * 3) / 2;
597 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
600 name->name[name->num_ids++] = bstrdup(row[0]);
606 * Construct name list
608 static int make_name_list(const char *query, NAME_LIST *name_list)
610 name_list->num_ids = 0;
611 name_list->num_del = 0;
612 name_list->tot_ids = 0;
614 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
615 printf("%s", db_strerror(db));
622 * Print names in the list
624 static void print_name_list(NAME_LIST *name_list)
626 for (int i=0; i < name_list->num_ids; i++) {
627 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 */
709 query = "SELECT Path, count(Path) as Count FROM Path "
710 "GROUP BY Path HAVING count(Path) > 1";
712 if (!make_name_list(query, &name_list)) {
715 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
716 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
717 print_name_list(&name_list);
723 /* Loop through list of duplicate names */
724 for (int i=0; i<name_list.num_ids; i++) {
725 /* Get all the Ids of each name */
726 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
727 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
731 if (!make_id_list(buf, &id_list)) {
735 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
737 /* Force all records to use the first id then delete the other ids */
738 for (int j=1; j<id_list.num_ids; j++) {
739 char ed1[50], ed2[50];
740 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
741 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
745 db_sql_query(db, buf, NULL, NULL);
746 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
750 db_sql_query(db, buf, NULL, NULL);
754 free_name_list(&name_list);
757 static void eliminate_orphaned_jobmedia_records()
759 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
760 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
761 "WHERE Job.JobId IS NULL LIMIT 300000";
763 printf(_("Checking for orphaned JobMedia entries.\n"));
764 if (!make_id_list(query, &id_list)) {
767 /* Loop doing 300000 at a time */
768 while (id_list.num_ids != 0) {
769 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
770 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
771 for (int i=0; i < id_list.num_ids; i++) {
773 bsnprintf(buf, sizeof(buf),
774 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
775 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
776 edit_int64(id_list.Id[i], ed1));
777 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
778 printf("%s\n", db_strerror(db));
786 if (fix && id_list.num_ids > 0) {
787 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
788 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
790 break; /* get out if not updating db */
792 if (!make_id_list(query, &id_list)) {
798 static void eliminate_orphaned_file_records()
800 const char *query = "SELECT File.FileId,Job.JobId FROM File "
801 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
802 "WHERE Job.JobId IS NULL LIMIT 300000";
804 printf(_("Checking for orphaned File entries. This may take some time!\n"));
806 printf("%s\n", query);
808 if (!make_id_list(query, &id_list)) {
811 /* Loop doing 300000 at a time */
812 while (id_list.num_ids != 0) {
813 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
814 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
815 for (int i=0; i < id_list.num_ids; i++) {
817 bsnprintf(buf, sizeof(buf),
818 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
819 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
820 edit_int64(id_list.Id[i], ed1));
821 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
822 printf("%s\n", db_strerror(db));
829 if (fix && id_list.num_ids > 0) {
830 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
831 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
833 break; /* get out if not updating db */
835 if (!make_id_list(query, &id_list)) {
841 static void eliminate_orphaned_path_records()
844 /* check the existence of the required "one column" index */
845 if (!check_idx("PathId")) {
846 if (yes_no(_("Create temporary index? (yes/no): "))) {
847 /* create temporary index PathId */
848 create_tmp_idx("idxPIchk", "File", "PathId");
852 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
853 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
854 "WHERE File.PathId IS NULL LIMIT 300000";
856 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
858 printf("%s\n", query);
860 if (!make_id_list(query, &id_list)) {
863 /* Loop doing 300000 at a time */
864 while (id_list.num_ids != 0) {
865 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
866 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
867 for (int i=0; i < id_list.num_ids; i++) {
869 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
870 edit_int64(id_list.Id[i], ed1));
871 db_sql_query(db, buf, print_name_handler, NULL);
877 if (fix && id_list.num_ids > 0) {
878 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
879 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
881 break; /* get out if not updating db */
883 if (!make_id_list(query, &id_list)) {
887 /* drop temporary index idx_tmp_name */
888 drop_tmp_idx("idxPIchk", "File");
891 static void eliminate_orphaned_filename_records()
894 /* check the existence of the required "one column" index */
895 if (!check_idx("FilenameId") ) {
896 if (yes_no(_("Create temporary index? (yes/no): "))) {
897 /* create temporary index FilenameId */
898 create_tmp_idx("idxFIchk", "File", "FilenameId");
902 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
903 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
904 "WHERE File.FilenameId IS NULL LIMIT 300000";
906 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
908 printf("%s\n", query);
910 if (!make_id_list(query, &id_list)) {
913 /* Loop doing 300000 at a time */
914 while (id_list.num_ids != 0) {
915 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
916 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
917 for (int i=0; i < id_list.num_ids; i++) {
919 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
920 edit_int64(id_list.Id[i], ed1));
921 db_sql_query(db, buf, print_name_handler, NULL);
927 if (fix && id_list.num_ids > 0) {
928 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
929 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
931 break; /* get out if not updating db */
933 if (!make_id_list(query, &id_list)) {
937 /* drop temporary index idx_tmp_name */
938 drop_tmp_idx("idxFIchk", "File");
942 static void eliminate_orphaned_fileset_records()
946 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
947 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
948 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
949 "WHERE Job.FileSetId IS NULL";
951 printf("%s\n", query);
953 if (!make_id_list(query, &id_list)) {
956 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
957 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
958 for (int i=0; i < id_list.num_ids; i++) {
960 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
961 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
962 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
963 printf("%s\n", db_strerror(db));
970 if (fix && id_list.num_ids > 0) {
971 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
972 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
976 static void eliminate_orphaned_client_records()
980 printf(_("Checking for orphaned Client entries.\n"));
982 * Wiffle through Client for every Client
983 * joining with the Job table including every Client even if
984 * there is not a match in Job (left outer join), then
985 * filter out only those where no Job points to a Client
986 * i.e. Job.Client is NULL
988 query = "SELECT Client.ClientId,Client.Name FROM Client "
989 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
990 "WHERE Job.ClientId IS NULL";
992 printf("%s\n", query);
994 if (!make_id_list(query, &id_list)) {
997 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
998 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
999 for (int i=0; i < id_list.num_ids; i++) {
1001 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1002 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1003 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1004 printf("%s\n", db_strerror(db));
1011 if (fix && id_list.num_ids > 0) {
1012 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1013 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1017 static void eliminate_orphaned_job_records()
1021 printf(_("Checking for orphaned Job entries.\n"));
1023 * Wiffle through Job for every Job
1024 * joining with the Client table including every Job even if
1025 * there is not a match in Client (left outer join), then
1026 * filter out only those where no Client exists
1027 * i.e. Client.Name is NULL
1029 query = "SELECT Job.JobId,Job.Name FROM Job "
1030 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1031 "WHERE Client.Name IS NULL";
1033 printf("%s\n", query);
1035 if (!make_id_list(query, &id_list)) {
1038 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1039 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1040 for (int i=0; i < id_list.num_ids; i++) {
1042 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1043 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1044 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1045 printf("%s\n", db_strerror(db));
1052 if (fix && id_list.num_ids > 0) {
1053 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1054 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1055 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1056 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1057 printf(_("Deleting Log records of orphaned Job records.\n"));
1058 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1063 static void eliminate_admin_records()
1067 printf(_("Checking for Admin Job entries.\n"));
1068 query = "SELECT Job.JobId FROM Job "
1069 "WHERE Job.Type='D'";
1071 printf("%s\n", query);
1073 if (!make_id_list(query, &id_list)) {
1076 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1077 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1078 for (int i=0; i < id_list.num_ids; i++) {
1080 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1081 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1082 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1083 printf("%s\n", db_strerror(db));
1090 if (fix && id_list.num_ids > 0) {
1091 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1092 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1096 static void eliminate_restore_records()
1100 printf(_("Checking for Restore Job entries.\n"));
1101 query = "SELECT Job.JobId FROM Job "
1102 "WHERE Job.Type='R'";
1104 printf("%s\n", query);
1106 if (!make_id_list(query, &id_list)) {
1109 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1110 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1111 for (int i=0; i < id_list.num_ids; i++) {
1113 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1114 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1115 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1116 printf("%s\n", db_strerror(db));
1123 if (fix && id_list.num_ids > 0) {
1124 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1125 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1132 static void repair_bad_filenames()
1137 printf(_("Checking for Filenames with a trailing slash\n"));
1138 query = "SELECT FilenameId,Name from Filename "
1139 "WHERE Name LIKE '%/'";
1141 printf("%s\n", query);
1143 if (!make_id_list(query, &id_list)) {
1146 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1147 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1148 for (i=0; i < id_list.num_ids; i++) {
1150 bsnprintf(buf, sizeof(buf),
1151 "SELECT Name FROM Filename WHERE FilenameId=%s",
1152 edit_int64(id_list.Id[i], ed1));
1153 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1154 printf("%s\n", db_strerror(db));
1161 if (fix && id_list.num_ids > 0) {
1162 POOLMEM *name = get_pool_memory(PM_FNAME);
1163 char esc_name[5000];
1164 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1165 for (i=0; i < id_list.num_ids; i++) {
1168 bsnprintf(buf, sizeof(buf),
1169 "SELECT Name FROM Filename WHERE FilenameId=%s",
1170 edit_int64(id_list.Id[i], ed1));
1171 if (!db_sql_query(db, buf, get_name_handler, name)) {
1172 printf("%s\n", db_strerror(db));
1174 /* Strip trailing slash(es) */
1175 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1183 db_escape_string(NULL, db, esc_name, name, len);
1185 bsnprintf(buf, sizeof(buf),
1186 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1187 esc_name, edit_int64(id_list.Id[i], ed1));
1189 printf("%s\n", buf);
1191 db_sql_query(db, buf, NULL, NULL);
1196 static void repair_bad_paths()
1201 printf(_("Checking for Paths without a trailing slash\n"));
1202 query = "SELECT PathId,Path from Path "
1203 "WHERE Path NOT LIKE '%/'";
1205 printf("%s\n", query);
1207 if (!make_id_list(query, &id_list)) {
1210 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1211 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1212 for (i=0; i < id_list.num_ids; i++) {
1214 bsnprintf(buf, sizeof(buf),
1215 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1216 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1217 printf("%s\n", db_strerror(db));
1224 if (fix && id_list.num_ids > 0) {
1225 POOLMEM *name = get_pool_memory(PM_FNAME);
1226 char esc_name[5000];
1227 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1228 for (i=0; i < id_list.num_ids; i++) {
1231 bsnprintf(buf, sizeof(buf),
1232 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1233 if (!db_sql_query(db, buf, get_name_handler, name)) {
1234 printf("%s\n", db_strerror(db));
1236 /* Strip trailing blanks */
1237 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1240 /* Add trailing slash */
1241 len = pm_strcat(&name, "/");
1242 db_escape_string(NULL, db, esc_name, name, len);
1243 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1244 esc_name, edit_int64(id_list.Id[i], ed1));
1246 printf("%s\n", buf);
1248 db_sql_query(db, buf, NULL, NULL);
1255 * Gen next input command from the terminal
1257 static char *get_cmd(const char *prompt)
1259 static char cmd[1000];
1261 printf("%s", prompt);
1262 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1267 strip_trailing_junk(cmd);
1271 static bool yes_no(const char *prompt)
1274 cmd = get_cmd(prompt);
1279 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1282 bool python_set_prog(JCR*, char const*) { return false; }
1286 * The code below to add indexes is needed only for MySQL, and
1287 * that to improve the performance.
1292 typedef struct s_idx_list {
1294 int count_key; /* how many times the index meets *key_name */
1295 int count_col; /* how many times meets the desired column name */
1298 static IDX_LIST idx_list[MAXIDX];
1301 * Called here with each table index to be added to the list
1303 static int check_idx_handler(void *ctx, int num_fields, char **row)
1305 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1306 * File | 0 | PRIMARY | 1 | FileId |...
1308 char *name, *key_name, *col_name;
1314 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1315 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1316 idx_list[i].count_key++;
1318 if (strcasecmp(col_name, name) == 0) {
1319 idx_list[i].count_col++;
1324 /* if the new Key_name, add it to the list */
1326 len = strlen(key_name) + 1;
1327 idx_list[i].key_name = (char *)malloc(len);
1328 bstrncpy(idx_list[i].key_name, key_name, len);
1329 idx_list[i].count_key = 1;
1330 if (strcasecmp(col_name, name) == 0) {
1331 idx_list[i].count_col = 1;
1333 idx_list[i].count_col = 0;
1341 * Return TRUE if "one column" index over *col_name exists
1343 static bool check_idx(const char *col_name)
1349 memset(&idx_list, 0, sizeof(idx_list));
1350 const char *query = "SHOW INDEX FROM File";
1351 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1352 printf("%s\n", db_strerror(db));
1355 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1356 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1357 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1358 /* "one column" index over *col_name found */
1364 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1367 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1377 * Create temporary one-column index
1379 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1380 const char *col_name)
1382 idx_tmp_name = NULL;
1383 printf(_("Create temporary index... This may take some time!\n"));
1384 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1386 printf("%s\n", buf);
1388 if (db_sql_query(db, buf, NULL, NULL)) {
1389 idx_tmp_name = idx_name;
1391 printf(_("Temporary index created.\n"));
1394 printf("%s\n", db_strerror(db));
1401 * Drop temporary index
1403 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1405 if (idx_tmp_name != NULL) {
1406 printf(_("Drop temporary index.\n"));
1407 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1409 printf("%s\n", buf);
1411 if (!db_sql_query(db, buf, NULL, NULL)) {
1412 printf("%s\n", db_strerror(db));
1416 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1420 idx_tmp_name = NULL;