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:

1. 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.

Toscanini

https://mcmap.net/q/596416/-laravel-sqlite-sqlstate-hy000-general-error-8-attempt-to-write-a-readonly-database – Robbert

@Robbert This is what I did – Toscanini

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()? – Froward

@Froward 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 – Toscanini

@Toscanini 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). – Froward

@Froward - Thanks but it's not working – Toscanini

Check the privileges to your folder, maybe php doesn't have permissions to directory – Osmen

@Dmitry - yes, it has 0777 – Toscanini

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 – Coimbatore

@g9m29 - It's a docker container and I'm running as root, so the var_dump outputs "root". root user has full permissions – Toscanini

@Toscanini The original sqlite status code can be more specific than 'readonly' if you turn on extended result codes. – Commanding

@JánosRoden No idea how to do this in PHP – Toscanini

@Toscanini I'm not sure either, but I'd try $yourConnection->setAttribute(PDO:: PDO_SQLITE_ATTR_EXTENDED_RESULT_CODES) based on this example and this PR – Commanding

@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' – Toscanini

@Toscanini It's a PHP 7.4 feature. Maybe you're using older version (?) – Commanding

@JánosRoden - You're right. I'm using PHP 5. This is needed by now – Toscanini

Answer 1

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.

Floyd

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? – Toscanini

@Toscanini 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..? – Floyd

No, but I'll try it. – Toscanini

It works if I execute the queries manually, so this doesn't seem to be the problem – Toscanini