[bug] SQL error during conan download

[lhocmf]

Environment details

• Operating System+version: Windows Server 2019, Windows 10
• Compiler+version: VS 2022
• Conan version: 2.0.9
• Python version: 3.11

Steps to reproduce

1. Start with a clean cache
2. Add a remote (e.g. conancenter). (No login necessary, the error happens before any request is sent to any remote.)
3. Try to download a recipe (conan download --remote conancenter cmake/3.27.1#7d5f14447c023feef9f59640be53619a)

This also happens when using a package list on the conan download command. It seems a SQL error is being misreported in the following snippet:

conan/conan/internal/cache/db/recipes_table.py

Lines 40 to 46 in 67dbbfd

	with self.db_connection() as conn:
	try:
	conn.execute(f'INSERT INTO {self.table_name} '
	f'VALUES ({placeholders})',
	[str(ref), ref.revision, path, ref.timestamp])
	except sqlite3.IntegrityError:
	raise ConanReferenceAlreadyExistsInDB(f"Reference '{repr(ref)}' already exists")

Logs

Downloading recipe 'cmake/3.27.1#7d5f14447c023feef9f59640be53619a'
ERROR: Reference 'cmake/3.27.1#7d5f14447c023feef9f59640be53619a' already exists

[memsharded]

Hi @lhocmf

Thanks for your report.
I am trying to reproduce without success. Are you sure you completely removed the whole cache, including the embedded DB?

This looks weird, not sure what could be happening. Can you please try adding the following in the Conan code and running it again?

except sqlite3.IntegrityError as e: 
    raise ConanReferenceAlreadyExistsInDB(f"Reference '{repr(ref)}' already exists: {e}") 

[lhocmf]

Hi @memsharded

Capturing the error message gives the following internal error: NOT NULL constraint failed: recipes.timestamp

However, it seems I cannot reproduce the error using the steps given before; I was sure they produced the same error yesterday. I thought I reduced it to "conan download $RECIPE fails, conan download $PACKAGE succeeds", but apparently I cannot reproduce that anymore. Currently used reproduction steps (possibly not minimal):

1. Create a package a: conan install --requires a/1.0.0 --format json >graph.json
2. conan list --graph=install.json --format json >installed.json
3. conan upload --list installed.json --remote $REMOTE --format json --confirm >upload.json
4. Switch to clean CONAN_HOME
5. Copy some global.conf into CONAN_HOME, setting core.download:download_cache, core.sources:download_cache and core:non_interactive = True
6. Add remote $REMOTE
7. conan download --list upload.json --format json >download.json

Not quite sure if step 5 matters.

[memsharded]

Thanks very much for the details! I have managed to reproduce with it.
It is an unfortunate coincidence of the multiple steps involved, using PackageLists and the above flow.

I am already working on a fix for next release.

[memsharded]

#14526 has fixed this (you can try now from the release/2.0 branch running from source if you want), it will be in next 2.0.10 release