[MDEV-6501] Failing assertion with TEMP table for a procedure called from a function Created: 2014-07-28  Updated: 2015-02-12  Resolved: 2015-02-12

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 5.5.38
Fix Version/s: 5.5.42

Type: Bug Priority: Major
Reporter: Markus Frosch Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: crash, function, procedure, temporary, upstream-fixed
Environment:

Debian/jessie - ONLY tested with MySQL (5.5.37, 5.5.38, 5.5.40, 5.6.21, 5.7.4)

Issue Links:
PartOf
is part of MDEV-7564 5.5.42 merge Closed

 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



 Comments   
Comment by Markus Frosch [ 2014-07-28 ]

MySQL Bug: http://bugs.mysql.com/bug.php?id=73412

Comment by Markus Frosch [ 2014-09-28 ]

Update from MySQL.com:

Updated by: Daniel Price
Reported by: Markus Frosch
Category: Server: DML
Severity: S2 (Serious)
-Status: Verified
+Status: Closed
Version: 5.5.37, 5.5.38, 5.5.40, 5.6.21, 5.7.4
OS: Linux
OS Details: Debian/jessie
Tags: FUNCTION, PROCEDURE, temporary table

[26 Sep 18:20] Daniel Price

Fixed as of the upcoming 5.5.41, 5.6.22, 5.7.6 release, and here's the
changelog entry:

A procedure, which was called from a function to perform an operation
on
a temporary table, caused the server to halt.

Thank you for the bug report.

Comment by Sergei Golubchik [ 2014-12-19 ]

Fixed in InnoDB in 5.5.41. XtraDB is lagging behind a bit, there is no XtraDB-5.5.41 release yet.

Generated at Thu Feb 08 07:12:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.