Mysql – Counting show variable result in MySQL

MySQL

I am reading this article and there doesn't seem a way to do something like this:

SELECT COUNT(*) 
WHERE SHOW VARIABLES 
WHERE Variable_name = 'innodb_ft_cache_size'

I can do the below but this doesn't work in MySQL 5.7 (No variables in table)

SELECT COUNT(*) 
FROM information_schema.GLOBAL_VARIABLES 
WHERE Variable_name = 'innodb_ft_cache_size' (always returns 0)

The reason I need to do this is to detect in a stored procedure if they have the ability to do full text. It needs to work in MySQL and variants of MySQL so I cannot just simply parse @@verison.

Is there another way to do this?

Here is a stored procedure that works on 5.5,5.6, and 5.7 to detect presence of full text abilities. This should also work on mysql variants

begin
set @supports_ft = 0;
set @information_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'information_schema');

if @information_schema_exists > 0 then
    set @information_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'information_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
        if @information_schema_exists_global_vars_exists > 0 then
            set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
        end if;
end if;

if @supports_ft = 0 then
    set @performance_schema_exists := (SELECT COUNT(SCHEMA_NAME) FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = 'performance_schema');
    if @performance_schema_exists > 0 then
        set @performance_schema_exists_global_vars_exists := (SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'performance_schema' AND TABLE_NAME = 'GLOBAL_VARIABLES');
            if @performance_schema_exists_global_vars_exists > 0 then
                set @supports_ft := (SELECT COUNT(*) FROM performance_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'innodb_ft_cache_size');
            end if;
    end if;
end if;

Best Answer

You can run

flush status;
show global variables like 'innodb_ft_cache_size';
show status like 'handler_read_rnd_next';

If it is 2 (or incremented by 2) than you have the variable. If it is 1 (or incremented by 1) you don't.

That should work on every version.

Related Question