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 ...
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.
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.
Child of the Storm has Harry who, true to form, finds his powers more of a trial than a blessing, usually because they tend to turn on and off pretty much at random. He is, for instance, often worried that his Super Strength, which tends to activate when he's angry or scared and completely without warning, will lead to him one day shaking someone's hand and crushing their bones to powder. And then there's the Psychic Powers. Though he gets a handle on them relatively quickly after they manifest, he goes through one hell of a case of Power Incontinence.
Taylor suffers this in The Last Daughter, owing to her nature as a Kryptonian. She constantly fears hurting friends and loved ones with her titanic strength.
The Harry Potter/Marvel Universe crossover A Third Path to the Future has Melody, a young mutant whose powers change anything she sees into music. While Melody can tell exactly what kind of person someone is by the songs she hears from them, her mind has been so warped that she has no concept of numbers and has little to no social skills. According to Xavier, she's one of the few mutants in The Vault simply because she's incapable of taking care of herself.
The Three Kings: Hunt: The Mages who get magical powers which can be very powerful but are also very specific and limited; these same powers also cause mages to be hunted down and killed by wizards. Further according to the author not all these magical powers are very useful (for example generating globes of light)
In The Bridge, Aria Blaze manages to sing a melody that calms down the human transformed Kaizer Ghidorah. Pro? She avoids getting killed by Kaizer and siphons a large boost in energy from him that amplifies her own powers. Con? She did it right when another kaiju named Enjin was hunting Kaizer, tracking him via his energy signature. An energy signature Aria now shares, causing Enjin to target her too. Oops.
In Overlady Jessica is half-incubus, not half-succubus. So if she loses her focus, she'll radiate immense manliness and appear incredibly handsome despite having a voluptuous feminine figure. According to her, it's murder trying to get a boyfriend when her manliness makes them all feel insecure.
In the AULies On Light Yagami is revealed to be an incubus on his eighteenth birthday meaning he's really a super-powered immortaldemon and that he can't go anywhere without someone trying to molest him. Also if he ever wants to pursue a relationship with anyone he has to take extra care not to suck out their lifeforce.
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.
Red Lightning gives us Jimmy, who is completely immortal. Unfortunately, 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!
Luna Lovegood in When Is It a Contract? can answer any question she's asked directly even if she doesn't know the answer (i.e. "Why are we required to have dress robes?" Or "How can I make sure I have a better year?"). However, since she also doesn't remember giving said answers, she often appears crazy whenever someone asks an indirect question.
A Type 4 appears in 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 lose the will to live.
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.
Within the Tripytch Continuum, A Mark Of Appeal kicks off when a character supposedly "blessed" with ludicrous sex appeal comes to Luna with a simple request: to destroy her mark, magic, or both. (The short version: her talent has been growing stronger as she's aged, starting from simply inspiring look-here-first interest in her classmates. It's currently at the point where it crosses gender and species barriers within minutes, creating infatuation, arousal, and then steadily strengthening both. Anyone she tries to speak with will eventually attempt seduction — or worse — and she can't turn it off.) However, the act of directly changing or destroying a mark is beyond even alicorn magic... With the most recent chapter, it's been implied that the curse may actually be genetic, as her parents appear to have had their personalities consumed by their own talents....
The Hasbroverse: In a Recursive Fanfiction of The Conversion Bureau, Megan Richards gets transformed into a powerful alicorn, instead of a "newfoal". That would seem like a dream come true. Unfortunately, the potion also had an added side effect of allowing TCB!Celestia to brainwash her if she gets too close. Megan spends much of the story trying to shield her mind from TCB!Celestia's influence.
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.
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.
Quistis in Eroninja has a kekkei genkai much like the Sharingan in that it allows her to learn any jutsu. The problem is that she has to be hit by said jutsu first.
In Pareidolia, Kyuubi hates his power as The Empath since he can only sense hatred with it. No matter where he goes, all he can sense is hatred. According to him, this is what caused his Face–Heel Turn.
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.
The dampener removal in Poké 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.
Tails gets infected by Shroud in Sonic X: Dark Chaos, which ends up giving him a Lovecraftian Superpower and makes him immune to the influence of Dark Tails. Except he still feels the corrupting influence of Dark Tails at every moment, and he starts to gain Bizarre Alien Biology by the bucketload. And his temper gives him the chance of losing control of said superpower at any moment - he nearly destroys the Blue Typhoon and devours his friends at the end of Episode 73.
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...
A Semblance Of Failure is set in a support group for people with useless Semblances. One guy can tell exactly how much salt there is in his proximity; another can grow her nails at an accelerated rate.
In Tales of the Emperasque the Emperor merges with Tarrasque, giving him phenomenal psychic power, resilience and physical strength, but first, he's really, really large, which carries its own batch of problems, and second, a Tarrasque is a daemon in this setting, causing panic and multiple friendly fire incidents nearly everywhere he appears.