13

I'm again looking at rene's SEDE query Status of the SEDE Refresh on Sunday 03:00 UTC to check the status, which is currently (in part):

. category database_id Description create_date days ago
3 restoring 211 StackExchange.Literature_Temp 2023-04-16 03:12:49 0

... which is about 10 hours before I write this. I do hope nothing is -- or was -- on fire!

This particular problem seems to be DISTINCT from the most recent issue of a failure on a backup share that caused the process to be disabled, since there's evidence of previous restoration activity:

. category database_id Description create_date days ago
4 days group start 11 # databases 42 2023-04-16 03:09:39 0
2 newest 210 StackExchange.Literature.Meta 2023-04-16 03:12:48 0
15
  • Yeah, happens to often. :( Commented Apr 16, 2023 at 14:04
  • 3
    I've heard it said: software eventually works; hardware eventually fails! Commented Apr 16, 2023 at 15:28
  • Also, really hope Aaron isn't on vacation, he's the only one who handle such cases. If he's away, it's going to be very long wait until SEDE will be updated for the stuck site. (and it might also cause the whole process to fail, making SEDE not refreshing at all.) Commented Apr 16, 2023 at 19:29
  • 4
    So, SEDE got stuck in a good book?
    – rene
    Commented Apr 17, 2023 at 6:16
  • @AaronBertrand sadly it's still bugged, it added few records yesterday around 6pm but that's it, nothing else, and there are posts edited today. (Query, it sorts by last activity) Commented Apr 17, 2023 at 13:03
  • 2
    @Shadow The Literature database was restored successfully in SEDE at 2023-04-16 23:42:34.467 UTC. So it can't possibly be expected to contain posts edited today.
    – Aaron Bertrand Staff
    Commented Apr 17, 2023 at 13:30
  • @AaronBertrand ohh, oops. Well, it appears Drones is now stuck, are you aware? Commented Apr 17, 2023 at 13:32
  • 2
    @ShadowTheSpringWizard Yes.
    – Aaron Bertrand Staff
    Commented Apr 17, 2023 at 13:33
  • @AaronBertrand but what's going on? Why the job get stuck so often? Is this some bug in the code, or something in the database itself? Commented Apr 17, 2023 at 13:39
  • 4
    It was a schema change in all databases that went out on Friday afternoon. A column was made NULLable and then some NULL values were added in a few databases, and SEDE wasn't prepared to let that column have NULL values. We're working on it, you need to give us time to solve the problem before we can give you a full debrief. :-)
    – Aaron Bertrand Staff
    Commented Apr 17, 2023 at 13:43
  • I tried searching Meta.SE (recent changes) and Literature.Meta for hints (remembering that schema changes have broken SEDE in the past), but came up empty. Good old Friday afternoon changes -- gotta watch those! :) Hope it's an easy fix! Commented Apr 17, 2023 at 13:51
  • 4
    @JeffSchaller This change had to do with letting us disassociate a user from being the owner of a tag synonym. I doubt that type of change rose to the level of meta announcement, and obviously nobody involved predicted any downstream impact to SEDE. Also it didn't involve Literature specifically except in a data sense, so you wouldn't find anything on its meta, either. It was just the first database where the issue happened, and stopped the job; the next database where the issue happened was Drones.
    – Aaron Bertrand Staff
    Commented Apr 17, 2023 at 14:05
  • @AaronBertrand aka fixing bugs cause more bugs. :D Commented Apr 17, 2023 at 14:52
  • 2
    @ShadowTheSpringWizard Always.
    – Aaron Bertrand Staff
    Commented Apr 17, 2023 at 16:51
  • 1
    Its ---turtles--- bugs all the way down? Commented Apr 19, 2023 at 2:15

1 Answer 1

19

Really, really, really TL;DR:

This has been fixed.

TL;DR:

The source of the problem was a schema change* to allow NULL values for TagSynonyms.OwnerUserId. Previously, we internally weren't able to disassociate a (say, deleted) user from being the owner of a tag synonym, because we couldn't change the column value to NULL. This change (in combination with data changes in the meantime) caused some of the databases in the SEDE process to fail. The two databases observed here were Literature and Drones, but there were others.

* I joked in the comments about Friday afternoon releases, but had this specific change been shipped on Monday morning or Tuesday at lunch, we still would have had the same result over the weekend.

Background:

The process of populating the SEDE databases is that in the production database we have a set of views, one for each table we copy over to SEDE. We use views because we don't necessarily need all of the columns for any given table, or we want to change the name of the table (e.g. PostsWithDeleted), or the order of the columns, or what have you. So normally the process looks like this:

Normal populate SEDE process

As I've written about recently here, changes to underlying tables can cause the views that reference them to have stale / inaccurate metadata. Usually this involves adding, dropping, or renaming columns, or changing the data type, and usually it is also accompanied by views that erroneously use SELECT *. In this case, the view does not use SELECT *, and the only thing that changed was whether the column allows NULL (from no to yes), a less common change, but still vulnerable. So when the SEDE process ran and generated a new table based on the metadata of the view, the view still thought the column was not nullable, so the generated destination table had that column defined as NOT NULL:

SEDE process after schema change

When it started loading data from the source table, though, in a few databases, it encountered NULL values, and raised this exception (which made a lot more sense after unpeeling the entire onion):

Attempting to set a non-NULL-able column's value to NULL

In the previous version of the code, this failure halts the job, and it goes no further. I've updated the code to skip past any such failures, which allowed the job to process the rest of the databases to minimize the delay. I corrected the problematic ones in the meantime, by running sp_refreshsqlmodule against the TagSynonyms view in the source database; then, I processed those ones again manually, and this time the destination column allowed NULL. (As an aside, this manual part is why Drones is the newest database, instead of the usual StackOverflow.)

Nobody involved with reviewing the PR (myself included) anticipated the downstream effect on SEDE. We've certainly added it to our internal "lessons learned" documentation.

I've got discussion items queued up for how we'll avoid this in the future, including whether we should just refresh all of the SEDE views after any schema change or, more simply, prior to the weekly SEDE process.

5
  • Is there a relevant post or chat message for the ability to dissociate users from tag synonyms? Commented Apr 17, 2023 at 18:25
  • 4
    @SonictheAnonymousHedgehog There's no functional change to how tag synonyms work. There's no disassociation, but now we maintain better data integrity by not keeping ids of deleted users in the synonyms table.
    – Adam Lear StaffMod
    Commented Apr 17, 2023 at 18:26
  • 9
    As is becoming tradition, your answer to this post is a phenomenal breakdown of what went wrong. Thank you for the care in which you take when writing them, I can assure you that it's greatly appreciated!
    – Spevacus
    Commented Apr 18, 2023 at 23:45
  • 4
    @Spevacus Thanks! I really don't like when companies gloss over and hand-wave what went wrong and how they fixed it, so I like to report on the issue the way I'd want to read about it. Finding the right balance of too much detail or not enough can be hard at scale, but I think I have a decent handle on the audience here. :-)
    – Aaron Bertrand Staff
    Commented Apr 19, 2023 at 0:30
  • 2
    @Spevacus sadly, he's the only one, or one of very few. :( Commented Apr 19, 2023 at 7:51

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .