search

smartcontractkit / chainlink

node of the decentralized oracle network, bridging on and off-chain computation
https://chain.link
Other
7.02k stars 1.71k forks source link

kaiko OCR - foreign key constraint violation deleting a chain without nodes from UI #7400

Open SLoeuillet opened 2 years ago

SLoeuillet commented 2 years ago

Description Had a Avalanche EVM ChainID 41113 registered with 5 nodes attached to it. Was an error, so I did change EVM ChainId to 41114 with the very same 5 nodes. Both times, chains/nodes provisionned from EVM_NODES env variable.

Deleting the bad 41113 chain from interface (which has no more nodes associated to it, it is give an exception and didn't succeed in removing the chain :

ERROR: update or delete on table "evm_chains" violates foreign key constraint "eth_key_states_evm_chain_id_fkey" on table "evm_key_states" (SQLSTATE 23503)

Basic Information [replace this line with basic information about the issue you are experiencing, including but not limited to all relevant logs and any other relevant information, such as if you are using a Docker container to run the node, job specification, oracle contract address, transaction IDs, etc.]

This happens if the chain is enabled or disabled, same error trying to remove it

Sep 12 07:52:43 offchainreporting00-chainlink-ovh docker[1741695]: 2022-09-12T07:52:43.264Z [INFO]  DELETE /v2/chains/evm/43113                        web/router.go:533                clientIP=10.170.0.38 errors=Error #01: ERROR: update or delete on table "evm_chains" violates foreign key constraint "eth_key_states_evm_chain_id_fkey" on table "evm_key_states" (SQLSTATE 23503)
Sep 12 07:58:40 offchainreporting00-chainlink-ovh docker[1741695]: 2022-09-12T07:58:40.070Z [INFO]  DELETE /v2/chains/evm/43113                        web/router.go:533                clientIP=10.170.0.38 errors=Error #01: ERROR: update or delete on table "evm_chains" violates foreign key constraint "eth_key_states_evm_chain_id_fkey" on table "evm_key_states" (SQLSTATE 23503)
Sep 12 08:04:17 offchainreporting00-chainlink-ovh docker[1741695]: 2022-09-12T08:04:17.748Z [INFO]  DELETE /v2/chains/evm/43113                        web/router.go:533                clientIP=10.170.0.38 errors=Error #01: ERROR: update or delete on table "evm_chains" violates foreign key constraint "eth_key_states_evm_chain_id_fkey" on table "evm_key_states" (SQLSTATE 23503)
SLoeuillet commented 2 years ago

using official chainlink v1.8.0

SLoeuillet commented 2 years ago

44113 is still present in the following tables : evm_chains evm_key_states

Also, in the interface, under EVM Chain Accounts, I have: error getting EVM Chain: chain not found with id 43113

SLoeuillet commented 2 years ago

Capture d’écran du 2022-09-12 10-20-22

SLoeuillet commented 2 years ago

Capture d’écran du 2022-09-12 10-21-07

SLoeuillet commented 2 years ago

To fix it localy, did the following :

DELETE FROM evm_key_states WHERE evm_chain_id=43113 DELETE FROM evm_chains WHERE id=43113;

Then restarting chainlink node

rgottleber commented 2 years ago

Thanks for opening this issue and providing all the details. We will take a look.

braddmiller commented 2 years ago

@SLoeuillet thanks for the report, this should have been fixed in a release that went out a few weeks ago. Can you confirm that this is no longer an issue?

SLoeuillet commented 2 years ago

I did update to v1.9.0 a few days ago but did a clean-up on DB side before so no way to test, sorry.

SLoeuillet commented 2 years ago

I see a few constraints around public.evm_chains changed between 1.8.1 & 1.9.0(DEFFERABLE => ON DELETE CASCADE DEFERRABLE NOT VALID)

But I see no change in DB schema about evm_key_states so I'm not sure the fix is full.