2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2008 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 two of the GNU 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 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 "lib/runscript.h"
42 #include "dird/dird_conf.h"
44 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
47 int generate_daemon_event(JCR *jcr, const char *event)
50 typedef struct s_id_ctx {
51 int64_t *Id; /* ids to be modified */
52 int num_ids; /* ids stored */
53 int max_ids; /* size of array */
54 int num_del; /* number deleted */
55 int tot_ids; /* total to process */
58 typedef struct s_name_ctx {
59 char **name; /* list of names */
60 int num_ids; /* ids stored */
61 int max_ids; /* size of array */
62 int num_del; /* number deleted */
63 int tot_ids; /* total to process */
66 /* Global variables */
67 static bool fix = false;
68 static bool batch = false;
70 static ID_LIST id_list;
71 static NAME_LIST name_list;
72 static char buf[20000];
73 static bool quit = false;
74 static CONFIG *config;
76 #define MAX_ID_LIST_LEN 10000000
78 /* Forward referenced functions */
79 static int make_id_list(const char *query, ID_LIST *id_list);
80 static int delete_id_list(const char *query, ID_LIST *id_list);
81 static int make_name_list(const char *query, NAME_LIST *name_list);
82 static void print_name_list(NAME_LIST *name_list);
83 static void free_name_list(NAME_LIST *name_list);
84 static char *get_cmd(const char *prompt);
85 static void eliminate_duplicate_filenames();
86 static void eliminate_duplicate_paths();
87 static void eliminate_orphaned_jobmedia_records();
88 static void eliminate_orphaned_file_records();
89 static void eliminate_orphaned_path_records();
90 static void eliminate_orphaned_filename_records();
91 static void eliminate_orphaned_fileset_records();
92 static void eliminate_orphaned_client_records();
93 static void eliminate_orphaned_job_records();
94 static void eliminate_admin_records();
95 static void eliminate_restore_records();
96 static void repair_bad_paths();
97 static void repair_bad_filenames();
98 static void do_interactive_mode();
99 static bool yes_no(const char *prompt);
100 static bool check_idx(const char *col_name);
101 static bool create_tmp_idx(const char *idx_name, const char *table_name,
102 const char *col_name);
103 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
105 static int check_idx_handler(void *ctx, int num_fields, char **row);
109 /* Global variables */
110 static const char *idx_tmp_name;
116 "Usage: dbcheck [-c config] [-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 " -d <nn> set debug level to <nn>\n"
121 " -dt print timestamp in debug output\n"
122 " -f fix inconsistencies\n"
124 " -? print this message\n\n");
128 int main (int argc, char *argv[])
131 const char *user, *password, *db_name, *dbhost;
133 char *configfile = NULL;
134 char *catalogname = NULL;
137 setlocale(LC_ALL, "");
138 bindtextdomain("bacula", LOCALEDIR);
139 textdomain("bacula");
141 my_name_is(argc, argv, "dbcheck");
142 init_msg(NULL, NULL); /* setup message handler */
144 memset(&id_list, 0, sizeof(id_list));
145 memset(&name_list, 0, sizeof(name_list));
147 while ((ch = getopt(argc, argv, "bc:C:d:fv?")) != -1) {
149 case 'b': /* batch */
153 case 'C': /* CatalogName */
154 catalogname = optarg;
157 case 'c': /* configfile */
161 case 'd': /* debug level */
162 if (*optarg == 't') {
163 dbg_timestamp = true;
165 debug_level = atoi(optarg);
166 if (debug_level <= 0) {
172 case 'f': /* fix inconsistencies */
194 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
196 config = new_config_parser();
197 parse_dir_config(config, configfile, M_ERROR_TERM);
199 foreach_res(catalog, R_CATALOG) {
200 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
203 } else if (!catalogname) { // stop on first if no catalogname is given
211 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
213 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
219 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
222 Pmsg0(0, _("Error no Director resource defined.\n"));
225 set_working_directory(director->working_directory);
226 db_name = catalog->db_name;
227 user = catalog->db_user;
228 password = catalog->db_password;
229 dbhost = catalog->db_address;
230 if (dbhost && dbhost[0] == 0) {
233 dbport = catalog->db_port;
237 Pmsg0(0, _("Wrong number of arguments.\n"));
242 Pmsg0(0, _("Working directory not supplied.\n"));
246 /* 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"));
286 db = db_init_database(NULL, db_name, user, password, dbhost, dbport, NULL, 0);
287 if (!db_open_database(NULL, db)) {
288 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
294 repair_bad_filenames();
295 eliminate_duplicate_filenames();
296 eliminate_duplicate_paths();
297 eliminate_orphaned_jobmedia_records();
298 eliminate_orphaned_file_records();
299 eliminate_orphaned_path_records();
300 eliminate_orphaned_filename_records();
301 eliminate_orphaned_fileset_records();
302 eliminate_orphaned_client_records();
303 eliminate_orphaned_job_records();
304 eliminate_admin_records();
305 eliminate_restore_records();
307 do_interactive_mode();
310 db_close_database(NULL, db);
316 static void do_interactive_mode()
320 printf(_("Hello, this is the database check/correct program.\n"));
322 printf(_("Modify database is on."));
324 printf(_("Modify database is off."));
326 printf(_(" Verbose is on.\n"));
328 printf(_(" Verbose is off.\n"));
330 printf(_("Please select the fuction you want to perform.\n"));
335 " 1) Toggle modify database flag\n"
336 " 2) Toggle verbose flag\n"
337 " 3) Repair bad Filename records\n"
338 " 4) Repair bad Path records\n"
339 " 5) Eliminate duplicate Filename records\n"
340 " 6) Eliminate duplicate Path records\n"
341 " 7) Eliminate orphaned Jobmedia records\n"
342 " 8) Eliminate orphaned File records\n"
343 " 9) Eliminate orphaned Path records\n"
344 " 10) Eliminate orphaned Filename records\n"
345 " 11) Eliminate orphaned FileSet records\n"
346 " 12) Eliminate orphaned Client records\n"
347 " 13) Eliminate orphaned Job records\n"
348 " 14) Eliminate all Admin records\n"
349 " 15) Eliminate all Restore records\n"
354 " 1) Toggle modify database flag\n"
355 " 2) Toggle verbose flag\n"
356 " 3) Check for bad Filename records\n"
357 " 4) Check for bad Path records\n"
358 " 5) Check for duplicate Filename records\n"
359 " 6) Check for duplicate Path records\n"
360 " 7) Check for orphaned Jobmedia records\n"
361 " 8) Check for orphaned File records\n"
362 " 9) Check for orphaned Path records\n"
363 " 10) Check for orphaned Filename records\n"
364 " 11) Check for orphaned FileSet records\n"
365 " 12) Check for orphaned Client records\n"
366 " 13) Check for orphaned Job records\n"
367 " 14) Check for all Admin records\n"
368 " 15) Check for all Restore records\n"
373 cmd = get_cmd(_("Select function number: "));
375 int item = atoi(cmd);
380 printf(_("Database will be modified.\n"));
382 printf(_("Database will NOT be modified.\n"));
385 verbose = verbose?0:1;
387 printf(_(" Verbose is on.\n"));
389 printf(_(" Verbose is off.\n"));
392 repair_bad_filenames();
398 eliminate_duplicate_filenames();
401 eliminate_duplicate_paths();
404 eliminate_orphaned_jobmedia_records();
407 eliminate_orphaned_file_records();
410 eliminate_orphaned_path_records();
413 eliminate_orphaned_filename_records();
416 eliminate_orphaned_fileset_records();
419 eliminate_orphaned_client_records();
422 eliminate_orphaned_job_records();
425 eliminate_admin_records();
428 eliminate_restore_records();
431 repair_bad_filenames();
433 eliminate_duplicate_filenames();
434 eliminate_duplicate_paths();
435 eliminate_orphaned_jobmedia_records();
436 eliminate_orphaned_file_records();
437 eliminate_orphaned_path_records();
438 eliminate_orphaned_filename_records();
439 eliminate_orphaned_fileset_records();
440 eliminate_orphaned_client_records();
441 eliminate_orphaned_job_records();
442 eliminate_admin_records();
443 eliminate_restore_records();
453 static int print_name_handler(void *ctx, int num_fields, char **row)
456 printf("%s\n", row[0]);
461 static int get_name_handler(void *ctx, int num_fields, char **row)
463 POOLMEM *buf = (POOLMEM *)ctx;
465 pm_strcpy(&buf, row[0]);
470 static int print_job_handler(void *ctx, int num_fields, char **row)
472 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
473 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
478 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
480 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
481 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
485 static int print_file_handler(void *ctx, int num_fields, char **row)
487 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
488 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
492 static int print_fileset_handler(void *ctx, int num_fields, char **row)
494 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
495 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
499 static int print_client_handler(void *ctx, int num_fields, char **row)
501 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
502 NPRT(row[0]), NPRT(row[1]));
508 * Called here with each id to be added to the list
510 static int id_list_handler(void *ctx, int num_fields, char **row)
512 ID_LIST *lst = (ID_LIST *)ctx;
514 if (lst->num_ids == MAX_ID_LIST_LEN) {
517 if (lst->num_ids == lst->max_ids) {
518 if (lst->max_ids == 0) {
519 lst->max_ids = 10000;
520 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
522 lst->max_ids = (lst->max_ids * 3) / 2;
523 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
526 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
531 * Construct record id list
533 static int make_id_list(const char *query, ID_LIST *id_list)
535 id_list->num_ids = 0;
536 id_list->num_del = 0;
537 id_list->tot_ids = 0;
539 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
540 printf("%s", db_strerror(db));
547 * Delete all entries in the list
549 static int delete_id_list(const char *query, ID_LIST *id_list)
552 for (int i=0; i < id_list->num_ids; i++) {
553 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
555 printf(_("Deleting: %s\n"), buf);
557 db_sql_query(db, buf, NULL, NULL);
563 * Called here with each name to be added to the list
565 static int name_list_handler(void *ctx, int num_fields, char **row)
567 NAME_LIST *name = (NAME_LIST *)ctx;
569 if (name->num_ids == MAX_ID_LIST_LEN) {
572 if (name->num_ids == name->max_ids) {
573 if (name->max_ids == 0) {
574 name->max_ids = 10000;
575 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
577 name->max_ids = (name->max_ids * 3) / 2;
578 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
581 name->name[name->num_ids++] = bstrdup(row[0]);
587 * Construct name list
589 static int make_name_list(const char *query, NAME_LIST *name_list)
591 name_list->num_ids = 0;
592 name_list->num_del = 0;
593 name_list->tot_ids = 0;
595 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
596 printf("%s", db_strerror(db));
603 * Print names in the list
605 static void print_name_list(NAME_LIST *name_list)
607 for (int i=0; i < name_list->num_ids; i++) {
608 printf("%s\n", name_list->name[i]);
614 * Free names in the list
616 static void free_name_list(NAME_LIST *name_list)
618 for (int i=0; i < name_list->num_ids; i++) {
619 free(name_list->name[i]);
621 name_list->num_ids = 0;
624 static void eliminate_duplicate_filenames()
629 printf(_("Checking for duplicate Filename entries.\n"));
631 /* Make list of duplicated names */
632 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
633 "HAVING count(Name) > 1";
635 if (!make_name_list(query, &name_list)) {
638 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
639 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
640 print_name_list(&name_list);
646 /* Loop through list of duplicate names */
647 for (int i=0; i<name_list.num_ids; i++) {
648 /* Get all the Ids of each name */
649 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
650 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
654 if (!make_id_list(buf, &id_list)) {
658 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
660 /* Force all records to use the first id then delete the other ids */
661 for (int j=1; j<id_list.num_ids; j++) {
662 char ed1[50], ed2[50];
663 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
664 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
668 db_sql_query(db, buf, NULL, NULL);
669 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
674 db_sql_query(db, buf, NULL, NULL);
678 free_name_list(&name_list);
681 static void eliminate_duplicate_paths()
686 printf(_("Checking for duplicate Path entries.\n"));
688 /* Make list of duplicated names */
690 query = "SELECT Path, count(Path) as Count FROM Path "
691 "GROUP BY Path HAVING count(Path) > 1";
693 if (!make_name_list(query, &name_list)) {
696 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
697 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
698 print_name_list(&name_list);
704 /* Loop through list of duplicate names */
705 for (int i=0; i<name_list.num_ids; i++) {
706 /* Get all the Ids of each name */
707 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
708 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
712 if (!make_id_list(buf, &id_list)) {
716 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
718 /* Force all records to use the first id then delete the other ids */
719 for (int j=1; j<id_list.num_ids; j++) {
720 char ed1[50], ed2[50];
721 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
722 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
726 db_sql_query(db, buf, NULL, NULL);
727 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
731 db_sql_query(db, buf, NULL, NULL);
735 free_name_list(&name_list);
738 static void eliminate_orphaned_jobmedia_records()
740 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
741 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
742 "WHERE Job.JobId IS NULL LIMIT 300000";
744 printf(_("Checking for orphaned JobMedia entries.\n"));
745 if (!make_id_list(query, &id_list)) {
748 /* Loop doing 300000 at a time */
749 while (id_list.num_ids != 0) {
750 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
751 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
752 for (int i=0; i < id_list.num_ids; i++) {
754 bsnprintf(buf, sizeof(buf),
755 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
756 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
757 edit_int64(id_list.Id[i], ed1));
758 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
759 printf("%s\n", db_strerror(db));
767 if (fix && id_list.num_ids > 0) {
768 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
769 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
771 break; /* get out if not updating db */
773 if (!make_id_list(query, &id_list)) {
779 static void eliminate_orphaned_file_records()
781 const char *query = "SELECT File.FileId,Job.JobId FROM File "
782 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
783 "WHERE Job.JobId IS NULL LIMIT 300000";
785 printf(_("Checking for orphaned File entries. This may take some time!\n"));
787 printf("%s\n", query);
789 if (!make_id_list(query, &id_list)) {
792 /* Loop doing 300000 at a time */
793 while (id_list.num_ids != 0) {
794 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
795 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
796 for (int i=0; i < id_list.num_ids; i++) {
798 bsnprintf(buf, sizeof(buf),
799 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
800 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
801 edit_int64(id_list.Id[i], ed1));
802 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
803 printf("%s\n", db_strerror(db));
810 if (fix && id_list.num_ids > 0) {
811 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
812 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
814 break; /* get out if not updating db */
816 if (!make_id_list(query, &id_list)) {
822 static void eliminate_orphaned_path_records()
825 /* check the existence of the required "one column" index */
826 if (!check_idx("PathId")) {
827 if (yes_no(_("Create temporary index? (yes/no): "))) {
828 /* create temporary index PathId */
829 create_tmp_idx("idxPIchk", "File", "PathId");
833 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
834 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
835 "WHERE File.PathId IS NULL LIMIT 300000";
837 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
839 printf("%s\n", query);
841 if (!make_id_list(query, &id_list)) {
844 /* Loop doing 300000 at a time */
845 while (id_list.num_ids != 0) {
846 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
847 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
848 for (int i=0; i < id_list.num_ids; i++) {
850 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
851 edit_int64(id_list.Id[i], ed1));
852 db_sql_query(db, buf, print_name_handler, NULL);
858 if (fix && id_list.num_ids > 0) {
859 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
860 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
862 break; /* get out if not updating db */
864 if (!make_id_list(query, &id_list)) {
868 /* drop temporary index idx_tmp_name */
869 drop_tmp_idx("idxPIchk", "File");
872 static void eliminate_orphaned_filename_records()
875 /* check the existence of the required "one column" index */
876 if (!check_idx("FilenameId") ) {
877 if (yes_no(_("Create temporary index? (yes/no): "))) {
878 /* create temporary index FilenameId */
879 create_tmp_idx("idxFIchk", "File", "FilenameId");
883 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
884 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
885 "WHERE File.FilenameId IS NULL LIMIT 300000";
887 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
889 printf("%s\n", query);
891 if (!make_id_list(query, &id_list)) {
894 /* Loop doing 300000 at a time */
895 while (id_list.num_ids != 0) {
896 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
897 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
898 for (int i=0; i < id_list.num_ids; i++) {
900 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
901 edit_int64(id_list.Id[i], ed1));
902 db_sql_query(db, buf, print_name_handler, NULL);
908 if (fix && id_list.num_ids > 0) {
909 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
910 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
912 break; /* get out if not updating db */
914 if (!make_id_list(query, &id_list)) {
918 /* drop temporary index idx_tmp_name */
919 drop_tmp_idx("idxFIchk", "File");
923 static void eliminate_orphaned_fileset_records()
927 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
928 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
929 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
930 "WHERE Job.FileSetId IS NULL";
932 printf("%s\n", query);
934 if (!make_id_list(query, &id_list)) {
937 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
938 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
939 for (int i=0; i < id_list.num_ids; i++) {
941 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
942 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
943 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
944 printf("%s\n", db_strerror(db));
951 if (fix && id_list.num_ids > 0) {
952 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
953 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
957 static void eliminate_orphaned_client_records()
961 printf(_("Checking for orphaned Client entries.\n"));
963 * Wiffle through Client for every Client
964 * joining with the Job table including every Client even if
965 * there is not a match in Job (left outer join), then
966 * filter out only those where no Job points to a Client
967 * i.e. Job.Client is NULL
969 query = "SELECT Client.ClientId,Client.Name FROM Client "
970 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
971 "WHERE Job.ClientId IS NULL";
973 printf("%s\n", query);
975 if (!make_id_list(query, &id_list)) {
978 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
979 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
980 for (int i=0; i < id_list.num_ids; i++) {
982 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
983 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
984 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
985 printf("%s\n", db_strerror(db));
992 if (fix && id_list.num_ids > 0) {
993 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
994 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
998 static void eliminate_orphaned_job_records()
1002 printf(_("Checking for orphaned Job entries.\n"));
1004 * Wiffle through Job for every Job
1005 * joining with the Client table including every Job even if
1006 * there is not a match in Client (left outer join), then
1007 * filter out only those where no Client exists
1008 * i.e. Client.Name is NULL
1010 query = "SELECT Job.JobId,Job.Name FROM Job "
1011 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1012 "WHERE Client.Name IS NULL";
1014 printf("%s\n", query);
1016 if (!make_id_list(query, &id_list)) {
1019 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1020 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1021 for (int i=0; i < id_list.num_ids; i++) {
1023 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1024 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1025 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1026 printf("%s\n", db_strerror(db));
1033 if (fix && id_list.num_ids > 0) {
1034 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1035 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1036 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1037 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1038 printf(_("Deleting Log records of orphaned Job records.\n"));
1039 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1044 static void eliminate_admin_records()
1048 printf(_("Checking for Admin Job entries.\n"));
1049 query = "SELECT Job.JobId FROM Job "
1050 "WHERE Job.Type='D'";
1052 printf("%s\n", query);
1054 if (!make_id_list(query, &id_list)) {
1057 printf(_("Found %d Admin Job 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 JobId,Name,StartTime FROM Job "
1062 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1063 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1064 printf("%s\n", db_strerror(db));
1071 if (fix && id_list.num_ids > 0) {
1072 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1073 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1077 static void eliminate_restore_records()
1081 printf(_("Checking for Restore Job entries.\n"));
1082 query = "SELECT Job.JobId FROM Job "
1083 "WHERE Job.Type='R'";
1085 printf("%s\n", query);
1087 if (!make_id_list(query, &id_list)) {
1090 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1091 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1092 for (int i=0; i < id_list.num_ids; i++) {
1094 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1095 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1096 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1097 printf("%s\n", db_strerror(db));
1104 if (fix && id_list.num_ids > 0) {
1105 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1106 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1113 static void repair_bad_filenames()
1118 printf(_("Checking for Filenames with a trailing slash\n"));
1119 query = "SELECT FilenameId,Name from Filename "
1120 "WHERE Name LIKE '%/'";
1122 printf("%s\n", query);
1124 if (!make_id_list(query, &id_list)) {
1127 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1128 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1129 for (i=0; i < id_list.num_ids; i++) {
1131 bsnprintf(buf, sizeof(buf),
1132 "SELECT Name FROM Filename WHERE FilenameId=%s",
1133 edit_int64(id_list.Id[i], ed1));
1134 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1135 printf("%s\n", db_strerror(db));
1142 if (fix && id_list.num_ids > 0) {
1143 POOLMEM *name = get_pool_memory(PM_FNAME);
1144 char esc_name[5000];
1145 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1146 for (i=0; i < id_list.num_ids; i++) {
1149 bsnprintf(buf, sizeof(buf),
1150 "SELECT Name FROM Filename WHERE FilenameId=%s",
1151 edit_int64(id_list.Id[i], ed1));
1152 if (!db_sql_query(db, buf, get_name_handler, name)) {
1153 printf("%s\n", db_strerror(db));
1155 /* Strip trailing slash(es) */
1156 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1164 db_escape_string(NULL, db, esc_name, name, len);
1166 bsnprintf(buf, sizeof(buf),
1167 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1168 esc_name, edit_int64(id_list.Id[i], ed1));
1170 printf("%s\n", buf);
1172 db_sql_query(db, buf, NULL, NULL);
1177 static void repair_bad_paths()
1182 printf(_("Checking for Paths without a trailing slash\n"));
1183 query = "SELECT PathId,Path from Path "
1184 "WHERE Path NOT LIKE '%/'";
1186 printf("%s\n", query);
1188 if (!make_id_list(query, &id_list)) {
1191 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1192 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1193 for (i=0; i < id_list.num_ids; i++) {
1195 bsnprintf(buf, sizeof(buf),
1196 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1197 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1198 printf("%s\n", db_strerror(db));
1205 if (fix && id_list.num_ids > 0) {
1206 POOLMEM *name = get_pool_memory(PM_FNAME);
1207 char esc_name[5000];
1208 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1209 for (i=0; i < id_list.num_ids; i++) {
1212 bsnprintf(buf, sizeof(buf),
1213 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1214 if (!db_sql_query(db, buf, get_name_handler, name)) {
1215 printf("%s\n", db_strerror(db));
1217 /* Strip trailing blanks */
1218 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1221 /* Add trailing slash */
1222 len = pm_strcat(&name, "/");
1223 db_escape_string(NULL, db, esc_name, name, len);
1224 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1225 esc_name, edit_int64(id_list.Id[i], ed1));
1227 printf("%s\n", buf);
1229 db_sql_query(db, buf, NULL, NULL);
1236 * Gen next input command from the terminal
1238 static char *get_cmd(const char *prompt)
1240 static char cmd[1000];
1242 printf("%s", prompt);
1243 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1248 strip_trailing_junk(cmd);
1252 static bool yes_no(const char *prompt)
1255 cmd = get_cmd(prompt);
1260 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1263 bool python_set_prog(JCR*, char const*) { return false; }
1267 * The code below to add indexes is needed only for MySQL, and
1268 * that to improve the performance.
1273 typedef struct s_idx_list {
1275 int count_key; /* how many times the index meets *key_name */
1276 int count_col; /* how many times meets the desired column name */
1279 static IDX_LIST idx_list[MAXIDX];
1282 * Called here with each table index to be added to the list
1284 static int check_idx_handler(void *ctx, int num_fields, char **row)
1286 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1287 * File | 0 | PRIMARY | 1 | FileId |...
1289 char *name, *key_name, *col_name;
1295 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1296 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1297 idx_list[i].count_key++;
1299 if (strcasecmp(col_name, name) == 0) {
1300 idx_list[i].count_col++;
1305 /* if the new Key_name, add it to the list */
1307 len = strlen(key_name) + 1;
1308 idx_list[i].key_name = (char *)malloc(len);
1309 bstrncpy(idx_list[i].key_name, key_name, len);
1310 idx_list[i].count_key = 1;
1311 if (strcasecmp(col_name, name) == 0) {
1312 idx_list[i].count_col = 1;
1314 idx_list[i].count_col = 0;
1322 * Return TRUE if "one column" index over *col_name exists
1324 static bool check_idx(const char *col_name)
1330 memset(&idx_list, 0, sizeof(idx_list));
1331 char *query = "SHOW INDEX FROM File";
1332 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1333 printf("%s\n", db_strerror(db));
1336 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1337 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1338 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1339 /* "one column" index over *col_name found */
1345 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1348 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1358 * Create temporary one-column index
1360 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1361 const char *col_name)
1363 idx_tmp_name = NULL;
1364 printf(_("Create temporary index... This may take some time!\n"));
1365 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1367 printf("%s\n", buf);
1369 if (db_sql_query(db, buf, NULL, NULL)) {
1370 idx_tmp_name = idx_name;
1372 printf(_("Temporary index created.\n"));
1375 printf("%s\n", db_strerror(db));
1382 * Drop temporary index
1384 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1386 if (idx_tmp_name != NULL) {
1387 printf(_("Drop temporary index.\n"));
1388 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1390 printf("%s\n", buf);
1392 if (!db_sql_query(db, buf, NULL, NULL)) {
1393 printf("%s\n", db_strerror(db));
1397 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1401 idx_tmp_name = NULL;