Integrity constraint violation

[djschilling]

This is my config:

    $dbWrapper = new PDOMySQLSession();
    $session2DB = new Session2DB(
        '',
        $sessionLifetime,
        false,
        false,
        1,
        1000,
        'session_data',
        60,
        $dbWrapper,
        false
    );
    $session2DB->use_lock_via_mysql(null);
    return $session2DB->start();

I use version 2.1.2 of session2db.
I also use galera cluster as my mysql setup.
In general it works.

But i often get this error:

2018/01/16 16:50:10 [error] 789#789: *13705337 FastCGI sent in stderr: "PHP message: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'session_c0afbf3691e8cd2d85ec2e38ea52ae2441
1e3b4d' for key 'PRIMARY' in /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php:65
Stack trace:
#0 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php(65): PDO->query('\n      INSERT I...')
#1 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(418): churchtools\PDOMySQLSession->query('\n      INSERT I...')
#2 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(373): voku\helper\Session2DB->_get_lock_mysql_fake('session_c0afbf3...', '1516117870'
)
#3 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(723): voku\helper\Session2DB->_get_lock('92jS" while reading response header from upstream, client: 192.168.20.1, server: ~^(?P<sub>.+)\.church\.tools$, request: "POST /index.php?q=churchservice/ajax HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.0-fpm.sock:", host: "testanne.church.tools", referrer: "https://testanne.church.tools/?q=churchservice"

And also this:

2018/01/16 16:54:33 [error] 724#724: *14467437 FastCGI sent in stderr: "PHP message: PDOException: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'session_c0afbf3691e8cd2d85ec2e38ea52ae2441
1e3b4d' for key 'PRIMARY' in /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php:65
Stack trace:
#0 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php(65): PDO->query('\n      INSERT I...')
#1 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(418): churchtools\PDOMySQLSession->query('\n      INSERT I...')
#2 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(373): voku\helper\Session2DB->_get_lock_mysql_fake('session_c0afbf3...', '1516118133'
)
#3 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(723): voku\helper\Session2DB->_get_lock('92jS" while reading response header from upstream, client: 192.168.20.3, server: ~^(?P<sub>.+)\.church\.tools$, request: "POST /index.php?q=churchservice/ajax HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.0-fpm.sock:", host: "testanne.church.tools", referrer: "https://testanne.church.tools/?q=churchservice"
2018/01/16 16:54:33 [error] 724#724: *14467797 FastCGI sent in stderr: "PHP message: PDOException: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction in /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php:65
Stack trace:
#0 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/classes/churchtools/PDOMySQLSession.php(65): PDO->query('\n      INSERT I...')
#1 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(418): churchtools\PDOMySQLSession->query('\n      INSERT I...')
#2 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(373): voku\helper\Session2DB->_get_lock_mysql_fake('session_c0afbf3...', '1516118133')
#3 /opt/churchtools_application/versions/churchtools-3.26.0-RC1/system/composer/voku/session2db/src/voku/helper/Session2DB.php(723): voku\helper\Session2DB->_get_lock('92jSrGhDJGiJTqs...')
#4 [intern" while reading response header from upstream, client: 192.168.20.3, server: ~^(?P<sub>.+)\.church\.tools$, request: "POST /index.php?q=churchdb/ajax HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.0-fpm.sock:", host: "testanne.church.tools", referrer: "https://testanne.church.tools/?q=churchservice"

[djschilling]

I was able to remove the Deadlock problems.

But i still get the Error Duplicate entry 'session_c0afbf3691e8cd2d85ec2e38ea52ae2441.

[voku]

Hi, sorry for the late response. Any idea how I can reproduce the issue in a unit test?

-> maybe you can try this hack? "b5e7931"

[djschilling]

I think a lot of parallel saving of the same session_key should produce the error quit fast.

The problem in my opinion is this.

1. The session_lock table is checked if there is a lock.
2. When there is no lock a new lock is written to the database.

If there is another one trying to right to the session_lock between 1. and 2. it is possible cause the two steps are not atomar, or run in a transaction.