There are two distinct ways in which the term Mary Sue gets used in literary works (as well as any other fictional medium, really). The most common usage today is in the context of the perfect protagonist. This could mean a character that has a seemingly limitless aptitude for displaying/learning skills that go well beyond the realm of reason even within the reality of the fanciful narrative in which s/he exists.
Think of characters that are described as flawless physically, and around whom all the other characters gravitate towards, whether the plot necessitates it or not. Obvious examples are characters brought to life within the pages of fan-fiction, but I would say that such writings are somewhat of a given on account that they are meant to be tributes to existing characters, thus overemphasizing said characters attributes might be unavoidable in this genre. More worthwhile examples of Mary Sues are characters that are actually successful, and one could say well-respected, within literature.
Characters like James Bond and Nancy Drew in their original literary inceptions could very easily be argued to fit this description. James Bond speaks every language of every country he steps foot in, can fight (and always win) in every fighting style confronted with, and can (and will) seduce any woman he desires because every woman he meets just naturally lusts after him without hesitation. Likewise, Nancy Drew effortlessly picks up any activity she tries, is seemingly liked by everyone and often complimented on just how great she is by the other characters, and of course understands investigative deduction and forensic science well beyond what ought to be plausible for a person her age.
A word needs to be said about not going overboard and pinning the Mary Sue label on any character that just happens to be either capable, or powerful. For example, although Superman is essentially a god-like character in many regards, he’s not really a Mary Sue as the term is commonly used. Notwithstanding the fact that he has a fatal weakness in kryptonite, a lot of the narrative around Superman centers on the way his immense power keeps him on some scale separate–even isolated–from the very people he is dedicated to protect. No matter how humane he is, he is never going to be human, and will always be an outsider in that regard in the only world he knows as home (especially since his birth planet no longer exists). In this sense, there is a genuinely ongoing tragedy underlying the Superman saga, whether it is explicitly stated or not, in a way Mary Sues don’t really have to deal with.
There is a secondary definition to a Mary Sue, and it involves authors who essentially write themselves into the plot of their stories as a means of wish-fulfillment. To put it simply, when the main character in a story is written as a idealized version of the author her/himself, and is written in a way to fulfill the perfect protagonist archetypes described above, then we have a Mary Sue on our hands.
I can see why people dislike either incarnation of the Mary Sue trope sneaking into the pages of a story. Perfect character can get stale very quickly, because they are largely unrelatable to the vast majority of readers. Moreover, the overreaching plot of a story will become very boring if we can tell from the start that the main character will always save the day, get the love, or that every obstacle encountered is just a superficial plot piece that offers no real danger in the long run. However, despite all this reasonable criticism on why not to write characters in this way, the fact is that Mary Sues can actually resonate with readers if they find the story engaging enough–compelling writing just have a way of trumping all tropes. The two examples of James Bond and Nancy Drew can attest to this just by how prolific both characters have been through the decades. (It should be noted that I am aware how Bond has been greatly “de-Sued” in his cinematic portrayal over the years, in particular in the most recent Daniel Craig films, which show him as a far more vulnerable and broken person than he ever was in print.)
What this tells me is that people don’t mind Mary Sues so much as they like to use Mary Sues as a convenient way to write off a work of fiction they probably disliked to begin with. And I get that, too. Sometimes, characters in a book can just rub you the wrong way. I for one absolutely loathed Holden Caulfield when I first read The Catcher in the Rye, and am still not too found of the little shit to this day. (I’ve mellowed out about him because I’ve come to terms with the possibility that he’s a character I’m not meant to like.) If I discovered that Holden was written to serve as an idealized stand-in for J.D. Salinger my opinion would not be swayed one way or the other. This brings me to the final point I want to make on this topic, and it deals with the issue people have of authors writing themselves into the characters. As anyone who has ever written fiction can confirm, it is unavoidable that some part of you will come through, in some way, in every character you will ever create. I’ll even go as far as to say that I have never written a character that didn’t reflect some aspect of my personality, morbid curiosities, lived experiences, faced dilemmas, overcome setbacks, learned failures, and hard fought successes. And I know that people will object that I’m shamefully stirring away from the genuine opposition leveled against Mary Sues (i.e. an author’s perfect protagonist wish-fulfillment), but I would argue that the fear of not wanting to create a Mary Sue-type character may be holding some writers back from exploring the full depth they can push themselves to because they are too paranoid about falling into this trope. What I would urge instead is for a different approach.
You shouldn’t just see yourself as the author of the story, but remember that you are also its first reader. You are the first one who will look through the characters’ eyes and see the world as it is written for them to see. Regardless of whether you are a novice or been doing this for years, it is no easy feat to create an entire world from whole cloth, and then give to it a pair of eyes (several pairs, if we are being honest) for others to share in the experience. It can be a rather frustrating task to even know where to start. My take on the matter is simply to realize that, as you’re struggling to give sight to your story’s narrators, it is perfectly fine to first start with the pair of eyes ready made in your head, and expand from there without fear of breaking some unwritten rules of storytelling.