Will I destroy my career if I published a paper with a serious mistake?
Congrats on your paper. No, it wouldn't destroy your career but it would be awkward and embarrassing. It could potentially hurt your career if the mistake was the result of obvious sloppiness, gross incompetence, and worst of all, outright dishonesty. But for the first two of those, the damage would very likely be containable and if you keep doing research, after publishing another paper or two that had no mistakes, no one would remember this minor incident.

With that said, your advisor is correct that it's best to avoid publishing papers with mistakes in them if at all possible, so do make a sincere effort to check everything to the best of your abilities before submitting the paper.