7

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;
  • This doesn't work in MySQL 5.7 for non root user as performance schema is not accessible. Is there a way to select @@innodb_ft_cache_size without causing a fatal error in MySQL 5.5? – Chris Muench Aug 30 '15 at 6:09
  • Make the Stored Routine "SQL SECURITY DEFINER" and create it while being 'root'. Then you should have access. – Rick James Oct 17 '18 at 22:14
1

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.

0

Your query against information_schema.GLOBAL_VARIABLES doesn't work because it is disabled by default on MySQL 5.7. You need to enable the 5.6 compatibility feature first (no reboot required).

set global show_compatibility_56=1;
  • Will this work in later versions (like 8.0), and variants? – RDFozz Oct 17 '18 at 13:52
  • No, this setting was removed MySQL in 8.0.1. As for variants, it – socaire Oct 17 '18 at 14:33
  • As for variants, Percona Server has it but MariaDB doesn't seem to have it on any version – socaire Oct 17 '18 at 15:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.