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 *buf = 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(buf),
355 db->db_get_type(), working_directory);
356 db_close_database(NULL, db);
358 free_pool_memory(buf);
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 *buf = (POOLMEM *)ctx;
510 pm_strcpy(&buf, row[0]);
515 static int print_job_handler(void *ctx, int num_fields, char **row)
517 printf(_("JobId=%s Name=\"%s\" StartTime=%s\n"),
518 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
522 static int print_jobmedia_handler(void *ctx, int num_fields, char **row)
524 printf(_("Orphaned JobMediaId=%s JobId=%s Volume=\"%s\"\n"),
525 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
529 static int print_file_handler(void *ctx, int num_fields, char **row)
531 printf(_("Orphaned FileId=%s JobId=%s Volume=\"%s\"\n"),
532 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
536 static int print_fileset_handler(void *ctx, int num_fields, char **row)
538 printf(_("Orphaned FileSetId=%s FileSet=\"%s\" MD5=%s\n"),
539 NPRT(row[0]), NPRT(row[1]), NPRT(row[2]));
543 static int print_client_handler(void *ctx, int num_fields, char **row)
545 printf(_("Orphaned ClientId=%s Name=\"%s\"\n"),
546 NPRT(row[0]), NPRT(row[1]));
551 * Called here with each id to be added to the list
553 static int id_list_handler(void *ctx, int num_fields, char **row)
555 ID_LIST *lst = (ID_LIST *)ctx;
557 if (lst->num_ids == MAX_ID_LIST_LEN) {
560 if (lst->num_ids == lst->max_ids) {
561 if (lst->max_ids == 0) {
562 lst->max_ids = 10000;
563 lst->Id = (int64_t *)bmalloc(sizeof(int64_t) * lst->max_ids);
565 lst->max_ids = (lst->max_ids * 3) / 2;
566 lst->Id = (int64_t *)brealloc(lst->Id, sizeof(int64_t) * lst->max_ids);
569 lst->Id[lst->num_ids++] = str_to_int64(row[0]);
574 * Construct record id list
576 static int make_id_list(const char *query, ID_LIST *id_list)
578 id_list->num_ids = 0;
579 id_list->num_del = 0;
580 id_list->tot_ids = 0;
582 if (!db_sql_query(db, query, id_list_handler, (void *)id_list)) {
583 printf("%s", db_strerror(db));
590 * Delete all entries in the list
592 static int delete_id_list(const char *query, ID_LIST *id_list)
595 for (int i=0; i < id_list->num_ids; i++) {
596 bsnprintf(buf, sizeof(buf), query, edit_int64(id_list->Id[i], ed1));
598 printf(_("Deleting: %s\n"), buf);
600 db_sql_query(db, buf, NULL, NULL);
606 * Called here with each name to be added to the list
608 static int name_list_handler(void *ctx, int num_fields, char **row)
610 NAME_LIST *name = (NAME_LIST *)ctx;
612 if (name->num_ids == MAX_ID_LIST_LEN) {
615 if (name->num_ids == name->max_ids) {
616 if (name->max_ids == 0) {
617 name->max_ids = 10000;
618 name->name = (char **)bmalloc(sizeof(char *) * name->max_ids);
620 name->max_ids = (name->max_ids * 3) / 2;
621 name->name = (char **)brealloc(name->name, sizeof(char *) * name->max_ids);
624 name->name[name->num_ids++] = bstrdup(row[0]);
629 * Construct name list
631 static int make_name_list(const char *query, NAME_LIST *name_list)
633 name_list->num_ids = 0;
634 name_list->num_del = 0;
635 name_list->tot_ids = 0;
637 if (!db_sql_query(db, query, name_list_handler, (void *)name_list)) {
638 printf("%s", db_strerror(db));
645 * Print names in the list
647 static void print_name_list(NAME_LIST *name_list)
649 for (int i=0; i < name_list->num_ids; i++) {
650 printf("%s\n", name_list->name[i]);
655 * Free names in the list
657 static void free_name_list(NAME_LIST *name_list)
659 for (int i=0; i < name_list->num_ids; i++) {
660 free(name_list->name[i]);
662 name_list->num_ids = 0;
665 static void eliminate_duplicate_filenames()
670 printf(_("Checking for duplicate Filename entries.\n"));
673 * Make list of duplicated names
675 query = "SELECT Name, count(Name) as Count FROM Filename GROUP BY Name "
676 "HAVING count(Name) > 1";
678 if (!make_name_list(query, &name_list)) {
681 printf(_("Found %d duplicate Filename records.\n"), name_list.num_ids);
682 if (name_list.num_ids && verbose && yes_no(_("Print the list? (yes/no): "))) {
683 print_name_list(&name_list);
690 * Loop through list of duplicate names
692 for (int i=0; i<name_list.num_ids; i++) {
694 * Get all the Ids of each name
696 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
697 bsnprintf(buf, sizeof(buf), "SELECT FilenameId FROM Filename WHERE Name='%s'", esc_name);
701 if (!make_id_list(buf, &id_list)) {
705 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
708 * Force all records to use the first id then delete the other ids
710 for (int j=1; j<id_list.num_ids; j++) {
711 char ed1[50], ed2[50];
712 bsnprintf(buf, sizeof(buf), "UPDATE File SET FilenameId=%s WHERE FilenameId=%s",
713 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
717 db_sql_query(db, buf, NULL, NULL);
718 bsnprintf(buf, sizeof(buf), "DELETE FROM Filename WHERE FilenameId=%s",
723 db_sql_query(db, buf, NULL, NULL);
727 free_name_list(&name_list);
730 static void eliminate_duplicate_paths()
735 printf(_("Checking for duplicate Path entries.\n"));
738 * Make list of duplicated names
740 query = "SELECT Path, count(Path) as Count FROM Path "
741 "GROUP BY Path HAVING count(Path) > 1";
743 if (!make_name_list(query, &name_list)) {
746 printf(_("Found %d duplicate Path records.\n"), name_list.num_ids);
747 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
748 print_name_list(&name_list);
755 * Loop through list of duplicate names
757 for (int i=0; i<name_list.num_ids; i++) {
759 * Get all the Ids of each name
761 db_escape_string(NULL, db, esc_name, name_list.name[i], strlen(name_list.name[i]));
762 bsnprintf(buf, sizeof(buf), "SELECT PathId FROM Path WHERE Path='%s'", esc_name);
766 if (!make_id_list(buf, &id_list)) {
770 printf(_("Found %d for: %s\n"), id_list.num_ids, name_list.name[i]);
773 * Force all records to use the first id then delete the other ids
775 for (int j=1; j<id_list.num_ids; j++) {
776 char ed1[50], ed2[50];
777 bsnprintf(buf, sizeof(buf), "UPDATE File SET PathId=%s WHERE PathId=%s",
778 edit_int64(id_list.Id[0], ed1), edit_int64(id_list.Id[j], ed2));
782 db_sql_query(db, buf, NULL, NULL);
783 bsnprintf(buf, sizeof(buf), "DELETE FROM Path WHERE PathId=%s", ed2);
787 db_sql_query(db, buf, NULL, NULL);
791 free_name_list(&name_list);
794 static void eliminate_orphaned_jobmedia_records()
796 const char *query = "SELECT JobMedia.JobMediaId,Job.JobId FROM JobMedia "
797 "LEFT OUTER JOIN Job ON (JobMedia.JobId=Job.JobId) "
798 "WHERE Job.JobId IS NULL LIMIT 300000";
800 printf(_("Checking for orphaned JobMedia entries.\n"));
801 if (!make_id_list(query, &id_list)) {
805 * Loop doing 300000 at a time
807 while (id_list.num_ids != 0) {
808 printf(_("Found %d orphaned JobMedia records.\n"), id_list.num_ids);
809 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
810 for (int i=0; i < id_list.num_ids; i++) {
812 bsnprintf(buf, sizeof(buf),
813 "SELECT JobMedia.JobMediaId,JobMedia.JobId,Media.VolumeName FROM JobMedia,Media "
814 "WHERE JobMedia.JobMediaId=%s AND Media.MediaId=JobMedia.MediaId",
815 edit_int64(id_list.Id[i], ed1));
816 if (!db_sql_query(db, buf, print_jobmedia_handler, NULL)) {
817 printf("%s\n", db_strerror(db));
825 if (fix && id_list.num_ids > 0) {
826 printf(_("Deleting %d orphaned JobMedia records.\n"), id_list.num_ids);
827 delete_id_list("DELETE FROM JobMedia WHERE JobMediaId=%s", &id_list);
829 break; /* get out if not updating db */
831 if (!make_id_list(query, &id_list)) {
837 static void eliminate_orphaned_file_records()
839 const char *query = "SELECT File.FileId,Job.JobId FROM File "
840 "LEFT OUTER JOIN Job ON (File.JobId=Job.JobId) "
841 "WHERE Job.JobId IS NULL LIMIT 300000";
843 printf(_("Checking for orphaned File entries. This may take some time!\n"));
845 printf("%s\n", query);
847 if (!make_id_list(query, &id_list)) {
851 * Loop doing 300000 at a time
853 while (id_list.num_ids != 0) {
854 printf(_("Found %d orphaned File records.\n"), id_list.num_ids);
855 if (name_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
856 for (int i=0; i < id_list.num_ids; i++) {
858 bsnprintf(buf, sizeof(buf),
859 "SELECT File.FileId,File.JobId,Filename.Name FROM File,Filename "
860 "WHERE File.FileId=%s AND File.FilenameId=Filename.FilenameId",
861 edit_int64(id_list.Id[i], ed1));
862 if (!db_sql_query(db, buf, print_file_handler, NULL)) {
863 printf("%s\n", db_strerror(db));
870 if (fix && id_list.num_ids > 0) {
871 printf(_("Deleting %d orphaned File records.\n"), id_list.num_ids);
872 delete_id_list("DELETE FROM File WHERE FileId=%s", &id_list);
874 break; /* get out if not updating db */
876 if (!make_id_list(query, &id_list)) {
882 static void eliminate_orphaned_path_records()
886 * Check the existence of the required "one column" index
888 if (!check_idx("PathId")) {
889 if (yes_no(_("Create temporary index? (yes/no): "))) {
891 * create temporary index PathId
893 create_tmp_idx("idxPIchk", "File", "PathId");
897 const char *query = "SELECT DISTINCT Path.PathId,File.PathId FROM Path "
898 "LEFT OUTER JOIN File ON (Path.PathId=File.PathId) "
899 "WHERE File.PathId IS NULL LIMIT 300000";
901 printf(_("Checking for orphaned Path entries. This may take some time!\n"));
903 printf("%s\n", query);
905 if (!make_id_list(query, &id_list)) {
909 * Loop doing 300000 at a time
911 while (id_list.num_ids != 0) {
912 printf(_("Found %d orphaned Path records.\n"), id_list.num_ids);
913 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
914 for (int i=0; i < id_list.num_ids; i++) {
916 bsnprintf(buf, sizeof(buf), "SELECT Path FROM Path WHERE PathId=%s",
917 edit_int64(id_list.Id[i], ed1));
918 db_sql_query(db, buf, print_name_handler, NULL);
924 if (fix && id_list.num_ids > 0) {
925 printf(_("Deleting %d orphaned Path records.\n"), id_list.num_ids);
926 delete_id_list("DELETE FROM Path WHERE PathId=%s", &id_list);
928 break; /* get out if not updating db */
930 if (!make_id_list(query, &id_list)) {
935 * Drop temporary index idx_tmp_name
937 drop_tmp_idx("idxPIchk", "File");
940 static void eliminate_orphaned_filename_records()
944 * Check the existence of the required "one column" index
946 if (!check_idx("FilenameId") ) {
947 if (yes_no(_("Create temporary index? (yes/no): "))) {
949 * Create temporary index FilenameId
951 create_tmp_idx("idxFIchk", "File", "FilenameId");
955 const char *query = "SELECT Filename.FilenameId,File.FilenameId FROM Filename "
956 "LEFT OUTER JOIN File ON (Filename.FilenameId=File.FilenameId) "
957 "WHERE File.FilenameId IS NULL LIMIT 300000";
959 printf(_("Checking for orphaned Filename entries. This may take some time!\n"));
961 printf("%s\n", query);
963 if (!make_id_list(query, &id_list)) {
967 * Loop doing 300000 at a time
969 while (id_list.num_ids != 0) {
970 printf(_("Found %d orphaned Filename records.\n"), id_list.num_ids);
971 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
972 for (int i=0; i < id_list.num_ids; i++) {
974 bsnprintf(buf, sizeof(buf), "SELECT Name FROM Filename WHERE FilenameId=%s",
975 edit_int64(id_list.Id[i], ed1));
976 db_sql_query(db, buf, print_name_handler, NULL);
982 if (fix && id_list.num_ids > 0) {
983 printf(_("Deleting %d orphaned Filename records.\n"), id_list.num_ids);
984 delete_id_list("DELETE FROM Filename WHERE FilenameId=%s", &id_list);
986 break; /* get out if not updating db */
988 if (!make_id_list(query, &id_list)) {
993 * Drop temporary index idx_tmp_name
995 drop_tmp_idx("idxFIchk", "File");
999 static void eliminate_orphaned_fileset_records()
1003 printf(_("Checking for orphaned FileSet entries. This takes some time!\n"));
1004 query = "SELECT FileSet.FileSetId,Job.FileSetId FROM FileSet "
1005 "LEFT OUTER JOIN Job ON (FileSet.FileSetId=Job.FileSetId) "
1006 "WHERE Job.FileSetId IS NULL";
1008 printf("%s\n", query);
1010 if (!make_id_list(query, &id_list)) {
1013 printf(_("Found %d orphaned FileSet records.\n"), id_list.num_ids);
1014 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1015 for (int i=0; i < id_list.num_ids; i++) {
1017 bsnprintf(buf, sizeof(buf), "SELECT FileSetId,FileSet,MD5 FROM FileSet "
1018 "WHERE FileSetId=%s", edit_int64(id_list.Id[i], ed1));
1019 if (!db_sql_query(db, buf, print_fileset_handler, NULL)) {
1020 printf("%s\n", db_strerror(db));
1027 if (fix && id_list.num_ids > 0) {
1028 printf(_("Deleting %d orphaned FileSet records.\n"), id_list.num_ids);
1029 delete_id_list("DELETE FROM FileSet WHERE FileSetId=%s", &id_list);
1033 static void eliminate_orphaned_client_records()
1037 printf(_("Checking for orphaned Client entries.\n"));
1040 * Wiffle through Client for every Client
1041 * joining with the Job table including every Client even if
1042 * there is not a match in Job (left outer join), then
1043 * filter out only those where no Job points to a Client
1044 * i.e. Job.Client is NULL
1046 query = "SELECT Client.ClientId,Client.Name FROM Client "
1047 "LEFT OUTER JOIN Job ON (Client.ClientId=Job.ClientId) "
1048 "WHERE Job.ClientId IS NULL";
1050 printf("%s\n", query);
1052 if (!make_id_list(query, &id_list)) {
1055 printf(_("Found %d orphaned Client records.\n"), id_list.num_ids);
1056 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1057 for (int i=0; i < id_list.num_ids; i++) {
1059 bsnprintf(buf, sizeof(buf), "SELECT ClientId,Name FROM Client "
1060 "WHERE ClientId=%s", edit_int64(id_list.Id[i], ed1));
1061 if (!db_sql_query(db, buf, print_client_handler, NULL)) {
1062 printf("%s\n", db_strerror(db));
1069 if (fix && id_list.num_ids > 0) {
1070 printf(_("Deleting %d orphaned Client records.\n"), id_list.num_ids);
1071 delete_id_list("DELETE FROM Client WHERE ClientId=%s", &id_list);
1075 static void eliminate_orphaned_job_records()
1079 printf(_("Checking for orphaned Job entries.\n"));
1082 * Wiffle through Job for every Job
1083 * joining with the Client table including every Job even if
1084 * there is not a match in Client (left outer join), then
1085 * filter out only those where no Client exists
1086 * i.e. Client.Name is NULL
1088 query = "SELECT Job.JobId,Job.Name FROM Job "
1089 "LEFT OUTER JOIN Client ON (Job.ClientId=Client.ClientId) "
1090 "WHERE Client.Name IS NULL";
1092 printf("%s\n", query);
1094 if (!make_id_list(query, &id_list)) {
1097 printf(_("Found %d orphaned Job records.\n"), id_list.num_ids);
1098 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1099 for (int i=0; i < id_list.num_ids; i++) {
1101 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1102 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1103 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1104 printf("%s\n", db_strerror(db));
1111 if (fix && id_list.num_ids > 0) {
1112 printf(_("Deleting %d orphaned Job records.\n"), id_list.num_ids);
1113 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1114 printf(_("Deleting JobMedia records of orphaned Job records.\n"));
1115 delete_id_list("DELETE FROM JobMedia WHERE JobId=%s", &id_list);
1116 printf(_("Deleting Log records of orphaned Job records.\n"));
1117 delete_id_list("DELETE FROM Log WHERE JobId=%s", &id_list);
1121 static void eliminate_admin_records()
1125 printf(_("Checking for Admin Job entries.\n"));
1126 query = "SELECT Job.JobId FROM Job "
1127 "WHERE Job.Type='D'";
1129 printf("%s\n", query);
1131 if (!make_id_list(query, &id_list)) {
1134 printf(_("Found %d Admin Job records.\n"), id_list.num_ids);
1135 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1136 for (int i=0; i < id_list.num_ids; i++) {
1138 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1139 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1140 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1141 printf("%s\n", db_strerror(db));
1148 if (fix && id_list.num_ids > 0) {
1149 printf(_("Deleting %d Admin Job records.\n"), id_list.num_ids);
1150 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1154 static void eliminate_restore_records()
1158 printf(_("Checking for Restore Job entries.\n"));
1159 query = "SELECT Job.JobId FROM Job "
1160 "WHERE Job.Type='R'";
1162 printf("%s\n", query);
1164 if (!make_id_list(query, &id_list)) {
1167 printf(_("Found %d Restore Job records.\n"), id_list.num_ids);
1168 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1169 for (int i=0; i < id_list.num_ids; i++) {
1171 bsnprintf(buf, sizeof(buf), "SELECT JobId,Name,StartTime FROM Job "
1172 "WHERE JobId=%s", edit_int64(id_list.Id[i], ed1));
1173 if (!db_sql_query(db, buf, print_job_handler, NULL)) {
1174 printf("%s\n", db_strerror(db));
1181 if (fix && id_list.num_ids > 0) {
1182 printf(_("Deleting %d Restore Job records.\n"), id_list.num_ids);
1183 delete_id_list("DELETE FROM Job WHERE JobId=%s", &id_list);
1187 static void repair_bad_filenames()
1192 printf(_("Checking for Filenames with a trailing slash\n"));
1193 query = "SELECT FilenameId,Name from Filename "
1194 "WHERE Name LIKE '%/'";
1196 printf("%s\n", query);
1198 if (!make_id_list(query, &id_list)) {
1201 printf(_("Found %d bad Filename records.\n"), id_list.num_ids);
1202 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1203 for (i=0; i < id_list.num_ids; i++) {
1205 bsnprintf(buf, sizeof(buf),
1206 "SELECT Name FROM Filename WHERE FilenameId=%s",
1207 edit_int64(id_list.Id[i], ed1));
1208 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1209 printf("%s\n", db_strerror(db));
1216 if (fix && id_list.num_ids > 0) {
1217 POOLMEM *name = get_pool_memory(PM_FNAME);
1218 char esc_name[5000];
1219 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1220 for (i=0; i < id_list.num_ids; i++) {
1223 bsnprintf(buf, sizeof(buf),
1224 "SELECT Name FROM Filename WHERE FilenameId=%s",
1225 edit_int64(id_list.Id[i], ed1));
1226 if (!db_sql_query(db, buf, get_name_handler, name)) {
1227 printf("%s\n", db_strerror(db));
1230 * Strip trailing slash(es)
1232 for (len=strlen(name); len > 0 && IsPathSeparator(name[len-1]); len--)
1240 db_escape_string(NULL, db, esc_name, name, len);
1242 bsnprintf(buf, sizeof(buf),
1243 "UPDATE Filename SET Name='%s' WHERE FilenameId=%s",
1244 esc_name, edit_int64(id_list.Id[i], ed1));
1246 printf("%s\n", buf);
1248 db_sql_query(db, buf, NULL, NULL);
1253 static void repair_bad_paths()
1258 printf(_("Checking for Paths without a trailing slash\n"));
1259 query = "SELECT PathId,Path from Path "
1260 "WHERE Path NOT LIKE '%/'";
1262 printf("%s\n", query);
1264 if (!make_id_list(query, &id_list)) {
1267 printf(_("Found %d bad Path records.\n"), id_list.num_ids);
1268 if (id_list.num_ids && verbose && yes_no(_("Print them? (yes/no): "))) {
1269 for (i=0; i < id_list.num_ids; i++) {
1271 bsnprintf(buf, sizeof(buf),
1272 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1273 if (!db_sql_query(db, buf, print_name_handler, NULL)) {
1274 printf("%s\n", db_strerror(db));
1281 if (fix && id_list.num_ids > 0) {
1282 POOLMEM *name = get_pool_memory(PM_FNAME);
1283 char esc_name[5000];
1284 printf(_("Reparing %d bad Filename records.\n"), id_list.num_ids);
1285 for (i=0; i < id_list.num_ids; i++) {
1288 bsnprintf(buf, sizeof(buf),
1289 "SELECT Path FROM Path WHERE PathId=%s", edit_int64(id_list.Id[i], ed1));
1290 if (!db_sql_query(db, buf, get_name_handler, name)) {
1291 printf("%s\n", db_strerror(db));
1294 * Strip trailing blanks
1296 for (len=strlen(name); len > 0 && name[len-1]==' '; len--) {
1300 * Add trailing slash
1302 len = pm_strcat(&name, "/");
1303 db_escape_string(NULL, db, esc_name, name, len);
1304 bsnprintf(buf, sizeof(buf), "UPDATE Path SET Path='%s' WHERE PathId=%s",
1305 esc_name, edit_int64(id_list.Id[i], ed1));
1307 printf("%s\n", buf);
1309 db_sql_query(db, buf, NULL, NULL);
1315 * Gen next input command from the terminal
1317 static char *get_cmd(const char *prompt)
1319 static char cmd[1000];
1321 printf("%s", prompt);
1322 if (fgets(cmd, sizeof(cmd), stdin) == NULL) {
1327 strip_trailing_junk(cmd);
1331 static bool yes_no(const char *prompt)
1334 cmd = get_cmd(prompt);
1339 return (strcasecmp(cmd, "yes") == 0) || (strcasecmp(cmd, _("yes")) == 0);
1342 bool python_set_prog(JCR*, char const*) { return false; }
1345 * The code below to add indexes is needed only for MySQL, and
1346 * that to improve the performance.
1350 typedef struct s_idx_list {
1352 int count_key; /* how many times the index meets *key_name */
1353 int count_col; /* how many times meets the desired column name */
1356 static IDX_LIST idx_list[MAXIDX];
1359 * Called here with each table index to be added to the list
1361 static int check_idx_handler(void *ctx, int num_fields, char **row)
1364 * Table | Non_unique | Key_name | Seq_in_index | Column_name |...
1365 * File | 0 | PRIMARY | 1 | FileId |...
1367 char *name, *key_name, *col_name;
1374 for(i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX); i++) {
1375 if (strcasecmp(idx_list[i].key_name, key_name) == 0 ) {
1376 idx_list[i].count_key++;
1378 if (strcasecmp(col_name, name) == 0) {
1379 idx_list[i].count_col++;
1385 * If the new Key_name, add it to the list
1388 len = strlen(key_name) + 1;
1389 idx_list[i].key_name = (char *)malloc(len);
1390 bstrncpy(idx_list[i].key_name, key_name, len);
1391 idx_list[i].count_key = 1;
1392 if (strcasecmp(col_name, name) == 0) {
1393 idx_list[i].count_col = 1;
1395 idx_list[i].count_col = 0;
1402 * Return TRUE if "one column" index over *col_name exists
1404 static bool check_idx(const char *col_name)
1408 const char *query = "SHOW INDEX FROM File";
1410 switch (db_get_type_index(db)) {
1411 case SQL_TYPE_MYSQL:
1412 memset(&idx_list, 0, sizeof(idx_list));
1413 if (!db_sql_query(db, query, check_idx_handler, (void *)col_name)) {
1414 printf("%s\n", db_strerror(db));
1416 for (i = 0; (idx_list[i].key_name != NULL) && (i < MAXIDX) ; i++) {
1418 * NOTE : if (idx_list[i].count_key > 1) then index idx_list[i].key_name is "multiple-column" index
1420 if ((idx_list[i].count_key == 1) && (idx_list[i].count_col == 1)) {
1422 * "one column" index over *col_name found
1429 printf(_("Ok. Index over the %s column already exists and dbcheck will work faster.\n"), col_name);
1432 printf(_("Note. Index over the %s column not found, that can greatly slow down dbcheck.\n"), col_name);
1441 * Create temporary one-column index
1443 static bool create_tmp_idx(const char *idx_name, const char *table_name,
1444 const char *col_name)
1446 idx_tmp_name = NULL;
1447 printf(_("Create temporary index... This may take some time!\n"));
1448 bsnprintf(buf, sizeof(buf), "CREATE INDEX %s ON %s (%s)", idx_name, table_name, col_name);
1450 printf("%s\n", buf);
1452 if (db_sql_query(db, buf, NULL, NULL)) {
1453 idx_tmp_name = idx_name;
1455 printf(_("Temporary index created.\n"));
1458 printf("%s\n", db_strerror(db));
1465 * Drop temporary index
1467 static bool drop_tmp_idx(const char *idx_name, const char *table_name)
1469 if (idx_tmp_name != NULL) {
1470 printf(_("Drop temporary index.\n"));
1471 bsnprintf(buf, sizeof(buf), "DROP INDEX %s ON %s", idx_name, table_name);
1473 printf("%s\n", buf);
1475 if (!db_sql_query(db, buf, NULL, NULL)) {
1476 printf("%s\n", db_strerror(db));
1480 printf(_("Temporary index %s deleted.\n"), idx_tmp_name);
1484 idx_tmp_name = NULL;