We could instead write a maintenance script that SELECTs each row of the DB and compares it to today's file, and makes fixes where it finds discrepancies. If we run it after an update, we would expect to find nothing to correct, usually.

Should we also handle the reverse case, where entries from the latest source file didn't make it into the DB? The maintenance script would need to iterate over each line in the file, and do a SELECT query to check that it exists in the DB.

As long as the script could complete well within the time between the update finishing and the next update starting, it wouldn't matter if it was slow.

Maybe we could just run it as a matter of course, directly after the daily import, as part of the same cronjob. That has the benefit that Kubernetes knows it should not schedule another concurrent instance of that job.

Overall, I like this approach and see it as a worthwhile investment. The alternative is that we discover a problem, and have to drop the DB and do a full reimport, which will take several hours of computer time and a few hours of people time to coordinate, communicate, etc.

In T354944#9456127, @kostajh wrote:

We could instead write a maintenance script that SELECTs each row of the DB and compares it to today's file, and makes fixes where it finds discrepancies. If we run it after an update, we would expect to find nothing to correct, usually.

Should we also handle the reverse case, where entries from the latest source file didn't make it into the DB? The maintenance script would need to iterate over each line in the file, and do a SELECT query to check that it exists in the DB.

Yes - good point.

Just jotting down some thoughts about how we could do this.

Neat-looking approach

We could add a new mode to output-sql.js handlers along these lines:

fixDatabase: function ( line ) {
	const data = JSON.parse( line );
	// Is IP in DB?
		// No: insert it with (generateInsertActorQueries)
		// Yes: is data the same as in DB?
			// No: update row with new data (generateUpdateActorQueries)
			// Yes: update last_updated (generateUpdateActorQueries)
}

• We'd need a new function to create an actor object from a DB row
• We could have a separate script to delete all rows with last_updated older than MAX_AGE
• MAX_AGE could be configurable, and this could solve T351922: Allow querying historical information on IPs

Caveat
This all looks nice and neat, but it does mean making 30-odd million updates every time we run this, which is something we were advised not to do: T353440#9518844

Alternatives

The problematic part is updating last_updated, meaning we are now updating rows for every unchanged IP. This is how we're able to find removed IPs.

• Solution 1: We run fixDatabase infrequently (as long as DBAs say it's OK to do all these updates in one day, once in a while). I think this wouldn't be too bad, since we don't actually expect the database to break anyway. It's currently broken due to T356736 which was caused by a bad fundamental assumption, which feels like a rare event to me.
• Solution 2: Instead of updating last_updated, we find removed IPs by SELECTing all the IPs in the database (in manageable batches) then searching the data file for them. How do we search the file though?

In T354944#9518882, @Tchanders wrote:

• Solution 2: Instead of updating last_updated, we find removed IPs by SELECTing all the IPs in the database (in manageable batches) then searching the data file for them. How do we search the file though?

I believe we have enough RAM available (either now, or if we request it) such that we could iterate over each line of the file and store the IP (and maybe also the associated data) in a variable. Alternatively, I was wondering if we could iterate over each line in the file and write the contents to a SQLite file--that would use a lot less RAM, and I believe lookups would be much faster. But I'd lean towards exploring the "load everything into memory" option first.