Details
-
Bug
-
Status: Done
-
Medium
-
Resolution: Fixed
-
8.0.17-8
-
None
Description
Given a stored procedure such as:
CREATE DEFINER=`root`@`localhost` FUNCTION `handler_func2`() RETURNS int(11) begin declare exit handler for sqlexception return null; select fake_col into @a from fake_db.fake_table; return 1; end
Where fake_db is non-existent objects, the exit handler is not triggered but an error occurs. Handler for non-existent fake_table like below is handled correctly:
CREATE DEFINER=`root`@`localhost` FUNCTION `handler_func`() RETURNS int(11) begin declare exit handler for sqlexception return null; select fake_col into @a from fake_table; return 1; end mysql> select handler_func(); +----------------+ | handler_func() | +----------------+ | NULL | +----------------+ 1 row in set (0.00 sec)
How to repeat:
mysql> show create function handler_func2\G *************************** 1. row *************************** Function: handler_func2 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `handler_func2`() RETURNS int(11) begin declare exit handler for sqlexception return null; select fake_col into @a from fake_db.fake_table; return 1; end character_set_client: latin1 collation_connection: latin1_swedish_ci Database Collation: utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 6 rows in set (0.00 sec) mysql> select handler_func2(); ERROR 1049 (42000): Unknown database 'fake_db'
Suggested fix:
Avoid the error if DECLARE ... HANDLER is configured to catch sqlexceptions.