Integrity constraint violation when using database session driver

[thewinterwind]

Experienced this in 5.0 and 5.1

MySQL version: 5.6 Forge provisioned server.

We use Galera Cluster for MySQL, but each site reads and writes from only one database. I don't think that would have anything to do with this error. I used artisan session:table to create the table so everything is standard schema-wise. Happens quite intermittently, about once per day.

Illuminate\Database\QueryExceptionGET /diaz/edit
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4c1698dcfe5da87c5f77c90592855490817e575d' for key 'sessions_id_unique' (SQL: insert into `sessions` (`id`, `payload`, `last_activity`) values (4c1698dcfe5da87c5f77c90592855490817e575d, YTo1OntzOjY6Il90b2tlbiI7czo0MDoid1NLSjlmNGFxUml4RkowRVVkU0M3UjhNdldhWlZEY2hwTDhrbEU3YSI7czo3OiJtZXNzYWdlIjtzOjI2OiJMb2dpbiB0byBlZGl0IHlvdXIgcHJvZmlsZSI7czo1OiJmbGFzaCI7YToyOntzOjM6Im5ldyI7YTowOnt9czozOiJvbGQiO2E6MTp7aTowO3M6NzoibWVzc2FnZSI7fX1zOjk6Il9wcmV2aW91cyI7YToxOntzOjM6InVybCI7czozNzoiaHR0cHM6Ly9tYW5pbGEuZXNjb3J0cy5saWZlL2RpYXovZWRpdCI7fXM6OToiX3NmMl9tZXRhIjthOjM6e3M6MToidSI7aToxNDM0MTY4Njc0O3M6MToiYyI7aToxNDM0MTY4Njc0O3M6MToibCI7czoxOiIwIjt9fQ==, 1434168674))

[ryzr]

Issue is still present in Laravel v5.3.26.

Currently, we have anywhere between 3 and 40 instances serving our app, with a single read/write MariaDB database. Our sessions table typically contains 15k+ sessions at a time, expiring in 2 hour intervals.

Illuminate\Database\QueryException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'fLOk3VaO0TxW1vFxrXX6q3hcGg8DmGKGpb7QsIUZ' for key 'sessions_id_unique'

Stack trace:
#0 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Database/Connection.php(725): Illuminate\Database\Connection->runQueryCallback('insert into `se...', Array, Object(Closure))
#1 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Database/Connection.php(480): Illuminate\Database\Connection->run('insert into `se...', Array, Object(Closure))
#2 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Database/Connection.php(434): Illuminate\Database\Connection->statement('insert into `se...', Array)
#3 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2136): Illuminate\Database\Connection->insert('insert into `se...', Array)
#4 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Session/DatabaseSessionHandler.php(119): Illuminate\Database\Query\Builder->insert(Array)
#5 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Session/Store.php(263): Illuminate\Session\DatabaseSessionHandler->write('fLOk3VaO0TxW1vF...', 'a:4:{s:6:"_toke...')
#6 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(88): Illuminate\Session\Store->save()
#7 /app/xxx/current/src/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(177): Illuminate\Session\Middleware\StartSession->terminate(Object(Illuminate\Http\Request), Object(Illuminate\Http\Response))
#8 /app/xxx/current/src/public/index.php(62): Illuminate\Foundation\Http\Kernel->terminate(Object(Illuminate\Http\Request), Object(Illuminate\Http\Response))
#9 {main}

[taylorotwell]

There is absolutely no point in anyone else saying "I have this issue". It is very apparent people have had the issue. What is more helpful is someone working to fix the issue.

[trip-somers]

Here's another proposal since my earlier one didn't seem to gain any traction...

Instantly create/update the database record when a new session ID is generated, then proceed as normal. The idea would be to make sure there's a record (creating if necessary) when loading the session ('before' middleware) rather than waiting until storing it ('after' middleware).

[[NOTE: My previous proposal worked well for me, so I haven't spent any more time on this. Forgive me if I've spaced out or spoken incorrectly on the 'before' and 'after' parts of the session middleware.]]

[taylorotwell]

@trip-somers thanks for both suggestions. Have we actually nailed down why this is happening?

[trip-somers]

I still have no clue. Every scenario I have come up with makes no sense. It sure seems impossible that two "new" requests could exist with the same session ID. The only thing I haven't explored is the potential existence of some kind of sub/internal request/response that creates the race condition.

The theory here would be that something is happening internally that "passes" the new session ID into a subroutine that terminates (some kind of 404 or abort in template? really have no idea) in a way that triggers the DB write before the main request triggers it. This is my original "fork/branch" duplicate idea, but I still can't figure out what's forking or branching, so I don't totally buy it as possible.

Is there anything anyone (@taylorotwell, @GrahamCampbell, etc.) can think of that would spawn such a subroutine-based race condition?

Unless it more simple than that. Is it possible for the authentication cookie to have the new session ID before it is written to the database? This would fit the AJAX scenario described by some -- an on-load AJAX request with the "new" session ID terminates before main page response is completed. If so, this could be chalked up to very simple HTTP request latency / network traffic / slow connections.

[taylorotwell]

Fixed on Laravel 5.4.

[calebfavor]

@taylorotwell - Any chance you could point me to where this was patched? I am hoping to put together a fix for 4.2 as well.

**Edit: Never mind, I found it.

For those interested: https://github.com/laravel/framework/commit/d9e0a6a03891d16ed6a71151354445fbdc9e6f50

[roshangautam]

Can someone please tag a release in 5.1 for this fix ?

[mediaceh]

I have same error in version 5.3.23.

`[2017-02-06 11:37:18] production.ERROR: exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'XlZRvW1oqTObXyH3oI5HQi9r8HubHOoIt2eA0sJS' for key 'sessions_id_unique'' in /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:91 Stack trace:

0 /var/www/html/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php(91): PDOStatement->execute(NULL)

1 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(479): Doctrine\DBAL\Driver\PDOStatement->execute()

2 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(762): Illuminate\Database\Connection->Illuminate\Database{closure}(Object(Illuminate\Database\MySqlConnection), 'insert into `se...', Array)

3 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(725): Illuminate\Database\Connection->runQueryCallback('insert into `se...', Array, Object(Closure))

4 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(480): Illuminate\Database\Connection->run('insert into `se...', Array, Object(Closure))

5 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Connection.php(434): Illuminate\Database\Connection->statement('insert into `se...', Array)

6 /var/www/html/vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php(2136): Illuminate\Database\Connection->insert('insert into `se...', Array)

7 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/DatabaseSessionHandler.php(119): Illuminate\Database\Query\Builder->insert(Array)

8 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Store.php(263): Illuminate\Session\DatabaseSessionHandler->write('XlZRvW1oqTObXyH...', 'a:4:{s:6:"_toke...')

9 /var/www/html/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(88): Illuminate\Session\Store->save()

10 /var/www/html/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(176): Illuminate\Session\Middleware\StartSession->terminate(Object(Illuminate\Http\Request), Object(Illuminate\Http\Response))

11 /var/www/html/public/index.php(58): Illuminate\Foundation\Http\Kernel->terminate(Object(Illuminate\Http\Request), Object(Illuminate\Http\Response))

12 {main}`

[roshangautam]

How wonderful, this is not patched in 5.2 branch too.

[carltondickson]

This fix isn't in release v5.1.45 but is in 5.1 branch.

How often are new tags/releases created for the LTS releases? Would second a 5.1 release/tag assuming it's good to go.

[cupit]

Hello, I'm using v5.2.45 and I noticed many problems with this, usually when the website has rush hours.. any idea how to fix it in v5.2.45?

[markwinters]

We received the integrity constraint violation on the session table yesterday. But noticed in a request that happend 38 seconds later that another error occurd stating -> General error: 1205 Lock wait timeout exceeded. This happend on the garbage collection on the session table.

When looking into this I wonder, how can the fix work? It seems to update when the insert fails. But the database already received an invalid insert in the first place. I believe this fix to be a workarround on the real issue that is not solved. It should not try to insert in the first place.

I would like to propose a solution (that was already mentioned before) to do a count request or something like that to properly check if the session already exists in the database.

[art-vanesyan]

Laravel 9 Error: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'LgM92Cl35ZxNS3b6EACA3itfgmAn77BaO91HadLf' for key 'PRIMARY'. Transaction has been restarted. Attempt 7/10. SQL: insert into sessions (payload, last_activity, user_id, ip_address, user_agent, id) values ('YToyOntzOjY6Il90b2tlbiI7czo0MDoib0M2MTVrZ2xtdjJ6WkRvelFWZGExdW9BQVkxVHc5Zk5QSXRReklGbiI7czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319fQ==', '1669315180', '923363', '91.211.136.202', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/107.0.0.0 Safari/537.36', 'LgM92Cl35ZxNS3b6EACA3itfgmAn77BaO91HadLf')