DB set to read-only when executing a query from SQL panel #33
Description
When I manually execute a long-ish (5-10 seconds) query from the SQL panel it is executed correctly but all the Python jobs that need to write on the DB start failing with attempt to write a readonly database.
When this happens, on /var/log/php/php_errors.log appears either the following:
PHP Notice: Undefined variable: error in /var/www/html/mydomain.io/public/php/phpliteadmin/phpliteadmin.php on line 1670
, or the following:
PHP Notice: Undefined variable: error in /var/www/html/mydomain.io/public/php/phpliteadmin/phpliteadmin.php on line 1587
If I check the permissions of the database, they have been changed from:
mydomain.sqlite3 -rwxrwxr-x myuser www-data
mydomain.sqlite3-shm -rwxrwxr-x www-data www-data
mydomain.sqlite3-wal -rwxrwxr-x www-data www-data
, to the following:
mydomain.sqlite3 -rwxrwxr-x myuser www-data
mydomain.sqlite3-shm -rwxrwxr-x carlos carlos
mydomain.sqlite3-wal -rwxrwxr-x carlos carlos
This problem persists until I execute a new query from the SQL panel. At that moment, the permissions revert back to normal (www-data www-data) and all the jobs start writing on the DB correctly again.
I don’t know what is causing this behavior but it’s 100% reproducible on my server.
If you need more information, please don’t hesitate to tell me.
P.S.: For reference, the query that always set the DB to read-only is:
WITH RECURSIVE dates(date) AS (
VALUES('2022-06-01')
UNION ALL
SELECT date(date, '+1 day')
FROM dates
WHERE date < date('now')
)
SELECT da.date,
COUNT(id) AS total,
IFNULL(ROUND(COUNT(deletion_date) * 100.0 / COUNT(id), 2), 100.0) AS processed_percent,
COUNT(id) - COUNT(deletion_date) AS non_processed,
IFNULL(ROUND(COUNT(posted_date) * 100.0 / COUNT(id), 2), 100.0) AS posted_percent,
COUNT(id) - COUNT(posted_date) AS non_posted
FROM dates da
LEFT JOIN tw2deso
ON da.date = date(tw2deso.creation_date)
GROUP BY da.date
ORDER BY da.date DESC;
tw2deso is a table that contains about 10000 elements.