Duplicate DB Exceptions

[MrKrisKrisu]

I don't really understand, why we have so many Duplicate Database Exceptions in our log, while using Laravels updateOrCreate method.

One example:

[2022-04-15 xx:xx:xx] production.ERROR: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '........' for key 'train_stations_ibnr_unique' (SQL: insert into `train_stations` (`ibnr`, `name`, `latitude`, `longitude`, `updated_at`, `created_at`) values (.........)) {"userId":2084,"exception":"[object] (Illuminate\\Database\\QueryException(code: 23000): SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '........' for key 'train_stations_ibnr_unique' (SQL: insert into `train_stations` (`ibnr`, `name`, `latitude`, `longitude`, `updated_at`, `created_at`) values (.......)) at vendor/laravel/framework/src/Illuminate/Database/Connection.php:716)

truncated

The updateOrCreate function checks whether we already have a column with the given key in the database and ONLY IF NOT then creating a new one. In the example above there is checked, if we already have this TrainStation saved but laravels tries to insert a new one... Why the fuck?! https://github.com/Traewelling/traewelling/blob/e05949d40386d1688fde2d99f078931d42f34afc/app/Http/Controllers/HafasController.php#L86-L92

[MrKrisKrisu]

I've tested the function with this code, but... it workes...

        $ts1 = TrainStation::updateOrCreate([
                                                'ibnr' => 123456789
                                            ], [
                                                'name'      => 'testname',
                                                'latitude'  => 1.234,
                                                'longitude' => 1.234,
                                            ]);
        dump($ts1);
        $ts2 = TrainStation::updateOrCreate([
                                                'ibnr' => 123456789
                                            ], [
                                                'name'      => 'testname',
                                                'latitude'  => 1.234,
                                                'longitude' => 1.234,
                                            ]);
        dump($ts2);

[MrKrisKrisu]

Maybe this occurs, when the same request is sent multiple times, so the database requests collide?

[MrKrisKrisu]

We should replace these queries by upsert. This will use the databases own on duplicate entry update function which is really fast.