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 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 a 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");
143 my_name_is(argc, argv, "dbcheck");
144 init_msg(NULL, NULL); /* setup message handler */
146 memset(&id_list, 0, sizeof(id_list));
147 memset(&name_list, 0, sizeof(name_list));
149 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
152 print_catalog = true; /* get catalog information from config */
155 case 'b': /* batch */
159 case 'C': /* CatalogName */
160 catalogname = optarg;
163 case 'c': /* configfile */
167 case 'd': /* debug level */
168 if (*optarg == 't') {
169 dbg_timestamp = true;
171 debug_level = atoi(optarg);
172 if (debug_level <= 0) {
178 case 'f': /* fix inconsistencies */
200 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
202 config = new_config_parser();
203 parse_dir_config(config, configfile, M_ERROR_TERM);
205 foreach_res(catalog, R_CATALOG) {
206 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
209 } else if (!catalogname) { // stop on first if no catalogname is given
217 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
219 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
225 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
228 Pmsg0(0, _("Error no Director resource defined.\n"));
231 set_working_directory(director->working_directory);
233 /* Print catalog information and exit (-B) */
235 POOLMEM *buf = get_pool_memory(PM_MESSAGE);
236 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(buf),
237 db_get_type(), working_directory);
238 free_pool_memory(buf);
242 db_name = catalog->db_name;
243 user = catalog->db_user;
244 password = catalog->db_password;
245 dbhost = catalog->db_address;
246 if (dbhost && dbhost[0] == 0) {
249 dbport = catalog->db_port;
253 Pmsg0(0, _("Wrong number of arguments.\n"));
258 Pmsg0(0, _("Working directory not supplied.\n"));
262 /* This is needed by SQLite to find the db */
263 working_directory = argv[0];
272 } else if (argc == 3) {
275 } else if (argc == 4) {
279 } else if (argc == 5) {
284 } else if (argc == 6) {
290 dbport = strtol(argv[5], &endptr, 10);
291 if (*endptr != '\0') {
292 Pmsg0(0, _("Database port must be a numeric value.\n"));
294 } else if (errno == ERANGE) {
295 Pmsg0(0, _("Database port must be a int value.\n"));
302 db = db_init_database(NULL, db_name, user, password, dbhost, dbport, NULL, 0);
303 if (!db_open_database(NULL, db)) {
304 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
310 repair_bad_filenames();
311 eliminate_duplicate_filenames();
312 eliminate_duplicate_paths();
313 eliminate_orphaned_jobmedia_records();
314 eliminate_orphaned_file_records();
315 eliminate_orphaned_path_records();
316 eliminate_orphaned_filename_records();
317 eliminate_orphaned_fileset_records();
318 eliminate_orphaned_client_records();
319 eliminate_orphaned_job_records();
320 eliminate_admin_records();
321 eliminate_restore_records();
323 do_interactive_mode();
326 db_close_database(NULL, db);
333 static void do_interactive_mode()
337 printf(_("Hello, this is the database check/correct program.\n"));
339 printf(_("Modify database is on."));
341 printf(_("Modify database is off."));
343 printf(_(" Verbose is on.\n"));
345 printf(_(" Verbose is off.\n"));
347 printf(_("Please select the fuction you want to perform.\n"));
352 " 1) Toggle modify database flag\n"
353 " 2) Toggle verbose flag\n"
354 " 3) Repair bad Filename records\n"
355 " 4) Repair bad Path records\n"
356 " 5) Eliminate duplicate Filename records\n"
357 " 6) Eliminate duplicate Path records\n"
358 " 7) Eliminate orphaned Jobmedia records\n"
359 " 8) Eliminate orphaned File records\n"
360 " 9) Eliminate orphaned Path records\n"
361 " 10) Eliminate orphaned Filename records\n"
362 " 11) Eliminate orphaned FileSet records\n"
363 " 12) Eliminate orphaned Client records\n"
364 " 13) Eliminate orphaned Job records\n"
365 " 14) Eliminate all Admin records\n"
366 " 15) Eliminate all Restore records\n"
371 " 1) Toggle modify database flag\n"
372 " 2) Toggle verbose flag\n"
373 " 3) Check for bad Filename records\n"
374 " 4) Check for bad Path records\n"
375 " 5) Check for duplicate Filename records\n"
376 " 6) Check for duplicate Path records\n"
377 " 7) Check for orphaned Jobmedia records\n"
378 " 8) Check for orphaned File records\n"
379 " 9) Check for orphaned Path records\n"
380 " 10) Check for orphaned Filename records\n"
381 " 11) Check for orphaned FileSet records\n"
382 " 12) Check for orphaned Client records\n"
383 " 13) Check for orphaned Job records\n"
384 " 14) Check for all Admin records\n"
385 " 15) Check for all Restore records\n"
390 cmd = get_cmd(_("Select function number: "));
392 int item = atoi(cmd);
397 printf(_("Database will be modified.\n"));
399 printf(_("Database will NOT be modified.\n"));
402 verbose = verbose?0:1;
404 printf(_(" Verbose is on.\n"));
406 printf(_(" Verbose is off.\n"));
409 repair_bad_filenames();
415 eliminate_duplicate_filenames();
418 eliminate_duplicate_paths();
421 eliminate_orphaned_jobmedia_records();
424 eliminate_orphaned_file_records();
427 eliminate_orphaned_path_records();
430 eliminate_orphaned_filename_records();
433 eliminate_orphaned_fileset_records();
436 eliminate_orphaned_client_records();
439 eliminate_orphaned_job_records();
442 eliminate_admin_records();
445 eliminate_restore_records();
448 repair_bad_filenames();
450 eliminate_duplicate_filenames();
451 eliminate_duplicate_paths();
452 eliminate_orphaned_jobmedia_records();
453 eliminate_orphaned_file_records();
454 eliminate_orphaned_path_records();
455 eliminate_orphaned_filename_records();
456 eliminate_orphaned_fileset_records();
457 eliminate_orphaned_client_records();
458 eliminate_orphaned_job_records();
459 eliminate_admin_records();
460 eliminate_restore_records();
470 static int print_name_handler(void *ctx, int num_fields, char **row)
473 printf("%s\n", row[0]);
478 static int get_name_handler(void *ctx, int num_fields, char **row)
480 POOLMEM *buf = (POOLMEM *)ctx;
482 pm_strcpy(&buf, row[0]);
487 static int print_job_handler(void *ctx, int num_fields, char **row)
489 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
490 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
495 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
497 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
498 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
502 static int print_file_handler(void *ctx, int num_fields, char **row)
504 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
505 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
509 static int print_fileset_handler(void *ctx, int num_fields, char **row)
511 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
512 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
516 static int print_client_handler(void *ctx, int num_fields, char **row)
518 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
519 NPRT(row[0]), NPRT(row[1]));
525 * Called here with each id to be added to the list
527 static int id_list_handler(void *ctx, int num_fields, char **row)
529 ID_LIST *lst = (ID_LIST *)ctx;
531 if (lst->num_ids == MAX_ID_LIST_LEN) {
534 if (lst->num_ids == lst->max_ids) {
535 if (lst->max_ids == 0) {
536 lst->max_ids = 10000;
537 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
539 lst->max_ids = (lst->max_ids * 3) / 2;
540 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
543 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
548 * Construct record id list
550 static int make_id_list(const char *query, ID_LIST *id_list)
552 id_list->num_ids = 0;
553 id_list->num_del = 0;
554 id_list->tot_ids = 0;
556 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
557 printf("%s", db_strerror(db));
564 * Delete all entries in the list
566 static int delete_id_list(const char *query, ID_LIST *id_list)
569 for (int i=0; i < id_list->num_ids; i++) {
570 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
572 printf(_("Deleting: %s\n"), buf);
574 db_sql_query(db, buf, NULL, NULL);
580 * Called here with each name to be added to the list
582 static int name_list_handler(void *ctx, int num_fields, char **row)
584 NAME_LIST *name = (NAME_LIST *)ctx;
586 if (name->num_ids == MAX_ID_LIST_LEN) {
589 if (name->num_ids == name->max_ids) {
590 if (name->max_ids == 0) {
591 name->max_ids = 10000;
592 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
594 name->max_ids = (name->max_ids * 3) / 2;
595 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
598 name->name[name->num_ids++] = bstrdup(row[0]);
604 * Construct name list
606 static int make_name_list(const char *query, NAME_LIST *name_list)
608 name_list->num_ids = 0;
609 name_list->num_del = 0;
610 name_list->tot_ids = 0;
612 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
613 printf("%s", db_strerror(db));
620 * Print names in the list
622 static void print_name_list(NAME_LIST *name_list)
624 for (int i=0; i < name_list->num_ids; i++) {
625 printf("%s\n", name_list->name[i]);
631 * Free names in the list
633 static void free_name_list(NAME_LIST *name_list)
635 for (int i=0; i < name_list->num_ids; i++) {
636 free(name_list->name[i]);
638 name_list->num_ids = 0;
641 static void eliminate_duplicate_filenames()
646 printf(_("Checking for duplicate Filename entries.\n"));
648 /* Make list of duplicated names */
649 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
650 "HAVING count(Name) > 1";
652 if (!make_name_list(query, &name_list)) {
655 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
656 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
657 print_name_list(&name_list);
663 /* Loop through list of duplicate names */
664 for (int i=0; i<name_list.num_ids; i++) {
665 /* Get all the Ids of each name */
666 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
667 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
671 if (!make_id_list(buf, &id_list)) {
675 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
677 /* Force all records to use the first id then delete the other ids */
678 for (int j=1; j<id_list.num_ids; j++) {
679 char ed1[50], ed2[50];
680 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
681 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
685 db_sql_query(db, buf, NULL, NULL);
686 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
691 db_sql_query(db, buf, NULL, NULL);
695 free_name_list(&name_list);
698 static void eliminate_duplicate_paths()
703 printf(_("Checking for duplicate Path entries.\n"));
705 /* Make list of duplicated names */
707 query = "SELECT Path, count(Path) as Count FROM Path "
708 "GROUP BY Path HAVING count(Path) > 1";
710 if (!make_name_list(query, &name_list)) {
713 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
714 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
715 print_name_list(&name_list);
721 /* Loop through list of duplicate names */
722 for (int i=0; i<name_list.num_ids; i++) {
723 /* Get all the Ids of each name */
724 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
725 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
729 if (!make_id_list(buf, &id_list)) {
733 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
735 /* Force all records to use the first id then delete the other ids */
736 for (int j=1; j<id_list.num_ids; j++) {
737 char ed1[50], ed2[50];
738 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
739 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
743 db_sql_query(db, buf, NULL, NULL);
744 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
748 db_sql_query(db, buf, NULL, NULL);
752 free_name_list(&name_list);
755 static void eliminate_orphaned_jobmedia_records()
757 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
758 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
759 "WHERE Job.JobId IS NULL LIMIT 300000";
761 printf(_("Checking for orphaned JobMedia entries.\n"));
762 if (!make_id_list(query, &id_list)) {
765 /* Loop doing 300000 at a time */
766 while (id_list.num_ids != 0) {
767 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
768 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
769 for (int i=0; i < id_list.num_ids; i++) {
771 bsnprintf(buf, sizeof(buf),
772 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
773 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
774 edit_int64(id_list.Id[i], ed1));
775 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
776 printf("%s\n", db_strerror(db));
784 if (fix && id_list.num_ids > 0) {
785 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
786 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
788 break; /* get out if not updating db */
790 if (!make_id_list(query, &id_list)) {
796 static void eliminate_orphaned_file_records()
798 const char *query = "SELECT File.FileId,Job.JobId FROM File "
799 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
800 "WHERE Job.JobId IS NULL LIMIT 300000";
802 printf(_("Checking for orphaned File entries. This may take some time!\n"));
804 printf("%s\n", query);
806 if (!make_id_list(query, &id_list)) {
809 /* Loop doing 300000 at a time */
810 while (id_list.num_ids != 0) {
811 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
812 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
813 for (int i=0; i < id_list.num_ids; i++) {
815 bsnprintf(buf, sizeof(buf),
816 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
817 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
818 edit_int64(id_list.Id[i], ed1));
819 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
820 printf("%s\n", db_strerror(db));
827 if (fix && id_list.num_ids > 0) {
828 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
829 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
831 break; /* get out if not updating db */
833 if (!make_id_list(query, &id_list)) {
839 static void eliminate_orphaned_path_records()
842 /* check the existence of the required "one column" index */
843 if (!check_idx("PathId")) {
844 if (yes_no(_("Create temporary index? (yes/no): "))) {
845 /* create temporary index PathId */
846 create_tmp_idx("idxPIchk", "File", "PathId");
850 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
851 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
852 "WHERE File.PathId IS NULL LIMIT 300000";
854 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
856 printf("%s\n", query);
858 if (!make_id_list(query, &id_list)) {
861 /* Loop doing 300000 at a time */
862 while (id_list.num_ids != 0) {
863 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
864 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
865 for (int i=0; i < id_list.num_ids; i++) {
867 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
868 edit_int64(id_list.Id[i], ed1));
869 db_sql_query(db, buf, print_name_handler, NULL);
875 if (fix && id_list.num_ids > 0) {
876 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
877 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
879 break; /* get out if not updating db */
881 if (!make_id_list(query, &id_list)) {
885 /* drop temporary index idx_tmp_name */
886 drop_tmp_idx("idxPIchk", "File");
889 static void eliminate_orphaned_filename_records()
892 /* check the existence of the required "one column" index */
893 if (!check_idx("FilenameId") ) {
894 if (yes_no(_("Create temporary index? (yes/no): "))) {
895 /* create temporary index FilenameId */
896 create_tmp_idx("idxFIchk", "File", "FilenameId");
900 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
901 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
902 "WHERE File.FilenameId IS NULL LIMIT 300000";
904 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
906 printf("%s\n", query);
908 if (!make_id_list(query, &id_list)) {
911 /* Loop doing 300000 at a time */
912 while (id_list.num_ids != 0) {
913 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
914 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
915 for (int i=0; i < id_list.num_ids; i++) {
917 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
918 edit_int64(id_list.Id[i], ed1));
919 db_sql_query(db, buf, print_name_handler, NULL);
925 if (fix && id_list.num_ids > 0) {
926 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
927 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
929 break; /* get out if not updating db */
931 if (!make_id_list(query, &id_list)) {
935 /* drop temporary index idx_tmp_name */
936 drop_tmp_idx("idxFIchk", "File");
940 static void eliminate_orphaned_fileset_records()
944 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
945 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
946 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
947 "WHERE Job.FileSetId IS NULL";
949 printf("%s\n", query);
951 if (!make_id_list(query, &id_list)) {
954 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
955 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
956 for (int i=0; i < id_list.num_ids; i++) {
958 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
959 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
960 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
961 printf("%s\n", db_strerror(db));
968 if (fix && id_list.num_ids > 0) {
969 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
970 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
974 static void eliminate_orphaned_client_records()
978 printf(_("Checking for orphaned Client entries.\n"));
980 * Wiffle through Client for every Client
981 * joining with the Job table including every Client even if
982 * there is not a match in Job (left outer join), then
983 * filter out only those where no Job points to a Client
984 * i.e. Job.Client is NULL
986 query = "SELECT Client.ClientId,Client.Name FROM Client "
987 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
988 "WHERE Job.ClientId IS NULL";
990 printf("%s\n", query);
992 if (!make_id_list(query, &id_list)) {
995 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
996 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
997 for (int i=0; i < id_list.num_ids; i++) {
999 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1000 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1001 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1002 printf("%s\n", db_strerror(db));
1009 if (fix && id_list.num_ids > 0) {
1010 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1011 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1015 static void eliminate_orphaned_job_records()
1019 printf(_("Checking for orphaned Job entries.\n"));
1021 * Wiffle through Job for every Job
1022 * joining with the Client table including every Job even if
1023 * there is not a match in Client (left outer join), then
1024 * filter out only those where no Client exists
1025 * i.e. Client.Name is NULL
1027 query = "SELECT Job.JobId,Job.Name FROM Job "
1028 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1029 "WHERE Client.Name IS NULL";
1031 printf("%s\n", query);
1033 if (!make_id_list(query, &id_list)) {
1036 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1037 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1038 for (int i=0; i < id_list.num_ids; i++) {
1040 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1041 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1042 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1043 printf("%s\n", db_strerror(db));
1050 if (fix && id_list.num_ids > 0) {
1051 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1052 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1053 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1054 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1055 printf(_("Deleting Log records of orphaned Job records.\n"));
1056 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1061 static void eliminate_admin_records()
1065 printf(_("Checking for Admin Job entries.\n"));
1066 query = "SELECT Job.JobId FROM Job "
1067 "WHERE Job.Type='D'";
1069 printf("%s\n", query);
1071 if (!make_id_list(query, &id_list)) {
1074 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1075 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1076 for (int i=0; i < id_list.num_ids; i++) {
1078 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1079 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1080 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1081 printf("%s\n", db_strerror(db));
1088 if (fix && id_list.num_ids > 0) {
1089 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1090 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1094 static void eliminate_restore_records()
1098 printf(_("Checking for Restore Job entries.\n"));
1099 query = "SELECT Job.JobId FROM Job "
1100 "WHERE Job.Type='R'";
1102 printf("%s\n", query);
1104 if (!make_id_list(query, &id_list)) {
1107 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1108 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1109 for (int i=0; i < id_list.num_ids; i++) {
1111 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1112 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1113 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1114 printf("%s\n", db_strerror(db));
1121 if (fix && id_list.num_ids > 0) {
1122 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1123 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1130 static void repair_bad_filenames()
1135 printf(_("Checking for Filenames with a trailing slash\n"));
1136 query = "SELECT FilenameId,Name from Filename "
1137 "WHERE Name LIKE '%/'";
1139 printf("%s\n", query);
1141 if (!make_id_list(query, &id_list)) {
1144 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1145 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1146 for (i=0; i < id_list.num_ids; i++) {
1148 bsnprintf(buf, sizeof(buf),
1149 "SELECT Name FROM Filename WHERE FilenameId=%s",
1150 edit_int64(id_list.Id[i], ed1));
1151 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1152 printf("%s\n", db_strerror(db));
1159 if (fix && id_list.num_ids > 0) {
1160 POOLMEM *name = get_pool_memory(PM_FNAME);
1161 char esc_name[5000];
1162 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1163 for (i=0; i < id_list.num_ids; i++) {
1166 bsnprintf(buf, sizeof(buf),
1167 "SELECT Name FROM Filename WHERE FilenameId=%s",
1168 edit_int64(id_list.Id[i], ed1));
1169 if (!db_sql_query(db, buf, get_name_handler, name)) {
1170 printf("%s\n", db_strerror(db));
1172 /* Strip trailing slash(es) */
1173 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1181 db_escape_string(NULL, db, esc_name, name, len);
1183 bsnprintf(buf, sizeof(buf),
1184 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1185 esc_name, edit_int64(id_list.Id[i], ed1));
1187 printf("%s\n", buf);
1189 db_sql_query(db, buf, NULL, NULL);
1194 static void repair_bad_paths()
1199 printf(_("Checking for Paths without a trailing slash\n"));
1200 query = "SELECT PathId,Path from Path "
1201 "WHERE Path NOT LIKE '%/'";
1203 printf("%s\n", query);
1205 if (!make_id_list(query, &id_list)) {
1208 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1209 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1210 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, print_name_handler, NULL)) {
1215 printf("%s\n", db_strerror(db));
1222 if (fix && id_list.num_ids > 0) {
1223 POOLMEM *name = get_pool_memory(PM_FNAME);
1224 char esc_name[5000];
1225 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1226 for (i=0; i < id_list.num_ids; i++) {
1229 bsnprintf(buf, sizeof(buf),
1230 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1231 if (!db_sql_query(db, buf, get_name_handler, name)) {
1232 printf("%s\n", db_strerror(db));
1234 /* Strip trailing blanks */
1235 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1238 /* Add trailing slash */
1239 len = pm_strcat(&name, "/");
1240 db_escape_string(NULL, db, esc_name, name, len);
1241 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1242 esc_name, edit_int64(id_list.Id[i], ed1));
1244 printf("%s\n", buf);
1246 db_sql_query(db, buf, NULL, NULL);
1253 * Gen next input command from the terminal
1255 static char *get_cmd(const char *prompt)
1257 static char cmd[1000];
1259 printf("%s", prompt);
1260 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1265 strip_trailing_junk(cmd);
1269 static bool yes_no(const char *prompt)
1272 cmd = get_cmd(prompt);
1277 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1280 bool python_set_prog(JCR*, char const*) { return false; }
1284 * The code below to add indexes is needed only for MySQL, and
1285 * that to improve the performance.
1290 typedef struct s_idx_list {
1292 int count_key; /* how many times the index meets *key_name */
1293 int count_col; /* how many times meets the desired column name */
1296 static IDX_LIST idx_list[MAXIDX];
1299 * Called here with each table index to be added to the list
1301 static int check_idx_handler(void *ctx, int num_fields, char **row)
1303 /* Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1304 * File | 0 | PRIMARY | 1 | FileId |...
1306 char *name, *key_name, *col_name;
1312 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1313 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1314 idx_list[i].count_key++;
1316 if (strcasecmp(col_name, name) == 0) {
1317 idx_list[i].count_col++;
1322 /* if the new Key_name, add it to the list */
1324 len = strlen(key_name) + 1;
1325 idx_list[i].key_name = (char *)malloc(len);
1326 bstrncpy(idx_list[i].key_name, key_name, len);
1327 idx_list[i].count_key = 1;
1328 if (strcasecmp(col_name, name) == 0) {
1329 idx_list[i].count_col = 1;
1331 idx_list[i].count_col = 0;
1339 * Return TRUE if "one column" index over *col_name exists
1341 static bool check_idx(const char *col_name)
1347 memset(&idx_list, 0, sizeof(idx_list));
1348 const char *query = "SHOW INDEX FROM File";
1349 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1350 printf("%s\n", db_strerror(db));
1353 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1354 /* NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index */
1355 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1356 /* "one column" index over *col_name found */
1362 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1365 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1375 * Create temporary one-column index
1377 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1378 const char *col_name)
1380 idx_tmp_name = NULL;
1381 printf(_("Create temporary index... This may take some time!\n"));
1382 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1384 printf("%s\n", buf);
1386 if (db_sql_query(db, buf, NULL, NULL)) {
1387 idx_tmp_name = idx_name;
1389 printf(_("Temporary index created.\n"));
1392 printf("%s\n", db_strerror(db));
1399 * Drop temporary index
1401 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1403 if (idx_tmp_name != NULL) {
1404 printf(_("Drop temporary index.\n"));
1405 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1407 printf("%s\n", buf);
1409 if (!db_sql_query(db, buf, NULL, NULL)) {
1410 printf("%s\n", db_strerror(db));
1414 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1418 idx_tmp_name = NULL;