I believe we can use the script created in https://phabricator.wikimedia.org/T386477 to perform the schema update to the latest version we have (considering the open PR https://github.com/WikipediaLibrary/externallinks/pull/420 is correct).

I'm going to start downloading the old archives (there are a lot of them!) so I can run some tests.

I see two different approaches for solving this ticket:

1. Running the script created in https://github.com/WikipediaLibrary/externallinks/pull/420
   • It's relatively fast, considering the script focuses on fixing the schema in the JSON file directly and performing a model check at the end
   • The file "grouping" (old archives are grouped by year/month) remains the same since the script focuses on files

2. Performing a DB load + migration + unload
   • It's slower than option 1 due to the flow itself (load + migration + unload)
   • The file "grouping" will be adjusted to the new version (year/month/day), which will produce a lot more files as a result.

In T387887#10648941, @Mimurawil wrote:

I see two different approaches for solving this ticket:

1. Running the script created in https://github.com/WikipediaLibrary/externallinks/pull/420
   • It's relatively fast, considering the script focuses on fixing the schema in the JSON file directly and performing a model check at the end
   • The file "grouping" (old archives are grouped by year/month) remains the same since the script focuses on files

2. Performing a DB load + migration + unload
   • It's slower than option 1 due to the flow itself (load + migration + unload)
   • The file "grouping" will be adjusted to the new version (year/month/day), which will produce a lot more files as a result.

Oh, I like option 2! The reason this isn't already done is because of the expected super-duper-slowness. Since the script is looping through the objects, could we not add an option to regroup the output?

I'm thinking that it could be kept simple by breaking over to a day.x file when you encounter a date change within the current file. The downside would be that it could result in a lot of "extra" undersized files, but the benefit would be not having to worry about trying to track date boundaries across existing files.

In T387887#10652583, @jsn.sherman wrote:

I'm thinking that it could be kept simple by breaking over to a day.x file when you encounter a date change within the current file. The downside would be that it could result in a lot of "extra" undersized files, but the benefit would be not having to worry about trying to track date boundaries across existing files.

Sounds good! I can leave the files broken by day then.

Just for posterity, I'm running the scripts according to option 2. I'll post updates here as I progress.

I executed the scripts for all archives. Since the file is too large, I sent it over GDrive.

The following files had problems during the load process and got rejected:

• Integrity error on user_id
  • django.db.utils.IntegrityError: Problem installing fixtures: The row in table 'links_linkevent' with primary key '33038324' has an invalid foreign key: links_linkevent.username_id contains a value '727305' that does not have a corresponding value in organisations_user.id.
  • backup/links_linkevent_202406.4.json.gz
  • backup/links_linkevent_202406.5.json.gz
  • backup/links_linkevent_202406.6.json.gz
  • backup/links_linkevent_202406.7.json.gz
• Possible corrupted file
  • EOFError: Problem installing fixture '/app/backup/links_linkevent_202410.9.json.gz': Compressed file ended before the end-of-stream marker was reached
  • backup/links_linkevent_202410.9.json.gz

In T387887#10702024, @Mimurawil wrote:

I executed the scripts for all archives. Since the file is too large, I sent it over GDrive.

The following files had problems during the load process and got rejected:

• Integrity error on user_id
  • django.db.utils.IntegrityError: Problem installing fixtures: The row in table 'links_linkevent' with primary key '33038324' has an invalid foreign key: links_linkevent.username_id contains a value '727305' that does not have a corresponding value in organisations_user.id.
  • backup/links_linkevent_202406.4.json.gz
  • backup/links_linkevent_202406.5.json.gz
  • backup/links_linkevent_202406.6.json.gz
  • backup/links_linkevent_202406.7.json.gz
• Possible corrupted file
  • EOFError: Problem installing fixture '/app/backup/links_linkevent_202410.9.json.gz': Compressed file ended before the end-of-stream marker was reached
  • backup/links_linkevent_202410.9.json.gz

noting that @Mimurawil is investigating

For the integrity errors, the field username_id uses a key that doesn't exist in organisations_user anymore. I replaced those instances and set to null the username_id field (it's a nullable field in the table).

• backup/links_linkevent_202406.4.json.gz
  • Fixed 1850 records
• backup/links_linkevent_202406.5.json.gz
  • Fixed 6628 records
• backup/links_linkevent_202406.6.json.gz
  • Fixed 3645 records
• backup/links_linkevent_202406.7.json.gz
  • Fixed 4377 records

For the corrupted file error, I tried downloading the file from Google Drive again and re-ran the migration process successfully, this was possibly just a download issue.

I ran all the migration scripts for the fixed files above and made available in the GDrive as a separate file.

@Mimurawil
I went ahead and uploaded your fixed archives to the google drive.
I spot checked a few of the json files and they look good, thanks for your work!