A Mary Sue can still fail, they just usually succeed. The biggest issues with a Mary Sue aren’t their success, its the believability of their success. Is it reasonable for this person to be so skilled. If they have PHD level knowledge in 15 different fields, that’s a bit much. But they may have PHD level knowledge in 1 or 2 fields, and they may be able to get through like that without coming off as a Mary Sue, look at The Martian by Andy Weir (or the movie with Matt Damon) The premise of sending people with 2 PHDs in complementary fields to reduce the number of people needing to be sent makes logical sense, so him being an expert, and also being the right kind of expert, to survive makes sense. And the fact he isn’t an expert in everything else helped drive the narrative and provided the direction and the plot in a reasonable and believable way.

I think that’s what is important, not making your character flawless, or even introducing some flaws to a flawless character, because that still ends up coming off weird, but instead start with a flawed character and then remove flaws until you have just enough to make everything the character needs to survive believable. Another view of this, Die Hard, John McClaine wasn’t the typical Mary Sue, he wasn’t perfect and the audience feels like he’s constantly in danger and just a mixture of skill and luck gets him through it. A flawed character is more impactful to the reader. I am a flawed person, I relate better to flawed people.

The most very Mary Sue characters will fail as part of winning. “Oh gosh, I flipped the evil villain’s wall switch that forced all the robots into hard mode - I guess instead of an underwhelming fight scene I now have to go full badass.” Basically their flaws are usually just vehicles for even more exaggerated strengths.

Look at what Disney did with thier last few Star Wars shows. And then remember not doing it like that.

I think the worst thing about a Mary Sue is when their success comes trivially or randomly.

What usually helps me is making the obstacle more specific and diving into those specifics when they’re problem solving. You’ll find most things we broadly group into large lumps, like martial arts, swordfighting, researching, medicine, ect. often have an overwhelming amount of details that not only separates good from bad, but also have specific dynamics that change depending on circumstances.

If you want to make the successes feel earned, include enough detail about the problem that you can tell a story with the challenges involved. If your focus is swordfighting convey the kinds of techniques your protagonist know then put them up against opponents that can counter those techniques so they have to learn. If you focus is a doctor then instead of seeking out the Medicine Flower™, try conveying the roadmap to making medicine to the audience then make a story out of the process.

I feel like Breaking Bad is a good example of this. It depends a lot on actual chemistry and every chemistry advancement is a plot point. Mainly it’s figuring out how to procure the ingredients and equipment without leaving evidence to get caught from.

A Mary Sue can fail, but those failures don’t usually have a massive impact and are easily reversed without the feeling that the MS had to struggle to earn the reversal.

The more flaws a character has, the more they have to work to balance them out. Readers are more likely on the side of a character that has to work and make sacrifices to make it through the difficulties the plot throws at them.

Random Example: Diana Rowland’s “My Life as a White Trash Zombie”. Protagonist Angel has a criminal record, drug addiction, abusive home life, and generally makes very bad decisions. Because of her life course, she has very few resources (she can’t go to the cops, nobody she knows has money or connections, etc) but she can think quickly and has a sort of desperate resourcefulness. Because everything is working against her, she has to fight for any positive forward movement, and one misstep can be a serious threat - and those happen frequently, undoing any success and forcing her to burn her resources to try a new path. IIRC in one of the books the B-story is her trying just to earn her GED as the main plot around her is utter pandemonium. Just that struggle to graduate high school is a herculean task given the deck stacked against her. Readers aren’t thinking “how will she win”, they’re thinking “well what’s going to go wrong this time?”

TL;DR: If every time your protagonist has a setback the readers shout “can’t she ever catch a break?” instead of “ah she’ll just breeze through this” you should be doing okay.

I’m writing a story, and I suspect that one of my characters is subconsciously a Gary Sue. To compensate for this, he gets all of the traits I hate about myself, complete with fuckups and repercussions.

Being a Mary Sue isn’t about how often you fail / succeed. Your character can win all the time if their victories are entertaining enough (Munchausen), if they are established as being the best in their field (Saitama) or if their wins feel ‘deserved’ (Sherlock Holmes[1]). When they win and the audience thinks ‘ugh, not again’, then you have a Mary Sue. Or if they win at something they should not be good at according to the rest of the story - like Holmes winning a poetry contest or Saitama landing a blow on a mosquito - without any explanation given, you have a Mary Sue.

[1] Yes, I know he fails a couple of times, but again, they’re entertaining, which is the important thing.

If your character is meant to be endearing, an odd flaw would work. If you’re aiming for a drastic fall from grace, make her screw up big time.