yii2: Database exception using DbSession

What steps will reproduce the problem?

When I’m trying to login on the website, from time to time I’m receiving an error message. After reloading of the page everything is ok. YII_DEBUG is enabled, YII_ENV_DEV is set to true.

Maybe this issue is linked with this one

Stackoverflow question

What do you get instead?

Integrity constraint violation – yii\db\IntegrityException

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’ The SQL being executed was: UPDATE session SET id=0 WHERE id=‘3u5r7gq6pqh8s0gjrusfqpmni7’ Error Info: Array ( [0] => 23000 [1] => 1062 [2] => Duplicate entry ‘0’ for key ‘PRIMARY’ ) ↵ Caused by: PDOException

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘0’ for key ‘PRIMARY’

in /var/www/develop/vendor/yiisoft/yii2/db/Command.php at line 844

Additional info

Q	A
Yii version	2.0.10
PHP version	7.0
Operating system	Ubuntu 14.04

About this issue

Original URL
State: closed
Created 8 years ago
Comments: 31 (28 by maintainers)

Most upvoted comments

This means that the hardest part (pgsql <9.5) is done. Other implementations are trivial. MySQL’s and MSSQL’s upserts are also completed.

sergeymakinen on Nov 13, 2017

@sergeymakinen is trying hard to finish upserts support.

samdark on Nov 28, 2017

Duplicate entry ‘1’ for key ‘PRIMARY’

your sessionid is 1?

cebe on Nov 13, 2017

@sergeymakinen https://github.com/symfony/symfony/pull/10652/files may give you some hints (related problem).

samdark on Nov 13, 2017

Yes, a hotfix in 2.0.13.1 sounds reasonable. For example it took a whole day to make PostgreSQL (pre 9.5, since 9.5 it’s as easy as ABC) QB produce this (it’s correct and handles lots of DBMS’ specifics):

WITH
        "EXCLUDED" ("email", "name", "address", "bool_status", "profile_id") AS (VALUES (:qp0, :qp1, :qp2, :qp3, CAST(NULL AS int4))),
        "upsert" AS (UPDATE "customer" SET "email"="EXCLUDED"."email", "name"="EXCLUDED"."name", "address"="EXCLUDED"."address", "bool_status"="EXCLUDED"."bool_status", "profile_id"="EXCLUDED"."profile_id" FROM "EXCLUDED" WHERE (("customer"."email"="EXCLUDED"."email")) RETURNING "customer".*)
INSERT INTO "customer" ("email", "name", "address", "bool_status", "profile_id") SELECT "email", "name", "address", "bool_status", "profile_id" FROM "EXCLUDED" WHERE NOT EXISTS (SELECT 1 FROM "upsert" WHERE (("upsert"."email"="EXCLUDED"."email")))

So it will definitely require testing (in addition to unit tests).

sergeymakinen on Nov 13, 2017

Implementation of upsert looks really complicated to me, it probably will be source of new bugs and may make situation even worse. I prefer use this hotfix in patch release and switch to upsert in 2.0.14.

rob006 on Nov 12, 2017

@bizley thanks for reasonable question! In this case DbSession:: gcSession will handle all expired data

andrewnester on Jan 18, 2017