Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-38611

Procedure statements do not write Explain entries to the slow log

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 12.2, 12.3
    • 12.2
    • Stored routines
    • None
    • Can result in unexpected behaviour

    Description

      CREATE OR REPLACE TABLE t1 (a INT);
      CREATE OR REPLACE TABLE t2 (a INT);
      DELIMITER $$
      CREATE OR REPLACE PROCEDURE p1() 
      BEGIN
        SELECT COUNT(a), SLEEP(0.05) FROM t2;
      END;
      $$
      DELIMITER ;
       
      SET GLOBAL slow_query_log=ON;
      SET SESSION authorization root@localhost;
      SET SESSION log_slow_verbosity='explain';
      SET log_slow_disabled_statements=admin;
      SET long_query_time=0.01;
      USE test;
      SELECT COUNT(a), SLEEP(0.05) FROM t1;
      CALL p1();
      

      # User@Host: root[root] @ localhost []
      # Thread_id: 3  Schema: test  QC_hit: No
      # Query_time: 0.050332  Lock_time: 0.000046  Rows_sent: 1  Rows_examined: 0
      # Rows_affected: 0  Bytes_sent: 105
      #
      # explain: id   select_type     table   type    possible_keys   key     key_len ref     rows    r_rows  filtered        r_filtered      Extra
      # explain: 1    SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    1       0.00    100.00  100.00
      #
      SET timestamp=1768973973;
      SELECT COUNT(a), SLEEP(0.05) FROM t1;
      # User@Host: root[root] @ localhost []
      # Thread_id: 3  Schema: test  QC_hit: No
      # Query_time: 0.050481  Lock_time: 0.000118  Rows_sent: 1  Rows_examined: 0
      # Rows_affected: 0  Bytes_sent: 105
      # Stored_routine: test.p1
      SET timestamp=1768973973;
      SELECT COUNT(a), SLEEP(0.05) FROM t2;
      # User@Host: root[root] @ localhost []
      # Thread_id: 3  Schema: test  QC_hit: No
      # Query_time: 0.050606  Lock_time: 0.000018  Rows_sent: 1  Rows_examined: 0
      # Rows_affected: 0  Bytes_sent: 116
      SET timestamp=1768973973;
      CALL p1();
      

      Notice, the slow log record for the standalone SELECT statement has an explain entry in the log, while the SELECT statement from the procedure does not.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Oleksandr Byelkin Oleksandr Byelkin
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.