I started playing Mario 64 relatively recently, and have been playing it on and off for the last couple of years. Honestly I'm still waiting for the game to really "click" for me (which is why my progress in it has been so scattered, I suppose). I absolutely adore SMB1-3/World/Yoshi's Island, and I've played plenty of "old" games years after the fact and still really enjoyed them - so it's not like I just have an inability to appreciate older games, or anything like that.
I've been trying to peg exactly what it is that bothers me about the game, and I think I've finally got it. It's the FALLING. I spend far too much time falling in this game. Mario games are always full of tricky jumps, but it's different in 64. I'm always falling off ledges, falling into water, falling back to the beginning of the level... there's generally just a hell of a lot of ways to fall. And Mario 64 places a pretty large emphasis on aerial mechanics overall, so this FALLING issue is tough to look past. There are all these fancy, pretty ways to jump, but that generally leads to more FALLING so it's hard for me to really embrace the fun of it. The cannons, the locations of stars, and the general layout of most of the levels are all skewed toward this aerial gameplay mechanic, and I'm just not to crazy about it!
Yeah I'm a bit of a 2-D nostalgiac, but I've enjoyed plenty of 3D games. And I've had more fun with many of those 3D games - both well-respected (Metroid Prime) and somewhat sub-par (BloodRayne) - than I have with Mario 64.
Hey maybe the game will grow on me and I'll end up falling in love with it... but for now that's where my opinion stands!