In normal circumstances Hydro seems among the safest [1] for both humans and the environment all things relative. It has served Quebec quite well at least with very low electricity rates and emissions, and environmental impacts are likely long paid off with no radioactive waste to manage. Being in a low populated region (a rare asset..) also helps on the safety side.
Bringing up a single dam incident due to an estimated once-in-2-millenia rainfall/typhoon [2] in a populated region to dismiss an entire renewable energy source.. sounds like that kind of irrational fear you mention.
I think the arguments around possible lack of locations, costs, planning, and water supplies are more relevant -- and affect both hydro and nuclear. Those are also what make wind/solar interesting -agility- as tech improves.
[1] https://energycentral.com/c/ec/deaths-nuclear-energy-compare...
[2] https://en.wikipedia.org/wiki/1975_Banqiao_Dam_failure#Gover...