Db session integrity constraint violation

Yii 2.0 General Discussions
1

I just switched from cookie session to db session and I’m getting many integrity constraint violations.

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '6' for key 'PRIMARY'
The SQL being executed was: UPDATE `session` SET `id`=6 WHERE  `id`='50g77234tqjiqtcgat5fg6d0ua'

Error Info: Array
(
    [0] => 23000
    [1] => 1062
    [2] => Duplicate entry '6' for key 'PRIMARY'

My migration:

    $this->createTable('{{%session}}', [
        'id' => $this->primaryKey(),
        'user_id' => $this->integer(),
        'expire' => $this->integer(),
        'data' => 'LONGBLOB',
        'last_write' => $this->integer(),
    ], $tableOptions);

This issue was apparently fixed with #11401 in version 2.0.14. I’m running version 2.0.27.

2

Do you have it for concurrent writes (in logs) or all the time? What MySQL version do you have? Is the error still there with latest Yii 2 version?

3

It’s still happening with latest Yii install.

The error occurs on both server and localhost.
MySQL:
10.1.37-MariaDB (localhost)
10.1.44-MariaDB (server)

It happens periodically. In a half hour of browsing in an attempt to see the error, I’ve encountered it a few times and gotten logged out a few times.

4

In order to fix it we need a way to reproduce it.

5

I found the cause. I set both backend and frontend to use db session and they’re using the same session table. I suppose I need to have a separate table for backend session.

…sorry, amateur mistake.

6

I’m not out of the woods yet. It’s doing multiple inserts per page load, so I end up with tens of rows in a short amount of time. Is this normal behavior? Once in a while it results in the integrity constraint violation.

For example, this is from a single page load:

image

image
image2092×2774 214 KB

7

That looks a bit weird. A single page load should not result in multiple session records.