| 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: | |
| 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 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.
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.