John has complete control over water and the body of a winged god; but he also has to eat constantly, has ears so sensitive that he folds up in pain at shrill sounds that don't bother other people, and, worst of all, is no longer human, so he's no longer sexually attracted to human women.
Paul is so ridiculously strong that he can barely move without causing chaos. He has practiced day and night to get to the point where he can walk around, but he has to keep constant watch on himself, keep his arms at his sides, etc. To revert to his more manageable lower level of strength, he has to explode, creating a large glassy crater and wiping out everything around him. Even at his lower level of strength he has to fiercely regulate his behavior. In neither case does he dare have sex. Oh, and neither of them can go home as is...
In Kyon: Big Damn Hero, Haruhi realizes her power and nearly goes into a Heroic BSOD when she realizes just how sensitive it is. Yuki helps subvert this trope by becoming a buffer, and Haruhi runs a Memory Gambit on herself to limit herself to more manageable Mind Over Matter powers.
All of the hybrids in Morphic to a greater or lesser degree (it is a Deconstruction after all), but Gabriel, a half-Slugma really takes the cake.
Doubled. Not only is he half a blob of magma, but if he absorbs mud or stone, he begins to heat and get fire powers, which is awesome ... until you realize that, as his body temperature goes up, major tissue and organ damage would result. He's only (part) human. Special mention also goes to Mia Kerrigan, the Scyther morph, who has bladed arms, lightning reflexes, accelerated growth and is probably the strongest of the cast ... but does not understand empathy, limits or self-control.
Also of special note is Katherine, who was mixed with a Roselia. She ages even faster than Mia and is considered the smartest and most mature of the hybrids... But apart from the fact that she actually dislikes aging so quickly, she also has roses for hands, which makes even simple tasks like holding a pencil difficult. She also gets tired easily if she's not near sunlight.
In Dragon Age: The Crown of Thorns, the dwarven noble protagonist is a Magic Knight because he harbors a tear in the veil. Unfortunately, while he did get some of the spirit warrior abilities form Awakening, this seems to have caused at least as may problems, some of them rather serious.
In the Tamers Forever Series, as the vessel of Chaos, Takato is gifted with immense power, but that same power is slowly and painfully killing him from the inside out.
A Naruto/negima crossover Fanfic, "Broken Faith" by Kur 0 Kishi has this in spades. Naruto inherited Kyuubi's core and powers before he came into Negima's era, giving him an extended and merged version of the red Kyuubi Cloak and the golden Kyuubi Chakra Mode from Canon manga with awesome horns and chains to boot, rendering him a literal God on the battlefield. The problem? It burns his flesh away every second unless he reinforces his organs liberally with Chakra first, to the point where people start to see his bones if he holds the mode up for anything longer than 5 minutes.
Doubled in that using the power accelerates the breaking of the seal that's keeping him weak and stuck in Mahora. Sounds good doesnt it? It isn't, if the seal is released in a way it wasn't meant to ( i.e ; Breaking), both him and the tree go up in an explosion powerful enough to kill Divinity, taking everything in radius along with him at the same time.
Axis Powers Hetalia fanfic Mistakes uses the common Fanon that the nations can't be killed by physical means and can recover from apparent "death". This isn't necessarily a good thing when the fic starts with China being effectively gang-raped to death and being thoroughly traumatised on waking (particularly since up until it happened to him he thought rape was unimportant and murder actually fun, since human morality doesn't quite work with immortals), and the Koreas trapped in Unit 731, where they get dissected again and again and again ...
The dampener removal in Poke Wars. Its removal greatly augments a Pokémon's abilities. Unfortunately it is extremely painful and results in Power Incontinence until they learn to control it. Even then, their attacks are always lethal and extremely destructive. It is especially magnified for Pokémon like Heatran or Magmar whose body temperature rises to such an extent that being near them is lethal.
Happy Families Are All Alike (A Naruto/Negima crossover) has Naruto himself. He has superhuman senses and is physically outstanding. He has powerful regeneration... but the Kyuubi has, as of chapter 7, firmly planted its feet in the Eldritch Abomination category. And it can tear his flesh apart in his dreams.
In A Growing Affection, Kohaku's status as a living Perception Filter is this. Her parents forgot her when they went on vacation a couple of times (doesn't help that its a big family), and she gets passed over for promotion because no-one can remember what she did in the Chunin Exams. But as a ninja being functionally invisible and easily forgotten is a very useful ability.
The Powers Of Harmony: Rarity's insomnia inspires some of her best work, but leaves her very weak (due to denying her magic font time to recharge). Turns out, Harmony engineered this as part of her master plan.
Twilight's Power Copying ability works by making her more sensitive to magic, which makes it easier to understand how spells work. However, this also makes her more vulnerable to corruptive magic, which leaves her weak and sick.
Discord made the Princesses immortal and linked them to the Sun and Moon because he knew that eventually they'd snap from the pressure of maintaining the world and succumb to the Chaos magic inside them. This is what led to Luna becoming Nightmare Moon.
A Type 4 appears in the Lost Girl fic Mad World. Lauren develops a rapid Healing Factor. While normally this would be really good, the sucky part comes in when she's captured and tortured by really sadistic Fae. Her wounds healing just means he can torture her again. He quickly abandons regular physical torture, in favor of doing something where even if Lauren could heal, her injury would only instantly reoccur. Her injuries are later compared to those associated with crucifixion. She, understandably, starts to loose the will to live.
In Shinigan Naruto gains the Mystic Eyes of Death Perception. While his power has a surprising amount of versitility (such as removing Sasuke and Anko's Cursed Seals with a senbon), for a long time anything he looked at with his Shinigan on would fall apart along the lines. Seeing walls and furniture randomly collapse in pieces freaks Naruto out but seeing people falling apart nearly drives him insane. Naruto had to learn the Bringer Of Darkness technique to temporarily blind himself to save his sanity.
Red Lightning gives us Jimmy, who is completly immortal. Unfortunatly, this rendered him unable to sleep or fall unconsious, and did nothing to numb his sense of pain. The poor kid had to stay consious while pulling himself back together as atoms in the air!
