Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6094

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Fixed
    • Affects Version/s: 8.0.17-8
    • Fix Version/s: 8.0.18-9
    • Component/s: 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.

        Attachments

          Activity

            People

            Assignee:
            kamil.holubicki Kamil Holubicki
            Reporter:
            jericho.rivera Jericho Rivera
            Votes:
            1 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 1 day, 5 hours, 40 minutes
                1d 5h 40m

                  Smart Checklist