Besides the usual starvation and dehydration, my early failures were misunderstanding when I did or did not need irrigation. So I would always build complex irrigation systems sometimes when they were not needed, floodgate locking large bodies of water to dump into different 5x2 farming "rooms". Sometimes I'd forget which lever did what, so I would have two floodgates open at the same time, flooding the fortress. Since I tend to build my fortress infrastructure directly down below my farms, this led to a waterfall directly into the dining room below.
On my latest map I broke the rule of one lever at a time. I had dug a hole down beside the river, which lead into a chamber several stories below. This first room, the "reservoir", could be filled and cut off with floodgates. The adjacent room, the "well bottom", was a smaller room over which two wells were right next to the dining room. I accidentally opened both, and ended up with water jetting up the wells to flood the well room, sweeping into the dining room. One dwarf child got swept into the well and sank to the bottom, while another dwarf managed to hit the lever and stop the wells from backing up. It would have been interesting if the body had poisoned the well, but that did not appear to happen.
So yeah, my early !!Fun!! was all water works related!