As someone who's driven a manual for the last 15 years (agree they are super fun), there are definitely cons to it:
* If you live in a place with hills, it will be annoying to drive. (Initially it might feel like it'd be a fun challenge to deal with the hills, but it gets old pretty quickly.)
* If you live in a place where you have to deal with frequent stops due to heavy traffic or multiple nearby stop signs, it will be annoying to drive. (No fun challenge here; this is just flat-out annoying.)
* If you want or need a friend to drive your car in a pinch (say you've had too much to drink, or you sprained your ankle on a hike), they likely will not know how (depends on where you live; I'm in the US, where most people don't know how). On the flip side, "oh, sorry, it has a manual transmission" is a great no-blame, no-awkward excuse if you don't want to let someone drive your car.
* Related, but your significant other, if you have one, might not know how to drive it, and might not be interested in learning (before anyone says anything: no, there's nothing wrong with this attitude; being able to drive stick doesn't give you some sort of moral superiority), and that could create some annoying logistical problems at times.
* Less of an issue if you're buying used, but in many markets (again, I mainly know about the US market), manufacturers are dropping the manual transmission options, or are only allowing them on the more expensive trim levels. Even if you're buying used, your available options may be many fewer than if you were looking for an automatic car. Then again, the used car market is nuts right now, and I don't know, but maybe that's making it easier to find a manual car, since they're not usually in high demand.
I'm tiptoeing into the market for a new car now, and as much as it will sadden me, I'm almost certainly going to go for an automatic transmission. Finding a manual is requiring more compromises on other features than I want to make, and some of the cons I've listed above have, over time, hit me kinda hard.