SQLite SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

Question

I'm using an SQLite connection and doctrine migrations for functional testing with PHPUnit. I'm making a DB migration from scratch in the setUp method:

public function setUp()
{
    parent::setUp();
    
    @unlink(__DIR__ . '/../../../../../../../var/sqlite.db');

    exec('./vendor/bin/doctrine-migrations migrations:migrate --db-configuration=migrations-db-test.php --configuration=migrations_test.yml --no-interaction');
}

and then I can write/read from DB. E.g.:

public function test_add_event_should_add_event()
{
    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::USER_IP);
    $response = $service->execute($request);

    $this->assertEquals(1, $response->getTotal());
}

and it works. And it does work even when I call twice the service with the same arguments. In this case it only has to write the first time:

public function test_add_two_same_events_should_add_one_event()
{
    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::USER_IP);
    // Call twice
    $service->execute($request);
    $response = $service->execute($request);

    $this->assertEquals(1, $response->getTotal());
}

The problem comes when I have to test two calls that have to write both:

public function test_add_two_different_events_should_add_two_events()
{
    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::USER_IP);
    $response = $service->execute($request);

    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::OTHER_USER_IP);
    $response = $service->execute($request); // **** It fails here

    $this->assertEquals(2, $response->getTotal());
}

Here comes the error:

XXX::test_add_two_different_events_should_add_two_event Doctrine\DBAL\Exception\ReadOnlyException: An exception occurred while executing 'INSERT INTO xxx (xxx, xxx, xxx, xxx) VALUES (?, ?, ?, ?)' with params [xxx, "xxx", "xxx", "xxx"]:

SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

xxx/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractSQLiteDriver.php:78 xxx/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:128 xxx/vendor/doctrine/dbal/lib/Doctrine/DBAL/Statement.php:178 xxx/vendor/doctrine/orm/lib/Doctrine/ORM/Persisters/Entity/BasicEntityPersister.php:281 xxx/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:1014 xxx/vendor/doctrine/orm/lib/Doctrine/ORM/UnitOfWork.php:378 xxx/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:356 xxx/vendor/doctrine/orm/lib/Doctrine/ORM/EntityManager.php:235 xxx/DoctrineSession.php:32 xxx/TransactionalApplicationService.php:39 xxx/xxx/test.php:100

I've tried changing the DB file permissions between calls, but nothing changes:

public function test_add_two_different_events_should_add_two_event()
{
    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::USER_IP);
    $response = $service->execute($request);

    chmod(__DIR__ . '/../../../../../../../var', 0777);
    chmod(__DIR__ . '/../../../../../../../var/sqlite.db', 0777);
    chown(__DIR__ . '/../../../../../../../var', 'www-data');
    chgrp(__DIR__ . '/../../../../../../../var', 'www-data');
    chown(__DIR__ . '/../../../../../../../var/sqlite.db', 'www-data');
    chgrp(__DIR__ . '/../../../../../../../var/sqlite.db', 'www-data');
   //die;
   // Here I checked the /var sqlite.db permissions. They are 0777

    $service = $this->getAdEventComparativesUpdateService();
    $request = AdEventComparativesUpdateServiceRequest::make(self::AD_ID, self::OTHER_USER_IP);
    $response = $service->execute($request);

    $this->assertEquals(2, $response->getTotal());
}

Any idea of where could the error come from? Every service call will call persist + flush in this case.

This is a stab in the dark, as I don't know doctrine... I can _conceive_ that the "real" error might not be that the database is readonly, but that it is locked in some way. _Perhaps_ (and I'm out on a limb) things are happening asynchronously and the `getTotal()` call synchronises things? If so (big "if"), it might be that both updates are trying to run at the same time. Does anything change if you add `$this->assertEquals(1, $response->getTotal());` after the first `execute()`? — TripeHound, Aug 08 '20 at 14:31
@TripeHound In fact the error is thrown in the second `execute` call. `getTotal` is just a getter. I even tried calling `sleep` for some seconds between calls, so they're not trying to update at the same time neither. BTW, if I call `execute` only once, it works. This is the weird part — Manolo, Aug 09 '20 at 16:05
@Manolo If it IS to do with asynchronous execution (and I could easily be wrong) the 2nd execute is pretty much where I'd expect it to go wrong... you've created two "services", and when you try to execute the second it returns an error because the DB is already in use. I'm guessing, but it's _possible_ the `response->getTotal()` waits for the first service to complete before returning. If it's not too difficult, I'd try with the extra call (essentially duplicate the four lines in your first test, but change the 2nd assert to 2). — TripeHound, Aug 09 '20 at 20:06
Check the privileges to your folder, maybe php doesn't have permissions to directory — Dmitry Leiko, Aug 18 '20 at 10:21
Maybe before those changes you can try: var_dump(exec('whoami')) To see the owner of the scrip executing at the moment and then not inside the php but rather in the terminal do sudo chown -R for the whole project directory — g9m29, Aug 19 '20 at 06:33
@g9m29 - It's a docker container and I'm running as root, so the `var_dump` outputs "root". root user has full permissions — Manolo, Aug 19 '20 at 09:30
@Manolo The original sqlite status code can be more specific than 'readonly' if you turn on [extended result codes](http://www.sqlite.org/c3ref/c_abort_rollback.html). — János Roden, Aug 19 '20 at 12:14
@Manolo I'm not sure either, but I'd try `$yourConnection->setAttribute(PDO:: PDO_SQLITE_ATTR_EXTENDED_RESULT_CODES)` based on [this example](https://github.com/nextcloud/3rdparty/blob/830ec344a47a108f78440cbc372d973efff34411/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php#L28) and [this PR](https://github.com/php/php-src/pull/4166/files) — János Roden, Aug 19 '20 at 12:48
@JánosRoden I tried `$this->setAttribute(PDO::SQLITE_ATTR_EXTENDED_RESULT_CODES, true);` but I get this error: Fatal error: Undefined class constant 'SQLITE_ATTR_EXTENDED_RESULT_CODES' — Manolo, Aug 19 '20 at 12:56
@Manolo It's a PHP [7.4 feature](https://www.php.net/manual/en/migration74.new-features.php). Maybe you're using older version (?) — János Roden, Aug 19 '20 at 13:06
@JánosRoden - You're right. I'm using PHP 5. This is needed by now — Manolo, Aug 19 '20 at 13:10

wp78de · Answer 1 · 2020-08-11T17:06:28.107

0

It's difficult to answer this because we do not have true [mre]. Most commonly this is a permission issue but it looks like you already explored this route. I think I have a hunch what's going on here:

It looks like the unit tests recreate the DB on every test, i.e. unlinking the DB file, and then when the connection is opened the DB file gets automatically created if it doesn’t exist. So, in case of the two write attempts the connection is opened first and stays open. Then the DB file unlink gets called by the same or another test and the subsequent write command fails.

edited Aug 11 '20 at 17:06

answered Aug 09 '20 at 03:14

wp78de

18,207
7
43
71

Sorry, I don't get your point. You're right in that the DB is deleted and recreated for every test method. The `setUp` method is called before every test method so that's exactly what it's doing. However the `unlink`, which deletes the DB file and the DB recreation command are called only once before the method, not between the two calls of the same method. And in fact, it works if the second call just reads the DB. So what's your suggestion? – Manolo Aug 09 '20 at 16:16
@Manolo I was thinking the DB gets recreated while the test is running. However, if a subsequent read works why should a write fail? Have you tried to capture the resulting SQL queries and enter them manually in the same sequence? Maybe some constraint violation..? – wp78de Aug 09 '20 at 17:41
No, but I'll try it. – Manolo Aug 09 '20 at 17:48
It works if I execute the queries manually, so this doesn't seem to be the problem – Manolo Aug 10 '20 at 07:20

SQLite SQLSTATE[HY000]: General error: 8 attempt to write a readonly database

1 Answers1