Bug #35434 Many variables stated as static are give an error Unknown System Variable
Submitted: 19 Mar 2008 12:18 Modified: 4 Dec 2008 11:05
Reporter: Rizwan Maredia Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.22, 5.1.23, 5.0.45 OS:Any (Windows XP)
Assigned to: CPU Architecture:Any
Tags: system variable, unknown system variables, unknown variables

[19 Mar 2008 12:18] Rizwan Maredia
Description:
The following variables are stated in the documentation as system variables and non static, while they could not be accessed and fail causing the error "Unknown system variable". The documentation does not state the version number however they are not found in version 5.1.22.

back_log
character_sets_dir
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
ft_stopword_file
have_archive
have_blackhole_engine
have_example_engine
have_federated_engine
have_isam
have_merge_engine
have_raid
have_row_based_replication
init_file
innodb_adaptive_hash_index
innodb_buffer_pool_awe_mem_mb
innodb_log_arch_dir
innodb_log_archive
innodb_stats_on_metadata
innodb_status_file
language
large_pages
large_page_size
locked_in_memory
log_bin
log_error
log_slave_updates
lower_case_file_system
lower_case_table_names
memlock
myisam_recover_options
named_pipe
ndb_log_orig
ndb_use_copying_alter_table
open_files_limit
pid_file
plugin_dir
port
protocol_version
relay_log_space_limit
report_password
shared_memory
shared_memory_base_name
skip_external_locking
skip_networking
skip_show_database
slave_load_tmpdir
slave_skip_errors
thread_concurrency
thread_stack

How to repeat:
SELECT @@Global.back_log;
SELECT @@Global.character_sets_dir;
SELECT @@Global.ft_max_word_len;
SELECT @@Global.ft_min_word_len;
SELECT @@Global.ft_query_expansion_limit;
SELECT @@Global.ft_stopword_file;
SELECT @@Global.have_archive;
SELECT @@Global.have_blackhole_engine;
SELECT @@Global.have_example_engine;
SELECT @@Global.have_federated_engine;
SELECT @@Global.have_isam;
SELECT @@Global.have_merge_engine;
SELECT @@Global.have_raid;
SELECT @@Global.have_row_based_replication;
SELECT @@Global.init_file;
SELECT @@Global.innodb_adaptive_hash_index;
SELECT @@Global.innodb_buffer_pool_awe_mem_mb;
SELECT @@Global.innodb_log_arch_dir;
SELECT @@Global.innodb_log_archive;
SELECT @@Global.innodb_stats_on_metadata;
SELECT @@Global.innodb_status_file;
SELECT @@Global.language;
SELECT @@Global.large_pages;
SELECT @@Global.large_page_size;
SELECT @@Global.locked_in_memory;
SELECT @@Global.log_bin;
SELECT @@Global.log_error;
SELECT @@Global.log_slave_updates;
SELECT @@Global.lower_case_file_system;
SELECT @@Global.lower_case_table_names;
SELECT @@Global.memlock;
SELECT @@Global.myisam_recover_options;
SELECT @@Global.named_pipe;
SELECT @@Global.ndb_log_orig;
SELECT @@Global.ndb_use_copying_alter_table;
SELECT @@Global.open_files_limit;
SELECT @@Global.pid_file;
SELECT @@Global.plugin_dir;
SELECT @@Global.port;
SELECT @@Global.protocol_version;
SELECT @@Global.relay_log_space_limit;
SELECT @@Global.report_password;
SELECT @@Global.shared_memory;
SELECT @@Global.shared_memory_base_name;
SELECT @@Global.skip_external_locking;
SELECT @@Global.skip_networking;
SELECT @@Global.skip_show_database;
SELECT @@Global.slave_load_tmpdir;
SELECT @@Global.slave_skip_errors;
SELECT @@Global.thread_concurrency;
SELECT @@Global.thread_stack;

Suggested fix:
The documentation should state that the variables are not supported in version 5.1.22 or they should be accessiable through a mysql session.
[19 Mar 2008 12:55] Valeriy Kravchuk
Thank you for a problem report.
[3 Apr 2008 19:18] Andrii Nikitin
Added version 5.0.45
Actually this has impact on customers, because e.g. there is no way to detect whether binlog is enabled on server or not.
[10 Apr 2008 9:29] Georgi Kodinov
We're aware of this problem (see e.g. the related bug #363) and we're working on it.
[10 Jul 2008 1:08] Ryan Thiessen
I believe this is not strictly related to bug #363 because the variables are not available even to the "show variables" command.  For example:

mysql> show variables like 'innodb_stats_on_metadata';
Empty set (0.00 sec)

As the previous comment suggested this makes it very difficult to audit server settings.
[9 Dec 2008 18:09] Paul DuBois
The variables listed in the original report and said to be "non static" (that is, dynamic or settable are runtime) are no longer listed in the manual as dynamic.

re: the coment about innodb_stats_on_metadata: This is not shown by SHOW VARIABLES because it is a command option only, not a system variable. The same is true of innodb_status_file.
[9 Dec 2008 18:22] Paul DuBois
Added a note to:

http://dev.mysql.com/doc/refman/5.1/en/using-system-variables.html
http://dev.mysql.com/doc/refman/5.1/en/set-option.html

Some variables displayed by SHOW VARIABLES may not not be available
using SELECT @@var_name syntax; an "Unknown system variable" occurs. As
a workaround in such cases, you can use SHOW VARIABLES LIKE 
'var_name'.
[9 Dec 2008 18:40] Paul DuBois
As of 5.1.31/6.0.9, the "Unknown system variable" problem was corrected for several of the variables listed here, and they can now be accessed using SELECT @@var_name.

These variables still cannot be selected with SELECT @@var_name:

have_archive
have_blackhole_engine
have_example_engine
have_federated_engine
have_isam
have_merge_engine
have_raid
have_row_based_replication
memlock
named_pipe
ndb_log_orig
ndb_use_copying_alter_table
shared_memory
shared_memory_base_name
thread_concurrency

Note: I did not test on Windows, so the shared_xxx variables might work on Windows. I did not list innodb_buffer_pool_awe_mem_mb, innodb_log_arch_dir, or innodb_log_archive because these variables has been removed.
[9 Dec 2008 18:41] Paul DuBois
Changing report to Server:General.