CRITICAL: Impossible meetings on DB

[danielljeon]

CRITICAL: Impossible meetings on DB

• tbl_meeting has meeting data for some courses that conflict within themselves, likely incorrect foreign key referencing or stale data

Example (Run 2023-09-02 at 23.07.49)

Original DB tbl_meeting query was based on:

1. course_id=401, references →

2. course_data_id=401 (result matches expected), references →

3. meeting_id=2065, 2066, 58736 (1 correct + 2 unknown meetings)

   

• Some columns have been hidden in the picture above

• course_data_id=58736 appears to be the only correct data, (verified via MyCampus below)

  

The sudden jump in meeting_ids 2065, 2066, 58736 seem to suggest that there is either incorrect data being referenced by foreign key or there is stale data being left over from the previous data set?

[danielljeon]

Verified the problem is due to old data being saved onto the DB

• After deleting and doing a full scrape and index the data is all correct
• The university is adding widely differing data more frequently than previously thought, a new solution will need to be found

[Minnowo]

I'm thinking to fix this, we can add a last_updated column to the required tables, and remove stuff older than a certain time frame. Since the scraper will update existing information in the db, anything that has not been updated in the most recent scrape can be considered not in the api anymore.

[danielljeon]

Yep, that's how the old system worked, we had a last_edited column with on update and on create default timestamp setting.

[Minnowo]

yeah latest commit has some code where i was testing that, later tonight i'm gonna clean it up and push to prod. Can we discuss possibly open sourcing, it would help with testing because it's easy to clone and handle public repos.

[Minnowo]

The latest updates to py_core have added db columns to help address this problem. All that remains is actually having the scraper or searchIndex delete old data.

Hopefully also with the new migration setup in py_core making changes to the database will no longer be the worst thing ever, we just need be careful in java land.

DB changes which will help with this:

• tbl_school now has last_scrap_id which lets us determine per-school which data is out of date by comparing this value to tbl_course_data.scrape_id
• tbl_meeting now has scrape_id which can be used the same way as tbl_course_data.scrape_id
• tbl_course_data now has should_be_indexed a boolean which should inform the searchIndex of what data should be re-indexed