My parents have a '98 626 V6 auto and I learned a lot about the tranny situation before that car was purchased. Long-story short, the Mazda 626 4-cyl auto was a Ford sourced auto tranny that was infamous for tranny failures on all the vehicles it was put into. The tranny was reinforced in '98 and failures from the newer tranny's have been reduced. The 626 V6 auto tranny is a reliable one from Mazda, but is notoriously known for its harsh shifts.
The Mazda3 auto tranny is an evolution of the Pro auto tranny. The key feature from the evolution was making the 1-2 shift faster (exactly one tenth faster). I've also found the Mazda3 auto tranny to have a very harsh 1-2 shift under certain conditions when the tranny is cold (but it's a very narrow condition that does not affect all cars and only happens in sub-freezing temps).
Bottom-line, Mazda's auto tranny's are not the best in the business. However, the Mazda3 auto tranny is fine reliability-wise and also performance-wise. The current Mazda3 4-speed auto tranny will be replaced with a 5-speed in either the '06 or '07 model year.
I haven't heard of any bad Mazda manual tranny's from a reliability standpoint.
