Bug #97682 Handler fails to trigger on Error 1049 or SQLSTATE 42000 or plain sqlexception

Submitted: 19 Nov 2019 3:08 Modified: 15 Jan 2020 2:39
Reporter: Jericho Rivera Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:8.0.18, 8.0.11 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[19 Nov 2019 3:08] Jericho Rivera 
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           |
+--------------------+
| cprDB              |
| 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.
[19 Nov 2019 11:48] MySQL Verification Team 
Hello Jericho Rivera,

Thank you for the report and test case.
Observed that 8.0.18(lowest checked 8.0.11, issue since initial DMR releases) is affected.

regards,
Umesh
[19 Nov 2019 11:49] MySQL Verification Team 
-- 8.0.18 (same in 8.0.11 and even in DMR releases)

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set global log_bin_trust_function_creators=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func2//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func2();
ERROR 1049 (42000): Unknown database 'fake_db'
mysql> show errors;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Error | 1049 | Unknown database 'fake_db' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Error | 1049 | Unknown database 'fake_db' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func();
+----------------+
| handler_func() |
+----------------+
|           NULL |
+----------------+
1 row in set (0.01 sec)

- dmr

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 8.0.1-dmr MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func2//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func2();
ERROR 1049 (42000): Unknown database 'fake_db'
mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func();
+----------------+
| handler_func() |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

mysql>
[19 Nov 2019 11:50] MySQL Verification Team 
- Looks like regression to me

-- 5.7.28

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func2//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func2();
+-----------------+
| handler_func2() |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func();
+----------------+
| handler_func() |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

-- 5.6.46

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.46 MySQL Community Server (GPL)

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func2//
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql> 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//
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func2();
+-----------------+
| handler_func2() |
+-----------------+
|            NULL |
+-----------------+
1 row in set (0.01 sec)

mysql> create database if not exists test;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use test;
Database changed
mysql> DELIMITER //
mysql> DROP PROCEDURE IF EXISTS handler_func//
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> 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//
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> DELIMITER ;
mysql> select handler_func();
+----------------+
| handler_func() |
+----------------+
|           NULL |
+----------------+
1 row in set (0.00 sec)

mysql>
[24 Nov 2019 8:17] Sveta Smirnova 
Bug is not repeatable with version 5.7
[3 Dec 2019 11:52] Kamil Holubicki 
Proposed fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: PS-6094.patch (text/x-patch), 3.91 KiB.
[3 Dec 2019 11:57] MySQL Verification Team 
Thank you Sveta for the feedback, updated 5.7 results for now.
Thank you Kamil for the contribution.

regards,
Umesh
[15 Jan 2020 2:39] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.20.

Within a stored program with an error handler defined for the error
condition of accessing a nonexistent table, the handler was not
invoked if the table was nonexistent because it was named in a
nonexistent database.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.056 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.