[QUESTION] getting error 524 when import excel

t0n1zz commented 4 years ago

Prerequisites

[X] Have read and understood: https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/SUPPORT.md
[X] Checked if your Laravel Excel version is still supported: https://docs.laravel-excel.com/3.1/getting-started/support.html#supported-versions
[X] Checked that your question isn't already asked before.
[X] Filled in the entire issue template

Versions

PHP version: 7.1.3
Laravel version: 5.6
Package version: 3.1

Description

hello, i am using import features and in site is using cloudflare cdn (free) which have some limititation or security measure they take so when there is some request and there is no any data returned for more than 100 second then cloudflare will terminate it and throw error 524.

and as far as i know the import feature have a feature called ShouldQueue that will queue the import. But why i am still getting this error 524?

and also i get this answer from cloudflare community tips

If you need to have scripts that run for longer than around 100 seconds without returning any data to the browser, you can’t run these through Cloudflare. There are a couple of options: Run the scripts via a grey-clouded subdomain or change the script so that it kicks off a long-running background process and quickly returns a status which the browser can poll until the background process has completed, at which point the full response can be returned. This is the way most people do this type of action as keeping HTTP connections open for a long time is unreliable and can be very taxing also.

but i have no idea how to do that, maybe somebody who experienced in this can point out how to do that?

My Import Code

<?php

namespace App\Imports;

use Auth;
use App\Cu;
use App\Tp;
use App\System;
use App\AnggotaCu;
use App\AnggotaCuCu;
use App\AnggotaCuCuDraft;
use App\AnggotaCuDraft;
use App\Region\Villages;
use App\Region\Districts;
use App\Region\Provinces;
use App\Region\Regencies;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Contracts\Queue\ShouldQueue;
use Maatwebsite\Excel\Concerns\WithBatchInserts;
use Maatwebsite\Excel\Concerns\WithChunkReading;

class AnggotaCuDraftImport implements ToModel, WithHeadingRow, WithBatchInserts, WithChunkReading, ShouldQueue
{

    public function model(array $row)
    {
        $gender = array_key_exists('gender', $row) ? strtoupper($row['gender']) : '';
        $status_pernikahan = array_key_exists('status_pernikahan', $row) ? strtoupper($row['status_pernikahan']) : '';
        $agama = array_key_exists('agama', $row) ? strtoupper($row['agama']) : '';
        $ktp = array_key_exists('ktp', $row)? preg_replace('/[^A-Za-z0-9]/', '',$row['ktp']) : '';
        $no_ba = array_key_exists('no_ba', $row) ? preg_replace('/[^A-Za-z0-9]/', '',$row['no_ba']) : '';
        $cu = Cu::where('no_ba', $row['no_ba_cu'])->select('id','no_ba')->first();
        $tp = Tp::where('id_cu', $cu->id)->where('no_tp', $row['kode_tp'])->select('id','id_cu','no_tp')->first();

        // check gender
        if($gender == 'L'){
            $gender = 'LAKI-LAKI';
        }else if($gender == 'P'){
            $gender = 'PEREMPUAN';
        }

        // check status
        if($status_pernikahan == 'KW'){
            $status_pernikahan = 'MENIKAH';
        }else if($status_pernikahan == 'TK'){
            $status_pernikahan = 'BELUM MENIKAH';
        }

        // check nik / ktp
        if($ktp != ''){
            $anggotaCu = AnggotaCu::where('nik',$ktp)->select('id','nik')->first();
            $anggotaCuDraft = AnggotaCuDraft::where('nik',$ktp)->select('id','nik')->first();
        }else{
            $kelas_ktp = System::findOrFail(1);
            $ktp = $kelas_ktp->nik;
            $val = $ktp + 1;
            $kelas_ktp->nik = str_pad($val,16,"0",STR_PAD_LEFT);
            $kelas_ktp->update();

            $anggotaCu = null;
            $anggotaCuDraft = null;
        }

        // check agama
        if($agama == 'KRISTEN'){
            $agama = 'PROTESTAN';
        }else if($agama == 'KHATOLIK'){
            $agama = 'KATOLIK';
        }

        // check no ba
        $anggotaCuCuBA = AnggotaCuCu::where('cu_id',$cu->id)->where('no_ba',$no_ba)->select('id','no_ba')->first();
        $anggotaCuCuDraftBA = AnggotaCuCuDraft::where('cu_id',$cu->id)->where('no_ba',$no_ba)->select('id','no_ba')->first();

        // if no ba not exist
        if(!$anggotaCuCuBA && !$anggotaCuCuDraftBA){
            // check provinsi
            if(array_key_exists('provinsi', $row) && $row['provinsi']){
                $provinces = Provinces::where('name','like', '%' .strtoupper($row['provinsi']). '%')->first();
                $provinces = $provinces ? $provinces->id : '';
            }else{
                $provinces = '';
            }
            // check kabupaten
            if(array_key_exists('kabupaten', $row) && $row['kabupaten']){
                if($provinces != ''){
                    $regencies = Regencies::where('province_id',$provinces)->where('name','like', '%' .strtoupper($row['kabupaten']). '%')->first();
                }else{
                    $regencies = Regencies::where('name','like', '%' .strtoupper($row['kabupaten']). '%')->first();
                }
                $regencies = $regencies ? $regencies->id : '';
            }else{
                $regencies = '';
            }
            // check kecamatan
            if(array_key_exists('kecamatan', $row) && $row['kecamatan']){
                if($regencies != ''){
                    $districts = Districts::where('regency_id',$regencies)->where('name','like', '%' .strtoupper($row['kecamatan']). '%')->first();
                }else{
                    $districts = Districts::where('name','like', '%' .strtoupper($row['kecamatan']). '%')->first();
                }
                $districts = $districts ? $districts->id : '';
            }else{
                $districts = '';
            }
            // check kelurahan
            if(array_key_exists('kelurahan', $row) && $row['kelurahan']){
                if($districts != ''){
                    $villages = Villages::where('district_id',$districts)->where('name','like', '%' .strtoupper($row['kelurahan']). '%')->first();
                }else{
                    $villages = Villages::where('name','like', '%' .strtoupper($row['kelurahan']). '%')->first(); 
                }
                $villages = $villages ? $villages->id : '';
            }else{
                $villages = '';
            }

            // old data exist
            if($anggotaCu){
                // check for no_ba
                $anggotaCuCu = AnggotaCuCu::where('no_ba',$no_ba)->select('id','no_ba')->first();
                // no old no_ba exist
                if(!$anggotaCuCu){
                    AnggotaCuCuDraft::create([
                        'anggota_cu_draft_id' => $anggotaCu->id,
                        'cu_id' => $cu->id,
                        'tp_id' => $tp->id,
                        'no_ba' => $no_ba,
                        'tanggal_masuk' => array_key_exists('tanggal_jadi_anggota', $row) ?\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['tanggal_jadi_anggota']) : '',
                        'keterangan_masuk' => array_key_exists('keterangan_jadi_anggota', $row) ? $row['keterangan_jadi_anggota'] : '',
                    ]);
                }
            }
            // no old data exist
            if(!$anggotaCu && !$anggotaCuDraft){
                $anggotaCuDraft = AnggotaCuDraft::create([
                    'name' => array_key_exists('nama', $row) ? $row['nama'] : '',
                    'id_provinces' => $provinces,
                    'id_regencies' => $regencies,
                    'id_districts' => $districts,
                    'id_villages' => $villages,
                    'nik' => $ktp,
                    'npwp' => array_key_exists('npwp', $row) ? $row['npwp'] : '',
                    'tempat_lahir' => array_key_exists('tempat_lahir', $row)? $row['tempat_lahir'] : '',
                    'tanggal_lahir' => array_key_exists('tanggal_lahir', $row) ? \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['tanggal_lahir']) : '',
                    'kelamin' => $gender,
                    'agama' => $agama,
                    'status' => $status_pernikahan,
                    'alamat' => array_key_exists('alamat', $row)? $row['alamat'] : '',
                    'rt' => array_key_exists('rt', $row) ? preg_replace('/[^A-Za-z0-9]/', '',$row['rt']) : '',
                    'rw' => array_key_exists('rw', $row) ? preg_replace('/[^A-Za-z0-9]/', '',$row['rw']) : '',
                    'kontak' => array_key_exists('kontak_lain', $row) ? $row['kontak_lain'] : '' ,
                    'darah' => array_key_exists('golongan_darah', $row) ? strtoupper($row['golongan_darah']) : '',
                    'tinggi' => array_key_exists('tinggi', $row) ? $row['tinggi'] : '',
                    'email' => array_key_exists('email', $row) ? $row['email'] : '',
                    'hp' => array_key_exists('hp', $row) ? preg_replace('/\s+/', '',$row['hp']) : '',
                    'pendidikan' => array_key_exists('pendidikan', $row) ? strtoupper($row['pendidikan']) : '',
                    'lembaga' => array_key_exists('tempat_kerja', $row) ? $row['tempat_kerja'] : '',
                    'jabatan' => array_key_exists('jabatan', $row) ? strtoupper($row['jabatan']) : '',
                    'organisasi' => array_key_exists('organisasi', $row) ? $row['organisasi'] : '',
                    'ahli_waris' => array_key_exists('ahli_waris', $row) ? $row['ahli_waris'] : '',
                    'pekerjaan' => array_key_exists('pekerjaan', $row) ? strtoupper($row['pekerjaan']) : '',
                    'penghasilan' => array_key_exists('rata_rata_penghasilan_perbulan', $row) ? $row['rata_rata_penghasilan_perbulan'] : 0,
                    'pengeluaran' => array_key_exists('rata_rata_pengeluaran_perbulan', $row) ? $row['rata_rata_pengeluaran_perbulan'] : 0,
                    'suku' => array_key_exists('suku', $row) ? strtoupper($row['suku']) : '',
                    'nama_ibu' => array_key_exists('nama_ibu', $row) ? $row['nama_ibu'] : '',
                    'kk' => array_key_exists('kk', $row) ? $row['kk'] : ''
                ]);

                if($anggotaCuDraft){
                    AnggotaCuCuDraft::create([
                        'anggota_cu_draft_id' => $anggotaCuDraft->id,
                        'cu_id' => $cu->id,
                        'tp_id' => $tp->id,
                        'no_ba' => $no_ba,
                        'tanggal_masuk' => array_key_exists('tanggal_jadi_anggota', $row) ?\PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($row['tanggal_jadi_anggota']) : '',
                        'keterangan_masuk' => array_key_exists('keterangan_jadi_anggota', $row) ? $row['keterangan_jadi_anggota'] : '',
                    ]);
                }
            }
        }
    }

    public function batchSize(): int
    {
        return 200;
    }

    public function chunkSize(): int
    {
        return 200;
    }
}

patrickbrouwers commented 4 years ago

Do you have a queue driver configured? (Not set to sync)

t0n1zz commented 4 years ago

oh okay i changed it into redis (since i am using redis as my cache driver) and it works fine for the first time upload. but for the second upload there is not data uploaded, is it because of cache in redis?

here is my redis configuration on config/queue.php

        'redis' => [
            'driver' => 'redis',
            'connection' => 'default',
            'queue' => 'default',
            'retry_after' => 90,
            'block_for' => null,
        ],

patrickbrouwers commented 4 years ago

I don't think it has to do with redis cache. Are you running the queue worker?

t0n1zz commented 4 years ago

hmm no, i don't think i have ever run queue worker, because i never setup any queue, just yesterday from your answer about error 524 above... i tried to upload and it just return no error with nothing in my database, no error, no data, nothing at all

patrickbrouwers commented 4 years ago

You need to run php artisan queue:work I would recommend reading up on queues if you are unfamiliar with it: https://laravel.com/docs/7.x/queues

t0n1zz commented 4 years ago

so for the queue to work, i need to first run php artisan queue:work and leave my terminal open? i tried to run it and it start to process my upload but when i close the terminal then it won't work.

is there any way to make it to run php artisan queue:work when there is an user that uploading their excel files?

update: i already adding supervisor into my server (i am using cloudways), and the job is already added, but still i need to manually run php artisan queue:work to make the upload work.

patrickbrouwers commented 4 years ago

I would recommend following some tutorial about using queues / workers. The question is a bit out of scope for this issue tracker. In case you can't figure it out, I would highly recommend trying a forum like Laracasts, lots of people happy to help you.

SpartnerNL / Laravel-Excel