Laravel 5.2 job jammed in database?

Question

I am using LARAVEL queues with jobs to insert a large excel in my database and some jobs are getting jammed and not executing.

I chunk the file contents(250 rows per job) and it inserts most of them until it stops.

Insert Code (job that inserts 250 or less rows)

public function handle()
{
    $uuid = Uuid::generate(4);
    $defaultsSize = 0;
    $customSize = 0;
    $defaultsIdFields = [];
    $customFields = [];
    if (sizeof($this->matrixDefaultFields) > 0) {
        $defaultsSize = sizeof($this->matrixDefaultFields[0][0]); //size of one of the vecs in default values
        $defaultsIdFields = $this->matrixDefaultFields[0][0]; // all default fields id
    }
    if (sizeof($this->matrixCustomFields) > 0) {
        $customSize = sizeof($this->matrixCustomFields[0][0]); //sizeof one of the vecs in custom values
        $customFields = $this->matrixCustomFields[0][0]; // all custom fields id
    }


    for ($i = 0; $i < sizeof($this->matrixContacts); $i++) {
        $contact = Contact::create(['UUID' => $uuid, 'id_contact_list' => $this->matrixContacts[$i][1],
            'nome' => $this->matrixContacts[$i][2], 'email' => $this->matrixContacts[$i][3], 'unsub_code' => $this->matrixContacts[$i][4]]);

        for ($j = 0; $j < $defaultsSize; $j++) {
            $defaultsValuesFields = $this->matrixDefaultFields[$i][1];  //field value
            CompanyListFieldValues::create(['company_id' => $this->idCompany,
                'contact_id' => $contact->id,
                'field_id' => $defaultsIdFields[$j],
                'isDefault' => 1,
                'value' => $defaultsValuesFields[$j]]);
        }

        for ($k = 0; $k < $customSize; $k++) {
            $customValuesFields = $this->matrixCustomFields[$i][1];  //field value
            CompanyListFieldValues::create(['company_id' => $this->idCompany,
                'contact_id' => $contact->id,
                'field_id' => $customFields[$k],
                'isDefault' => 0,
                'value' => $customValuesFields[$j]]);
        }

    }

}

Code from the generator of the jobs(this one works fine)

      for ($row = $this->startFrom; $row <= $highestRow; $row++) {
        if ($rowMatrix == $chunksize) {
            // $job = (new importExcelInsert($matrixContacts, $matrixDefaultFields, $matrixCustomFields, $this->idCompany))->delay($jobDelay)->onQueue('excelInserts');
            //   var_dump($matrixContacts);
            $job = (new importExcelInsert($matrixContacts, $matrixDefaultFields, $matrixCustomFields, $this->idCompany, $rowMatrix))->delay($jobDelay)->onQueue('excelInserts');

            $this->dispatch($job);
            $njobs++;
            $matrixContacts = [];
            $matrixDefaultFields = [];
            $matrixCustomFields = [];
            $rowMatrix = 0;
        }
        $userEmail = $sheet->getCellByColumnAndRow($posEmail, $row)->getValue();
        if ($userEmail == '' || !filter_var($userEmail, FILTER_VALIDATE_EMAIL)) {
            $error++;
        } else if (Contact::where('email', '=', $userEmail)->where('id_contact_list', '=', $this->idList)->count()) {
            $ignored++;
        } else {
            // $uuid = Uuid::generate(4);
            $matrixContacts[$rowMatrix][1] = $this->idList; //CONTACT_LIST_ID
            $matrixContacts[$rowMatrix][2] = $sheet->getCellByColumnAndRow($posNome, $row)->getValue(); //Name
            $matrixContacts[$rowMatrix][3] = $userEmail; //Email
            $matrixContacts[$rowMatrix][4] = $unsubCode; //Unsubscribe Code

            $vecFieldId = [];
            $vecValues = [];
            $i = 0;
            if (!$this->arrayDefaultFields[0] == null) {
                for ($j = 0; $j < sizeof($this->arrayDefaultFields); $j += 2) {
                    $vecFieldId[$i] = $this->arrayDefaultFields[$j];
                    $vecValues[$i] = $sheet->getCellByColumnAndRow($this->arrayDefaultFields[$j + 1], $row)->getValue();
                    $i++;
                }
                $matrixDefaultFields[$rowMatrix][0] = $vecFieldId;
                $matrixDefaultFields[$rowMatrix][1] = $vecValues;
            }
            $i = 0;
            if (!$this->arrayCustomFields[0] == null) {
                for ($j = 0; $j < sizeof($this->arrayCustomFields); $j += 2) {
                    $vecFieldId[$i] = $this->arrayCustomFields[$j];
                    $vecValues[$i] = $sheet->getCellByColumnAndRow($this->arrayCustomFields[$j + 1], $row)->getValue();
                    $i++;
                }
                $matrixCustomFields[$rowMatrix][0] = $vecFieldId;
                $matrixCustomFields[$rowMatrix][1] = $vecValues;

            }
            $rowMatrix++;
        }
    }

    if (!empty($matrixContacts)) {
        $job = (new importExcelInsert($matrixContacts, $matrixDefaultFields, $matrixCustomFields, $this->idCompany, $rowMatrix + 1))->delay($jobDelay)->onQueue('excelInserts');
        $this->dispatch($job);
        $njobs++;
    }

EDIT - Print screen of the database

Any insight or help with what the problem might be would be much appreciated.

have you tried with ini_set('max_execution_time', 0); at the beginning of handle() method? — Sangar82, Jun 01 '16 at 20:16
I've increased the time limit in PHP file to 5 minutes never put at 0 but that should make a difference, it calls the job but doesn't execute it, it just increases the attempt value. — Diogo Silva, Jun 01 '16 at 20:19
perhaps a max connections error in database? On background jobs is recommended close the connection. Try with DB::reconnect() at the beginning at DB::disconnect() at the bottom of handle() method. https://laravel.com/docs/5.0/database#accessing-connections. Try to use a daemon too https://laravel.com/docs/5.1/queues#daemon-queue-listener — Sangar82, Jun 01 '16 at 20:44
I dont have access to the code right now but i will try.... also no errors in logs — Diogo Silva, Jun 01 '16 at 21:26
Try with sync driver to see any error at execution to discard problems in your code — Sangar82, Jun 02 '16 at 19:27
i changed driver to sync and i got an error that has to do with a trigger in my database... im checking it right now — Diogo Silva, Jun 03 '16 at 11:29
actually after removing the trigger i have other error of maximum execution time... im checking right now — Diogo Silva, Jun 03 '16 at 17:19
you have the solution in my second comment! ini_set('max_execution_time', 0); but is another question! — Sangar82, Jun 03 '16 at 17:20

score 1 · Accepted Answer · answered Jun 03 '16 at 16:06

1

Change the driver to 'sync' to see errors in the job. This way, you can discard problems in your code or see what happen

answered Jun 03 '16 at 16:06

Sangar82

5,070
1
35
52

after analysing a lot of code i've come to the conclusion that the jobs were jamming because of an SQL exception and the timeout limit... thanks for the help – Diogo Silva Jun 06 '16 at 11:10

Laravel 5.2 job jammed in database?

Insert Code (job that inserts 250 or less rows)

Code from the generator of the jobs(this one works fine)

EDIT - Print screen of the database

1 Answers1