Preface: I have an obsession/sordid history with wells. About half of my fortress failures can be traced, directly or indirectly, to some problem with a well.
Most recently, I miscalculated how fast the well would fill after the cistern was full (it was a multi-well, with one shaft feeding both the hospital well and the lower drinking well in the dining room). I had precautions in place (floodgate from the river, and both well rooms had doors preventing watery death to the rest of the fort and the access shaft was tightly sealed on every level with floor hatches). I wanted the cistern to be completely full, and the shaft to be as full as possible, so the higher hospital well didn't take forever to get a bucket down into the water. It turns out the lower well was only 2 z levels above the cistern. Well, after the cistern was full, it didn't take too long for the 2-4 tiles of open space to fill with water, and adding the delay from level pull to floodgate closing, the lower well overflowed, leaving me with a well room flooded to 3/7. Not too bad, I'd just remove the door retaining the water, letting it wash over the main dining room floor, eventually leaving nothing but some mud that would be cleaned.
Facepalm moment: While the door was being deconstructed to begin the "Great Seeping," one industrious dorf waltzed into the well room, likely slurred something along of the lines of "I'll fixst it!" and dove into the well, promptly sinking to the bottom and drowning, contaminating the entire cistern. The next couple months consisted of me trying to build a new cistern under the old one to drain the contaminated water into before refilling the original cistern with clean water. I successfully drained the cistern (but the dorf's body mysteriously disappeared, it may have been flushed somewhere I didn't check) and was working on sealing the contaminated cistern when a cyclops appeared and slaughtered my entire (sorely under-equipped and untrained) military before suffocating from a fatal case of "Bone Lung" (read: multiple bone crossbow bolts puncturing its lungs). At this point, a goblin siege wiped out the rest of my dorfs and thus the fortress of Pastwounds fell. If I hadn't had to focus on fixing the contaminated well, I might have been able to train my military better (I had a danger room close to completion, but wanted a functional hospital, with well, before commencing the training.)
Other well-related mishaps from other forts include failing to irrigate a farm in time (the irrigation system drained into a cistern to feed a well, building the entire thing at once took too long), leading to starvation; reclaiming that fort and failing to irrigate a second time, but dying of thirst this time because the river froze; reclaiming the fort a third time and actually irrigating this time, but an oversight in floodgate construction (what? water flows upwards over floodgates?) led to flooding the lower half of the fort, including food and booze supplies, as well as drowning several dorfs asleep in their beds. Paintdrink (really... I wonder why it failed?) was deemed un-salvageable (and very, very haunted) after that.
In a separate fort, I accidentally designed a system to flush any invaders back into the caverns, if they didn't get too far into the fort. I say accidentally, because after looking at the layout for a while, I realized that by flooding the well, water should cascade down the stairs, missing anything important, and ultimately washing things into the caverns, never to be seen again. While it would have worked as stated, in practice it took too long because apparently the water needed to flood every other mineshaft before hitting the shaft(s) that actually led to the caverns. Of course, by time I noticed this, the water had been flowing for a while and a giant glass elephant that spewed paralyzing, necrotic dust everywhere was rampaging through my fort, not allowing anyone to close the extraneous mineshafts to expedite Operation: Toilet. Water finally hit the cavern about the same time the fricking elephant walked past the point where it could be flushed and killed the last dorfs in the fort.
My very first fort fell when a tantrumming dorf tossed the lever to the well's floodgate, drowning a child and a cat, leading to a tantrum spiral that killed everyone.