1

I found this answer for how to have conditional logic for execution of mysql statements (https://stackoverflow.com/a/7537348/627473). Seems to work great.

But I need to find a way to execute code for a very specific condition. The code can only execute if the database supports FULL TEXT indexing for innodb. This happens in mysql 5.6.4 or greater. BUT I also need this to work for maria db other forks of mysql.

I am sure I could find a way for mysql version; but I don't really like relying on a version and would rather relying on feature detection.

Is there a way to do this?

EDIT: Here is code. I just have on more error that I am not sure can be fixed.

In phpMyAdmin the error "#2014 - Commands out of sync; you can't run this command now" is displayed. This does NOT happen from CLI. Is there a way around this?

delimiter //

create procedure update_phppos() 

begin
set @supports_ft := (SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size');

if  @supports_ft > 0 then 

    REPLACE INTO `phppos_app_config` (`key`, `value`) VALUES ('version', '15.0');

    else
        SELECT 'An error has occured. You MUST have mysql 5.6.4 or higher to update php point of sale' as 'ERROR';
    end if;
end;

//

delimiter ;

-- Execute the procedure
call update_phppos();

-- Drop the procedure
drop procedure update_phppos;
  • select @@version; will return the exact version. – ypercubeᵀᴹ Aug 16 '15 at 22:56
  • You could also check with select @@innodb_ft_cache_size;. If you have version previous to 5.6.4, you'll get an error ;) – ypercubeᵀᴹ Aug 16 '15 at 23:01
  • @ypercube is there a way to NOT raise an error so I can give a message? something like if it is set or it does NOT exist? – Chris Muench Aug 16 '15 at 23:22
  • Sorry, no time now to post a proper answer, someone else may write this or give you a better option. You can catch the error though and execute accordingly. See this for examples of handling errors: mysqltutorial.org/mysql-error-handling-in-stored-procedures – ypercubeᵀᴹ Aug 16 '15 at 23:47
  • I tired adding error handling with no luck. Code above – Chris Muench Aug 17 '15 at 0:35
2

Plan A: Notice how you can comment out things if the version is older than something: (I am running 5.6.12.)

mysql> SELECT /*!50604 '5.6.4 is running', */ 'new or old';
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+
| 5.6.4 is running | new or old |
+------------------+------------+

mysql> SELECT /*!50700 '5.7 is running', */ 'new or old';
+------------+
| new or old |
+------------+
| new or old |
+------------+

You can see lots of examples in the output of mysqldump.

Plan B: You could also parse @@version inside a Stored Procedure (or client code).

Plan C: Or, maybe this

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
        WHERE Variable_name = 'Junk';
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES
      WHERE Variable_name = 'innodb_ft_cache_size';
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
  • Thank you so much. I am getting closer. Is information_schema always available in mysql 5 and newer for ALL users. Are there any permission issues? Do you any documentation to point me to? Also; if you look at the edit you will see I still have one error in phpMyAdmin (A lot of my users have this program) – Chris Muench Aug 17 '15 at 1:12
  • See if replacing the set with this would avoid the problem: SELECT @supports_ft := COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size'; – Rick James Aug 17 '15 at 1:53
  • That didn't seem to fix it. (I still get the error; even in mysql 5.6 (The error was only happening in mysql 5.5 before this change) – Chris Muench Aug 17 '15 at 2:06
  • SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES WHERE Variable_name = 'innodb_ft_cache_size'; does NOT work in mysql 5.7, Is there another way? – Chris Muench Aug 29 '15 at 15:37
  • SHOW VARIABLES LIKE 'innodb_ft_cache_size'; – Rick James Aug 29 '15 at 18:12
0

EDIT: The only issue wit this is that it only works with ROOT user in mysql 5.7. Does anyone know of an other way?

Works on mysql 5.5,5.6,5.7

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;

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.