[BUG] Queued Export Failing Silently communication link failure

[gpanos]

Prerequisites

• [ ] Able to reproduce the behaviour outside of your code, the problem is isolated to Laravel Excel.
• [x] Checked that your issue isn't already filed.
• [x] Checked if no PR was submitted that fixes this problem.

Versions

• PHP version: 7.1
• Laravel version: 5.6
• Nova version: 1.3.2
• Package version: 1.1.3

Description

When trying to do a queued export (tried with both database and redis) it fails silently without throwing any error or having any failed jobs. After investigating further the queue processing stops at Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet i opened the logs (when using the database driver) and i saw the error communication link failure: 1153 Got a packet bigger than max_allowed_packet bytes. From what i understand it tries to serialize a huge request object and fails.

Steps to Reproduce

Expected behavior: Queue excel exports

Actual behavior: Excel export queuing fails everytime no matter how large the selected dataset is.

Any help will be greatly appreciated thanks in advance!

[patrickbrouwers]

Which queue driver are you using?

[gpanos]

We tested with redis and database. The issue happens on both drivers database and redis i was able to understand the error while using the database driver

[patrickbrouwers]

Do you have a stack trace for the exception that happens when using redis perhaps?

[gpanos]

Not really the queue is failing silently when we run the queue workers as daemon. So we suspect a memory leak issue.

[patrickbrouwers]

Can you check the redis log (/var/log/redis/redis-server.log or where-ever it's located for you) ?

[gpanos]

Yes for sure basically the only thing i see is the following: ' 1541:M 26 Feb 17:43:01.063 # Can't save in background: fork: Cannot allocate memory 1541:M 26 Feb 17:43:07.015 10 changes in 300 seconds. Saving... 1541:M 26 Feb 17:43:07.015 # Can't save in background: fork: Cannot allocate memory 1541:M 26 Feb 17:43:13.049 10 changes in 300 seconds. Saving... 1541:M 26 Feb 17:43:13.049 # Can't save in background: fork: Cannot allocate memory 1541:M 26 Feb 17:43:19.081 10 changes in 300 seconds. Saving... 1541:M 26 Feb 17:43:19.081 # Can't save in background: fork: Cannot allocate memory 1541:M 26 Feb 17:43:25.012 10 changes in 300 seconds. Saving... 1541:M 26 Feb 17:43:25.012 # Can't save in background: fork: Cannot allocate memory 1541:M 26 Feb 17:43:31.056 * 10 changes in 300 seconds. Saving... 1541:M 26 Feb 17:43:31.056 # Can't save in background: fork: Cannot allocate memory '

[patrickbrouwers]

Sounds indeed like to big of a job to save.

Could you perhaps share a bit more of your code? Seems like something on your nova resources is a lot bigger than it is for us. Perhaps something we don't even need to serialize.

[gpanos]

Sorry for the late reply.. Yes for sure this is the user resource: `class User extends Resource { use ImageUploader;

public static $model = 'App\\User';

public static $search = [
    'id',
    'phone',
    'email',
    'last_name',
    'first_name',
];

public static $searchRelations = [];

public function title()
{
    return "{$this->id} {$this->full_name}";
}

public function subtitle()
{
    return ucfirst($this->role);
}

public function fields(Request $request)
{
    return [
        ImpersonateUser::make($this),

        ID::make()->sortable(),

        Image::make('Profile Photo', 'image')
            ->withMeta([
                'downloadable' => false,
                'deletable' => false
            ])
            ->store(function(Request $request, $model) {
                return ['image' => $this->createThumbnail($request->image, 'avatars')];
            })
            ->preview(function () {
                return $this->image;
            })
            ->thumbnail(function () {
                return $this->image;
            })
            ->hideFromIndex(),

        Select::make('Type', 'role')
            ->options(array_flip(Model::roleOptions()))
            ->sortable()
            ->displayUsingLabels(),

        Select::make('Language')
            ->options(collect(LaravelLocalization::getSupportedLocales())->mapWithKeys(function ($data, $key) {
                return [$key => $data['name']];
            }))
            ->displayUsingLabels()
            ->rules('required'),

        Text::make('Name', function () {
            $title = $this->gender === 'female' ? 'Mrs' : 'Mr';

            return "{$title}. {$this->full_name}";
        }),

        Text::make('First Name')->onlyOnForms(),

        Text::make('Last Name')->onlyOnForms(),

        Select::make('Gender')->options([
            'male' => 'Male',
            'female' => 'Female',
        ])->onlyOnForms(),

        Text::make('Email')
            ->sortable()
            ->rules('required', 'email', 'max:254')
            ->creationRules('unique:users,email,NULL,id,deleted_at,NULL')
            ->updateRules('unique:users,email,{{resourceId}},id,deleted_at,NULL'),

        DateTime::make('Logged In At')->onlyOnDetail(),

        DateTime::make('Created at')->onlyOnDetail(),

        DateTime::make('Updated At')->onlyOnDetail(),

        DateTime::make('Deleted At')->onlyOnDetail(),

        Text::make('Phone')->hideFromIndex(),

        Text::make('City'),

        Url::make('Profile', function () {
            return route('users.show', [
                'user' => $this->id
            ]);
        })->label('Profile')->clickable()->hideFromIndex(),

        Text::make('Position', function () {
            return "{$this->lat}, {$this->lon}";
        })->hideFromIndex(),

        Date::make('Birthdate')->hideFromIndex(),

        Textarea::make('Description')->hideFromIndex(),

        Image::make('Cover Image')
            ->withMeta([
                'downloadable' => false,
                'deletable' => false
            ])
            ->store(function(Request $request, $model) {
                return ['cover_image' => $this->createNormal($request->cover_image, 'cover-images')];
            })
            ->preview(function () {
                return $this->cover_image;
            })
            ->thumbnail(function () {
                return $this->cover_image;
            })
            ->hideFromIndex(),

        Select::make('Country', 'country_code')->options(Countries::novaOptions())->hideFromIndex(),

        Password::make('Password')
            ->onlyOnForms()
            ->creationRules('required', 'string', 'min:6')
            ->updateRules('nullable', 'string', 'min:6'),

        Boolean::make('Email Verified')->hideFromIndex(),

        Boolean::make('Phone Verified')->hideFromIndex(),

        Boolean::make('Facebook account', function() {
            return $this->facebook_id !== null;
        })->onlyOnDetail(),

        MorphMany::make('Notes'),

        HasMany::make('Flights'),

        HasMany::make('Bookings'),

        HasMany::make('Coupons'),

        HasMany::make('Verifications'),

        HasOne::make('Billing Information', 'mangopayClient'),

        HasOne::make('Flight Log', 'flightLog'),

        HasMany::make('Aircrafts'),

        BelongsToMany::make('Homebases', 'airports', Airport::class)->searchable(),

        BelongsToMany::make('Qualifications'),

        BelongsToMany::make('Notifications Permissions', 'notificationsPermissions'),
    ];
}

public function cards(Request $request)
{
    return [
        new Metrics\TotalUsers,

        new Metrics\NewPilots,
    ];
}

public function filters(Request $request)
{
    return [
        new Filters\Country,

        new Filters\UserType,

        new Filters\UserLanguage,

        new Filters\ApprovedByFFA,

        new Filters\NewsletterSubscribers,

        new CityRadius,

        new Filters\DateFrom,

        new Filters\DateTo,
    ];
}

public function lenses(Request $request)
{
    return [
        new Lenses\PilotCalls,

        new Lenses\PilotMails,

        new Lenses\PilotAutomaticallyAddedCalls,
    ];
}

public function actions(Request $request)
{
    return [
        (new QueuedExport)
            ->withWriterType(Excel::CSV)
            ->withHeadings('ID', 'Type', 'Email', 'City', 'First Name', 'Last Name')
            ->only('id', 'role', 'email', 'city', 'first_name', 'last_name'),

        (new Actions\DownloadAdvancedUserExport)->canSee(function ($request) {
            return $request->user()->isSuper || $request->user()->isAdmin;
        })->withWriterType(Excel::CSV),

        (new Actions\DisconnectFacebookAccount)->onlyOnDetail(),

        new AddToCall,

        new AddToMail,
    ];
}

public static function authorizedToCreate(Request $request)
{
    return $request->user()->isSuper;
}

public function authorizedToDelete(Request $request)
{
    return (! $request->isLens()) && $this->accountCanBeDeleted;
}

public function authorizedToForceDelete(Request $request)
{
    return $request->user()->isSuper;
}

}`

It's a huge resource but as you can see we only try to export just a few fields (new QueuedExport) ->withWriterType(Excel::CSV) ->withHeadings('ID', 'Type', 'Email', 'City', 'First Name', 'Last Name') ->only('id', 'role', 'email', 'city', 'first_name', 'last_name'),

[patrickbrouwers]

Can you try commenting out all the stuff that is not used in the export and see how much better that performs? Using ->only() doesn't remove stuff from the resource, only doesn't add it to the export.

[gpanos]

Ok i reduced the fields that i are used for the export and removed all the other. Still didn't manage to do a successful export they processing slows down after a few AppendQueryToSheet and then stops without an error...

`art queue:work [2019-02-27 12:16:49][1] Processing: Maatwebsite\Excel\Jobs\QueueExport [2019-02-27 12:16:49][1] Processed: Maatwebsite\Excel\Jobs\QueueExport [2019-02-27 12:16:49][2] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:50][2] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:50][3] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:52][3] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:52][4] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:53][4] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:53][5] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:55][5] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:55][6] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:58][6] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:16:58][7] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:00][7] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:00][8] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:05][8] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:05][9] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:08][9] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:08][10] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:12][10] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:12][11] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:16][11] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:16][12] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet [2019-02-27 12:17:21][12] Processed: Maatwebsite\Excel\Jobs\AppendQueryToSheet

sudo tail /var/log/redis/redis-server.log 1541:M 27 Feb 12:10:04.042 10 changes in 300 seconds. Saving... 1541:M 27 Feb 12:10:04.044 Background saving started by pid 23846 23846:C 27 Feb 12:10:04.047 DB saved on disk 23846:C 27 Feb 12:10:04.049 RDB: 4 MB of memory used by copy-on-write 1541:M 27 Feb 12:10:04.145 Background saving terminated with success 1541:M 27 Feb 12:15:05.079 10 changes in 300 seconds. Saving... 1541:M 27 Feb 12:15:05.081 Background saving started by pid 24029 24029:C 27 Feb 12:15:05.194 DB saved on disk 24029:C 27 Feb 12:15:05.196 RDB: 5 MB of memory used by copy-on-write 1541:M 27 Feb 12:15:05.282 Background saving terminated with success`

[patrickbrouwers]

Can you try to increase the chunk size?

[gpanos]

Yes I already did with no luck (new QueuedExport) ->withChunkCount(3000) ->withWriterType(Excel::CSV) ->withHeadings('ID', 'Type', 'Email', 'City', 'First Name', 'Last Name') ->only('id', 'role', 'email', 'city', 'first_name', 'last_name'),

Just to clarify i am testing the exports for 256k database records

[patrickbrouwers]

I'll see if I can reproduce it soon. You could try to increase the chunk count even more, like 20 000, so it limits the amount of jobs on the queue.

[gpanos]

Ok thanks i'll give it a go

[gpanos]

art queue:work [2019-02-27 13:35:31][1] Processing: Maatwebsite\Excel\Jobs\QueueExport [2019-02-27 13:35:31][1] Processed: Maatwebsite\Excel\Jobs\QueueExport [2019-02-27 13:35:31][2] Processing: Maatwebsite\Excel\Jobs\AppendQueryToSheet Killed With 20 000 chunk count :)

[patrickbrouwers]

Perhaps a bit smaller than 20 000 then 🤣

[gpanos]

Don't know if it helps but i was able to make it work with chunk count 15 000 and instead of using the art queue:work i used art queue:listen

[patrickbrouwers]

With listen it spans up a new instance of Laravel on each job, it might have positive influence on this. I'll see if we can improve the serialization part.

[patrickbrouwers]

Hey @gpanos I've perhaps found a workaround. Can you test it on the 1.1 branch perhaps?

[gpanos]

@patrickbrouwers for sure i'll give it a go asap thanks!! FYI we went for our own implementation since we were in a hurry to deliver using league-csv behind the scenes heavily influenced from your implementation. I let you know as soon as i test thanks again for the great work!