Btukfyl

I have bulk delete operation in Yii2, following is the code snipped to delete the records with activity log (Here DBMS is PostgreSQL)-

$companies = Yii::$app->request->post('ids', '');

if($companies && count($companies)) {

    foreach ($companies as $company) {

        try{

            $utsendelseid = $company['utsendelseid'];

            $mailid = $company['mailid'];



            $model = MailSend::find()->where(['utsendelseid' => $utsendelseid, 'mailid' => $mailid])->one();



            if($model && !$model->mailsendt) {

                if($model->delete()) {

                    // if model deleted successfully then keep a log using commandBus

                    $companyModel = $this->findCompanyModel($model->kundenr);

                    $questionnaireModel = $this->findModel($utsendelseid);



                    Yii::$app->commandBus->handle(new AddCompanyLogCommand([

                    'kundenr' => $model->kundenr,

                    'portal_id' => ($questionnaireModel?$questionnaireModel->prosjektkode:''),

                    'activity' => Yii::t('backend', 'Slettet fra spørreskjema'),

                    'notes' => Yii::t('backend', 'Company "{company}"  has been deleted from questionnaire - "{questionnaire}"("{questionnaire_id}")', [

                        'company' => ($companyModel?$companyModel->etternavn:''),

                        'questionnaire' => ($questionnaireModel?$questionnaireModel->beskrivelse:''),

                        'questionnaire_id' => $utsendelseid

                    ]),

                    'activity_type_code' => 30,

                    ]));

                }

            }

        }

        // even tried with PDOException

        catch(Exception $e){

            echo $e->getMessage();

            // exit the request processing here

            exit(0);

        }

    }

}

This works fine if the number of records need to be deleted is below 300. I am getting Internal Server Error if the number of records above 300 even the code is inside try{}catch() block. I set max execution time to -1, and max memory allocation to 5120M which is not the issue for now. I thought it's a problem of too many connections error while have that much SQL operations, thus, I enable persistent connection in Yii2 by putting 'attributes'=> [PDO::ATTR_PERSISTENT => true]. But problem is still there.

Here, query is executed upto 45 seconds and error occured there. With in 45 seconds it delete upto 300 records and keep log of them. After that it gives Internal Server Error. There is no error in query. Ok, I wrote custom query to speed up and that deletes upto 500 records. But need to face same problem while having more than 500 records.

My questions are -

1. Why I am getting Internal Server Error even though code is inside try{}catch() block?




2. Why it works upto 45 seconds?




3. Is that a case of sub-process killing by Apache?




4. Is it a problem of max time allowed to run a query?




5. If this case is of too many connections then why the problem occurred with persistent connection?

Thanks in advance.

The execution is running longer than the configurations are allowing. Two ways to fix: Break the source data set into smaller parts and execute separately; or change the PHP / POSTgres execution time limit.