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;
115 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
117 " -C catalog name in the director conf file\n"
118 " -c Director conf filename\n"
119 " -B print catalog configuration and exit\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 bool print_catalog=false;
134 char *configfile = NULL;
135 char *catalogname = NULL;
138 setlocale(LC_ALL, "");
139 bindtextdomain("bacula", LOCALEDIR);
140 textdomain("bacula");
142 my_name_is(argc, argv, "dbcheck");
143 init_msg(NULL, NULL); /* setup message handler */
145 memset(&id_list, 0, sizeof(id_list));
146 memset(&name_list, 0, sizeof(name_list));
148 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
151 print_catalog = true; /* get catalog information from config */
154 case 'b': /* batch */
158 case 'C': /* CatalogName */
159 catalogname = optarg;
162 case 'c': /* configfile */
166 case 'd': /* debug level */
167 if (*optarg == 't') {
168 dbg_timestamp = true;
170 debug_level = atoi(optarg);
171 if (debug_level <= 0) {
177 case 'f': /* fix inconsistencies */
199 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
201 config = new_config_parser();
202 parse_dir_config(config, configfile, M_ERROR_TERM);
204 foreach_res(catalog, R_CATALOG) {
205 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
208 } else if (!catalogname) { // stop on first if no catalogname is given
216 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
218 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
224 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
227 Pmsg0(0, _("Error no Director resource defined.\n"));
230 set_working_directory(director->working_directory);
232 /* Print catalog information and exit (-B) */
234 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
235 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(buf),
236 db_get_type(), working_directory);
237 free_pool_memory(buf);
241 db_name = catalog->db_name;
242 user = catalog->db_user;
243 password = catalog->db_password;
244 dbhost = catalog->db_address;
245 if (dbhost && dbhost[0] == 0) {
248 dbport = catalog->db_port;
252 Pmsg0(0, _("Wrong number of arguments.\n"));
257 Pmsg0(0, _("Working directory not supplied.\n"));
261 /* This is needed by SQLite to find the db */
262 working_directory = argv[0];
271 } else if (argc == 3) {
274 } else if (argc == 4) {
278 } else if (argc == 5) {
283 } else if (argc == 6) {
289 dbport = strtol(argv[5], &endptr, 10);
290 if (*endptr != '\0') {
291 Pmsg0(0, _("Database port must be a numeric value.\n"));
293 } else if (errno == ERANGE) {
294 Pmsg0(0, _("Database port must be a int value.\n"));
301 db = db_init_database(NULL, db_name, user, password, dbhost, dbport, NULL, 0);
302 if (!db_open_database(NULL, db)) {
303 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
309 repair_bad_filenames();
310 eliminate_duplicate_filenames();
311 eliminate_duplicate_paths();
312 eliminate_orphaned_jobmedia_records();
313 eliminate_orphaned_file_records();
314 eliminate_orphaned_path_records();
315 eliminate_orphaned_filename_records();
316 eliminate_orphaned_fileset_records();
317 eliminate_orphaned_client_records();
318 eliminate_orphaned_job_records();
319 eliminate_admin_records();
320 eliminate_restore_records();
322 do_interactive_mode();
325 db_close_database(NULL, db);
331 static void do_interactive_mode()
335 printf(_("Hello, this is the database check/correct program.\n"));
337 printf(_("Modify database is on."));
339 printf(_("Modify database is off."));
341 printf(_(" Verbose is on.\n"));
343 printf(_(" Verbose is off.\n"));
345 printf(_("Please select the fuction you want to perform.\n"));
350 " 1) Toggle modify database flag\n"
351 " 2) Toggle verbose flag\n"
352 " 3) Repair bad Filename records\n"
353 " 4) Repair bad Path records\n"
354 " 5) Eliminate duplicate Filename records\n"
355 " 6) Eliminate duplicate Path records\n"
356 " 7) Eliminate orphaned Jobmedia records\n"
357 " 8) Eliminate orphaned File records\n"
358 " 9) Eliminate orphaned Path records\n"
359 " 10) Eliminate orphaned Filename records\n"
360 " 11) Eliminate orphaned FileSet records\n"
361 " 12) Eliminate orphaned Client records\n"
362 " 13) Eliminate orphaned Job records\n"
363 " 14) Eliminate all Admin records\n"
364 " 15) Eliminate all Restore records\n"
369 " 1) Toggle modify database flag\n"
370 " 2) Toggle verbose flag\n"
371 " 3) Check for bad Filename records\n"
372 " 4) Check for bad Path records\n"
373 " 5) Check for duplicate Filename records\n"
374 " 6) Check for duplicate Path records\n"
375 " 7) Check for orphaned Jobmedia records\n"
376 " 8) Check for orphaned File records\n"
377 " 9) Check for orphaned Path records\n"
378 " 10) Check for orphaned Filename records\n"
379 " 11) Check for orphaned FileSet records\n"
380 " 12) Check for orphaned Client records\n"
381 " 13) Check for orphaned Job records\n"
382 " 14) Check for all Admin records\n"
383 " 15) Check for all Restore records\n"
388 cmd = get_cmd(_("Select function number: "));
390 int item = atoi(cmd);
395 printf(_("Database will be modified.\n"));
397 printf(_("Database will NOT be modified.\n"));
400 verbose = verbose?0:1;
402 printf(_(" Verbose is on.\n"));
404 printf(_(" Verbose is off.\n"));
407 repair_bad_filenames();
413 eliminate_duplicate_filenames();
416 eliminate_duplicate_paths();
419 eliminate_orphaned_jobmedia_records();
422 eliminate_orphaned_file_records();
425 eliminate_orphaned_path_records();
428 eliminate_orphaned_filename_records();
431 eliminate_orphaned_fileset_records();
434 eliminate_orphaned_client_records();
437 eliminate_orphaned_job_records();
440 eliminate_admin_records();
443 eliminate_restore_records();
446 repair_bad_filenames();
448 eliminate_duplicate_filenames();
449 eliminate_duplicate_paths();
450 eliminate_orphaned_jobmedia_records();
451 eliminate_orphaned_file_records();
452 eliminate_orphaned_path_records();
453 eliminate_orphaned_filename_records();
454 eliminate_orphaned_fileset_records();
455 eliminate_orphaned_client_records();
456 eliminate_orphaned_job_records();
457 eliminate_admin_records();
458 eliminate_restore_records();
468 static int print_name_handler(void *ctx, int num_fields, char **row)
471 printf("%s\n", row[0]);
476 static int get_name_handler(void *ctx, int num_fields, char **row)
478 POOLMEM *buf = (POOLMEM *)ctx;
480 pm_strcpy(&buf, row[0]);
485 static int print_job_handler(void *ctx, int num_fields, char **row)
487 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
488 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
493 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
495 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
496 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
500 static int print_file_handler(void *ctx, int num_fields, char **row)
502 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
503 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
507 static int print_fileset_handler(void *ctx, int num_fields, char **row)
509 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
510 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
514 static int print_client_handler(void *ctx, int num_fields, char **row)
516 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
517 NPRT(row[0]), NPRT(row[1]));
523 * Called here with each id to be added to the list
525 static int id_list_handler(void *ctx, int num_fields, char **row)
527 ID_LIST *lst = (ID_LIST *)ctx;
529 if (lst->num_ids == MAX_ID_LIST_LEN) {
532 if (lst->num_ids == lst->max_ids) {
533 if (lst->max_ids == 0) {
534 lst->max_ids = 10000;
535 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
537 lst->max_ids = (lst->max_ids * 3) / 2;
538 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
541 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
546 * Construct record id list
548 static int make_id_list(const char *query, ID_LIST *id_list)
550 id_list->num_ids = 0;
551 id_list->num_del = 0;
552 id_list->tot_ids = 0;
554 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
555 printf("%s", db_strerror(db));
562 * Delete all entries in the list
564 static int delete_id_list(const char *query, ID_LIST *id_list)
567 for (int i=0; i < id_list->num_ids; i++) {
568 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
570 printf(_("Deleting: %s\n"), buf);
572 db_sql_query(db, buf, NULL, NULL);
578 * Called here with each name to be added to the list
580 static int name_list_handler(void *ctx, int num_fields, char **row)
582 NAME_LIST *name = (NAME_LIST *)ctx;
584 if (name->num_ids == MAX_ID_LIST_LEN) {
587 if (name->num_ids == name->max_ids) {
588 if (name->max_ids == 0) {
589 name->max_ids = 10000;
590 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
592 name->max_ids = (name->max_ids * 3) / 2;
593 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
596 name->name[name->num_ids++] = bstrdup(row[0]);
602 * Construct name list
604 static int make_name_list(const char *query, NAME_LIST *name_list)
606 name_list->num_ids = 0;
607 name_list->num_del = 0;
608 name_list->tot_ids = 0;
610 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
611 printf("%s", db_strerror(db));
618 * Print names in the list
620 static void print_name_list(NAME_LIST *name_list)
622 for (int i=0; i < name_list->num_ids; i++) {
623 printf("%s\n", name_list->name[i]);
629 * Free names in the list
631 static void free_name_list(NAME_LIST *name_list)
633 for (int i=0; i < name_list->num_ids; i++) {
634 free(name_list->name[i]);
636 name_list->num_ids = 0;
639 static void eliminate_duplicate_filenames()
644 printf(_("Checking for duplicate Filename entries.\n"));
646 /* Make list of duplicated names */
647 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
648 "HAVING count(Name) > 1";
650 if (!make_name_list(query, &name_list)) {
653 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
654 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
655 print_name_list(&name_list);
661 /* Loop through list of duplicate names */
662 for (int i=0; i<name_list.num_ids; i++) {
663 /* Get all the Ids of each name */
664 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
665 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
669 if (!make_id_list(buf, &id_list)) {
673 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
675 /* Force all records to use the first id then delete the other ids */
676 for (int j=1; j<id_list.num_ids; j++) {
677 char ed1[50], ed2[50];
678 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
679 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
683 db_sql_query(db, buf, NULL, NULL);
684 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
689 db_sql_query(db, buf, NULL, NULL);
693 free_name_list(&name_list);
696 static void eliminate_duplicate_paths()
701 printf(_("Checking for duplicate Path entries.\n"));
703 /* Make list of duplicated names */
705 query = "SELECT Path, count(Path) as Count FROM Path "
706 "GROUP BY Path HAVING count(Path) > 1";
708 if (!make_name_list(query, &name_list)) {
711 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
712 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
713 print_name_list(&name_list);
719 /* Loop through list of duplicate names */
720 for (int i=0; i<name_list.num_ids; i++) {
721 /* Get all the Ids of each name */
722 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
723 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
727 if (!make_id_list(buf, &id_list)) {
731 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
733 /* Force all records to use the first id then delete the other ids */
734 for (int j=1; j<id_list.num_ids; j++) {
735 char ed1[50], ed2[50];
736 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
737 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
741 db_sql_query(db, buf, NULL, NULL);
742 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
746 db_sql_query(db, buf, NULL, NULL);
750 free_name_list(&name_list);
753 static void eliminate_orphaned_jobmedia_records()
755 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
756 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
757 "WHERE Job.JobId IS NULL LIMIT 300000";
759 printf(_("Checking for orphaned JobMedia entries.\n"));
760 if (!make_id_list(query, &id_list)) {
763 /* Loop doing 300000 at a time */
764 while (id_list.num_ids != 0) {
765 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
766 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
767 for (int i=0; i < id_list.num_ids; i++) {
769 bsnprintf(buf, sizeof(buf),
770 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
771 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
772 edit_int64(id_list.Id[i], ed1));
773 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
774 printf("%s\n", db_strerror(db));
782 if (fix && id_list.num_ids > 0) {
783 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
784 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
786 break; /* get out if not updating db */
788 if (!make_id_list(query, &id_list)) {
794 static void eliminate_orphaned_file_records()
796 const char *query = "SELECT File.FileId,Job.JobId FROM File "
797 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
798 "WHERE Job.JobId IS NULL LIMIT 300000";
800 printf(_("Checking for orphaned File entries. This may take some time!\n"));
802 printf("%s\n", query);
804 if (!make_id_list(query, &id_list)) {
807 /* Loop doing 300000 at a time */
808 while (id_list.num_ids != 0) {
809 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
810 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
811 for (int i=0; i < id_list.num_ids; i++) {
813 bsnprintf(buf, sizeof(buf),
814 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
815 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
816 edit_int64(id_list.Id[i], ed1));
817 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
818 printf("%s\n", db_strerror(db));
825 if (fix && id_list.num_ids > 0) {
826 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
827 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
829 break; /* get out if not updating db */
831 if (!make_id_list(query, &id_list)) {
837 static void eliminate_orphaned_path_records()
840 /* check the existence of the required "one column" index */
841 if (!check_idx("PathId")) {
842 if (yes_no(_("Create temporary index? (yes/no): "))) {
843 /* create temporary index PathId */
844 create_tmp_idx("idxPIchk", "File", "PathId");
848 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
849 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
850 "WHERE File.PathId IS NULL LIMIT 300000";
852 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
854 printf("%s\n", query);
856 if (!make_id_list(query, &id_list)) {
859 /* Loop doing 300000 at a time */
860 while (id_list.num_ids != 0) {
861 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
862 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
863 for (int i=0; i < id_list.num_ids; i++) {
865 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
866 edit_int64(id_list.Id[i], ed1));
867 db_sql_query(db, buf, print_name_handler, NULL);
873 if (fix && id_list.num_ids > 0) {
874 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
875 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
877 break; /* get out if not updating db */
879 if (!make_id_list(query, &id_list)) {
883 /* drop temporary index idx_tmp_name */
884 drop_tmp_idx("idxPIchk", "File");
887 static void eliminate_orphaned_filename_records()
890 /* check the existence of the required "one column" index */
891 if (!check_idx("FilenameId") ) {
892 if (yes_no(_("Create temporary index? (yes/no): "))) {
893 /* create temporary index FilenameId */
894 create_tmp_idx("idxFIchk", "File", "FilenameId");
898 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
899 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
900 "WHERE File.FilenameId IS NULL LIMIT 300000";
902 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
904 printf("%s\n", query);
906 if (!make_id_list(query, &id_list)) {
909 /* Loop doing 300000 at a time */
910 while (id_list.num_ids != 0) {
911 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
912 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
913 for (int i=0; i < id_list.num_ids; i++) {
915 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
916 edit_int64(id_list.Id[i], ed1));
917 db_sql_query(db, buf, print_name_handler, NULL);
923 if (fix && id_list.num_ids > 0) {
924 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
925 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
927 break; /* get out if not updating db */
929 if (!make_id_list(query, &id_list)) {
933 /* drop temporary index idx_tmp_name */
934 drop_tmp_idx("idxFIchk", "File");
938 static void eliminate_orphaned_fileset_records()
942 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
943 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
944 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
945 "WHERE Job.FileSetId IS NULL";
947 printf("%s\n", query);
949 if (!make_id_list(query, &id_list)) {
952 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
953 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
954 for (int i=0; i < id_list.num_ids; i++) {
956 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
957 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
958 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
959 printf("%s\n", db_strerror(db));
966 if (fix && id_list.num_ids > 0) {
967 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
968 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
972 static void eliminate_orphaned_client_records()
976 printf(_("Checking for orphaned Client entries.\n"));
978 * Wiffle through Client for every Client
979 * joining with the Job table including every Client even if
980 * there is not a match in Job (left outer join), then
981 * filter out only those where no Job points to a Client
982 * i.e. Job.Client is NULL
984 query = "SELECT Client.ClientId,Client.Name FROM Client "
985 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
986 "WHERE Job.ClientId IS NULL";
988 printf("%s\n", query);
990 if (!make_id_list(query, &id_list)) {
993 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
994 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
995 for (int i=0; i < id_list.num_ids; i++) {
997 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
998 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
999 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1000 printf("%s\n", db_strerror(db));
1007 if (fix && id_list.num_ids > 0) {
1008 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1009 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1013 static void eliminate_orphaned_job_records()
1017 printf(_("Checking for orphaned Job entries.\n"));
1019 * Wiffle through Job for every Job
1020 * joining with the Client table including every Job even if
1021 * there is not a match in Client (left outer join), then
1022 * filter out only those where no Client exists
1023 * i.e. Client.Name is NULL
1025 query = "SELECT Job.JobId,Job.Name FROM Job "
1026 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1027 "WHERE Client.Name IS NULL";
1029 printf("%s\n", query);
1031 if (!make_id_list(query, &id_list)) {
1034 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1035 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1036 for (int i=0; i < id_list.num_ids; i++) {
1038 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1039 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1040 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1041 printf("%s\n", db_strerror(db));
1048 if (fix && id_list.num_ids > 0) {
1049 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1050 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1051 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1052 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1053 printf(_("Deleting Log records of orphaned Job records.\n"));
1054 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1059 static void eliminate_admin_records()
1063 printf(_("Checking for Admin Job entries.\n"));
1064 query = "SELECT Job.JobId FROM Job "
1065 "WHERE Job.Type='D'";
1067 printf("%s\n", query);
1069 if (!make_id_list(query, &id_list)) {
1072 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1073 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1074 for (int i=0; i < id_list.num_ids; i++) {
1076 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1077 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1078 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1079 printf("%s\n", db_strerror(db));
1086 if (fix && id_list.num_ids > 0) {
1087 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1088 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1092 static void eliminate_restore_records()
1096 printf(_("Checking for Restore Job entries.\n"));
1097 query = "SELECT Job.JobId FROM Job "
1098 "WHERE Job.Type='R'";
1100 printf("%s\n", query);
1102 if (!make_id_list(query, &id_list)) {
1105 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1106 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1107 for (int i=0; i < id_list.num_ids; i++) {
1109 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1110 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1111 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1112 printf("%s\n", db_strerror(db));
1119 if (fix && id_list.num_ids > 0) {
1120 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1121 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1128 static void repair_bad_filenames()
1133 printf(_("Checking for Filenames with a trailing slash\n"));
1134 query = "SELECT FilenameId,Name from Filename "
1135 "WHERE Name LIKE '%/'";
1137 printf("%s\n", query);
1139 if (!make_id_list(query, &id_list)) {
1142 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1143 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1144 for (i=0; i < id_list.num_ids; i++) {
1146 bsnprintf(buf, sizeof(buf),
1147 "SELECT Name FROM Filename WHERE FilenameId=%s",
1148 edit_int64(id_list.Id[i], ed1));
1149 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1150 printf("%s\n", db_strerror(db));
1157 if (fix && id_list.num_ids > 0) {
1158 POOLMEM *name = get_pool_memory(PM_FNAME);
1159 char esc_name[5000];
1160 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1161 for (i=0; i < id_list.num_ids; i++) {
1164 bsnprintf(buf, sizeof(buf),
1165 "SELECT Name FROM Filename WHERE FilenameId=%s",
1166 edit_int64(id_list.Id[i], ed1));
1167 if (!db_sql_query(db, buf, get_name_handler, name)) {
1168 printf("%s\n", db_strerror(db));
1170 /* Strip trailing slash(es) */
1171 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1179 db_escape_string(NULL, db, esc_name, name, len);
1181 bsnprintf(buf, sizeof(buf),
1182 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1183 esc_name, edit_int64(id_list.Id[i], ed1));
1185 printf("%s\n", buf);
1187 db_sql_query(db, buf, NULL, NULL);
1192 static void repair_bad_paths()
1197 printf(_("Checking for Paths without a trailing slash\n"));
1198 query = "SELECT PathId,Path from Path "
1199 "WHERE Path NOT LIKE '%/'";
1201 printf("%s\n", query);
1203 if (!make_id_list(query, &id_list)) {
1206 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1207 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1208 for (i=0; i < id_list.num_ids; i++) {
1210 bsnprintf(buf, sizeof(buf),
1211 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1212 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1213 printf("%s\n", db_strerror(db));
1220 if (fix && id_list.num_ids > 0) {
1221 POOLMEM *name = get_pool_memory(PM_FNAME);
1222 char esc_name[5000];
1223 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1224 for (i=0; i < id_list.num_ids; i++) {
1227 bsnprintf(buf, sizeof(buf),
1228 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1229 if (!db_sql_query(db, buf, get_name_handler, name)) {
1230 printf("%s\n", db_strerror(db));
1232 /* Strip trailing blanks */
1233 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1236 /* Add trailing slash */
1237 len = pm_strcat(&name, "/");
1238 db_escape_string(NULL, db, esc_name, name, len);
1239 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1240 esc_name, edit_int64(id_list.Id[i], ed1));
1242 printf("%s\n", buf);
1244 db_sql_query(db, buf, NULL, NULL);
1251 * Gen next input command from the terminal
1253 static char *get_cmd(const char *prompt)
1255 static char cmd[1000];
1257 printf("%s", prompt);
1258 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1263 strip_trailing_junk(cmd);
1267 static bool yes_no(const char *prompt)
1270 cmd = get_cmd(prompt);
1275 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1278 bool python_set_prog(JCR*, char const*) { return false; }
1282 * The code below to add indexes is needed only for MySQL, and
1283 * that to improve the performance.
1288 typedef struct s_idx_list {
1290 int count_key; /* how many times the index meets *key_name */
1291 int count_col; /* how many times meets the desired column name */
1294 static IDX_LIST idx_list[MAXIDX];
1297 * Called here with each table index to be added to the list
1299 static int check_idx_handler(void *ctx, int num_fields, char **row)
1301 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1302 * File | 0 | PRIMARY | 1 | FileId |...
1304 char *name, *key_name, *col_name;
1310 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1311 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1312 idx_list[i].count_key++;
1314 if (strcasecmp(col_name, name) == 0) {
1315 idx_list[i].count_col++;
1320 /* if the new Key_name, add it to the list */
1322 len = strlen(key_name) + 1;
1323 idx_list[i].key_name = (char *)malloc(len);
1324 bstrncpy(idx_list[i].key_name, key_name, len);
1325 idx_list[i].count_key = 1;
1326 if (strcasecmp(col_name, name) == 0) {
1327 idx_list[i].count_col = 1;
1329 idx_list[i].count_col = 0;
1337 * Return TRUE if "one column" index over *col_name exists
1339 static bool check_idx(const char *col_name)
1345 memset(&idx_list, 0, sizeof(idx_list));
1346 char *query = "SHOW INDEX FROM File";
1347 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1348 printf("%s\n", db_strerror(db));
1351 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1352 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1353 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1354 /* "one column" index over *col_name found */
1360 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1363 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1373 * Create temporary one-column index
1375 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1376 const char *col_name)
1378 idx_tmp_name = NULL;
1379 printf(_("Create temporary index... This may take some time!\n"));
1380 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1382 printf("%s\n", buf);
1384 if (db_sql_query(db, buf, NULL, NULL)) {
1385 idx_tmp_name = idx_name;
1387 printf(_("Temporary index created.\n"));
1390 printf("%s\n", db_strerror(db));
1397 * Drop temporary index
1399 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1401 if (idx_tmp_name != NULL) {
1402 printf(_("Drop temporary index.\n"));
1403 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1405 printf("%s\n", buf);
1407 if (!db_sql_query(db, buf, NULL, NULL)) {
1408 printf("%s\n", db_strerror(db));
1412 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1416 idx_tmp_name = NULL;