Debug Failed Backup Job

On production we run backups which works most of the time, but we do get the occasional failure running backup job App\Jobs\ProjectBackupJobfailure

Checking Bugsnag we then see

App\Jobs\ProjectBackupJob has been attempted too many times or run too long. The job may have previously timed out.

And code we check on this mentions:

vendor/laravel/framework/src/Illuminate/Queue/Worker.php:755 Illuminate\Queue\Worker::maxAttemptsExceededException
     */
    protected function maxAttemptsExceededException($job)
    {
        return new MaxAttemptsExceededException(
            $job->resolveName().' has been attempted too many times or run too long. The job may have previously timed out.'
        );
    }

breadcrumb path shows this error

Error Occurred 1ms before
PROCESS Query executed
sql select * from `projects` where `projects`.`id` = ? limit 1
time 2.05ms
connection mysql

Horizon only keeps loading failed jobs briefly and then removes them to make way for other data. Only under metrics I did see the job ran 30 times and shortly after another 17 times. The second run 6 minutes after the first took 88 seconds! Failed jobs table however kept two failed jobs at 2 am this morning when we run

// Create new backup using Spatie Backup
 $schedule->command('backup:run')->daily()->at('02:00');
// Backup full projects daily and store on Digital Ocean Spaces
 $schedule->command('backup:projects')->daily()->at('02:00')->withoutOverlapping();

Second one is related to our job so it loads/runs too long. It does backup all database JSON data for all projects and file data.

One, how can we debug why things went wrong here? The database connection took too long and the database call is mentioned, but what would be too long?

Two, I can update the timeout for the database connection in queue.php as mentioned at https://laracasts.com/discuss/channels/laravel/maxattemptsexceededexception-job-has-been-attempted-too-many-times-or-run-too-long-the-job-may-have-previously-timed-out. We now only have:

 'database' => [
            'driver' => 'database',
            'table' => 'jobs',
            'queue' => 'default',
            'retry_after' => 90,
            'after_commit' => false,
        ],

but that would not really solve the issue. Perhaps the job needs patching too.

rhand

3 years ago

Level 6

In https://laracasts.com/discuss/channels/eloquent/very-very-slow-queryhow-to-speed-up kobear mentions how you can convert database query to raw SQL and then run that query against the database using EXPLAINhttps://dev.mysql.com/doc/refman/8.0/en/using-explain.html to see the indexing used for the query. Do see that project id is already indexed for main and backup database and max 3000 rows thus far (for backups) so not sure what else to index however to speed things up. But may try this to check for issues as well as debugbar. Latter just not on production.

rhand

3 years ago

Level 6

It seems to fail running this query

/* ------------------------------------------------------------
* Save Project
* ------------------------------------------------------------ */
// Remove old backup
$projects = BackupProject::where('project_id', $this->project->id)
                ->where('daily', $this->daily)
                ->orderBy('version', 'DESC')
                ->skip($this->daily ? 6 : 5)
                ->take(100)
                ->get();

$projects->each(function ($project) {
    $this->cleanBackup($project);
});

$lastProject = BackupProject::where('project_id', $this->project->id)
                    ->orderBy('version', 'DESC')
                    ->first();

$this->version = ($lastProject->version ?? 0) + 1;

$backupProject = new BackupProject();
$backupProject->fill($this->project->toArray());
$backupProject->project_id = $this->project->id;
$backupProject->id = null;
$backupProject->version = $this->version;
$backupProject->daily = $this->daily;
$backupProject->save();

It does not fail right away but after x job queries. More than 30 seem to pass and then jobs top and are taken on again. And it then hangs on the first query for projects in the job.

rhand

3 years ago

Level 6

Considering using ShouldBeUnique:

use App\Models\Editor\Project;
use App\Services\ProjectBackupService;
use Illuminate\Bus\Queueable;
+ use Illuminate\Contracts\Queue\ShouldBeUnique;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;

- class ProjectBackupJob implements ShouldQueue
+  class ProjectBackupJob implements ShouldQueue, ShouldBeUnique
{
    use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;

Besides using ->withoutOverlapping() in app/Console/Kernel.php But still not quite sure if I should use these too in tandem and whether that will help with failing backup jobs.

Please or to participate in this conversation.