Are you aware of an existing DBA opening or consulting requirement in your organization? Enter your email for a chance to win one year's access to Safari Books.
SHOW VARIABLES Shows Variables MySQL Does Not Know About
The listing of Dynamic System Variables on the MySQL Reference Manual’s page is supposed to show those variables you can change on-the-fly.
innodb_data_home_dir is listed there as a dynamic variable, but it is not one, and trying to change it on-the-fly doesn’t work:
mysql> set global innodb_data_home_dir="/data/mysql/data"; ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_dir' mysql> set session innodb_data_home_dir="/data/mysql/data"; ERROR 1193 (HY000): Unknown system variable 'innodb_data_home_dir'
The same goes when trying to use SET @@innodb_data_home_dir, and with log-slow-queries (changed to log_slow_queries as a parameter, of course). The irony is that innodb_data_home_dir shows up when SHOW VARIABLES is issued — so SHOW VARIABLES shows variables that MySQL is not aware of.
The link at the top is a link to the 4.1 version of the manual, just in case folks are thinking I’m using a newer manual version. Mind you, I have this problem with 5.0.45 as well as 4.1.20.
And of course the manual specifies, “The last column indicates for each variable whether GLOBAL or SESSION (or both) apply.” Which seems to be untrue.
Jan 8, 2008
Category: Group Blog Posts, MySQL
Tags: dynamic variables, MySQL, mysql gotchas
Hi Sheeri,
The manual indicates that the variable “innodb_data_home_dir” is Global and is Not Dynamic.
MySQL 4.1 - http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
MySQL 5.0 - http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
In the case of log-slow-queries sees: Bug #32565 - log_slow_queries throws an error when set dynamically - http://bugs.mysql.com/bug.php?id=32565
I wrote a program that runs through the list of variables and compares it to the table online, and finds where the online table isn’t accurate. I submitted the script as part of a bug report. Hopefully that has helped them fix the remaining problems (at the time I submitted it, there were tons of inaccuracies).
William — thanx! It looks like the chart on those pages has information that the dynamic page wants — the dynamic page says it shoudl have a column for global or session or both, but it doesn’t. Very helpful!
I’m glad to know that those are the exceptions, not the rule. Still, innodb_data_home_dir shouldn’t be on the list of dynamic variables if it’s not dynamic; I’ll accept that the other case is a bug. :)
Baron — interesting. I’m guessing by “table online” you mean the internal table?