yii\db\sqlite\QueryBuilder::upsert() not supported on sqlite < 3.8.3 - breaks yii\web\DbSession when using sqlite

[doublenb]

What steps will reproduce the problem?

Run these commands:

composer create-project --prefer-dist yiisoft/yii2-app-basic basic
cd basic
touch sessiondb.db
sqlite3 sessiondb.db "CREATE TABLE session (id CHAR (40) PRIMARY KEY NOT NULL, expire INTEGER, data BLOB);"

Add the following to components in config/web.php

'sessiondb' => [
    'class' => 'yii\db\Connection',
    'dsn' => 'sqlite:' . realpath(__DIR__ . "/../") . '/sessiondb.db',
    'charset' => 'utf8',
],
'session' => [
    'class' => 'yii\web\DbSession',
    'db' => 'sessiondb',
    'timeout' => 7200 // 7200 seconds = 2 hours
],

As far as I can tell, the error page also uses sessions, and causes the page to break without showing a Yii error message (it throws a yii\web\HeadersAlreadySentException as seen in nginx error logs). To help with this (just for illustration of this bug), at the top of web/index.php, add this below the opening <?php tag

ob_start();

What is the expected result?

The home page of the basic app to load successfully

What do you get instead?

A yii\db\Exception error page showing the following:

Database Exception – yii\db\Exception
SQLSTATE[HY000]: General error: 1 near "WITH": syntax error
Failed to prepare SQL: WITH "EXCLUDED" (`data`, `id`, `expire`) AS (VALUES (:qp0, :qp1, :qp2)) UPDATE `session` SET `data`=(SELECT `data` FROM `EXCLUDED`), `expire`=(SELECT `expire` FROM `EXCLUDED`) WHERE `session`.`id`=(SELECT `id` FROM `EXCLUDED`);

Additional info

Upon some investigation, the error seems to come from this commit which added upsert support for yii\db\QueryBuilder.

From what I can gather, the "WITH" command (or Common Table Expressions) was only added to sqlite in version 3.8.3 as detailed in https://www.sqlite.org/changes.html

"2014-02-03 (3.8.3)" "1. Added support for common table expressions and the WITH clause."

Q	A
Yii version	2.0.14
PHP version	PHP 7.2.14 (from remi-php72)
Operating system	CentOS Linux release 7.6.1810
sqlite version	3.7.17
PHP SQLite module version	7.2.14
PHP SQLite Library	3.7.17

[samdark]

Is upgrading SQLite possible? Not having upsert for session is a huge drawback (concurrency issues will be there).

[rob006]

Using SQLite as session backend already implies concurrency issues :D

[doublenb]

@samdark - I'm investigating if I can upgrade it, just thought I would raise the issue.

I see there is some consideration in the QueryBuilder for what the SQLite version is (e.g. in batchInsert()), but not in upsert(), so just thought the devs would like to know.

@rob006 - we have in the past seen some session locking problems using PHP default sessions, which is what the SQLite session DB helped with. Along with http2, it improved loading times on pages where there are multiple ajax calls with long running database queries.

[samdark]

@doublenb consider Redis. It's much better in this regard.

[santilin]

Just to remember that my project santilin/yii2-sqlite3-full-support adds some functionality to sqlite3. Although I have not yet implemented upsert(), I could try to implement it if required.