Definition: A "Mary Sue" character is an impossibly good, cool or powerful character in a work of fiction. Often, she is an authorial self-insertion character or wish-fulfilling fantasy. A "Gary Stu" is the male equivalent of a Mary Sue.
(For your entertainment, subject your protagonist to the Mary Sue Litmus Test!)
I think Mary Sues (I use that term in a gender-neutral way) are a stage all writers go through. We start by creating self-insertion characters - reflections of ourselves - and eventually learn to create very warped and unrecognisable reflections.
The main character of my novel-under-construction started out as a Mary Sue (aeons ago). After I realised she was, the fix was simple - she became two characters. My protagonist now has a side-kick, and both women are more realistic for being less powerful. (They're also more interesting, because bifurcation didn't agree with them - they don't get along. Yay! More conflict!)
As I mentioned a few posts ago, I'm reading Story by Robert McKee (which is now overdue at the library... Oops...) Chapter 14 has the following: "A protagonist and his story can only be as intellectually fascinating and emotionally compelling as the forces of antagonism make them."
In other words, your hero is defined by your villain. The way to make your protagonist more compelling is to make your villain a better foil for him.
Writers of Mary Sues get this backward. They try to make the character compelling by giving the Sue attributes like superpowers, exceptional beauty, and the obsessive interest of everyone the Sue encounters. Even worse, they often make the villain weaker, or more evil than believable, in order to give the Sue opportunity to shine.
The problem with Sue is that she's just too powerful. If she's obviously never going to lose, the story has no tension because it has no stakes.
In Writing the Breakout Novel, Donald Maass says your main character should have "stature". He then describes attributes that lend stature to a character, and some of these could be called attributes of the dreaded Sue.
I don't think these two books are giving conflicting advice; I think it's a matter of balance. Your protagonist can be a goddess, provided the antagonist is her equal (both in power and depth of personality.)
*cues the violins* Do you remember the first Sue you ever wrote? What was she like? Sexy? Powerful? Charismatic? Ridiculous? Was it true love, or did you manage to get over her?
*kills the violins* More importantly, what was your Sue's villain like?
