Problem/Motivation

Trying to get automated tests to work in SQlite that work fine when using MySQL connection.

Steps to reproduce

1. Apply patch from https://www.drupal.org/project/dbal/issues/3185995#comment-13923913
2. Set up phpunit to use sqlite.
3. Run phpunit , eg:

phpunit web/modules/contrib/dbal/tests/src/Kernel/DbalConnectionKernelTest.php

Proposed resolution

Update the dbal module to work with sqlite.

This is related to
https://www.drupal.org/project/dbal/issues/3185995

Issue fork dbal-3186378

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

Finn Lewis created an issue. See original summary.

finn lewis’s picture

finn lewis
Location Oxford
commented

Here's the error: 

phpunit  web/modules/contrib/dbal/tests/src/Kernel/DbalConnectionKernelTest.php 
PHPUnit 7.5.20 by Sebastian Bergmann and contributors.

Testing Drupal\Tests\dbal\Kernel\DbalConnectionKernelTest
E                                                                   1 / 1 (100%)

Time: 1.73 seconds, Memory: 6.00 MB

There was 1 error:

1) Drupal\Tests\dbal\Kernel\DbalConnectionKernelTest::testConnectionFactory
Doctrine\DBAL\Exception\TableNotFoundException: An exception occurred while executing 'INSERT INTO test16658908semaphore (name, value, expire) VALUES (?, ?, ?)' with params ["dbal_test", "dbal_test", 1607031156]:

SQLSTATE[HY000]: General error: 1 no such table: test16658908semaphore

/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractSQLiteDriver.php:63
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:184
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:158
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1088
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:800
/app/web/modules/contrib/dbal/tests/src/Kernel/DbalConnectionKernelTest.php:38

Caused by
Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 1 no such table: test16658908semaphore

/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:82
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1076
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:800
/app/web/modules/contrib/dbal/tests/src/Kernel/DbalConnectionKernelTest.php:38

Caused by
PDOException: SQLSTATE[HY000]: General error: 1 no such table: test16658908semaphore

/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:80
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1076
/app/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:800
/app/web/modules/contrib/dbal/tests/src/Kernel/DbalConnectionKernelTest.php:38

ERRORS!
Tests: 1, Assertions: 0, Errors: 1.
larowlan’s picture

larowlan
Location 🇦🇺🏝.au GMT+10
commented

Please see the known issues on the project page - https://www.drupal.org/project/dbal is this the same thing?

finn lewis’s picture

finn lewis
Location Oxford
commented

Hi larowlan,

Thanks for the rapid response!

Please note that table-prefixing isn't supported by DBAL, so you will need to make sure that your tests only write to the prefixed table ($this->getDatabasePrefix()) and not the base installation.

I'm not sure if this is the same thing, but it might be related.

The test in question is being run in isolation.

When the SIMPLETEST_DB variables is set to use mysql, it works fine: 

    <env name="SIMPLETEST_DB" value="mysql://database:database@database/database"/>

When the SIMPLETEST_DB variables is set to use sqlite, it fails: 

    <env name="SIMPLETEST_DB" value="sqlite://localhost/dev/shm/test.sqlite"/>

In neither case are we setting a specific table prefix in the connection string.

It looks to me like phpunit creates its db prefix when setting up the test.

Just to confirm, this is the test:

https://git.drupalcode.org/project/dbal/-/blob/8.x-1.x/tests/src/Kernel/...

larowlan’s picture

larowlan
Location 🇦🇺🏝.au GMT+10
commented

The main issue is that all the Drupal SQL drivers use a special query syntax, where the table names are wrapped in {} and then this is replaced with the prefixed name at query execution time.

There is no such concept (that I know of) in dbal.

In this scenario, are there any tables in the sqlite file?

finn lewis’s picture

finn lewis
Location Oxford
commented

With MySQL I see that the two tables are created, with the prefix:

test84518174semaphore
test84518174config

Which then allows the dbal connection to insert the row to test84518174semaphore.

With SQLite

Pausing the debugger before the insert is attempted at

https://git.drupalcode.org/project/dbal/-/blob/8.x-1.x/tests/src/Kernel/...

I see the following files created:

-rw-r--r-- 1 finn finn 4096 Dec 3 22:24 testfile.sqlite
-rw-r--r-- 1 finn finn 32768 Dec 3 22:25 testfile.sqlite-shm
-rw-r--r-- 1 finn finn 28672 Dec 3 22:25 testfile.sqlite-test81396060

Looking inside those files, the testfile.sqlite-shm file does have sql that suggests the creation of the same tables (long with lots of other crazy characters): 

@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@X^F^F^W-^_^Asindexsemaphore_expiresemaphore^GCREATE INDEX semaphore_expire ON semaphore (expire)U^E^F^W+^_^Aoindexsemaphore_valuesemaphore^FCREATE INDEX semaphore_value ON semaphore (value)<81>6^C^G^W^_^_^A<82>;tablesemaphoresemaphore^DCREATE TABLE semaphore (
name VARCHAR(255) NOT NULL DEFAULT '',
value VARCHAR(255) NOT NULL DEFAULT '',
expire FLOAT NOT NULL,
 PRIMARY KEY (name)
)1^D^F^WE^_^A^@indexsqlite_autoindex_semaphore_1semaphore^E<81>D^A^G^W^Y^Y^A<82>ctableconfigconfig^BCREATE TABLE config (
collection VARCHAR(255) NOT NULL DEFAULT '',
name VARCHAR(255) NOT NULL DEFAULT '',
data BLOB NULL DEFAULT NULL,
 PRIMARY KEY (collection, name)
)+^B^F^W?^Y^A^@indexsqlite_autoindex_config_1config^C^@^@^@^H^@^@^@^@^M^@^@^@^A^Ot^@^Ot^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@

Allowing the test to finsh and fail, leaves just the one file

-rw-r--r-- 1 finn finn 4096 Dec 3 22:24 testfile.sqlite

So it seems that sqlite creates a number of files as it does it's thing, but perhaps the dbal connection is still looking at the original file name.

finn lewis’s picture

finn lewis
Location Oxford
commented
Issue summary: View changes
finn lewis’s picture

finn lewis
Location Oxford
commented

I should also have been clear that I have applied the patch from https://www.drupal.org/project/dbal/issues/3185995#comment-13923913

I've updated the summary to clarify that as a step to reproduce.

I should probably have just extended that issue to be honest, sorry.

Is the issue something around not providing the dbal connection the right information about the sqlite connection?

What is the correct array of options to pass to this for an sqlite connection?

$this->cache[$target] = DriverManager::getConnection($options, new Configuration());

https://git.drupalcode.org/project/dbal/-/blob/8.x-1.x/src/ConnectionFac...

ekes made their first commit to this issue’s fork.

ekes opened merge request !2

ekes’s picture

ekes commented

I've made a similar comment in the commit message on that branch, but I'll pop it here too.

Drupal handles prefixes for SQLite quite differently from MySQL. SQLite has a concept where you have attachment databases to the current connection. These are accessed via `attachment_name.table_name`, with a period, much as in MySQL you'd specify a database. When you request the ::getFullQualifiedTableName() MySQL will return `prefix_stringtable_name` where as SQLite will return 'prefix_string.table_name' so it is actually using one of these attachment databases. When using DBAL you can access MySQL prefixed tables if you take the responsibility to access the correct table name by just adding the prefix. When using DBAL with SQLite the attachment databases need to be added first.

  • ekes committed 1e03f3d on 8.x-1.x 
    Issue #3186378: Fix sqlite, by attaching 'prefix' dbs, use in tests....
larowlan’s picture

larowlan
Location 🇦🇺🏝.au GMT+10
commented

❤️ thank you, I had no idea about any of these concepts.

Rolling a new release with this change included.

larowlan’s picture

larowlan
Location 🇦🇺🏝.au GMT+10
commented
Status: Active » Fixed
finn lewis’s picture

finn lewis
Location Oxford
commented

Amazing, thanks @ekes and thanks @larowlan for rolling a new release so quickly.

larowlan’s picture

larowlan
Location 🇦🇺🏝.au GMT+10
commented

No worries, commits that sit in HEAD without a release for a long time is a pet peeve, so I favour release early, release often

You should see entity_hierarchy's list of releases

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.