[BUG] Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes for QueuedExport

[bjrnblm]

Prerequisites

• Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• Checked that your issue isn't already filed. (probably related to [BUG] Queued Export Failing Silently communication link failure #44, but I didn't want to hijack that issue)
• Checked if no PR was submitted that fixes this problem.

Versions

• PHP version: 7.2
• Laravel version: 5.7.27
• Nova version: 1.3.2
• Package versions: Excel: 3.1.7, Nova Excel: 1.1.3

Description

On exporting ~3K subscriptions records via a QueuedExport we get an SQL exception:

Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes (SQL: insert into `jobs` (`queue`, `attempts`, `reserved_at`, `available_at`, `created_at`, `payload`)

We are using the database driver in combination with php artisan queue:work --tries=5 --delay=5

The Subscription Nova Resource looks like:

public function fields(Request $request)
{
        return [
            ID::make()
                ->hideFromIndex(),

            BelongsTo::make('User', 'user', \App\Nova\Resources\User\User::class),

            Text::make('Stripe id'),

            Text::make('Initial source')
                ->hideFromIndex(),

            DateTime::make('End date')
                ->sortable(),

            DateTime::make('Cancelation requested at')
                ->sortable(),

            DateTime::make('Canceled at')
                ->sortable(),

            Code::make('Import data')
                ->json()
                ->hideFromIndex(),

            DateTime::make('Created at')
                ->sortable()
                ->hideFromIndex(),

            HasMany::make('Payments', 'payments', \App\Nova\Resources\Program\Subscription\SubscriptionPayment::class),
        ];
}

public function actions(Request $request)
{
        $filename = 'exports/subscriptions/subscriptions-' . Carbon::now() . '.xlsx';
        $disk = 's3';

        return [
            (new ExportSubscription)
                ->withFilename($filename)
                ->withDisk($disk)
                ->withChunkCount(1000)
                ->onSuccess(function (ActionRequest $request, PendingDispatch $queue) use ($filename, $disk) {
                    $queue->chain([
                        new NotifyAdminOfCompletedExport($request->user(), $filename, $disk),
                    ]);
                    return Action::message('The export is being created, when done it will be send via email to '.$request->user()->email);
                }),
        ];
}

The ExportSubscription looks like:

class ExportSubscription extends QueuedExport implements WithMapping, WithHeadings
{
    /**
     * Headers
     *
     * @return array
     */
    public function headings(): array
    {
        return [
            'ID',
            'External ID',
            'Name',
            'Email',
            'Stripe ID',
            'Initial source',
            'End date',
            'Cancelation requested at',
            'Canceled at',
            'Created at',
        ];
    }

    /**
     * @param Subscription $subscription
     *
     * @return array
     */
    public function map($subscription): array
    {
        return [
            $subscription->id,
            $subscription->user ? $subscription->user->external_id : null,
            $subscription->user ? $subscription->user->name : null,
            $subscription->user ? $subscription->user->email : null,
            $subscription->stripe_id,
            $subscription->initial_source,
            $subscription->end_date ? $subscription->end_date : null,
            $subscription->cancelation_requested_at ? $subscription->cancelation_requested_at : null,
            $subscription->canceled_at ? $subscription->canceled_at : null,
            $subscription->created_at ? $subscription->created_at : null,
        ];
    }
}

When I check the payload column it is stacked with information that is not my $subscription or $subscription->user for example (small excerpt):

O:55:\"Maatwebsite\\LaravelNovaExcel\\Requests\\SerializedRequest\":3:{s:66:\"\u0000Maatwebsite\\LaravelNovaExcel\\Requests\\SerializedRequest\u0000className\";s:65:\"Maatwebsite\\LaravelNovaExcel\\Requests\\ExportResourceActionRequest\";s:65:\"\u0000Maatwebsite\\LaravelNovaExcel\\Requests\\SerializedRequest\u0000resource\";s:13:\"subscriptions\";s:61:\"\u0000Maatwebsite\\LaravelNovaExcel\\Requests\\SerializedRequest\u0000lens\";N;}s:11:\"\u0000*\u0000resource\";s:52:\"App\\Nova\\Resources\\Program\\Subscription\\Subscription\";s:22:\"\u0000*\u0000serializedResources\";a:43854:{i:0;s:35:\"Laravel\\Nova\\Actions\\ActionResource\";i:1;s:35:\"Laravel\\Nova\\Actions\\ActionResource\";i:2;s:30:\"App\\Nova\\Resources\\Admin\\Admin\";i:3;s:32:\"App\\Nova\\Resources\\Clinic\\Clinic\";i:4;s:36:\"App\\Nova\\Resources\\Clinic\\ClinicUser\";i:5;s:31:\"App\\Nova\\Resources\\Course\\Block\";i:6;s:32:\"App\\Nova\\Resources\\Course\\Course\";i:7;s:36:\"App\\Nova\\Resources\\Course\\CourseUser\";i:8;s:34:\"App\\Nova\\Resources\\Course\\Exercise\";i:9;s:34:\"App\\Nova\\Resources\\Course\\Location\";i:10;s:33:\"App\\Nova\\Resources\\Course\\Message\";i:11;s:33:\"App\\Nova\\Resources\\Course\\Workout\";i:12;s:29:\"App\\Nova\\Resources\\Discipline\";i:13;s:23:\"App\\Nova\\Resources\\Link\";i:14;s:27:\"App\\Nova\\Resources\\Material\";i:15;s:32:\"App\\Nova\\Resources\\Program\\Block\";i:16;s:35:\"App\\Nova\\Resources\\Program\\Exercise\";i:17;s:34:\"App\\Nova\\Resources\\Program\\Program\";i:18;s:38:\"App\\Nova\\Resources\\Program\\ProgramUser\";i:19;s:45:\"App\\Nova\\Resources\\Program\\ProgramUserWorkout\";i:20;s:52:\"App\\Nova\\Resources\\Program\\Subscription\\Subscription\";i:21;s:59:\"App\\Nova\\Resources\\Program\\Subscription\\SubscriptionPayment\";i:22;s:32:\"App\\Nova\\Resources\\Program\\Theme\";i:23;s:34:\"App\\Nova\\Resources\\Program\\Workout\";i:24;s:34:\"App\\Nova\\Resources\\User\\Onboarding\";i:25;s:28:\"App\\Nova\\Resources\\User\\User\";i:26;s:24:\"App\\Nova\\Resources\\Video\";i:27;s:35:\"Laravel\\Nova\\Actions\\ActionResource\";i:28;s:30:\"App\\Nova\\Resources\\Admin\\Admin\";i:29;s:32:\"App\\Nova\\Resources\\Clinic\\Clinic\";i:30;s:36:\"App\\Nova\\Resources\\Clinic\\ClinicUser\";i:31;s:31:\"App\\Nova\\Resources\\Course\\Block\";i:32;s:32:\"App\\Nova\\Resources\\Course\\Course\";i:33;s:36:\"App\\Nova\\Resources\\Course\\CourseUser\";i:34;s:34:\"App\\Nova\\Resources\\Course\\Exercise\";i:35;s:34:\"App\\Nova\\Resources\\Course\\Location\";i:36;s:33:\"App\\Nova\\Resources\\Course\\Message\";i:37;s:33:\"App\\Nova\\Resources\\Course\\Workout\";i:38;

I would not expect App\Nova\Resources\Admin\Admin and App\Nova\Resources\Program\Block etc in there. It looks like it's serializing the entire Nova Object every time and appending it?

We played around with withChunkCount($i) and it helps a bit, but seems like a temporary fix, after more records in the database it started happening again.

Is there something we can do to optimize the serializing? Send less data to the queue?

[patrickbrouwers]

I would not expect App\Nova\Resources\Admin\Admin and App\Nova\Resources\Program\Block etc in there. It looks like it's serializing the entire Nova Object every time and appending it?

Yes, and that's indeed the problem. But if we don't, resolving fields (especially relations) don't work. And unfortunately I haven't found a way around it yet.

[patrickbrouwers]

Hey @bjrnblm I might have found a workaround, would you be able to give it a test. I haven't released it yet, but you can install the 1.1 dev version to try it out.

[bjrnblm]

Hey @patrickbrouwers the 1.1 dev version seems to be working much better! I even removed the custom withChunkCount() and the job finished without an SQL exception.

[bjrnblm]

You already seemed to have released these fixes in 1.1.4, is this correct?

[patrickbrouwers]

Thanks for testing @bjrnblm . It wasn't released yet no. It will be released as 1.1.5 soon.

[LeonardFollner]

Hey, I have encountered the same error in Laravel-Excel. Is it possible, that the cause is the same?

[LeonardFollner]

Should I open a separate issue there?

[GlennM]

It's always good to have specific information about the exact issue and the code/versions you're using, so a separate issue on Laravel Excel would be appreciated.

[LeonardFollner]

That makes sense, @GlennM . I did so here.