Sqlite cache issues with network file system and/or concurrent conda_index processes

[meesepyter]

Checklist

• I added a descriptive title
• I searched open reports and couldn't find a duplicate

What happened?

We are storing an in-house conda channel on a NFS drive. After a recent upgrade from conda-build 3.27.0 to conda-index 0.5.0 (used conda index before, now python -m conda-index), we are facing problems with "database is locked" or "file is not a database" error messages from the sqlite3 module.

conda-index/index/common.py uses

conn.execute("PRAGMA journal_mode = WAL")
conn.execute("PRAGMA synchronous = 1")

The sqlite docs list as first disadvantage "All processes using a database must be on the same host computer; WAL does not work over a network filesystem", which is both not satisfied in our scenario.

Can the journal mode be changed to SQLite's default DELETE (not sure about PRAGMA synchronous ) to allow NFS storage of the channel (and concurrent conda index from different client machines)?

Conda Info

No response

Conda Config

No response

Conda list

No response

Additional Context

No response

[dholth]

Sounds feasible. We may have to check that it doesn't interfere with local multiprocess indxing.

[jtroe]

Any update on this? I'm having to use the old conda index command because of this.

Background: Using on a docker container, volume mapped from a network drive:

concurrent.futures.process._RemoteTraceback: 

"""

Traceback (most recent call last):

  File "/opt/conda/lib/python3.12/concurrent/futures/process.py", line 263, in _process_worker

    r = call_item.fn(*call_item.args, **call_item.kwargs)

        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/process.py", line 212, in _process_chunk

    return [fn(*args) for args in chunk]

            ^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/sqlitecache.py", line 201, in extract_to_cache_info_object

    return self._extract_to_cache(

           ^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/sqlitecache.py", line 224, in _extract_to_cache

    index_json = self.extract_to_cache_unconditional(fn, abs_fn, size, mtime)

                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/sqlitecache.py", line 312, in extract_to_cache_unconditional

    with self.db:

         ^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/sqlitecache.py", line 81, in __get__

    value = self.wrapped(inst)

            ^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/sqlitecache.py", line 136, in db

    conn = common.connect(str(self.db_filename))

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/common.py", line 12, in connect

    conn.execute("PRAGMA journal_mode = WAL")

sqlite3.OperationalError: locking protocol

"""



The above exception was the direct cause of the following exception:



Traceback (most recent call last):

  File "<frozen runpy>", line 198, in _run_module_as_main

  File "<frozen runpy>", line 88, in _run_code

  File "/opt/conda/lib/python3.12/site-packages/conda_index/__main__.py", line 3, in <module>

    conda_index.cli.cli()

  File "/opt/conda/lib/python3.12/site-packages/click/core.py", line 1157, in __call__

    return self.main(*args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/click/core.py", line 1078, in main

    rv = self.invoke(ctx)

         ^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/click/core.py", line 1434, in invoke

    return ctx.invoke(self.callback, **ctx.params)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/click/core.py", line 783, in invoke

    return __callback(*args, **kwargs)

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/cli/__init__.py", line 157, in cli

    channel_index.index(

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/__init__.py", line 586, in index

    futures = [

              ^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 619, in result_iterator

    yield _result_or_cancel(fs.pop())

          ^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 317, in _result_or_cancel

    return fut.result(timeout)

           ^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 456, in result

    return self.__get_result()

           ^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result

    raise self._exception

  File "/opt/conda/lib/python3.12/concurrent/futures/thread.py", line 58, in run

    result = self.fn(*self.args, **self.kwargs)

             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/__init__.py", line 574, in extract_wrapper

    return self.extract_subdir_to_cache(

           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/site-packages/conda_index/index/__init__.py", line 812, in extract_subdir_to_cache

    for fn, mtime, size, index_json in thread_executor.map(

  File "/opt/conda/lib/python3.12/concurrent/futures/process.py", line 642, in _chain_from_iterable_of_lists

    for element in iterable:

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 619, in result_iterator

    yield _result_or_cancel(fs.pop())

          ^^^^^^^^^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 317, in _result_or_cancel

    return fut.result(timeout)

           ^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 456, in result

    return self.__get_result()

           ^^^^^^^^^^^^^^^^^^^

  File "/opt/conda/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result

    raise self._exception

sqlite3.OperationalError: locking protocol

[dholth]

We would accept a PR that removes the "enable WAL" pragma

Would you have any use for a postgresql database instead of sqlite?