MySQL Bugs: #34314: System variables in SHOW VARIABLES not selectable with SELECT

Bug #34314	System variables in SHOW VARIABLES not selectable with SELECT
Submitted:	5 Feb 2008 17:22	Modified:	13 Mar 2008 15:06
Reporter:	Paul DuBois	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	all	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Several of the system variables that are displayed by SHOW VARIABLES cannot be selected with SELECT @@{GLOBAL,SESSION}.var_name. Example:

mysql> SHOW GLOBAL VARIABLES LIKE 'back\_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log      | 50    | 
+---------------+-------+
1 row in set (0.01 sec)

mysql> SELECT @@GLOBAL.back_log;
ERROR 1193 (HY000): Unknown system variable 'back_log'

It's inconsistent for some system variables to be SELECT-able and not others.
It shouldn't be necessary to have to test whether a system variable can be used in this way, it should work for all of them.

How to repeat:
For 5.0.56, here is a partial listing of the variables not SELECT-able (along with the SHOW VARIABLES statements that display the values). Following the list is the Ruby program I used to generate the list. I will attach the full list to the report after submitting it.

SHOW GLOBAL VARIABLES LIKE 'back\_log';
SELECT @@GLOBAL.back_log;
SHOW GLOBAL VARIABLES LIKE 'character\_sets\_dir';
SELECT @@GLOBAL.character_sets_dir;
SHOW GLOBAL VARIABLES LIKE 'ft\_max\_word\_len';
SELECT @@GLOBAL.ft_max_word_len;
SHOW GLOBAL VARIABLES LIKE 'ft\_min\_word\_len';
SELECT @@GLOBAL.ft_min_word_len;
SHOW GLOBAL VARIABLES LIKE 'ft\_query\_expansion\_limit';
SELECT @@GLOBAL.ft_query_expansion_limit;
SHOW GLOBAL VARIABLES LIKE 'ft\_stopword\_file';
SELECT @@GLOBAL.ft_stopword_file;
SHOW GLOBAL VARIABLES LIKE 'have\_archive';
SELECT @@GLOBAL.have_archive;
SHOW GLOBAL VARIABLES LIKE 'have\_bdb';
SELECT @@GLOBAL.have_bdb;
SHOW GLOBAL VARIABLES LIKE 'have\_blackhole\_engine';
SELECT @@GLOBAL.have_blackhole_engine;
SHOW GLOBAL VARIABLES LIKE 'have\_compress';
SELECT @@GLOBAL.have_compress;
SHOW GLOBAL VARIABLES LIKE 'have\_crypt';
SELECT @@GLOBAL.have_crypt;
SHOW GLOBAL VARIABLES LIKE 'have\_csv';
SELECT @@GLOBAL.have_csv;
SHOW GLOBAL VARIABLES LIKE 'have\_dynamic\_loading';
SELECT @@GLOBAL.have_dynamic_loading;
SHOW GLOBAL VARIABLES LIKE 'have\_example\_engine';
SELECT @@GLOBAL.have_example_engine;
SHOW GLOBAL VARIABLES LIKE 'have\_federated\_engine';
SELECT @@GLOBAL.have_federated_engine;
SHOW GLOBAL VARIABLES LIKE 'have\_geometry';
SELECT @@GLOBAL.have_geometry;
SHOW GLOBAL VARIABLES LIKE 'have\_isam';
SELECT @@GLOBAL.have_isam;
SHOW GLOBAL VARIABLES LIKE 'have\_merge\_engine';
SELECT @@GLOBAL.have_merge_engine;
SHOW GLOBAL VARIABLES LIKE 'have\_ndbcluster';
SELECT @@GLOBAL.have_ndbcluster;
SHOW GLOBAL VARIABLES LIKE 'have\_openssl';
SELECT @@GLOBAL.have_openssl;
SHOW GLOBAL VARIABLES LIKE 'have\_ssl';
SELECT @@GLOBAL.have_ssl;
SHOW GLOBAL VARIABLES LIKE 'have\_query\_cache';
SELECT @@GLOBAL.have_query_cache;
SHOW GLOBAL VARIABLES LIKE 'have\_raid';
SELECT @@GLOBAL.have_raid;
SHOW GLOBAL VARIABLES LIKE 'have\_rtree\_keys';
SELECT @@GLOBAL.have_rtree_keys;
SHOW GLOBAL VARIABLES LIKE 'have\_symlink';
SELECT @@GLOBAL.have_symlink;
SHOW GLOBAL VARIABLES LIKE 'init\_file';
SELECT @@GLOBAL.init_file;
SHOW GLOBAL VARIABLES LIKE 'innodb\_additional\_mem\_pool\_size';
SELECT @@GLOBAL.innodb_additional_mem_pool_size;

Ruby program to generate list:

#!/usr/bin/ruby -w
# sysvar2.rb - Discover global system variables that are shown by SHOW
# variables but cannot be selected via SELECT @@GLOBAL.var_name.

require "optparse"
require "dbi"

# variable-scope constants

GLOBAL = 1
SESSION = 2

def test_sysvar(dbh, var_name, scope)
  scope_name = (scope == GLOBAL ? "GLOBAL" : "SESSION")
  begin
    row = dbh.select_one("SELECT @@#{scope_name}.#{var_name}")
  rescue DBI::DatabaseError => e
    # print statements that will show the variable is displayed by SHOW
    # variables but cannot be selected with SELECT.
    esc_var_name = var_name.gsub(/_/, "\\_")
    puts "SHOW #{scope_name} VARIABLES LIKE '#{esc_var_name}';"
    puts "SELECT @@#{scope_name}.#{var_name};"
  end
end

# ----------------------------------------------------------------------
# main program
# ----------------------------------------------------------------------

host_name = "127.0.0.1"
port_num = 3306

opts = OptionParser.new do |opts|
  opts.summary_indent = ""
  opts.summary_width -= 3
  opts.banner = "Usage: #{$0} [options] command"
  opts.on("-?", "--help",
               "Display this message and exit") do
    puts opts
    exit 0
  end
  opts.on("-h", "--host=host_name", String,
          "Host to connect to") do |str|
    host_name = str
  end
  opts.on("-P", "--port=port_num", Integer,
          "TCP/IP port number") do |val|
    port_num = val
  end
end

opts.parse!(ARGV)

# Expect no more arguments

unless ARGV.empty?
  puts opts
  exit 1
end

dsn = "dbi:Mysql:host=#{host_name};port=#{port_num}" +
      ";mysql_read_default_group=client"
dbh = DBI.connect(dsn,nil,nil)

dbh.select_all("SHOW GLOBAL VARIABLES").each do |row|
  test_sysvar(dbh, row[0], GLOBAL)
end
dbh.select_all("SHOW SESSION VARIABLES").each do |row|
  test_sysvar(dbh, row[0], SESSION)
end

dbh.disconnect if dbh

Script to demonstrate non-SELECT-able system variables

Attachment: non-selectable-sysvars.txt (text/plain), 19.34 KiB.

Thank you for the report.

Verified as described.

There is similar bug #25430 One should be duplicate of other.

Duplicated with bug #25430