Using snapshots doesn't work

[YuXiaoCoder]

I downloaded the May 8th snapshot from DeWi ETL and it imported successfully after 30 hours, I thought the software version and SQL were a one-to-one match, so I ran /opt /etl/bin/blockchain_etl migrations run to migrate, but the program is stuck in the application /opt/etlmigrations/1650926462-gateway_last_block.sql, according to the timestamp, this SQL should be April 26, and should not be executed again.

I observed it through /opt/etl/bin/blockchain_etl migrations list command:

By looking at the SQL content, I found that it takes 40 minutes to execute SELECT MAX(block) FROM transaction_actors, gateway_inventory where actor = gateway_inventory.address; once, and it needs to be executed 717120 times, which is too long to wait, so I I tried to start the service directly, but the service startup reported an error, the log is as follows:

bash-5.1# tail -10 console.log
2022-05-20 16:07:07.980 [info] <0.16377.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"13WTQri9Xmo6gFDkyM8RhFRAkHjeUuNPzkTewWoYMhx3HXbsfjt">>: {error,{badmatch,{error,not_found_found}} }
2022-05-20 16:07:07.980 [info] <0.16380.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"11P4QwHEAKSAgiKv9Npx7GXQQfUyiCf8hvdbYcRXKvmuqCEoAph">>: {error,{badmatch,{error,not_found}} }
2022-05-20 16:07:07.980 [info] <0.16382.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"13nyDBFaQWLVeUk6r8DeemD8js41Go4Gw3gq7enoMgKo3rqmofV">>: {error,{badmatch,{error,not_found}} }
2022-05-20 16:07:07.980 [info] <0.16384.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"14fQ1qk1g4HzFxagbuMYTVqFenbHNsuv7X8di3ydaiUWBHCtdPf">>: {error,{badmatch,{error,not_found}} }
2022-05-20 16:07:07.980 [info] <0.16379.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"112LdoNERWN3CU1cT77YTjKTkcdWySxQV7WkZEPf8hDJwngJsuvc">>: {error,{badmatch,{error,not_found_found}} }
2022-05-20 16:07:07.980 [info] <0.16378.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"11R99YxsiitTp6TnJYGLk4i5eRZ7Luz3SXvHjYePG2akhnzXJxZ">>: {error,{badmatch,{error,not_found_found_}} }
2022-05-20 16:07:07.980 [info] <0.16381.12>@be_db_validator_status:request_status:211 Failed to update validator status for <<"13KWJo4G2kgEjrCnsqJoYEdsMhKMr4JkzQQjtz5SjhqcVLz2B5j">>}: {error,{bad_match,{error,found_match,{error,found] }
2022-05-20 16:07:08.251 [info] <0.1574.0>@be_db_pending_txn:handle_info:136 Submitting 0 pending transactions

bash-5.1# tail -10 error.log
2022-05-20 15:48:10.441 [error] <0.22909.6>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/11377seDMjMAcfRhWwv51QJEbx4kphYCnGqTbjY6Q92MBriRvHL" not_found
2022-05-20 15:48:30.486 [error] <0.26109.6>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/112c14Sz2TgDZf6EiYCTHy8Nt6uwAzZAyo2Epzg9o3NWVH7AG7wU" not_found
2022-05-20 15:52:50.180 [error] <0.9708.8>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/11oinwAwfA5t9GBeWDGhsz8c67k5Sf43YzhzqrTqZWiBwobY5MM" not_found
2022-05-20 15:53:10.199 [error] <0.13535.8>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/112XQ8UVDD9RXnRX8vGFYUZjY2fLTy1oEf1ZNp2K1wr6m7TCE4Ss" not_found
2022-05-20 15:53:35.236 [error] <0.16808.8>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/11eNTbDs9aMbCk3iMfoFszbJExbHrguGMEpE7PvugEi4YC1uZLp" not_found
2022-05-20 15:57:26.368 [error] <0.20674.9>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/112SZFkjgCFpNPDhq3MdkxhQhoamcC5YNiaA2PvsKx5wG2o78GHu" not_found
2022-05-20 15:57:46.388 [error] <0.25027.9>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/11hw5iyMt55hvp664XVc6ikyTYETDjuvPGStVRNZWzEv6WN3JY9" not_found
2022-05-20 16:01:35.814 [error] <0.27146.10>@libp2p_transport_proxy:connect_rcv:113 stream <0.28223.10> went down normal
2022-05-20 16:04:52.357 [error] <0.26648.11>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/112AhT9A4spHhL1gNWBZMmkNB7RnsGiFXCJbYDHPgNykXGbEgvZH" not_found
2022-05-20 16:07:50.194 [error] <0.23083.12>@libp2p_transport_proxy:connect_to:69 failed to dial proxy server "/p2p/112GYGbyFwr2AJ86NhdxXX5Sz2jQYYpgYLqiHRUc2AQL4EkDJsP5" not_found

[madninja]

that gateway_last_block migration takes a very long time without a key index that can be added as follows:

1. Stop your ETL service. Ensure that there are no other active clients connected to the database (like http workers or other sql clients)
2. Run the following snippet in a sql terminal

alter table transaction_actors drop constraint transaction_actors_pkey;
alter table transaction_actors add constraint transaction_actors_pkey primary key (actor, block, transaction_hash, actor_role);

3. this will take a number of hours to complete. Once complete restart ETL and run migrations again. It should complete within a few minutes.

If you have any questions please join us in Discord in the blockchain-development channel