2 Bacula® - The Network Backup Solution
4 Copyright (C) 2002-2011 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
30 * Program to check a Bacula database for consistency and to
33 * Kern E. Sibbald, August 2002
38 #include "cats/cats.h"
39 #include "cats/sql_glue.h"
40 #include "lib/runscript.h"
41 #include "dird/dird_conf.h"
43 extern bool parse_dir_config(CONFIG *config, const char *configfile, int exit_code);
48 int generate_daemon_event(JCR *jcr, const char *event)
51 typedef struct s_id_ctx {
52 int64_t *Id; /* ids to be modified */
53 int num_ids; /* ids stored */
54 int max_ids; /* size of array */
55 int num_del; /* number deleted */
56 int tot_ids; /* total to process */
59 typedef struct s_name_ctx {
60 char **name; /* list of names */
61 int num_ids; /* ids stored */
62 int max_ids; /* size of array */
63 int num_del; /* number deleted */
64 int tot_ids; /* total to process */
70 static bool fix = false;
71 static bool batch = false;
73 static ID_LIST id_list;
74 static NAME_LIST name_list;
75 static char buf[20000];
76 static bool quit = false;
77 static CONFIG *config;
78 static const char *idx_tmp_name;
80 #define MAX_ID_LIST_LEN 10000000
83 * Forward referenced functions
85 static void print_catalog_details(CAT *catalog, const char *working_dir);
86 static int make_id_list(const char *query, ID_LIST *id_list);
87 static int delete_id_list(const char *query, ID_LIST *id_list);
88 static int make_name_list(const char *query, NAME_LIST *name_list);
89 static void print_name_list(NAME_LIST *name_list);
90 static void free_name_list(NAME_LIST *name_list);
91 static char *get_cmd(const char *prompt);
92 static void eliminate_duplicate_filenames();
93 static void eliminate_duplicate_paths();
94 static void eliminate_orphaned_jobmedia_records();
95 static void eliminate_orphaned_file_records();
96 static void eliminate_orphaned_path_records();
97 static void eliminate_orphaned_filename_records();
98 static void eliminate_orphaned_fileset_records();
99 static void eliminate_orphaned_client_records();
100 static void eliminate_orphaned_job_records();
101 static void eliminate_admin_records();
102 static void eliminate_restore_records();
103 static void repair_bad_paths();
104 static void repair_bad_filenames();
105 static void do_interactive_mode();
106 static bool yes_no(const char *prompt);
107 static bool check_idx(const char *col_name);
108 static bool create_tmp_idx(const char *idx_name, const char *table_name,
109 const char *col_name);
110 static bool drop_tmp_idx(const char *idx_name, const char *table_name);
111 static int check_idx_handler(void *ctx, int num_fields, char **row);
116 "Usage: dbcheck [-c config ] [-B] [-C catalog name] [-d debug_level] <working-directory> <bacula-database> <user> <password> [<dbhost>] [<dbport>]\n"
118 " -C catalog name in the director conf file\n"
119 " -c Director conf filename\n"
120 " -B print catalog configuration and exit\n"
121 " -d <nn> set debug level to <nn>\n"
122 " -dt print a timestamp in debug output\n"
123 " -f fix inconsistencies\n"
124 " -t test if client library is thread-safe\n"
126 " -? print this message\n\n");
130 int main (int argc, char *argv[])
133 const char *user, *password, *db_name, *dbhost;
135 bool print_catalog=false;
136 char *configfile = NULL;
137 char *catalogname = NULL;
140 setlocale(LC_ALL, "");
141 bindtextdomain("bacula", LOCALEDIR);
142 textdomain("bacula");
145 my_name_is(argc, argv, "dbcheck");
146 init_msg(NULL, NULL); /* setup message handler */
148 memset(&id_list, 0, sizeof(id_list));
149 memset(&name_list, 0, sizeof(name_list));
151 while ((ch = getopt(argc, argv, "bc:C:d:fvB?")) != -1) {
154 print_catalog = true; /* get catalog information from config */
156 case 'b': /* batch */
159 case 'C': /* CatalogName */
160 catalogname = optarg;
162 case 'c': /* configfile */
165 case 'd': /* debug level */
166 if (*optarg == 't') {
167 dbg_timestamp = true;
169 debug_level = atoi(optarg);
170 if (debug_level <= 0) {
175 case 'f': /* fix inconsistencies */
195 Pmsg0(0, _("Warning skipping the additional parameters for working directory/dbname/user/password/host.\n"));
197 config = new_config_parser();
198 parse_dir_config(config, configfile, M_ERROR_TERM);
200 foreach_res(catalog, R_CATALOG) {
201 if (catalogname && !strcmp(catalog->hdr.name, catalogname)) {
204 } else if (!catalogname) { // stop on first if no catalogname is given
212 Pmsg2(0, _("Error can not find the Catalog name[%s] in the given config file [%s]\n"), catalogname, configfile);
214 Pmsg1(0, _("Error there is no Catalog section in the given config file [%s]\n"), configfile);
220 director = (DIRRES *)GetNextRes(R_DIRECTOR, NULL);
223 Pmsg0(0, _("Error no Director resource defined.\n"));
226 set_working_directory(director->working_directory);
229 * Print catalog information and exit (-B)
232 print_catalog_details(catalog, director->working_directory);
236 db_name = catalog->db_name;
237 user = catalog->db_user;
238 password = catalog->db_password;
239 dbhost = catalog->db_address;
240 if (dbhost && dbhost[0] == 0) {
243 dbport = catalog->db_port;
247 Pmsg0(0, _("Wrong number of arguments.\n"));
252 Pmsg0(0, _("Working directory not supplied.\n"));
257 * This is needed by SQLite to find the db
259 working_directory = argv[0];
268 } else if (argc == 3) {
271 } else if (argc == 4) {
275 } else if (argc == 5) {
280 } else if (argc == 6) {
286 dbport = strtol(argv[5], &endptr, 10);
287 if (*endptr != '\0') {
288 Pmsg0(0, _("Database port must be a numeric value.\n"));
290 } else if (errno == ERANGE) {
291 Pmsg0(0, _("Database port must be a int value.\n"));
300 db = db_init_database(NULL, NULL, db_name, user, password, dbhost, dbport, NULL, false, false);
301 if (!db_open_database(NULL, db)) {
302 Emsg1(M_FATAL, 0, "%s", db_strerror(db));
307 * Drop temporary index idx_tmp_name if it already exists
309 drop_tmp_idx("idxPIchk", "File");
313 repair_bad_filenames();
314 eliminate_duplicate_filenames();
315 eliminate_duplicate_paths();
316 eliminate_orphaned_jobmedia_records();
317 eliminate_orphaned_file_records();
318 eliminate_orphaned_path_records();
319 eliminate_orphaned_filename_records();
320 eliminate_orphaned_fileset_records();
321 eliminate_orphaned_client_records();
322 eliminate_orphaned_job_records();
323 eliminate_admin_records();
324 eliminate_restore_records();
326 do_interactive_mode();
330 * Drop temporary index idx_tmp_name
332 drop_tmp_idx("idxPIchk", "File");
334 db_close_database(NULL, db);
341 static void print_catalog_details(CAT *catalog, const char *working_dir)
343 POOLMEM *catalog_details = get_pool_memory(PM_MESSAGE);
346 * Instantiate a B_DB class and see what db_type gets assigned to it.
348 db = db_init_database(NULL, catalog->db_driver, catalog->db_name, catalog->db_user,
349 catalog->db_password, catalog->db_address,
350 catalog->db_port, catalog->db_socket,
351 catalog->mult_db_connections,
352 catalog->disable_batch_insert);
354 printf("%sdb_type=%s\nworking_dir=%s\n", catalog->display(catalog_details),
355 db->db_get_type(), working_directory);
356 db_close_database(NULL, db);
358 free_pool_memory(catalog_details);
361 static void do_interactive_mode()
365 printf(_("Hello, this is the database check/correct program.\n"));
367 printf(_("Modify database is on."));
369 printf(_("Modify database is off."));
371 printf(_(" Verbose is on.\n"));
373 printf(_(" Verbose is off.\n"));
375 printf(_("Please select the function you want to perform.\n"));
380 " 1) Toggle modify database flag\n"
381 " 2) Toggle verbose flag\n"
382 " 3) Repair bad Filename records\n"
383 " 4) Repair bad Path records\n"
384 " 5) Eliminate duplicate Filename records\n"
385 " 6) Eliminate duplicate Path records\n"
386 " 7) Eliminate orphaned Jobmedia records\n"
387 " 8) Eliminate orphaned File records\n"
388 " 9) Eliminate orphaned Path records\n"
389 " 10) Eliminate orphaned Filename records\n"
390 " 11) Eliminate orphaned FileSet records\n"
391 " 12) Eliminate orphaned Client records\n"
392 " 13) Eliminate orphaned Job records\n"
393 " 14) Eliminate all Admin records\n"
394 " 15) Eliminate all Restore records\n"
399 " 1) Toggle modify database flag\n"
400 " 2) Toggle verbose flag\n"
401 " 3) Check for bad Filename records\n"
402 " 4) Check for bad Path records\n"
403 " 5) Check for duplicate Filename records\n"
404 " 6) Check for duplicate Path records\n"
405 " 7) Check for orphaned Jobmedia records\n"
406 " 8) Check for orphaned File records\n"
407 " 9) Check for orphaned Path records\n"
408 " 10) Check for orphaned Filename records\n"
409 " 11) Check for orphaned FileSet records\n"
410 " 12) Check for orphaned Client records\n"
411 " 13) Check for orphaned Job records\n"
412 " 14) Check for all Admin records\n"
413 " 15) Check for all Restore records\n"
418 cmd = get_cmd(_("Select function number: "));
420 int item = atoi(cmd);
425 printf(_("Database will be modified.\n"));
427 printf(_("Database will NOT be modified.\n"));
430 verbose = verbose?0:1;
432 printf(_(" Verbose is on.\n"));
434 printf(_(" Verbose is off.\n"));
437 repair_bad_filenames();
443 eliminate_duplicate_filenames();
446 eliminate_duplicate_paths();
449 eliminate_orphaned_jobmedia_records();
452 eliminate_orphaned_file_records();
455 eliminate_orphaned_path_records();
458 eliminate_orphaned_filename_records();
461 eliminate_orphaned_fileset_records();
464 eliminate_orphaned_client_records();
467 eliminate_orphaned_job_records();
470 eliminate_admin_records();
473 eliminate_restore_records();
476 repair_bad_filenames();
478 eliminate_duplicate_filenames();
479 eliminate_duplicate_paths();
480 eliminate_orphaned_jobmedia_records();
481 eliminate_orphaned_file_records();
482 eliminate_orphaned_path_records();
483 eliminate_orphaned_filename_records();
484 eliminate_orphaned_fileset_records();
485 eliminate_orphaned_client_records();
486 eliminate_orphaned_job_records();
487 eliminate_admin_records();
488 eliminate_restore_records();
498 static int print_name_handler(void *ctx, int num_fields, char **row)
501 printf("%s\n", row[0]);
506 static int get_name_handler(void *ctx, int num_fields, char **row)
508 POOLMEM *name = (POOLMEM *)ctx;
511 pm_strcpy(&name, row[0]);
516 static int print_job_handler(void *ctx, int num_fields, char **row)
518 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
519 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
523 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
525 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
526 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
530 static int print_file_handler(void *ctx, int num_fields, char **row)
532 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
533 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
537 static int print_fileset_handler(void *ctx, int num_fields, char **row)
539 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
540 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
544 static int print_client_handler(void *ctx, int num_fields, char **row)
546 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
547 NPRT(row[0]), NPRT(row[1]));
552 * Called here with each id to be added to the list
554 static int id_list_handler(void *ctx, int num_fields, char **row)
556 ID_LIST *lst = (ID_LIST *)ctx;
558 if (lst->num_ids == MAX_ID_LIST_LEN) {
561 if (lst->num_ids == lst->max_ids) {
562 if (lst->max_ids == 0) {
563 lst->max_ids = 10000;
564 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
566 lst->max_ids = (lst->max_ids * 3) / 2;
567 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
570 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
575 * Construct record id list
577 static int make_id_list(const char *query, ID_LIST *id_list)
579 id_list->num_ids = 0;
580 id_list->num_del = 0;
581 id_list->tot_ids = 0;
583 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
584 printf("%s", db_strerror(db));
591 * Delete all entries in the list
593 static int delete_id_list(const char *query, ID_LIST *id_list)
596 for (int i=0; i < id_list->num_ids; i++) {
597 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
599 printf(_("Deleting: %s\n"), buf);
601 db_sql_query(db, buf, NULL, NULL);
607 * Called here with each name to be added to the list
609 static int name_list_handler(void *ctx, int num_fields, char **row)
611 NAME_LIST *name = (NAME_LIST *)ctx;
613 if (name->num_ids == MAX_ID_LIST_LEN) {
616 if (name->num_ids == name->max_ids) {
617 if (name->max_ids == 0) {
618 name->max_ids = 10000;
619 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
621 name->max_ids = (name->max_ids * 3) / 2;
622 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
625 name->name[name->num_ids++] = bstrdup(row[0]);
630 * Construct name list
632 static int make_name_list(const char *query, NAME_LIST *name_list)
634 name_list->num_ids = 0;
635 name_list->num_del = 0;
636 name_list->tot_ids = 0;
638 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
639 printf("%s", db_strerror(db));
646 * Print names in the list
648 static void print_name_list(NAME_LIST *name_list)
650 for (int i=0; i < name_list->num_ids; i++) {
651 printf("%s\n", name_list->name[i]);
656 * Free names in the list
658 static void free_name_list(NAME_LIST *name_list)
660 for (int i=0; i < name_list->num_ids; i++) {
661 free(name_list->name[i]);
663 name_list->num_ids = 0;
666 static void eliminate_duplicate_filenames()
671 printf(_("Checking for duplicate Filename entries.\n"));
674 * Make list of duplicated names
676 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
677 "HAVING count(Name) > 1";
679 if (!make_name_list(query, &name_list)) {
682 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
683 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
684 print_name_list(&name_list);
691 * Loop through list of duplicate names
693 for (int i=0; i<name_list.num_ids; i++) {
695 * Get all the Ids of each name
697 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
698 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
702 if (!make_id_list(buf, &id_list)) {
706 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
709 * Force all records to use the first id then delete the other ids
711 for (int j=1; j<id_list.num_ids; j++) {
712 char ed1[50], ed2[50];
713 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
714 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
718 db_sql_query(db, buf, NULL, NULL);
719 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
724 db_sql_query(db, buf, NULL, NULL);
728 free_name_list(&name_list);
731 static void eliminate_duplicate_paths()
736 printf(_("Checking for duplicate Path entries.\n"));
739 * Make list of duplicated names
741 query = "SELECT Path, count(Path) as Count FROM Path "
742 "GROUP BY Path HAVING count(Path) > 1";
744 if (!make_name_list(query, &name_list)) {
747 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
748 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
749 print_name_list(&name_list);
756 * Loop through list of duplicate names
758 for (int i=0; i<name_list.num_ids; i++) {
760 * Get all the Ids of each name
762 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
763 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
767 if (!make_id_list(buf, &id_list)) {
771 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
774 * Force all records to use the first id then delete the other ids
776 for (int j=1; j<id_list.num_ids; j++) {
777 char ed1[50], ed2[50];
778 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
779 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
783 db_sql_query(db, buf, NULL, NULL);
784 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
788 db_sql_query(db, buf, NULL, NULL);
792 free_name_list(&name_list);
795 static void eliminate_orphaned_jobmedia_records()
797 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
798 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
799 "WHERE Job.JobId IS NULL LIMIT 300000";
801 printf(_("Checking for orphaned JobMedia entries.\n"));
802 if (!make_id_list(query, &id_list)) {
806 * Loop doing 300000 at a time
808 while (id_list.num_ids != 0) {
809 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
810 if (id_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 JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
815 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
816 edit_int64(id_list.Id[i], ed1));
817 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
818 printf("%s\n", db_strerror(db));
826 if (fix && id_list.num_ids > 0) {
827 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
828 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
830 break; /* get out if not updating db */
832 if (!make_id_list(query, &id_list)) {
838 static void eliminate_orphaned_file_records()
840 const char *query = "SELECT File.FileId,Job.JobId FROM File "
841 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
842 "WHERE Job.JobId IS NULL LIMIT 300000";
844 printf(_("Checking for orphaned File entries. This may take some time!\n"));
846 printf("%s\n", query);
848 if (!make_id_list(query, &id_list)) {
852 * Loop doing 300000 at a time
854 while (id_list.num_ids != 0) {
855 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
856 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
857 for (int i=0; i < id_list.num_ids; i++) {
859 bsnprintf(buf, sizeof(buf),
860 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
861 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
862 edit_int64(id_list.Id[i], ed1));
863 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
864 printf("%s\n", db_strerror(db));
871 if (fix && id_list.num_ids > 0) {
872 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
873 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
875 break; /* get out if not updating db */
877 if (!make_id_list(query, &id_list)) {
883 static void eliminate_orphaned_path_records()
887 db_sql_query(db, "SELECT 1 FROM Job WHERE HasCache=1 LIMIT 1",
888 db_int64_handler, &lctx);
890 if (lctx.count == 1) {
891 printf(_("Pruning orphaned Path entries isn't possible when using BVFS.\n"));
897 * Check the existence of the required "one column" index
899 if (!check_idx("PathId")) {
900 if (yes_no(_("Create temporary index? (yes/no): "))) {
902 * create temporary index PathId
904 create_tmp_idx("idxPIchk", "File", "PathId");
908 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
909 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
910 "WHERE File.PathId IS NULL LIMIT 300000";
912 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
914 printf("%s\n", query);
916 if (!make_id_list(query, &id_list)) {
920 * Loop doing 300000 at a time
922 while (id_list.num_ids != 0) {
923 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
924 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
925 for (int i=0; i < id_list.num_ids; i++) {
927 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
928 edit_int64(id_list.Id[i], ed1));
929 db_sql_query(db, buf, print_name_handler, NULL);
935 if (fix && id_list.num_ids > 0) {
936 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
937 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
939 break; /* get out if not updating db */
941 if (!make_id_list(query, &id_list)) {
946 * Drop temporary index idx_tmp_name
948 drop_tmp_idx("idxPIchk", "File");
951 static void eliminate_orphaned_filename_records()
955 * Check the existence of the required "one column" index
957 if (!check_idx("FilenameId") ) {
958 if (yes_no(_("Create temporary index? (yes/no): "))) {
960 * Create temporary index FilenameId
962 create_tmp_idx("idxFIchk", "File", "FilenameId");
966 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
967 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
968 "WHERE File.FilenameId IS NULL LIMIT 300000";
970 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
972 printf("%s\n", query);
974 if (!make_id_list(query, &id_list)) {
978 * Loop doing 300000 at a time
980 while (id_list.num_ids != 0) {
981 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
982 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
983 for (int i=0; i < id_list.num_ids; i++) {
985 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
986 edit_int64(id_list.Id[i], ed1));
987 db_sql_query(db, buf, print_name_handler, NULL);
993 if (fix && id_list.num_ids > 0) {
994 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
995 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
997 break; /* get out if not updating db */
999 if (!make_id_list(query, &id_list)) {
1004 * Drop temporary index idx_tmp_name
1006 drop_tmp_idx("idxFIchk", "File");
1010 static void eliminate_orphaned_fileset_records()
1014 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1015 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1016 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1017 "WHERE Job.FileSetId IS NULL";
1019 printf("%s\n", query);
1021 if (!make_id_list(query, &id_list)) {
1024 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1025 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1026 for (int i=0; i < id_list.num_ids; i++) {
1028 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1029 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1030 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1031 printf("%s\n", db_strerror(db));
1038 if (fix && id_list.num_ids > 0) {
1039 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1040 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1044 static void eliminate_orphaned_client_records()
1048 printf(_("Checking for orphaned Client entries.\n"));
1051 * Wiffle through Client for every Client
1052 * joining with the Job table including every Client even if
1053 * there is not a match in Job (left outer join), then
1054 * filter out only those where no Job points to a Client
1055 * i.e. Job.Client is NULL
1057 query = "SELECT Client.ClientId,Client.Name FROM Client "
1058 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1059 "WHERE Job.ClientId IS NULL";
1061 printf("%s\n", query);
1063 if (!make_id_list(query, &id_list)) {
1066 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1067 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1068 for (int i=0; i < id_list.num_ids; i++) {
1070 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1071 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1072 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1073 printf("%s\n", db_strerror(db));
1080 if (fix && id_list.num_ids > 0) {
1081 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1082 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1086 static void eliminate_orphaned_job_records()
1090 printf(_("Checking for orphaned Job entries.\n"));
1093 * Wiffle through Job for every Job
1094 * joining with the Client table including every Job even if
1095 * there is not a match in Client (left outer join), then
1096 * filter out only those where no Client exists
1097 * i.e. Client.Name is NULL
1099 query = "SELECT Job.JobId,Job.Name FROM Job "
1100 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1101 "WHERE Client.Name IS NULL";
1103 printf("%s\n", query);
1105 if (!make_id_list(query, &id_list)) {
1108 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1109 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1110 for (int i=0; i < id_list.num_ids; i++) {
1112 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1113 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1114 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1115 printf("%s\n", db_strerror(db));
1122 if (fix && id_list.num_ids > 0) {
1123 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1124 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1125 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1126 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1127 printf(_("Deleting Log records of orphaned Job records.\n"));
1128 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1132 static void eliminate_admin_records()
1136 printf(_("Checking for Admin Job entries.\n"));
1137 query = "SELECT Job.JobId FROM Job "
1138 "WHERE Job.Type='D'";
1140 printf("%s\n", query);
1142 if (!make_id_list(query, &id_list)) {
1145 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1146 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1147 for (int i=0; i < id_list.num_ids; i++) {
1149 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1150 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1151 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1152 printf("%s\n", db_strerror(db));
1159 if (fix && id_list.num_ids > 0) {
1160 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1161 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1165 static void eliminate_restore_records()
1169 printf(_("Checking for Restore Job entries.\n"));
1170 query = "SELECT Job.JobId FROM Job "
1171 "WHERE Job.Type='R'";
1173 printf("%s\n", query);
1175 if (!make_id_list(query, &id_list)) {
1178 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1179 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1180 for (int i=0; i < id_list.num_ids; i++) {
1182 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1183 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1184 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1185 printf("%s\n", db_strerror(db));
1192 if (fix && id_list.num_ids > 0) {
1193 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1194 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1198 static void repair_bad_filenames()
1203 printf(_("Checking for Filenames with a trailing slash\n"));
1204 query = "SELECT FilenameId,Name from Filename "
1205 "WHERE Name LIKE '%/'";
1207 printf("%s\n", query);
1209 if (!make_id_list(query, &id_list)) {
1212 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1213 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1214 for (i=0; i < id_list.num_ids; i++) {
1216 bsnprintf(buf, sizeof(buf),
1217 "SELECT Name FROM Filename WHERE FilenameId=%s",
1218 edit_int64(id_list.Id[i], ed1));
1219 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1220 printf("%s\n", db_strerror(db));
1227 if (fix && id_list.num_ids > 0) {
1228 POOLMEM *name = get_pool_memory(PM_FNAME);
1229 char esc_name[5000];
1230 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1231 for (i=0; i < id_list.num_ids; i++) {
1234 bsnprintf(buf, sizeof(buf),
1235 "SELECT Name FROM Filename WHERE FilenameId=%s",
1236 edit_int64(id_list.Id[i], ed1));
1237 if (!db_sql_query(db, buf, get_name_handler, name)) {
1238 printf("%s\n", db_strerror(db));
1241 * Strip trailing slash(es)
1243 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1251 db_escape_string(NULL, db, esc_name, name, len);
1253 bsnprintf(buf, sizeof(buf),
1254 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1255 esc_name, edit_int64(id_list.Id[i], ed1));
1257 printf("%s\n", buf);
1259 db_sql_query(db, buf, NULL, NULL);
1261 free_pool_memory(name);
1265 static void repair_bad_paths()
1270 printf(_("Checking for Paths without a trailing slash\n"));
1271 query = "SELECT PathId,Path from Path "
1272 "WHERE Path NOT LIKE '%/'";
1274 printf("%s\n", query);
1276 if (!make_id_list(query, &id_list)) {
1279 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1280 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1281 for (i=0; i < id_list.num_ids; i++) {
1283 bsnprintf(buf, sizeof(buf),
1284 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1285 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1286 printf("%s\n", db_strerror(db));
1293 if (fix && id_list.num_ids > 0) {
1294 POOLMEM *name = get_pool_memory(PM_FNAME);
1295 char esc_name[5000];
1296 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1297 for (i=0; i < id_list.num_ids; i++) {
1300 bsnprintf(buf, sizeof(buf),
1301 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1302 if (!db_sql_query(db, buf, get_name_handler, name)) {
1303 printf("%s\n", db_strerror(db));
1306 * Strip trailing blanks
1308 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1312 * Add trailing slash
1314 len = pm_strcat(&name, "/");
1315 db_escape_string(NULL, db, esc_name, name, len);
1316 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1317 esc_name, edit_int64(id_list.Id[i], ed1));
1319 printf("%s\n", buf);
1321 db_sql_query(db, buf, NULL, NULL);
1323 free_pool_memory(name);
1328 * Gen next input command from the terminal
1330 static char *get_cmd(const char *prompt)
1332 static char cmd[1000];
1334 printf("%s", prompt);
1335 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1340 strip_trailing_junk(cmd);
1344 static bool yes_no(const char *prompt)
1347 cmd = get_cmd(prompt);
1352 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1355 bool python_set_prog(JCR*, char const*) { return false; }
1358 * The code below to add indexes is needed only for MySQL, and
1359 * that to improve the performance.
1363 typedef struct s_idx_list {
1365 int count_key; /* how many times the index meets *key_name */
1366 int count_col; /* how many times meets the desired column name */
1369 static IDX_LIST idx_list[MAXIDX];
1372 * Called here with each table index to be added to the list
1374 static int check_idx_handler(void *ctx, int num_fields, char **row)
1377 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1378 * File | 0 | PRIMARY | 1 | FileId |...
1380 char *name, *key_name, *col_name;
1387 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1388 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1389 idx_list[i].count_key++;
1391 if (strcasecmp(col_name, name) == 0) {
1392 idx_list[i].count_col++;
1398 * If the new Key_name, add it to the list
1401 len = strlen(key_name) + 1;
1402 idx_list[i].key_name = (char *)malloc(len);
1403 bstrncpy(idx_list[i].key_name, key_name, len);
1404 idx_list[i].count_key = 1;
1405 if (strcasecmp(col_name, name) == 0) {
1406 idx_list[i].count_col = 1;
1408 idx_list[i].count_col = 0;
1415 * Return TRUE if "one column" index over *col_name exists
1417 static bool check_idx(const char *col_name)
1421 const char *query = "SHOW INDEX FROM File";
1423 switch (db_get_type_index(db)) {
1424 case SQL_TYPE_MYSQL:
1425 memset(&idx_list, 0, sizeof(idx_list));
1426 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1427 printf("%s\n", db_strerror(db));
1429 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1431 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1433 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1435 * "one column" index over *col_name found
1442 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1445 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1454 * Create temporary one-column index
1456 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1457 const char *col_name)
1459 idx_tmp_name = NULL;
1460 printf(_("Create temporary index... This may take some time!\n"));
1461 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1463 printf("%s\n", buf);
1465 if (db_sql_query(db, buf, NULL, NULL)) {
1466 idx_tmp_name = idx_name;
1468 printf(_("Temporary index created.\n"));
1471 printf("%s\n", db_strerror(db));
1478 * Drop temporary index
1480 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1482 if (idx_tmp_name != NULL) {
1483 printf(_("Drop temporary index.\n"));
1484 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1486 printf("%s\n", buf);
1488 if (!db_sql_query(db, buf, NULL, NULL)) {
1489 printf("%s\n", db_strerror(db));
1493 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1497 idx_tmp_name = NULL;