MySQL 4.1.9 Breaks Invocation: How to Fix with --datadir

This one goes out to the MySQL administrators out there who were baffled, like me, why when they upgraded through their normal process from 4.1.8 to 4.1.9 (or to 4.1.10) their invocation scripts broke given them strange and annoying errors. The reason is that the MySQL developers changed the context into which mysqld is launched via safe_mysqld. I'm writing this post so it can be found with a clear answer (much clearer than is documented over the MySQL developer's site) on how to fix it.

I have two instances of MySQL running on a dual-processor machine in order to balance load. This made a large difference when I split up databases of equal utility among them. So I invoke them by defining a my.cnf file in /etc/ with settings applicable to both, and then I have a my.main.cnfand my.second.cnf set of files that are particular with ports, sockets, and other details.

I invoke mysqld as

mysqld_safe --defaults-extra-file=/etc/my.main.cf
mysqld_safe --defaults-extra-file=/etc/my.second.cf

Now this stopped working when I upgraded from 4.1.8 to 4.1.9. I stepped backwards assuming a bug and didn't have time to deal with it. The other night I tried to jump to 4.1.10. Same problem. I start reading bug reports and resolutions and release notes and find an obscure note about the data directory being resolved to a path relative to mysqld if mysqld is invoked via mysqld_safe through a relative means. This didn't make complete sense as I had defined the datadir in my .cnf files.

But there you go: I had to add an explicit --datadir declaration into the safe launching:

mysqld_safe --defaults-extra-file=/etc/my.main.cf --datadir=/path/mysql
mysqld_safe --defaults-extra-file=/etc/my.second.cf --datadir=/path/mysql_second

Then it worked. Really frustrating, and it took me about 45 minutes to sort it out because the behavior is inconsistent. I'm not sure whether it's a bug or just something that you can no longer define within a .cnf file. I've added a note to the bug report to see whether they'll either deprecate use of datadir within configuration files or fix this problem.