It “really goes wrong” only because we've set a really high bar for safety when talking about nuclear risks compared to most other carcinogenic risks: air pollution, pesticides, alcohol, tobacco, etc. If people where living in Prypiat right now, most of their cancer would come from other sources.
Because of the cultural stigma associated with radiations (which itself comes from the very real fear of a world-ending nuclear war during the early cold war) most smokers would refused to live around a nuclear accident site, even though it's quickly (after the most radioactive elements, namely iodine, has decayed away) much less dangerous than the cigarette they knowingly smoke all day.
Fun fact: did you know that in Germany alone the area of what has been destroyed by coal mining is comparable in size to the exclusion zone of Fukushima. This is when things go alright with coal: https://nitter.42l.fr/autommen/status/1538496930262704128#m
And yet we’re all still here and given the number of nuclear power plants and how long we’ve been using them compared to the tiny number of accidents it shows that it is truly the safest form of large scale power generation.
It is also a lot safer today if new reactors are built. The problem are the old reactors need to be shut down because they're based on older designs. We need to start building Pebble Beach reactors where even if there is a containment break, safe small carbon balls with a thin sliver of fissile material just spill out on the floor. These spread out and reduce their combined temperate averting a meltdown. Individually the balls themselves are not dangerous, they're lukewarm and could be held in the hand (not that you would). I think there are some even newer designs that go beyond this in safety. The problem with the anti-nuclear argument is that it is based on the old rod reactors like the one that failed in Fukushima prefect, Chernobyl or 3 mile island. Of course we shouldn't run those older models anymore. You need to start building the new safer reactors before you begin shutting down the old ones so you can logistically switch, however. Instead we're unfortunately heading for a future where we eventually just shut down these old reactors for safety without a real plan for replacing them. Or, we keep running the old models until they become the very cautionary tale that makes everyone nervous about nuclear.
At the moment it seems that the CO2 problem is more pressing and of greater magnitude than the problem of nuclear waste. It sounds like a relevant tradeoff.
