Basic import throws DB exception on unique validation

[ishitat]

I am importing excel with column name FirstName, LastName and Username. Username is unique in db and model validation both. I am trying to import excel which has 2 row with same username.

It throws below error:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'a@2.com' for key 'username' But this error should not come as before importing excelsheet there is no such value in DB. And model validation should detect duplicate value.

Please help

[arogachev]

Please provide code and Excel data that causes the error so I can reproduce it.

[ishitat]

Controller code:

 $importer = new Importer([
                'filePath' => $upload_path,
                'standardModelsConfig' => [
                    [
                        'setScenario' => 'import',
                        'className' => Applicant::className(),
                        'standardAttributesConfig' => [
                            ['name' => 'city',
                                'valueReplacement' => function ($value) {
                                    return (!empty($value)) ? City::find()->select('id')->where(['name' => $value]) : NULL;
                                }
                                    ],
                                    ['name' => 'province',
                                        'valueReplacement' => function ($value) {
                                            return (!empty($value)) ? Province::find()->select('id')->where(['name' => $value]) : NULL;
                                        },],
                                            ['name' => 'country',
                                                'valueReplacement' => function ($value) {
                                                    return (!empty($value)) ? Country::find()->select('id')->where(['name' => $value]) : NULL;
                                                },],
                                                    ['name' => 'dob',
                                                        'valueReplacement' => function ($value) {
                                                            return (!empty($value)) ? date(Yii::$app->params['DB_DATE_FORMAT'], strtotime('1899-12-31+' . ($value - 1) . ' days')) : NULL;
                                                        },],
                                                ],
                                            ],
                                        ],
                                    ]);
                                    if (!$importer->run()) {
                                        echo $importer->error;

                                        if ($importer->wrongModel) {
                                            echo Html::errorSummary($importer->wrongModel);
                                        }
                                        return $this->render('import', [
                                                    'model' => $model,
                                        ]);
                                    } else {
                                        \Yii::$app->getSession()->setFlash('import_success', 'Records are imported successfully');
                                        return $this->redirect('import');
                                    }

import.xlsx

[arogachev]

Please post model code as well. I think the error is there.

[ishitat]

public function rules() {

    return [
        [['first_name', 'last_name', 'sin_expiry', 'password_hash', 'sin_no', 'gender','mobile_number'], 'required', 'on' => 'default'],
        [['email', 'username'], 'required'],
        [['address', 'address_2', 'authorize', 'gender', 'shift_availability', 'status','password_hash'], 'string','on'=>'default'],
        [['log_id', 'employee_id', 'position', 'city', 'province', 'country', 'applicant_status', 'confirmed_at', 'blocked_at', 'flags', 'created_at', 'updated_at'], 'integer','on'=>'default'],
        [['year', 'graduate_year', 'start_date', 'end_date', 'dob'], 'safe'],
        [['first_name', 'last_name', 'email', 'college_name', 'company_name', 'name_of_supervisor', 'sin'], 'string', 'max' => 50,'on'=>'default'],
        [['photo', 'password_hash', 'intersection', 'resume', 'photo_id', 'forklift_license', 'any_other_certification'], 'string', 'max' => 100,'on'=>'default'],
        [['home_number','mobile_number', 'sin_status', 'sin_no'], 'string', 'max' => 20, 'on' => 'default'],
        [['auth_key'], 'string', 'max' => 32],
        [['postal_code', 'sin_expiry'], 'string', 'max' => 10],
        [['registration_ip'], 'string', 'max' => 45],
        //password rules
        [['password_hash'], 'string','min' => Yii::$app->params['minpasswordlength'],'on'=>'default'],
        ['cnp', 'compare', 'compareAttribute' => 'password_hash'],
        'log_idUnique' => ['log_id', 'unique', 'message' => 'This Log ID has already been taken'],
        // username rules
        'usernameLength' => ['username', 'string', 'min' => 3, 'max' => 255],
        'usernameTrim' => ['username', 'filter', 'filter' => 'trim'],
        'usernamePattern' => ['username', 'match', 'pattern' => Yii::$app->params['usernameRegexp']],
        'usernameUnique' => ['username', 'unique', 'message' => 'This username has already been taken'],
        // email rules
        'emailTrim' => ['email', 'filter', 'filter' => 'trim'],
        'emailPattern' => ['email', 'email'],
        'emailUnique' => ['email', 'unique', 'message' => 'This email address has already been taken'],
    ];
}

public function scenarios() {
    return [
        'default' => ['first_name', 'log_id', 'employee_id', 'last_name', 'username', 'position', 'home_number','mobile_number', 'email', 'password_hash', 'cnp', 'photo', 'address', 'address_2', 'sin_no',  'company_name', 'authorize', 'name_of_supervisor', 'resume', 'start_date', 'end_date', 'intersection', 'province', 'postal_code', 'college_name', 'year', 'graduate_year', 'city', 'province', 'country', 'dob', 'gender', 'shift_availability', 'sin_expiry', 'applicant_status', 'created_at', 'updated_at', 'status'],
        'import' => ['log_id', 'first_name', 'employee_id', 'last_name', 'username', 'home_number','mobile_number', 'email', 'password_hash', 'address', 'address_2', 'city', 'province', 'country', 'dob', 'gender','sin_no',  'sin_expiry', 'postal_code']
    ];
}

[arogachev]

I just checked unique validation and it failed on first duplicate - with validation error, not with DB exception.

I can't see a@2.com in file you provided (by the way, you deleted it already). And I guess it's email, not username?

[ishitat]

Actually the file I tried before posting the issue was not with me when you asked the code n excelsheet. so I created new file which generated the same error.

Please have a look at the error snapshot. I have used the same file. The email and username both are unique in my application.

I got the DB exception and not the validation error. I do get validation errors when there is record in db with same email/username. But it throw DB exception when we have duplicate email/username in the same excel file.

If you wanna check more, then I can mail you the live URL.