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

Failing assertion with TEMP table for a procedure called from a function

    XMLWordPrintable

    Details

      Description

      Hey MariaDB Team,
      I've submitted the following bug to MySQL, as I have no MariaDB environment to test it myself, I'm only able to copy / paste here.

      They have hidden their bug report.

      Please mark as hidden as well if it makes sense for you!


      This is a rather complicated problem with crashing the MySQL server, so that mysql_safe has to restart it.

      I'm not sure how to classify the problem, so I try to describe it.

      Choosing severity S2, because the whole mysqld crashes from a user proc.

      Please note: the crash does happen with INNODB being used as engine for a temporary table.

      Context:
      A function is called

      • the function calls a procedure to generate some cache
        • the procedure creates a temporary table (if not exists)
        • table is getting cleaned of outdated rows
        • table is checked for cache data (for the arguments supplied)
        • cache is generated
      • the generated cache is queried and summarized to some extend
      • function returns a value

      The crash:

      140728 14:24:12  InnoDB: Assertion failure in thread 139778932287232 in file row0mysql.c line 1463
      InnoDB: Failing assertion: node->pcur->rel_pos == BTR_PCUR_ON
      InnoDB: We intentionally generate a memory trap.
      ---snip---

      Full stacktrace can be supplied when needed.

      The proc and function is derived from a SLA calculation tool.

      The crash does NOT happen when:

      • ENGINE=MEMORY is used
      • the table is not created as a temporary one

      Regards
      Markus

      -- 
      -- the procedure
      --
       
      DROP PROCEDURE IF EXISTS cachedata;
       
      DELIMITER $$
      CREATE PROCEDURE cachedata(
        IN obj_id BIGINT UNSIGNED,
        IN start  DATETIME,
        IN end    DATETIME
      )
       
      cachedata:BEGIN
        DECLARE cache_count BIGINT;
       
        SET @timestamp := NOW();
       
        CREATE TEMPORARY TABLE IF NOT EXISTS cachedata (
          timestamp    DATETIME,
          object_id    BIGINT UNSIGNED NOT NULL,
          start        DATETIME,
          end          DATETIME,
          seqno        BIGINT AUTO_INCREMENT,
          value        FLOAT,
          PRIMARY KEY (seqno),
          INDEX (timestamp),
          INDEX (object_id, start, end)
        ) ENGINE=INNODB;
       
        DELETE FROM cachedata WHERE
          timestamp < DATE_SUB(@timestamp, INTERVAL 15 SECOND);
       
        SELECT count(*) INTO cache_count FROM cachedata WHERE
          object_id = obj_id
          AND start = start
          AND end = end;
       
        IF cache_count > 0 THEN LEAVE cachedata;
        END IF;
       
        -- some fake cache
        INSERT INTO cachedata (timestamp, object_id, start, end, value) VALUES
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 2345),
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 2345),
          (@timestamp, obj_id, start, end, 1234),
          (@timestamp, obj_id, start, end, 4567),
          (@timestamp, obj_id, start, end, 8901),
          (@timestamp, obj_id, start, end, 2345);
       
      END$$
       
      -- 
      -- the function
      --
       
      DROP FUNCTION IF EXISTS get_cache$$
       
      CREATE FUNCTION get_cache(
        obj_id   BIGINT UNSIGNED,
        start    DATETIME,
        end      DATETIME
      )
        RETURNS FLOAT
        READS SQL DATA
      BEGIN
        DECLARE result FLOAT;
       
        CALL cachedata(obj_id, start, end);
       
        -- calculate something
       
        SELECT SUM(value) INTO result FROM cachedata WHERE
          object_id = obj_id
          AND start = start
          AND end = end;
       
        RETURN result;
      END$$
       
      DELIMITER ;
       
      --
      -- INSTRUCTIONS
      --
       
      -- import the FUNC and PROC above
       
      -- run a query:
      SELECT get_cache(1, '2014-01-01', '2014-02-01');
       
      -- now wait a bit, from 15-60 seconds in my tests
      -- then rerun the query
       
      -- MySQL crashes

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              lazyfrosch Markus Frosch
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: