[BUG] Duplicated entry (ID number) from key PRIMARY when using batch inserts and chunk reading

[neoacevedo]

1️⃣ Is the bug applicable and reproducable to the latest version of the package and hasn't it been reported before? (Currently 3.1.x) Yes

2️⃣ What version of the package, Laravel and PHP do you use? Please provide the FULL version number. Package: 3.1.30 Laravel: 6.20.24 PHP: 7.4.19

3️⃣ Describe your issue. Please be short, but concise. On using batch inserts, it inserts the rows came from the file but once the last row is inserted, it launches the integrity constraint violation described in the title.

4️⃣ How can the issue be reproduced? Please provide easy-to-reproduce steps (repository, simple code example, failing unit test). Following the batch inserts example code: https://docs.laravel-excel.com/3.1/imports/batch-inserts.html I implements the WithHeadingRow.

I try to insert from a CSV file.

I configured the batchSize to 100, 250... and I configured the chunkSize to 1000. I also configured SkipsErrors to skip existings records with existing email.

The log displays the following:

[2021-05-24 18:12:07] local.DEBUG: Insertada fila con ID de usuario 30 {"context":"App\\Imports\\OwnersImport"} 
[2021-05-24 18:12:09] local.DEBUG: Insertada fila con ID de usuario 31 {"context":"App\\Imports\\OwnersImport"} 
[2021-05-24 18:12:10] local.DEBUG: Insertada fila con ID de usuario 32 {"context":"App\\Imports\\OwnersImport"} 
[2021-05-24 18:12:50] local.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '30' for key 'PRIMARY' (SQL: insert into `owner` (email, name, id) values ("30@email", "my name 30", 30), ("31@email", "my name 31", 31), ("32@email", "my name 32", 32)) at Laravel6/vendor/laravel/framework/src/Illuminate/Database/Connection.php:669)
#0 Laravel6/vendor/laravel/framework/src/Illuminate/Database/Connection.php(629): Illuminate\\Database\\Connection->runQueryCallback('insert into `ow...', Array, Object(Closure))
...

5️⃣ What should be the expected behaviour? Inserting a similar number of rows in the file skipping rows with same emails aready registered in the database and posibly displaying at the end the skipped data.

6️⃣ Have you filled in the entire issue template? If not, your issue will be closed. Thanks for using the package! 🙌

[patrickbrouwers]

Cannot reproduce with the information you provided. Please try to provide a failing unit test, that will be most helpful.

[patrickbrouwers]

Closing due to no response

[bkremenovic]

I encountered the same issue and spent hours debugging it. The root cause was having two unique indexes. Removing one resolved the problem, though it's difficult to explain why this occurred.

I was pulling airport data from: https://raw.githubusercontent.com/davidmegginson/ourairports-data/main/airports.csv and had indexes on both iata_code and ident fields. Changes in the rows from airports.csv led to my unique indexes failing during insert.

If anyone encounters the same issue, comment here and I'll explain the solution, in details.