Boy George had a “breakdown” of some type, did he not? Substance abuse was involved, but I’m not sure whether that was the cause or the effect of his mental state.
It’s been said already, but:
Michael Jackson
I know substance abuse doesn’t count, but Shane MacGowan of Pogues fame might qualify as quite insane on many levels.
Ozzy Osbourne’s problems have gone beyond mere substance abuse. His shakes aren’t caused by drinking, he’s on anti-psychotics after he attacked Sharon a few years back.
Jerry Lee Lewis, if you believe all the rumors.
Aside from marrying his thirteen year old cousin in the 1950’s, several years ago an article in Rolling Stone all but accused him of killing his fourth wife (found dead in their swimming pool) and then his fifth wife died the next year of an overdose…not to mention other alleged incidents of gunplay, arson, and drug abuse.
True, but I do think Ozzy attacked Sharon whilst high. He’s not a psychotic per se, he’s just got a fried brain from years of drug abuse. Also, I don’t know for certain what causes the shaking of his hands, but it’s not necessarily his meds.
Plus, he’s still the fucking Prince of Darkness, so mind your words. 
Elvis may have had a coupla screws loose with or without the medications. Recall his bizarre visit with Nixon.
Wesley Willis has parlayed his schizophrenia into a fairly successful (though thoroughly bizarre) musical career.
I found an interesting biography of Roky Erikson. It chronicles somewhat his life in the mental institution.
Not rock, but jazz star Sun Ra claimed that he was taken for a ride in a spaceship by aliens who informed him that he was born not in Birmingham, Alabama, but on Saturn. This didn’t stop him from long, varied and colorful career (and life).
Denny Kirwain, an early member of Fleetwood Mac, left the group due to mental problems. Another Mac member, Jeremy Spencer, had drug-induced mental problems and had to quit. And Peter Green, who founded the group, left due to mental problems due to the ingestion of hallucinogens.
Didn’t the lead singer of Question Mark and the Mysterians, of “96 Tears” fame, change his name to Question Mark or just the punctutation symbol “?”. IIRC, he also claims to be an alien from another planet, much like Sun Ra.
Ron Strykert, guitarist from Men At Work, for a time joined a cult that was building underground shelters in preparation for a coming apocalypse of some sort.
And, personally, I’ve always wondered about the sanity of Captain Beefheart.
Well, Liam Gallagher from OASIS doesn’t sound very proof to me…
Quite the contrary. He was so wasted that he says the only thing he remembers is waking up in jail. This was quite a while back, by the way–around 1988, IIRC.
He certainly is still alive. I saw him play a show in Detroit a couple of years ago, with John Mayall’s Bluesbreakers.
It was a smaller club, no seats at all. Everyone stood. My husband and I got there early enough to be just a row back from the stage. It was an excellent show, and Peter Green played quite well. He seemed a little nervous, but it was good to watch, and great to see that he was playing again.
I seem to remember hearing that Bill Haley ended up as some eccentric recluse somewhere in Texas.
Excellent. That’s good to know. I’ve always liked that relatively little-known period in that band’s career, and he was the main reason why.
“Yeah, but she turned 14 the next day. They never report that.” - Jerry Lee Lewis 
One of the Meat Puppet brothers went MIA and IIRC basically a homeless drifter suffering from mental problems and substance abuse
I think Dave Davies of The Kinks seems a bit wacky these days, although whether it’s just from the drugs or what is anyone’s guess. He’s another one who claims to be in touch with aliens.