Litestream-restored db: errors on 3.11 and 3.10.8; but works on py3.10.7 and 3.10.6

[justmars]

Hi! Wondering if this issue is limited to my local system or if it affects others as well.

It seems like 3.11 errors out on a "litestream-restored" database. On further investigation, it also appears to conk out on 3.10.8 but works on 3.10.7 and 3.10.6.

To demo issue I created a test database, replicated it to an aws s3 bucket, then restored the same under various .pyenv-versioned shells where I test whether I can read the database via the sqlite3 cli.

# create new shell with 3.11.3
litestream restore -o data/db.sqlite s3://mytestbucketxx/db
sqlite3 data/db.sqlite  
# SQLite version 3.41.2 2023-03-22 11:56:21
# Enter ".help" for usage hints.
# sqlite> .tables
# _litestream_lock  _litestream_seq   movie           
# sqlite> 

However this get me an OperationalError when reading via datasette:

Error on 3.11.3 and 3.10.8

datasette data/db.sqlite

/tester/.venv/lib/python3.11/site-packages/pkg_resources/__init__.py:121: DeprecationWarning: pkg_resources is deprecated as an API
  warnings.warn("pkg_resources is deprecated as an API", DeprecationWarning)
Traceback (most recent call last):
  File "/tester/.venv/bin/datasette", line 8, in <module>
    sys.exit(cli())
             ^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/datasette/cli.py", line 143, in wrapped
    return fn(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/datasette/cli.py", line 615, in serve
    asyncio.get_event_loop().run_until_complete(check_databases(ds))
  File "/Users/mv/.pyenv/versions/3.11.3/lib/python3.11/asyncio/base_events.py", line 653, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/datasette/cli.py", line 660, in check_databases
    await database.execute_fn(check_connection)
  File "/tester/.venv/lib/python3.11/site-packages/datasette/database.py", line 213, in execute_fn
    return await asyncio.get_event_loop().run_in_executor(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/mv/.pyenv/versions/3.11.3/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/datasette/database.py", line 211, in in_thread
    return fn(conn)
           ^^^^^^^^
  File "/tester/.venv/lib/python3.11/site-packages/datasette/utils/__init__.py", line 951, in check_connection
    for r in conn.execute(
             ^^^^^^^^^^^^^
sqlite3.OperationalError: unable to open database file

Works on 3.10.7, 3.10.6

# create new shell with 3.10.7 / 3.10.6
litestream restore -o data/db.sqlite s3://mytestbucketxx/db
datasette data/db.sqlite
# ...
# INFO:     Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit)

In both scenarios, the only dependencies were the pinned python version and the latest Datasette version 0.64.

[justmars]

Curiously, after running commands on the database that was litestream-restored, datasette starts to work again, e.g.

litestream restore -o data/db.sqlite s3://mytestbucketxx/db
datasette data/db.sqlite
# fails (OperationalError described above)

litestream restore -o data/db.sqlite s3://mytestbucketxx/db
sqlite-utils enable-wal data/db.sqlite
datasette data/db.sqlite
# works

litestream restore -o data/db.sqlite s3://mytestbucketxx/db
sqlite-utils optimize data/db.sqlite
datasette data/db.sqlite
# works

litestream restore -o data/db.sqlite s3://mytestbucketxx/db
sqlite3 data/db.sqlite ".clone test.db"
datasette test.db
# works