Issue with SQlite == 3.49.1 and diskcache with conda-forge packages

[glemaitre]

With a freshly installed version using Python 3.12, I get the following type of error:

____________________________________________________________________________________________________________ test_disk_storage _____________________________________________________________________________________________________________
[gw7] darwin -- Python 3.11.11 /Users/glemaitre/Documents/scikit-learn-workspace/src/skore/.pixi/envs/dev/bin/python3.11

tmp_path = PosixPath('/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw7/test_disk_storage0')

    def test_disk_storage(tmp_path: Path):
        storage = DiskCacheStorage(tmp_path)
>       storage["key"] = "value"

storage    = DiskCacheStorage(directory='/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw7/test_disk_storage0')
tmp_path   = PosixPath('/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw7/test_disk_storage0')

unit/persistence/test_disk.py:10: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../src/skore/persistence/storage/disk_cache_storage.py:82: in __setitem__
    storage[key] = value
        key        = 'key'
        self       = DiskCacheStorage(directory='/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw7/test_disk_storage0')
        storage    = <diskcache.core.Cache object at 0x175589950>
        value      = 'value'
../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:823: in __setitem__
    self.set(key, value, retry=True)
        key        = 'key'
        self       = <diskcache.core.Cache object at 0x175589950>
        value      = 'value'
../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:808: in set
    self._row_insert(db_key, raw, now, columns)
        cleanup    = <built-in method append of list object at 0x175607a00>
        columns    = (None, None, 0, 1, None, 'value')
        db_key     = 'key'
        db_value   = 'value'
        expire     = None
        expire_time = None
        filename   = None
        key        = 'key'
        mode       = 1
        now        = 1740129515.347769
        raw        = True
        read       = False
        retry      = True
        rows       = []
        self       = <diskcache.core.Cache object at 0x175589950>
        size       = 0
        sql        = <built-in method execute of sqlite3.Connection object at 0x1754d3970>
        tag        = None
        value      = 'value'
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <diskcache.core.Cache object at 0x175589950>, key = 'key', raw = True, now = 1740129515.347769, columns = (None, None, 0, 1, None, 'value')

    def _row_insert(self, key, raw, now, columns):
        sql = self._sql
        expire_time, tag, size, mode, filename, value = columns
>       sql(
            'INSERT INTO Cache('
            ' key, raw, store_time, expire_time, access_time,'
            ' access_count, tag, size, mode, filename, value'
            ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            (
                key,
                raw,
                now,  # store_time
                expire_time,
                now,  # access_time
                0,  # access_count
                tag,
                size,
                mode,
                filename,
                value,
            ),
        )
E       sqlite3.OperationalError: no such column: "size" - should this be a string literal in single-quotes?

columns    = (None, None, 0, 1, None, 'value')
expire_time = None
filename   = None
key        = 'key'
mode       = 1
now        = 1740129515.347769
raw        = True
self       = <diskcache.core.Cache object at 0x175589950>
size       = 0
sql        = <built-in method execute of sqlite3.Connection object at 0x1754d3970>
tag        = None
value      = 'value'

../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:857: OperationalError
________________________________________________________________________________________________________________ test_clear ________________________________________________________________________________________________________________
[gw5] darwin -- Python 3.11.11 /Users/glemaitre/Documents/scikit-learn-workspace/src/skore/.pixi/envs/dev/bin/python3.11

tmp_path = PosixPath('/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw5/test_clear0')

    def test_clear(tmp_path):
        dirpath = tmp_path / "my-project.skore"
        project = Project(dirpath)
    
>       project.put("<key>", "<value>")

dirpath    = PosixPath('/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw5/test_clear0/my-project.skore')
project    = <skore.project.project.Project object at 0x16fca7390>
tmp_path   = PosixPath('/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw5/test_clear0')

unit/project/test_project.py:44: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
../src/skore/project/project.py:40: in wrapper
    return method(self, *args, **kwargs)
        args       = ('<key>', '<value>')
        kwargs     = {}
        method     = <function Project.put at 0x107413920>
        self       = <skore.project.project.Project object at 0x16fca7390>
../src/skore/project/project.py:192: in put
    self._item_repository.put_item(
        display_as = None
        key        = '<key>'
        note       = None
        self       = <skore.project.project.Project object at 0x16fca7390>
        value      = '<value>'
../src/skore/persistence/repository/item_repository.py:113: in put_item
    self.storage[key] = [_item]
        _item      = {'item': {'created_at': '2025-02-21T09:18:35.552777+00:00',
          'media': '<value>',
          'media_type': 'text/markdown',
          'note': None,
          'updated_at': '2025-02-21T09:18:35.552777+00:00'},
 'item_class_name': 'MediaItem'}
        item       = MediaItem(...)
        key        = '<key>'
        self       = <skore.persistence.repository.item_repository.ItemRepository object at 0x16fca5590>
../src/skore/persistence/storage/disk_cache_storage.py:82: in __setitem__
    storage[key] = value
        key        = '<key>'
        self       = DiskCacheStorage(directory='/private/var/folders/05/zp030dc14vzfb9435s3bz7fm0000gn/T/pytest-of-glemaitre/pytest-276/popen-gw5/test_clear0/my-project.skore/items')
        storage    = <diskcache.core.Cache object at 0x1782f6d90>
        value      = [{'item': {'created_at': '2025-02-21T09:18:35.552777+00:00',
           'media': '<value>',
           'media_type': 'text/markdown',
           'note': None,
           'updated_at': '2025-02-21T09:18:35.552777+00:00'},
  'item_class_name': 'MediaItem'}]
../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:823: in __setitem__
    self.set(key, value, retry=True)
        key        = '<key>'
        self       = <diskcache.core.Cache object at 0x1782f6d90>
        value      = [{'item': {'created_at': '2025-02-21T09:18:35.552777+00:00',
           'media': '<value>',
           'media_type': 'text/markdown',
           'note': None,
           'updated_at': '2025-02-21T09:18:35.552777+00:00'},
  'item_class_name': 'MediaItem'}]
../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:808: in set
    self._row_insert(db_key, raw, now, columns)
        cleanup    = <built-in method append of list object at 0x16fc64e80>
        columns    = (None, None, 0, 4, None, <memory at 0x16f925a80>)
        db_key     = '<key>'
        db_value   = <memory at 0x16f925a80>
        expire     = None
        expire_time = None
        filename   = None
        key        = '<key>'
        mode       = 4
        now        = 1740129515.560126
        raw        = True
        read       = False
        retry      = True
        rows       = []
        self       = <diskcache.core.Cache object at 0x1782f6d90>
        size       = 0
        sql        = <built-in method execute of sqlite3.Connection object at 0x16f87f3d0>
        tag        = None
        value      = [{'item': {'created_at': '2025-02-21T09:18:35.552777+00:00',
           'media': '<value>',
           'media_type': 'text/markdown',
           'note': None,
           'updated_at': '2025-02-21T09:18:35.552777+00:00'},
  'item_class_name': 'MediaItem'}]
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 

self = <diskcache.core.Cache object at 0x1782f6d90>, key = '<key>', raw = True, now = 1740129515.560126, columns = (None, None, 0, 4, None, <memory at 0x16f925a80>)

    def _row_insert(self, key, raw, now, columns):
        sql = self._sql
        expire_time, tag, size, mode, filename, value = columns
>       sql(
            'INSERT INTO Cache('
            ' key, raw, store_time, expire_time, access_time,'
            ' access_count, tag, size, mode, filename, value'
            ') VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
            (
                key,
                raw,
                now,  # store_time
                expire_time,
                now,  # access_time
                0,  # access_count
                tag,
                size,
                mode,
                filename,
                value,
            ),
        )
E       sqlite3.OperationalError: no such column: "size" - should this be a string literal in single-quotes?

columns    = (None, None, 0, 4, None, <memory at 0x16f925a80>)
expire_time = None
filename   = None
key        = '<key>'
mode       = 4
now        = 1740129515.560126
raw        = True
self       = <diskcache.core.Cache object at 0x1782f6d90>
size       = 0
sql        = <built-in method execute of sqlite3.Connection object at 0x16f87f3d0>
tag        = None
value      = <memory at 0x16f925a80>

../../../.pixi/envs/dev/lib/python3.11/site-packages/diskcache/core.py:857: OperationalError

The relevant issue is: conda-forge/sqlite-feedstock#130
While the SQlite package could resolve the issue, the python-diskcache could also do something: grantjenks/python-diskcache#311

However, since of the current low maintenance, it could be problematic.

[thomass-dev]

Is the issue only impacting conda users? We can temporarily pin the version of sqlite in our dependencies.

[glemaitre]

I think so because we don't have the issue on our CI that installed everything from pip. So it is really related to the sqlite provided by conda-forge.

[glemaitre]

We can temporarily pin the version of sqlite in our dependencies.

You can do that with conda but with pip I'm not sure how sqlite is installed because it is not a Python package. So I don't think that you really have the hand on this.

[thomass-dev]

We are not actually affected in the CI because we use the ubuntu-latest runner. It is based on Ubuntu 24.04 The Noble Numbat which bind sqlite3 3.45.1.

https://github.com/actions/runner-images/blob/main/images/ubuntu/Ubuntu2404-Readme.md
https://launchpad.net/ubuntu/+source/sqlite3

The latest version of SQLite is not actually available on the standard apt repositories.
I don't think lambda users will install another version of sqlite by hand.
It's not the best solution, but without hurrying too much, i think we can pin the sqlite version for the conda users, we'll soon be changing backend.

[glemaitre]

Yep we should modify the recipe.

[rouk1]

see conda-forge/skore-feedstock#16