[bug] sqlite3.OperationalError: no such column · Issue #16115 · conan-io/conan

link管理
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
Describe the bug

After a pkg upgrade , sqlite3 was updated from 3.44 to 3.45 , conan queries started failing with: "sqlite3.OperationalError: no such column". It appears the value is interpreted as a column name.
conan-2.2.3 on FreeBSD-13.2 .
ERROR: Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/recipes_table.py", line 47, in create
    conn.execute(f'INSERT INTO {self.table_name} '
sqlite3.IntegrityError: UNIQUE constraint failed: recipes.reference, recipes.rrev
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/cache.py", line 230, in assign_rrev
    self._db.create_recipe(new_path_relative, ref)
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/cache_database.py", line 88, in create_recipe
    self._recipes.create(path, ref)
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/recipes_table.py", line 51, in create
    raise ConanReferenceAlreadyExistsInDB(f"Reference '{repr(ref)}' already exists")
conans.errors.ConanReferenceAlreadyExistsInDB: Reference 'package/0.1.10@gicc#598d105f3a11de9e2fd48cf12b7427ed%1713531631.4639823' already exists
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/conan/cli/cli.py", line 283, in main
    cli.run(args)
  File "/usr/local/lib/python3.9/site-packages/conan/cli/cli.py", line 193, in run
    command.run(self._conan_api, args[0][1:])
  File "/usr/local/lib/python3.9/site-packages/conan/cli/command.py", line 164, in run
    info = self._method(conan_api, parser, *args)
  File "/usr/local/lib/python3.9/site-packages/conan/cli/commands/create.py", line 46, in create
    ref, conanfile = conan_api.export.export(path=path,
  File "/usr/local/lib/python3.9/site-packages/conan/api/subapi/export.py", line 18, in export
    return cmd_export(app, self.conan_api.config.global_conf, path, name, version, user, channel,
  File "/usr/local/lib/python3.9/site-packages/conans/client/cmd/export.py", line 69, in cmd_export
    cache.assign_rrev(recipe_layout)
  File "/usr/local/lib/python3.9/site-packages/conans/client/cache/cache.py", line 48, in assign_rrev
    return self._data_cache.assign_rrev(layout)
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/cache.py", line 234, in assign_rrev
    self._db.update_recipe_timestamp(ref)
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/cache_database.py", line 34, in update_recipe_timestamp
    self._recipes.update_timestamp(ref)
  File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/recipes_table.py", line 61, in update_timestamp
    conn.execute(query)
sqlite3.OperationalError: no such column: 1713531631.4639823
Everything works again after downgrading to sqlite3-3.44
How to reproduce it
No response
          Hi @jputcu
Thanks for reporting.

This is quite surprising. The code that is failing there is:
    def update_timestamp(self, ref: RecipeReference):
        assert ref.revision is not None
        assert ref.timestamp is not None
        query = f"UPDATE {self.table_name} " \
                f'SET {self.columns.timestamp} = "{ref.timestamp}" ' \
                f'WHERE {self.columns.reference}="{str(ref)}" ' \
                f'AND {self.columns.rrev} = "{ref.revision}" '
        with self.db_connection() as conn:
            conn.execute(query)




    

This is pretty simple and it has been working at scale for long time. I am not sure what could have changed, I see the changelog for 3.45 in https://www.sqlite.org/releaselog/3_45_0.html doesn't contain anything that could be related to that.
It is true that the normal sqlite3 that is bundled with python, which I think is more common in other distros rather than FreeBSD is typically older than sqlite3-3.45.
Do you have other systems you could try it, not FreeBSD?
          I noticed a similar crash, on FreeBSD too : https://github.com/ericLemanissier/conan-center-index/actions/runs/8781462100/job/24093550784#step:3:49449
======== Computing dependency graph ========
  ERROR: Traceback (most recent call last):
    File "/usr/local/lib/python3.9/site-packages/conan/cli/cli.py", line 283, in main
      cli.run(args)
    File "/usr/local/lib/python3.9/site-packages/conan/cli/cli.py", line 193, in run
      command.run(self._conan_api, args[0][1:])
    File "/usr/local/lib/python3.9/site-packages/conan/cli/command.py", line 164, in run
      info = self._method(conan_api, parser, *args)
    File "/usr/local/lib/python3.9/site-packages/conan/cli/commands/create.py", line 72, in create
      deps_graph = conan_api.graph.load_graph_requires(requires, tool_requires,
    File "/usr/local/lib/python3.9/site-packages/conan/api/subapi/graph.py", line 131, in load_graph_requires
      deps_graph = self.load_graph(root_node, profile_host=profile_host,
    File "/usr/local/lib/python3.9/site-packages/conan/api/subapi/graph.py", line 180, in load_graph
      deps_graph = builder.load_graph(root_node, profile_host, profile_build, lockfile)
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/graph_builder.py", line 53, in load_graph
      new_node = self._expand_require(require, node, dep_graph, profile_host,
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/graph_builder.py", line 93, in _expand_require
      new_node = self._create_new_node(node, require, graph, profile_host, profile_build,
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/graph_builder.py", line 338, in _create_new_node
      resolved = self._resolve_recipe(require.ref, graph_lock)
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/graph_builder.py", line 236, in _resolve_recipe
      result = self._proxy.get_recipe(ref, self._remotes, self._update, self._check_update)
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/proxy.py", line 25, in get_recipe
      resolved = self._get_recipe(ref, remotes, update, check_update)
    File "/usr/local/lib/python3.9/site-packages/conans/client/graph/proxy.py", line 39, in _get_recipe
      recipe_layout = self._cache.recipe_layout(reference)
    File "/usr/local/lib/python3.9/site-packages/conans/client/cache/cache.py", line 58, in recipe_layout
      return self._data_cache.get_recipe_layout(ref)
    File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/cache.py", line 99, in get_recipe_layout
      ref_data = self._db.get_recipe(ref)
    File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/cache_database.py", line 73, in get_recipe
      return self._recipes.get_recipe(ref)
    File "/usr/local/lib/python3.9/site-packages/conan/internal/cache/db/recipes_table.py", line 101, in get_recipe
      r = conn.execute(query)
  sqlite3.OperationalError: no such column: openfst/1.8.2
  ERROR: no such column: openfst/1.8.2
sqlite3 is 3.45.1,1
according to https://www.sqlite.org/quirks.html#double_quoted_string_literals_are_accepted, it could be that sqlite was compiled without double-quoted string literals / DQS. I guess conan sould use single quote for string literals in https://github.com/conan-io/conan/blob/develop2/conan/internal/cache/db/recipes_table.py