search

IntersectMBO / cardano-db-sync

A component that follows the Cardano chain and stores blocks and transactions in PostgreSQL
Apache License 2.0
292 stars 161 forks source link

Unique constraint violation on `unique_pool_metadata_ref` #1783

Closed jhbertra closed 3 months ago

jhbertra commented 4 months ago

OS Your OS: NixOS

Versions The db-sync version (eg cardano-db-sync --version): 13.2.0.2 PostgreSQL version: 15.7

Build/Install Method The method you use to build or install cardano-db-sync: Nix flakes

Run method The method you used to run cardano-db-sync (eg Nix/Docker/systemd/none): process-compose (from cardano-parts)

Additional context Add any other context about the problem here.

Problem Report Please do not include screenshots or images, but instead cut and paste any relevant log messages or errors.

When syncing sanchonet, db-sync crashes with a unique constraint violation:

[db-sync-node:Error:74] [2024-07-22 20:06:26.87 UTC] runDBThread: DbInsertException "PoolMetadataRef" (SqlError {sqlState = "23505", sqlExecStatus = FatalError, sqlErrorMsg = "duplicate key value violates unique constraint \"unique_pool_metadata_ref\"", sqlErrorDetail = "Key (pool_id, url, hash)=(6, https://happystaking.io/poolMetadataSanchonet.json, \\x88f21480bcd6a5709c1fcd3c8604a18d1e0c40b17966043bf4339184bbcd24de) already exists.", sqlErrorHint = ""})
[db-sync-node:Error:103] [2024-07-22 20:06:26.87 UTC] recvMsgRollForward: AsyncCancelled
[db-sync-node:Error:98] [2024-07-22 20:06:26.87 UTC] ChainSyncWithBlocksPtcl: AsyncCancelled
[db-sync-node.Subscription:Error:94] [2024-07-22 20:06:26.87 UTC] Identity Application Exception: LocalAddress "./.run/sanchonet/cardano-node/node.socket" SubscriberError {seType = SubscriberWorkerCancelled, seMessage = "SubscriptionWorker exiting", seStack = []}
cardano-db-sync: DbInsertException "PoolMetadataRef" (SqlError {sqlState = "23505", sqlExecStatus = FatalError, sqlErrorMsg = "duplicate key value violates unique constraint \"unique_pool_metadata_ref\"", sqlErrorDetail = "Key (pool_id, url, hash)=(6, https://happystaking.io/poolMetadataSanchonet.json, \\x88f21480bcd6a5709c1fcd3c8604a18d1e0c40b17966043bf4339184bbcd24de) already exists.", sqlErrorHint = ""})
jhbertra commented 4 months ago

Full logs dbsync.log

Cmdv commented 3 months ago

Hi @jhbertra, just had a look into this and it seems that the schema/code was updated in version 13.3.0 where this constraints were removed here. Is it possible for you to upgrade to 13.3.0?

ArturWieczorek commented 3 months ago

Closing as this one was fixed in 13.3.0.0 and similar issue with off_chain_pool_data table was fixed in 13.4.0.0.

pool_metadata_ref with several entries with the same pool_id, url, hash:

mainnet_13_4_0_0_from_scratch=# select * from pool_metadata_ref where pool_id=652;
  id   | pool_id |                             url                             |                                hash                                | registered_tx_id 
-------+---------+-------------------------------------------------------------+--------------------------------------------------------------------+------------------
  1213 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xa5f93062c597f682f09ce4114c694a64c9f75679cfcb17f43080c90a4f698a75 |          2460715
  1311 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xa5f93062c597f682f09ce4114c694a64c9f75679cfcb17f43080c90a4f698a75 |          2463508
  1876 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xc60118472aa470cd765df02edfb6eff25e19294535d3059080c69ecfe5ff09fc |          2481221
  2265 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xc60118472aa470cd765df02edfb6eff25e19294535d3059080c69ecfe5ff09fc |          2505378
  3315 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xc60118472aa470cd765df02edfb6eff25e19294535d3059080c69ecfe5ff09fc |          2571057
  4314 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \xc60118472aa470cd765df02edfb6eff25e19294535d3059080c69ecfe5ff09fc |          2657619
  4315 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          2657694
  8303 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          3201156
 10423 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          3953745
 10424 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          3953766
 10425 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          3954193
 10581 |     652 | https://chadle-git.github.io/Shelley/poolMetaData.json      | \x65d2f5e6668e51ee93617b1ef72c94e7e8b54b71fda3a3cb7ed63f87d983bd0d |          4074135
 22213 |     652 | https://chadle-git.github.io/Shelley/poolMetaDataBBHMM.json | \x8f03d0e5a1b1f345dbcdda1419936fc753ccd808a79889766f185024774e8d43 |         13242344
 22214 |     652 | https://chadle-git.github.io/Shelley/poolMetaDataBBHMM.json | \x8f03d0e5a1b1f345dbcdda1419936fc753ccd808a79889766f185024774e8d43 |         13242709
 25791 |     652 | https://pool.bbhmm.net/poolmeta.json                        | \xd0728e53f293bb080f3cccd4762c9d20c3ddf708c11709de8d2670ff3dc548e1 |         30336727
 27342 |     652 | https://pool.bbhmm.net/meta.json                            | \x37e5059bbf2798be79328086c34ad0b9940e5511cc3502fcc1d61ffa150f55dd |         40102347
 28514 |     652 | https://pool.bbhmm.net/poolmeta.json                        | \xd0728e53f293bb080f3cccd4762c9d20c3ddf708c11709de8d2670ff3dc548e1 |         49134207
 28515 |     652 | https://pool.bbhmm.net/poolmeta.json                        | \xd0728e53f293bb080f3cccd4762c9d20c3ddf708c11709de8d2670ff3dc548e1 |         49134836
 28516 |     652 | https://pool.bbhmm.net/poolmeta.json                        | \x37e5059bbf2798be79328086c34ad0b9940e5511cc3502fcc1d61ffa150f55dd |         49135745
 28517 |     652 | https://pool.bbhmm.net/poolmeta.json                        | \x37e5059bbf2798be79328086c34ad0b9940e5511cc3502fcc1d61ffa150f55dd |         49137064
(20 rows)