mysql error handling in function

Question

I am trying to do error handling as I can do in stored procedures in mysql but functions don't support to support them. Is there a way I can do something like this?

delimiter //
CREATE FUNCTION supports_ft()
 RETURNS INT
 BEGIN
 DECLARE CONTINUE HANDLER FOR SQLSTATE 'HY000' return 0;
 SELECT @@innodb_ft_cache_size;
 return 1;
END;

Answer 1

Functions support exception handlers, but that won't necessarily catch a blatantly invalid operation that could never succeed, such as what you are trying here, which is an unbounded select. This is only supported in stored procedures, not functions (or triggers).

ERROR 1415 (0A000): Not allowed to return a result set from a function

You need to try to load the system variable's content into a dummy variable. The following should work, unless MySQL tries to validate the variable at function declaration time, which it may or may not... you'll need to verify:

SET @dummy = @@innodb_ft_cache_size;

If validation is done early, you'll need to use a prepared statement to avoid that early validation.

HY000 is a pretty vague sqlstate, and you might be better off with DECLARE CONTINUE HANDLER FOR SQLEXCEPTION.