test_disk_storage_has_attached_database fails

[maksverver]

The comment does mention the test is brittle. Is this a real failure, or just a flake? If it's not a real failure, is there a way to make it pass more reliably?

Test output is below:

================================================================================== test session starts ==================================================================================
platform linux -- Python 3.12.5, pytest-8.3.2, pluggy-1.5.0
rootdir: /home/maks/Code/reader
configfile: pyproject.toml
testpaths: tests
plugins: typeguard-4.3.0, subtests-0.12.1, requests-mock-1.11.0
collected 2190 items                                                                                                                                                                    

tests/test__types.py ..........................................................................................                                                                   [  4%]
tests/test_app.py ssssss                                                                                                                                                          [  4%]
tests/test_app_wsgi.py .                                                                                                                                                          [  4%]
tests/test_bench.py sss                                                                                                                                                           [  4%]
tests/test_changes.py .........................                                                                                                                                   [  5%]
tests/test_cli.py ssssss..                                                                                                                                                        [  6%]
tests/test_config.py ........                                                                                                                                                     [  6%]
tests/test_exceptions.py ................                                                                                                                                         [  7%]
tests/test_hash_utils.py ................................                                                                                                                         [  8%]
tests/test_html_utils.py ........................................................................................                                                                 [ 12%]
tests/test_lazy_imports.py sssss                                                                                                                                                  [ 12%]
tests/test_parser.py ............................................................................................................................................................ [ 20%]
..............................................................ss..........................................................                                                        [ 25%]
tests/test_plugins_cli_status.py ss                                                                                                                                               [ 25%]
tests/test_plugins_enclosure_dedupe.py .                                                                                                                                          [ 25%]
tests/test_plugins_entry_dedupe.py ...........................................................................................                                                    [ 29%]
tests/test_plugins_mark_as_read.py .......                                                                                                                                        [ 30%]
tests/test_plugins_preview_feed_list.py ....s                                                                                                                                     [ 30%]
tests/test_plugins_readtime.py ..........................                                                                                                                         [ 31%]
tests/test_plugins_sqlite_releases.py .                                                                                                                                           [ 31%]
tests/test_plugins_ua_fallback.py ..                                                                                                                                              [ 31%]
tests/test_reader.py .s.s..s.s...ss..ss..sssss.s.s.......s.s......s.s..s.s..s.s....sss...sss........................................................ss..sssssssssssssssssssssssss [ 38%]
sssssssssssssss..........ssssssss......s....................................................ssssssss....ss........................................                                [ 45%]
tests/test_reader_context.py ...........s..s                                                                                                                                      [ 46%]
tests/test_reader_counts.py ......................................                                                                                                                [ 47%]
tests/test_reader_filter.py ......................ssssssssssssssssssssss......................ssssssssssssssssssssssssssssssssssssssssssss....................................... [ 54%]
.....ssssssssssssssssssssss......................ssssssssssssssssssssssssssssssssssssssssssss....................ssssssssssssssssssss....................ssssssssssssssssssssssss [ 62%]
ssssssssssssssss.....sssss.....ssssssssss..........ssssssssss..........ssssssssssssssssssss..........ssssssssss..........ssssssssssssssssssss...............                      [ 69%]
tests/test_reader_hooks.py .....ss.s..s..s..ss.....ss..                                                                                                                           [ 71%]
tests/test_reader_integration.py ...s                                                                                                                                             [ 71%]
tests/test_reader_plugins.py .........                                                                                                                                            [ 71%]
tests/test_reader_private.py ..ss..ss...s.s............ssssssss................ssssssss........                                                                                   [ 74%]
tests/test_reader_search.py ..................................ssss............................s.....                                                                              [ 78%]
tests/test_reader_sort.py sss...sss...ssssss...sss...sssssssssssssssssssss...sss...ssssss...sss...ssssssssssssssssssss..ss..ssss..ss..sssssssssssss.s.s                           [ 83%]
tests/test_reader_update.py .sss                                                                                                                                                  [ 84%]
tests/test_search.py .......sssssssss...........F.                                                                                                                                [ 85%]
tests/test_sql_utils.py ......                                                                                                                                                    [ 85%]
tests/test_sqlite_utils.py ..x......................s                                                                                                                             [ 86%]
tests/test_storage.py ..............ssssssssssssssssssssssssssssssssssss.........                                                                                                 [ 89%]
tests/test_tags.py ,,,.,,,.,,,,,,,.,,,,,,,.,,,,,,-.,,,,,,,,.,,,,,,,,.,,,,,,,,.,,,,,,,,.ss,,,,,,.,,,,,,.s..s.....................................                                  [ 91%]
tests/test_test_utils.py .                                                                                                                                                        [ 92%]
tests/test_types.py ............................................................................................................................................................. [ 99%]
..............                                                                                                                                                                    [ 99%]
tests/test_utils.py ....                                                                                                                                                          [100%]

======================================================================================= FAILURES ========================================================================================
________________________________________________________________________ test_disk_storage_has_attached_database ________________________________________________________________________

db_path = '/tmp/pytest-of-maks/pytest-24/test_disk_storage_has_attached0/db.sqlite', request = <FixtureRequest for <Function test_disk_storage_has_attached_database>>

    def test_disk_storage_has_attached_database(db_path, request):
        storage = Storage(db_path)
        request.addfinalizer(storage.close)

        search = Search(storage)
        search.enable()
        db = storage.factory()

        databases = {r[1:3] for r in db.execute('pragma database_list')}
        assert databases == {('main', db_path), ('search', db_path + '.search')}

        main_schema = {r[0] for r in db.execute('select name from main.sqlite_master')}
        assert 'entries_search' not in main_schema
        assert 'entries_search_sync_state' not in main_schema

        search_schema = {r[0] for r in db.execute('select name from search.sqlite_master')}
        assert 'entries_search' in search_schema
        assert 'entries_search_sync_state' in search_schema

        search.disable()

        search_schema = {r[0] for r in db.execute('select name from main.sqlite_master')}
        assert 'entries_search' not in search_schema
        assert 'entries_search_sync_state' not in search_schema

        # check the VACUUM actually happened; may be brittle
>       assert db.execute('pragma search.page_count').fetchone() == (1,)
E       assert (3,) == (1,)
E         
E         At index 0 diff: 3 != 1
E         Use -v to get more diff

tests/test_search.py:209: AssertionError
=================================================================================== warnings summary ====================================================================================
tests/test_html_utils.py: 176 warnings
tests/test_plugins_preview_feed_list.py: 4 warnings
tests/test_plugins_readtime.py: 11 warnings
tests/test_plugins_sqlite_releases.py: 1 warning
tests/test_reader.py: 7 warnings
tests/test_reader_context.py: 1 warning
tests/test_reader_counts.py: 7 warnings
tests/test_reader_filter.py: 1 warning
tests/test_reader_search.py: 29 warnings
tests/test_reader_sort.py: 9 warnings
tests/test_search.py: 2 warnings
  /usr/lib/python3.12/site-packages/bs4/builder/_lxml.py:124: DeprecationWarning: The 'strip_cdata' option of HTMLParser() has never done anything and will eventually be removed.
    parser = parser(

-- Docs: https://docs.pytest.org/en/stable/how-to/capture-warnings.html
================================================================================ short test summary info ================================================================================
FAILED tests/test_search.py::test_disk_storage_has_attached_database - assert (3,) == (1,)
=============================================== 1 failed, 1623 passed, 566 skipped, 1 xfailed, 248 warnings, 70 subtests passed in 6.28s ================================================

[lemon24]

Thank you for reporting this!

Is this a real failure, or just a flake?

Likely not flaky, but because of how the test checks VACUUM has run (possibly some wrong assumptions there).

If it's not a real failure, is there a way to make it pass more reliably?

Two ideas, for now, will try when I get some time:

• check difference in search.page_count rather than absolute value
  • update: or, just vacuum again and assert the page size does not change
• (if needed) add some data to the search db, to make sure there is a difference

Meanwhile, can you please paste the output of the following script? (not critical; trying to see what may have caused the difference)

import sqlite3

db = sqlite3.connect('')

def pq(sql):
    print('#', sql)
    print()
    for row in db.execute(sql):
        print(*row)
    print()

pq('select sqlite_version()')
pq('PRAGMA page_size')
pq('PRAGMA compile_options')

[maksverver]

# select sqlite_version()

3.46.1

# PRAGMA page_size

4096

# PRAGMA compile_options

ATOMIC_INTRINSICS=1
COMPILER=gcc-14.2.1 20240805
DEFAULT_AUTOVACUUM
DEFAULT_CACHE_SIZE=-2000
DEFAULT_FILE_FORMAT=4
DEFAULT_JOURNAL_SIZE_LIMIT=-1
DEFAULT_MMAP_SIZE=0
DEFAULT_PAGE_SIZE=4096
DEFAULT_PCACHE_INITSZ=20
DEFAULT_RECURSIVE_TRIGGERS
DEFAULT_SECTOR_SIZE=4096
DEFAULT_SYNCHRONOUS=2
DEFAULT_WAL_AUTOCHECKPOINT=1000
DEFAULT_WAL_SYNCHRONOUS=2
DEFAULT_WORKER_THREADS=0
DIRECT_OVERFLOW_READ
ENABLE_COLUMN_METADATA
ENABLE_DBSTAT_VTAB
ENABLE_FTS3
ENABLE_FTS3_PARENTHESIS
ENABLE_FTS3_TOKENIZER
ENABLE_FTS4
ENABLE_FTS5
ENABLE_MATH_FUNCTIONS
ENABLE_RTREE
ENABLE_STAT4
ENABLE_STMTVTAB
ENABLE_UNLOCK_NOTIFY
HAVE_ISNAN
MALLOC_SOFT_LIMIT=1024
MAX_ATTACHED=10
MAX_COLUMN=2000
MAX_COMPOUND_SELECT=500
MAX_DEFAULT_PAGE_SIZE=8192
MAX_EXPR_DEPTH=10000
MAX_FUNCTION_ARG=127
MAX_LENGTH=1000000000
MAX_LIKE_PATTERN_LENGTH=50000
MAX_MMAP_SIZE=0x7fff0000
MAX_PAGE_COUNT=0xfffffffe
MAX_PAGE_SIZE=65536
MAX_SQL_LENGTH=1000000000
MAX_TRIGGER_DEPTH=1000
MAX_VARIABLE_NUMBER=250000
MAX_VDBE_OP=250000000
MAX_WORKER_THREADS=8
MUTEX_PTHREADS
SECURE_DELETE
SYSTEM_MALLOC
TEMP_STORE=1
THREADSAFE=1

[maksverver]

Looks like the reason for the size increase are the internal table sqlite_stat1 and sqlite_stat4 that are created when running ANALYZE:

sqlite3 <<EOF

CREATE TABLE t(id);
PRAGMA page_count;  -- 2

DROP TABLE t;
VACUUM;
PRAGMA page_count;  -- 1

EOF

sqlite3 <<EOF

CREATE TABLE t(id);
ANALYZE;
PRAGMA page_count;  -- 4

DROP TABLE t;
VACUUM;
PRAGMA page_count;  -- 3

SELECT name FROM sqlite_master;  -- sqlite_stat1, sqlite_stat4
DROP TABLE sqlite_stat1;
DROP TABLE sqlite_stat4;
VACUUM;
PRAGMA page_count;  -- 1

EOF

(ANALYZE might be triggered indirectly by PRAGMA OPTIMIZE.)

So a solution might be dropping all tables from sqlite_stat1, sqlite_stat2, sqlite_stat3, and sqlite_stat4 if they exist (which are generated depends on how sqlite3 was compiled.)

[lemon24]

So a solution might be dropping all tables from sqlite_stat1, ...

Indeed: https://sqlite.org/forum/forumpost/5414ca8f89389f8a

Thank you for looking into this!

[lemon24]

Thank you for the PR and the research!