Recreate the whole database

[marcocastignoli]

Sourcify's database contains a series of problem:

• Creation matches without creation code in the database #1407
• Update contract_deployments after storing a better match with creatorTx information #1405
• Investigate deduplication in compiled_contracts table #1420

We will need to re-verify all the contracts recreating a new database from scratch. While the migration is in progress sourcify-server will write incoming contracts to both the old and the new database.

Overview of the process

1. Reactivate sourcify-server-migration, update it to the latest version after fixing: 1) Fix bytecode hash calculation #1414, 2) Update contract_deployments table after storing a match that previously didn't have contract creation information #1408
2. Create a new database for both environments: new databases that will store data in parallel with the databases now active in staging and production
3. Improve the migration script: potentially improve the migration script by automating the chain selection and preventing the script exiting from errors
4. Start writing to the new database: both sourcify-server-migration (only database, no repo) and sourcify-server store data in the new database.

We'll use this issue to keep track of the status of the migration

[mitjat]

Hi! Is it expected that some of the verification info is temporarily unavailable while this is in progress?
For example, https://sourcify.dev/server/files/contracts/23294 and https://sourcify.dev/server/files/contracts/42262 show 85 and 6 contracts currently, whereas our local Sourcify cache (created by polling Sourcify, and designed to only ever add contracts, never remove them) has 144 and 83, respectively.

Contracts that were verified on 42262 in the past (in case it's helpful)

\x49a670506377dfbe60bda8214ac45f6840a92b3f \x6f00d0dcd47548ace5000760655ac6d4e833a865 \x3ac3022d8df0104cb575072c8b6dc509318fe6c6 \x52c58191b3c81f58a9513c25c4f81833811fbf1d \x1ec046d831ffb1812b69460d48372cf2f7d6df55 \x04742eb8f14aa13d52625ab92340fae639d257ce \xe50d955fdce12e3dea538661078bc841a6ca77ef \xfb952b88b2d2c99d2a30e78ded6d393e2a2a4553 \x5bb0985287c32f49ea9fc20380bca059beb6d447 \x8593875bc450ebe8e9d6fda5298e44273fb7ea0a \xda294fde76f7369ed93d7c7a3fd2d5277c2003b5 \xe4b5fcfdf14dd8a1d3107cf14a8d206e0e770de4 \x4b214c971256200507c821c2b45db82996c058e2 \x01c42ba2d02d320584f62974d78cbc2b05e81d6d \xaee66f04c1abd110b9baf8eea607339dfeeba87d \x7a89dfe47979a008e34902fddc87534a6fc4750f \xaf92621eda1df939af121e51a126ea11ab52e0ef \xe222fbe074a436145b255442d919e4e3a6c6a480 \xc7a590291e07b9fe9e64b86c58fd8fc764308c4a \x21c718c22d52d0f3a789b752d4c2fd5908a8a733 \x031b36a65f15239b2ebe481218cd7e076a6f16f7 \xa1e932ebff4312e0dcc8dc7e9df1a071ef26176c \xbdc527d547528fb44591ef61e502835014bcc157 \x8dccba703e3ca40d3d50435a06637c577d36aafb \x331bd0541b2ec9218070a3419bebde7a9e51a334 \xf02b3e437304892105992512539f769423a515cb \x0036c2a5540a419f5277386721de51a978d726ee \x21085e9307a7ec5206ca17db95be9eba7c71362e \x5c78a65ad6d0ec6618788b6e8e211f31729111ca \xf18b0d2d471d0aa18b7f0de3c6dab41aa67c804e \xf88c56280acec4ca4b5b6ab93d53da1a73c8d7cd \xc0d84c4569e2317306835d45cd690fcf2e97ae25 \x92e86fc62db3c15170da2beca8fbe1cd0a185a4c \xb36afae1ab1fb4e659633f962ad94eeabe819e54 \x4cb049192baae756e38dc71384b28a7d7c3ce158 \x28c9d3e689b5d3629afc2d69ef6a2799578574e0 \x4254b9a998254988eed1760afdabd89ecc192694 \x9173a93cdcea2e10f6ae64e12a0412f6680fff29 \x870dd7a20893c2202ea1f2f3e3950c045a71cc97 \x96491226fde25249ff16ed84ca8c55f84e72190b \x9a43e76bf6361de1a80325a319cdb4f1927c0841 \xf1c113a6493e51bd546bcf7693871f6b03c75747 \x94fbffe5698db6f54d6ca524dbe673a7729014be \x0bc7dec6dc3f75d4b803b76a5f9fdd123bf81180 \xfb9bd52abe613a5c4a20e9fc09462a2ec5f2d1b2 \x8c14a40c488e16b055bc4e250563b5480047f01e \x8ad14a4bd095e18532a41286dd69a1b6d054c902 \xef5cb745b8cb5323cd2cb1625bd8678099997812 \x5b911fd5190ac6d942bf821eba6253dfb84dc905 \x6cb9750a92643382e020ea9a170abb83df05f30b \x896aecb9e73bf21c50855b7874729596d0e511cb \xd2076c230fe52572344dd7394fb50d8280c390d1 \xa76ff4482eca44694cdd17796b25c345c0621e21 \xd3c0a4ab6f68e3c12dec753255b9f769e0ba615b \x63217ac1600fb337a3b7a70533dc71870e6b68cc \xd1df9ce4b6159441d18bd6887dbd7320a8d52a05 \x0e1bc1433c5f546f4c7c489d0e9764a2aeba16bb \xfb83b2c9f65f92937fd8798acf8a79571b864273 \xed6541eb2dd7e2d98b7cd4e55a11fcc7629ab082 \xcab23d5fb9a5d64f0755914c17e283ff6563641d \xd8ac7f696ae99cd7b689726cf03c5711dd8dcdb4 \xfcfed3be2d333f24854ca8d3a351e772272d5842 \xd29069b6382aef2f24e3f189a37c5b8fb964acfb \xbe7494f2fa78fc658f986b4794f5af4827745c8a \xc7c455a3afa7b018f462b88bd3eae5fb3c036f02 \xca42cc69a8d1499c2b2085a05cfe9cdd82c0768a \xa6e864266b6707a6ea9529d83d8e0499e660f566 \xe785766b608d3ed42181a58dc136dae51cefd386 \x941494a56164ea04d79f9867dddb0dd754a625cc \x5e5af55e1061fbd11596e391da1d7b0137a9f29d \xd295d690b9bf17bd217b94bc50c12729762c1862 \x7769d0d3e0bfbf3c8ab3708a72442cef8c7fb7d2 \xe0b9c4e4ec0ee31afdb499eea6bfdf6355ca4835 \x0bff36be5cf671fa973f8206483b6641a90ce7d0 \x619e0f54969fabc894c3e6629fd86e19fda8bace \x4389c5a87dbdc1ae1323f4de24d8b1cfcc4e62ca \xabe08d013c1342e786c5964bc2e4e3f6b70797ea \xd0dd1dfe79bb4ad64d727ee99f51cb968e949bf4 \xbf19c3fe078258f1d1c34bec7e624ad8a1de343a \x4dd44ac05875247c9ae74bf493f8b98729795bf5 \xef15601b599f5c0696e38ab27f100c4075b36150 \xfa86d4ba31dd4b6b1e825ee0242b2cc9d07ad650 \xca11bde05977b3631167028862be2a173976ca11

[kuzdogan]

@mitjat yes unfortunately we had an incident and having temporary data loss: https://x.com/SourcifyEth/status/1799159285676687636

We're in the process of recovery and keep updated