Index: WORK/src/cats/protos.h =================================================================== RCS file: /cvs/netpilot/GPL/bacula-5.0.3/WORK/src/cats/protos.h,v retrieving revision 1.1 retrieving revision 1.2 diff -u -r1.1 -r1.2 --- WORK/src/cats/protos.h +++ WORK/src/cats/protos.h @@ -90,6 +90,8 @@ int db_delete_media_record(JCR *jcr, B_DB *mdb, MEDIA_DBR *mr); /* sql_find.c */ +int db_volume_has_dependents(JCR *jcr, B_DB *mdb, int mediaid); +void db_volume_list_dependents(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER sendit, void *ctx); bool db_find_last_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime, int JobLevel); bool db_find_job_start_time(JCR *jcr, B_DB *mdb, JOB_DBR *jr, POOLMEM **stime); bool db_find_last_jobid(JCR *jcr, B_DB *mdb, const char *Name, JOB_DBR *jr); @@ -131,6 +133,7 @@ void db_list_files_for_job(JCR *jcr, B_DB *db, uint32_t jobid, DB_LIST_HANDLER sendit, void *ctx); void db_list_media_records(JCR *jcr, B_DB *mdb, MEDIA_DBR *mdbr, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); void db_list_jobmedia_records(JCR *jcr, B_DB *mdb, JobId_t JobId, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); +void db_list_jobandmedia_records(JCR *jcr, B_DB *mdb, JobId_t JobId, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); void db_list_joblog_records(JCR *jcr, B_DB *mdb, JobId_t JobId, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); int db_list_sql_query(JCR *jcr, B_DB *mdb, const char *query, DB_LIST_HANDLER *sendit, void *ctx, int verbose, e_list_type type); void db_list_client_records(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER *sendit, void *ctx, e_list_type type); Index: WORK/src/cats/sql_cmds.c =================================================================== RCS file: /cvs/netpilot/GPL/bacula-5.0.3/WORK/src/cats/sql_cmds.c,v retrieving revision 1.1 retrieving revision 1.2 diff -u -r1.1 -r1.2 --- WORK/src/cats/sql_cmds.c +++ WORK/src/cats/sql_cmds.c @@ -95,10 +95,13 @@ const char *del_JobMedia = "DELETE FROM JobMedia WHERE JobId=%s"; const char *cnt_JobMedia = "SELECT count(*) FROM JobMedia WHERE MediaId=%s"; +/* Graham says: This was hacked so that it also selects jobs + in error, as well as those past the retention time. As of 2009-08-25, it + is only used in one place - ua_prune.c */ const char *sel_JobMedia = "SELECT DISTINCT JobMedia.JobId FROM JobMedia,Job " "WHERE MediaId=%s AND Job.JobId=JobMedia.JobId " - "AND Job.JobTDate<%s"; + "AND (Job.JobTDate<%s OR Job.JobStatus IN ('A','E','f'))"; /* Count Select JobIds for File deletion */ const char *count_select_job = Index: WORK/src/cats/sql_find.c =================================================================== RCS file: /cvs/netpilot/GPL/bacula-5.0.3/WORK/src/cats/sql_find.c,v retrieving revision 1.1 retrieving revision 1.2 diff -u -r1.1 -r1.2 --- WORK/src/cats/sql_find.c +++ WORK/src/cats/sql_find.c @@ -47,6 +47,578 @@ #if HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI +struct jmed +{ + long jobid; + long mediaid; + int expired; + int dependents; // others depend upon this one + int dependents_expired; // all that depend upon this one have expired +}; + +static int add_jmed(struct jmed ***jds, int *arrlen, const char *jobid, const char *mediaid, int expired, int dependents) +{ + struct jmed *j=NULL; + if(!(*jds=(struct jmed **)brealloc(*jds, + ((*arrlen)+2)*sizeof(struct jmed *))) + || !(j=(struct jmed *)bcalloc(1, sizeof(struct jmed)))) + { + return -1; + } + j->jobid=atol(jobid?:"0"); + j->mediaid=atol(mediaid?:"0"); + j->expired=expired; + j->dependents=dependents; + j->dependents_expired=0; + (*jds)[(*arrlen)++]=j; + return 0; +} + +/* The list comes out with the next dependent being the next in the list (if + the current item has the 'dependents' flag set. */ +static void get_whether_dependents_have_expired(struct jmed **jdeps, int jarrlen) +{ + int x=0; + // Go down the list backwards, figuring out whether dependents have + // expired. + for(x=jarrlen-1; x>=0; x--) + { + int y=x+1; + + if(ydependents) + { + // If the next one that x depends on + // has expired, and all the ones that that + // depends on have also expired, then all those + // that depend on x have also expired. + if(jdeps[y]->dependents_expired + && jdeps[y]->expired) + jdeps[x]->dependents_expired=1; + else + // If either of the above are false, the + // dependents have not expired. + jdeps[x]->dependents_expired=0; + } + else + { + // Has no dependents, therefore dependents + // have expired. + jdeps[x]->dependents_expired=1; + } + } + else + { + // End of the list - has no dependents, therefore + // dependents have expired. + jdeps[x]->dependents_expired=1; + } +/* + syslog(LOG_INFO, "get_whether: %li %li e:%d d:%d de:%d", + jdeps[x]->jobid, + jdeps[x]->mediaid, + jdeps[x]->expired, + jdeps[x]->dependents, + jdeps[x]->dependents_expired); +*/ + } +} + +static void get_dependents(JCR *jcr, B_DB *mdb, struct jmed ***jdeps, int *jarrlen) +{ + SQL_ROW row; + char skip[50]; + char curr[50]=""; + int expired=0; + int has_dependents=0; + + while((row=sql_fetch_row(mdb))) { + expired=atoi(row[9]); + + // Remember, there might be more than one job in a volume. + // if we did not find a dependent for a job, we need to + // continue and test the next job in the volume. + // While we are using one job per volume, we will never + // see this. + if(*skip) + { + if(!strcmp(row[0], skip)) continue; + *skip='\0'; + } + + if(strcmp(row[0], curr)) + { + if(*curr) add_jmed(jdeps, jarrlen, curr, + NULL, expired, has_dependents); + // We will be checking more + // jobids and need to reset has_dependents + has_dependents=0; + bsnprintf(curr, sizeof(curr), "%s", row[0]); + } + + if( !(row[3]) // It is NULL on the last diff/incr in a chain + || *(row[3])==L_FULL + || *(row[3])==L_BASE) + { + bsnprintf(skip, sizeof(skip), "%s", row[0]); + // Carry on to the next job in the volume to check + // the dependents for. + continue; + } + + // If the job in the volume is L_FULL, and the one we are + // checking is L_INCREMENTAL, or L_DIFFERENTIAL, the one we + // are checking depends upon the full. + if(*(row[1])==L_FULL || *(row[1])==L_BASE) + { + if((*(row[3])==L_INCREMENTAL + || (*(row[3])==L_DIFFERENTIAL))) { + // Extra check for virtuals. + // If a full on the left and a diff/incr on the + // right have the same StartTime, then the + // diff/incr is *not* dependent on the full. + // It is actually the diff/incr that the full + // was based on. + if(strcmp(row[7], row[8])) has_dependents++; + } + } + // Incrementals depend on previous incrementals. + else if(*(row[1])==L_DIFFERENTIAL || (*row[1])==L_INCREMENTAL) + { + if((*row[3])==L_INCREMENTAL) { + has_dependents++; + } + // Differential is since the last full backup, + // so this one is fine. + else if((*row[3])==L_DIFFERENTIAL) { + bsnprintf(skip, sizeof(skip), "%s", row[0]); + continue; + } + } + } + + add_jmed(jdeps, jarrlen, curr, NULL, expired, has_dependents); + + get_whether_dependents_have_expired(*jdeps, *jarrlen); +} + +int db_volume_has_dependents(JCR *jcr, B_DB *mdb, int mediaid) +{ + int x=0; + char ed1[50]; + char ed2[50]; + int jarrlen=0; + int has_dependents=0; + struct jmed **jdeps=NULL; + + db_lock(mdb); + + /* For each Job in the Media, find all the jobs that might depend on + it. That is, those jobs that match the name, ClientId, FileSetId, + whose start time is later than our end time, and are not + in error conditions that might be recovered. */ + /* 2009-08-25: Added 'JobMedia km' so as not to select jobs that have + no JobMedia records. We found that bacula never removes such jobs, + and this would cause the system to never recycle volumes that had + such a job as a dependency. */ + /* The ORDER BY ... k.Level DESC on the end is in attempt to sort + redundant incrementals (I) that have been superceded by + virtualfulls (F) so that they appear first in the list and will + therefore get purged before the virtualfull. */ + /* 14/04/2010: Added a wrapping select that does a LEFT JOIN, so that + we also get returned the JobIds that do not have dependents. */ + /* 26/04/2010: Get the Expiry status on the outer select, so that it + is filled in for JobIds that do not have dependents. */ + + Mmsg(mdb->cmd, +"SELECT J.JobId, J.Level, KJobId, K.Level, K.name, J.ClientId," +" J.FilesetId, J.StartTime, K.StartTime," +" if(J.JobTDate+m.Volretentioncmd)) { + Mmsg2(&mdb->errmsg, _("Query error for Jobs in MediaId request: ERR=%s\nCMD=%s\n"), sql_strerror(mdb), mdb->cmd); + db_unlock(mdb); + return -1; + } + + get_dependents(jcr, mdb, &jdeps, &jarrlen); + + sql_free_result(mdb); + + db_unlock(mdb); + +// Jmsg(jcr, M_INFO, 0, _("MediaId %d has %sdependents.\n"), +// mediaid, jarrlen?"":_("no ")); + // Free the list + for(x=0; xdependents) has_dependents++; + free(jdeps[x]); + } + if(jarrlen) free(jdeps); + + return has_dependents; +} + +static void db_volume_get_all_dependents(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER sendit, void *ctx, struct jmed ***jdeps, int *jarrlen) +{ + db_lock(mdb); + + /* Same as in the function above, but do not specify MediaId. */ + Mmsg(mdb->cmd, +"SELECT J.JobId, J.Level, KJobId, K.Level, K.name, J.ClientId," +" J.FilesetId, J.StartTime, K.StartTime," +" if(J.JobTDate+m.Volretentioncmd)) { + char msg[1024]=""; + bsnprintf(msg, sizeof(msg), _("Query error for Jobs in mediadeps request: ERR=%s\nCMD=%s\n"), sql_strerror(mdb), mdb->cmd); + sendit(ctx, msg); + db_unlock(mdb); + return; + } + + get_dependents(jcr, mdb, jdeps, jarrlen); + + sql_free_result(mdb); + + db_unlock(mdb); +} + +void db_volume_list_dependents(JCR *jcr, B_DB *mdb, DB_LIST_HANDLER sendit, void *ctx) +{ + int x=0; + int jarrlen=0; + struct jmed **jdeps=NULL; + db_volume_get_all_dependents(jcr, mdb, sendit, ctx, &jdeps, &jarrlen); + if(ctx && jarrlen) + { + char msg[256]=""; + //sendit(ctx, "JobIds that other JobIds depend upon:\n"); + for(x=0; xjobid); + sendit(ctx, msg); + if(jdeps[x]->dependents) for(y=x+1; yjobid); + sendit(ctx, msg); + if(!jdeps[y]->dependents) break; + } + sendit(ctx, "\n"); + } + } + // Free the list + for(x=0; xcmd, + "SELECT DISTINCT m.MediaId, VolBytes>1, j.JobStatus," + " JobTDate+m.VolRetention<=UNIX_TIMESTAMP(), m.VolStatus, j.JobId" + " FROM Media m " + "LEFT JOIN JobMedia jm ON jm.MediaId=m.MediaId " + "LEFT JOIN Job j ON j.JobId=jm.JobId "); + + if (!QUERY_DB(jcr, mdb, mdb->cmd)) { + db_unlock(mdb); + return -1; + } + // Get all the jobids and mediaids. + while((row=sql_fetch_row(mdb))) { + // Ignore it if nothing was written to it. + if(!row[1] || *row[1]=='0') continue; + + // No JobStatus means that there are no JobMedia records, and + // the volume can be purged. + if(!row[2] + // 'A', 'E', or 'f' also means that it failed. + || !strcmp(row[2], "A") + || !strcmp(row[2], "E") + || !strcmp(row[2], "f")) + expired=1; + else if(row[3] && *row[3]=='1' + || (row[4] && + (!strcmp(row[4], "Purged") || !strcmp(row[4], "Recycle")))) + expired=1; + else + expired=0; + + add_jmed(jds, arrlen, row[5], row[0], expired, 0); + } + if(*arrlen) (*jds)[*arrlen]=NULL; + sql_free_result(mdb); + db_unlock(mdb); + + // Get a list of all the jobids and dependencies + db_volume_get_all_dependents(jcr, mdb, NULL, NULL, &jdeps, &jarrlen); + + //for(x=0; xjobid); + + // Mark up our master list with the dependency information. + for(x=0; x<*arrlen; x++) + { + int y=0; + struct jmed *j=(*jds)[x]; + + if(!j->jobid || !j->mediaid) continue; + + for(y=0; yjobid==jdeps[y]->jobid) + { + j->dependents=jdeps[y]->dependents; + j->dependents_expired=jdeps[y]->dependents_expired; + // Do not set 'expired', as we have already got it. + //j->expired=jdeps[y]->expired; + } + } + + //for(x=0; x<*arrlen; x++) + // syslog(LOG_INFO, "%li %li e:%d d:%d de:%d", + // (*jds)[x]->jobid, (*jds)[x]->mediaid, (*jds)[x]->expired, + // (*jds)[x]->dependents, (*jds)[x]->dependents_expired); + + // Free the list of mediaids that have dependents. + for(x=0; xmediaid, + jds[i]->expired, + jds[i]->dependents); + + sendit(ctx, msg); + */ + if(!error) + { + if(insert_into_temp_dep_table(mdb, + "KeepOrReuse", jds[i]->mediaid, + jds[i]->expired, + jds[i]->dependents, + jds[i]->dependents_expired)) error++; + } + free(jds[i]); + } + if(arrlen) free(jds); + if(error) return -1; + return 0; +} + +/* Added by Graham: combine the job/jobmedia/media tables to have a more useful + view. */ +void db_list_jobandmedia_records(JCR *jcr, B_DB *mdb, uint32_t JobId, + DB_LIST_HANDLER *sendit, void *ctx, e_list_type type) +{ + char ed1[50]; + int arrlen=0; + struct jmed **jd=NULL; + + if(get_mediaids_with_expiry_and_dependence_statuses(jcr, + mdb, sendit, ctx, &jd, &arrlen)) + return; + + db_lock(mdb); + + if(setup_temp_dep_table(mdb, jd, arrlen, sendit, ctx)) + { + drop_temp_dep_table(mdb, "KeepOrReuse"); + db_unlock(mdb); + return; + } +if (JobId > 0) { // do by JobId + // Graham: I was previously using this... + // "SEC_TO_TIME(m.`VolRetention`) AS VolRetention," + // ...but SEC_TO_TIME has an upper bound of 838:59:59, so it goes + // wrong if you have a volume retention greater than a month. + // So, instead, divide the seconds by 3600 to give the figure in + // hours. This is OK as long as we do not offer retention times + // broken down to intervals less than an hour. + Mmsg(mdb->cmd, + "SELECT DISTINCT j.JobId,m.MediaId,m.VolumeName,c.Name as Client," + "j.Name,Level,JobStatusLong,SchedTime,StartTime,EndTime," + "TIMEDIFF(`EndTime`,`StartTime`) AS Duration," + "CONCAT(TRUNCATE(m.`VolRetention`/3600, 0),':00:00') AS VolumeRetention," + "FROM_UNIXTIME(`JobTDate`+m.`VolRetention`) AS ExpireTime," + "JobTDate+m.VolRetention AS ExpireTimeSecs," + "VolBytes,FileSet,p.Name AS PoolName," + "t.Name AS StorageName," + "kor.Required, " + "j.JobId IS NULL AS isnull " + "FROM KeepOrReuse kor " + "LEFT JOIN Media m ON kor.MediaId=m.MediaId " + "LEFT JOIN JobMedia jm ON jm.MediaId=m.MediaId " + "LEFT JOIN Job j ON j.JobId=jm.JobId " + "LEFT JOIN Client c ON j.ClientId=c.ClientId " + "LEFT JOIN Pool p ON j.PoolId=p.PoolId " + "LEFT JOIN FileSet f ON j.FileSetid=f.FileSetid " + "LEFT JOIN Status s ON j.JobStatus=s.JobStatus " + "LEFT JOIN Storage t ON m.StorageId=t.StorageId " + "WHERE JobId='%s' ", + "ORDER BY isnull ASC, SchedTime ASC", + edit_int64(JobId, ed1)); +} else { + Mmsg(mdb->cmd, + "SELECT DISTINCT j.JobId,m.MediaId,m.VolumeName,c.Name as Client," + "j.Name,Level,JobStatusLong,SchedTime,StartTime,EndTime," + "TIMEDIFF(`EndTime`,`StartTime`) AS Duration," + "CONCAT(TRUNCATE(m.`VolRetention`/3600, 0),':00:00') AS VolumeRetention," + "FROM_UNIXTIME(`JobTDate`+m.`VolRetention`) AS ExpireTime," + "JobTDate+m.VolRetention AS ExpireTimeSecs," + "VolBytes,FileSet,p.Name AS PoolName," + "t.Name AS StorageName," + "kor.Required, " + "j.JobId IS NULL AS isnull " + "FROM KeepOrReuse kor " + "LEFT JOIN Media m ON kor.MediaId=m.MediaId " + "LEFT JOIN JobMedia jm ON jm.MediaId=m.MediaId " + "LEFT JOIN Job j ON j.JobId=jm.JobId " + "LEFT JOIN Client c ON j.ClientId=c.ClientId " + "LEFT JOIN Pool p ON j.PoolId=p.PoolId " + "LEFT JOIN FileSet f ON j.FileSetid=f.FileSetid " + "LEFT JOIN Status s ON j.JobStatus=s.JobStatus " + "LEFT JOIN Storage t ON m.StorageId=t.StorageId " + "ORDER BY isnull ASC, SchedTime ASC"); +} + if(!QUERY_DB(jcr, mdb, mdb->cmd)) { + drop_temp_dep_table(mdb, "KeepOrReuse"); + db_unlock(mdb); + return; + } + + list_result(jcr, mdb, sendit, ctx, type); + sql_free_result(mdb); + drop_temp_dep_table(mdb, "KeepOrReuse"); + + db_unlock(mdb); +} + /* ----------------------------------------------------------------------- * * Generic Routines (or almost generic) Index: WORK/src/dird/autoprune.c =================================================================== RCS file: /cvs/netpilot/GPL/bacula-5.0.3/WORK/src/dird/autoprune.c,v retrieving revision 1.1 retrieving revision 1.3 diff -u -r1.1 -r1.3 --- WORK/src/dird/autoprune.c +++ WORK/src/dird/autoprune.c @@ -181,6 +181,16 @@ /* Prune only Volumes with status "Full", or "Used" */ if (strcmp(lmr.VolStatus, "Full") == 0 || strcmp(lmr.VolStatus, "Used") == 0) { + int dret=0; + + /* Do not prune/purge jobs that other jobs depend upon! + db_volume_has_dependents() returns -1 on error, 0 if there were no + dependents, and 1 if there were. */ + if((dret=db_volume_has_dependents(jcr, jcr->db, lmr.MediaId))!=0) { + if(dret>0) Dmsg2(100, _("Volume '%s' (%d) has dependents.\n"), lmr.VolumeName, (int)lmr.MediaId); + continue; + } + Dmsg2(100, "Add prune list MediaId=%d Volume %s\n", (int)lmr.MediaId, lmr.VolumeName); count = get_prune_list_for_volume(ua, &lmr, &prune_list); Dmsg1(100, "Num pruned = %d\n", count); Index: WORK/src/dird/ua_output.c =================================================================== RCS file: /cvs/netpilot/GPL/bacula-5.0.3/WORK/src/dird/ua_output.c,v retrieving revision 1.1 retrieving revision 1.2 diff -u -r1.1 -r1.2 --- WORK/src/dird/ua_output.c +++ WORK/src/dird/ua_output.c @@ -385,6 +385,28 @@ /* List for all jobs (jobid=0) */ db_list_jobmedia_records(ua->jcr, ua->db, 0, prtit, ua, llist); } + /* Added by Graham: Combine the job/jobmedia/media tables to get a more + useful view. */ + } else if (strcasecmp(ua->argk[i], NT_("jobandmedia")) == 0) { + int done = FALSE; + for (j=i+1; jargc; j++) { + if (strcasecmp(ua->argk[j], NT_("ujobid")) == 0 && ua->argv[j]) { + bstrncpy(jr.Job, ua->argv[j], MAX_NAME_LENGTH); + jr.JobId = 0; + db_get_job_record(ua->jcr, ua->db, &jr); + jobid = jr.JobId; + } else if (strcasecmp(ua->argk[j], NT_("jobid")) == 0 && ua->argv[j]) { + jobid = str_to_int64(ua->argv[j]); + } else { + continue; + } + db_list_jobandmedia_records(ua->jcr, ua->db, jobid, prtit, ua, llist); + done = TRUE; + } + if (!done) { + /* List for all jobs (jobid=0) */ + db_list_jobandmedia_records(ua->jcr, ua->db, 0, prtit, ua, llist); + } /* List JOBLOG */ } else if (strcasecmp(ua->argk[i], NT_("joblog")) == 0) {