search

conda-incubator / conda-store

Data science environments, for collaboration. ✨
https://conda.store
BSD 3-Clause "New" or "Revised" License
149 stars 50 forks source link

Update conda-package-build causes an integrity error on conda_package_build table #961

Closed soapy1 closed 2 days ago

soapy1 commented 2 weeks ago

related to https://github.com/conda-incubator/conda-store/issues/852

Description

Previously, whenever the packages for a channel would get updated, duplicated packages across channels would cause an sql error.

conda-store-worker-1  | (Background on this error at: https://sqlalche.me/e/14/gkpj)
conda-store-worker-1  | [2024-11-07 05:27:06,310: ERROR/ForkPoolWorker-5] Task task_update_conda_channel[73d1f6c4-e39e-4d77-a73d-0a61c5824648] raised unexpected: IntegrityError('(psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "_conda_package_build_uc"\nDETAIL:  Key (channel_id, package_id, subdir, build, build_number, sha256)=(3, 5618, linux-64, py27_0, 0, d65b4ca2fa3e2e9f6dc84b4883255c395a381bb11a3009c2f6fefa24aa284db8) already exists.\n')
conda-store-worker-1  | Traceback (most recent call last):
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1890, in _execute_context
conda-store-worker-1  |     self.dialect.do_executemany(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 982, in do_executemany
conda-store-worker-1  |     context._psycopg2_fetched_rows = xtras.execute_values(
conda-store-worker-1  |                                      ^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/psycopg2/extras.py", line 1299, in execute_values
conda-store-worker-1  |     cur.execute(b''.join(parts))
conda-store-worker-1  | psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "_conda_package_build_uc"
conda-store-worker-1  | DETAIL:  Key (channel_id, package_id, subdir, build, build_number, sha256)=(3, 5618, linux-64, py27_0, 0, d65b4ca2fa3e2e9f6dc84b4883255c395a381bb11a3009c2f6fefa24aa284db8) already exists.
conda-store-worker-1  | 
conda-store-worker-1  | 
conda-store-worker-1  | The above exception was the direct cause of the following exception:
conda-store-worker-1  | 
conda-store-worker-1  | Traceback (most recent call last):
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/celery/app/trace.py", line 453, in trace_task
conda-store-worker-1  |     R = retval = fun(*args, **kwargs)
conda-store-worker-1  |                  ^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/celery/app/trace.py", line 736, in __protected_call__
conda-store-worker-1  |     return self.run(*args, **kwargs)
conda-store-worker-1  |            ^^^^^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda-store-server/conda_store_server/_internal/worker/tasks.py", line 191, in task_update_conda_channel
conda-store-worker-1  |     channel.update_packages(db, subdirs=settings.conda_platforms)
conda-store-worker-1  |   File "/opt/conda-store-server/conda_store_server/_internal/orm.py", line 685, in update_packages
conda-store-worker-1  |     raise e
conda-store-worker-1  |   File "/opt/conda-store-server/conda_store_server/_internal/orm.py", line 679, in update_packages
conda-store-worker-1  |     db.bulk_insert_mappings(CondaPackageBuild, flatten)
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3810, in bulk_insert_mappings
conda-store-worker-1  |     self._bulk_save_mappings(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3912, in _bulk_save_mappings
conda-store-worker-1  |     with util.safe_reraise():
conda-store-worker-1  |          ^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 70, in __exit__
conda-store-worker-1  |     compat.raise_(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
conda-store-worker-1  |     raise exception
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3901, in _bulk_save_mappings
conda-store-worker-1  |     persistence._bulk_insert(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 107, in _bulk_insert
conda-store-worker-1  |     _emit_insert_statements(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1097, in _emit_insert_statements
conda-store-worker-1  |     c = connection._execute_20(
conda-store-worker-1  |         ^^^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
conda-store-worker-1  |     return meth(self, args_10style, kwargs_10style, execution_options)
conda-store-worker-1  |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
conda-store-worker-1  |     return connection._execute_clauseelement(
conda-store-worker-1  |            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
conda-store-worker-1  |     ret = self._execute_context(
conda-store-worker-1  |           ^^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
conda-store-worker-1  |     self._handle_dbapi_exception(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in _handle_dbapi_exception
conda-store-worker-1  |     util.raise_(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
conda-store-worker-1  |     raise exception
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1890, in _execute_context
conda-store-worker-1  |     self.dialect.do_executemany(
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/sqlalchemy/dialects/postgresql/psycopg2.py", line 982, in do_executemany
conda-store-worker-1  |     context._psycopg2_fetched_rows = xtras.execute_values(
conda-store-worker-1  |                                      ^^^^^^^^^^^^^^^^^^^^^
conda-store-worker-1  |   File "/opt/conda/envs/conda-store-server/lib/python3.12/site-packages/psycopg2/extras.py", line 1299, in execute_values
conda-store-worker-1  |     cur.execute(b''.join(parts))
conda-store-worker-1  | sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "_conda_package_build_uc"
conda-store-worker-1  | DETAIL:  Key (channel_id, package_id, subdir, build, build_number, sha256)=(3, 5618, linux-64, py27_0, 0, d65b4ca2fa3e2e9f6dc84b4883255c395a381bb11a3009c2f6fefa24aa284db8) already exists.
conda-store-worker-1  | 
conda-store-worker-1  | [SQL: INSERT INTO conda_package_build (package_id, channel_id, build, build_number, constrains, depends, md5, sha256, size, subdir, timestamp) VALUES (%(package_id)s, %(channel_id)s, %(build)s, %(build_number)s, %(constrains)s, %(depends)s, %(md5)s, %(sha256)s, %(size)s, %(subdir)s, %(timestamp)s)]

The root of the issue was that conda_package_builds were not getting the correct package/channel reference. For example, see the state of the db after a package update was run. In particular, the package_id with different channel_id's. This is incorrect, the channel_id should match the package's channel id. So, for the package_id 5618, that should be channel_id 1.

conda-store=# select * from conda_package where name='hyperlink' and version='18.0.0'
conda-store-# ;
  id  | channel_id | license | license_family |   name    | version |              summary               | description 
------+------------+---------+----------------+-----------+---------+------------------------------------+-------------
 9558 |          3 | MIT     | MIT            | hyperlink | 18.0.0  | Immutable, Pythonic, correct URLs. | 
 5618 |          1 | MIT     | MIT            | hyperlink | 18.0.0  | Immutable, Pythonic, correct URLs. | 
(2 rows)

conda-store=# select package_id, channel_id, id, build from conda_package_build where package_id = 9558 or package_id = 5618
conda-store-# ;
 package_id | channel_id |  id   | build  
------------+------------+-------+--------
       5618 |          1 | 29288 | py27_0
       5618 |          3 | 26873 | py27_0
       5618 |          3 | 26874 | py35_0
       5618 |          3 | 26875 | py36_0
       5618 |          3 | 26876 | py37_0
       5618 |          1 | 29289 | py35_0
       5618 |          1 | 29290 | py36_0
       5618 |          1 | 29291 | py37_0
(8 rows)

Pull request checklist

netlify[bot] commented 2 weeks ago

Deploy Preview for conda-store canceled.

Name Link
Latest commit 6599c7efc13229ffe5b105d19a1858f7c04ee9a7
Latest deploy log https://app.netlify.com/sites/conda-store/deploys/672c0ea7d267dd0008770e36
netlify[bot] commented 2 weeks ago

Deploy Preview for conda-store ready!

Name Link
Latest commit 2bf6f1004d3fa95de570afcfe017b35f7bb439c6
Latest deploy log https://app.netlify.com/sites/conda-store/deploys/673bb7a8f28a380008914149
Deploy Preview https://deploy-preview-961--conda-store.netlify.app
Preview on mobile
Toggle QR Code...

QR Code

Use your smartphone camera to open QR code link.

To edit notification comments on pull requests, go to your Netlify site configuration.