I hate fixed cameras... Though they usually make things a little easier for me (they reduce the motion sickness feeling that I get playing video games). Still, I like feeling immersed in environment of the video game; fixed cameras really make the player feel like he is just watching the game rather than being part of the game (in my opinion, of course).
Probably the worst camera problems I've ever experienced were what I like to call the "SEGA camera problems" -- the problems of the camera becoming uncooperative at one point and then I get stuck in the game because of it. These problems have only happened to me in SEGA games, hence the name I gave them.
The most memorable experience I have had with this problem is this experience I had while playing Skies of Arcadia. I was walking down a pathway in the Ixa'taka region and the camera followed Vyse from behind as usual. As the path began to turn, the camera began to... get stuck behind a wall. So I tried to move the camera. It would spin around Vyse, but it would keep returning to its spot behind the wall. So I reset the game and tried again. My head hurts just thinking about that experience.