1--source include/have_innodb.inc 2--source include/big_test.inc 3 4--echo # 5--echo # Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE 6--echo # CALLED FROM A FUNCTION 7--echo # 8 9call mtr.add_suppression("MySQL is trying to drop table"); 10 11DELIMITER $$; 12CREATE PROCEDURE cachedata( 13 IN obj_id BIGINT UNSIGNED, 14 IN start DATETIME, 15 IN end DATETIME 16) 17 18cachedata:BEGIN 19 DECLARE cache_count BIGINT; 20 21 SET @timestamp := NOW(); 22 23 CREATE TEMPORARY TABLE IF NOT EXISTS cachedata ( 24 timestamp DATETIME, 25 object_id BIGINT UNSIGNED NOT NULL, 26 start DATETIME, 27 end DATETIME, 28 seqno BIGINT AUTO_INCREMENT, 29 value FLOAT, 30 PRIMARY KEY (seqno), 31 INDEX (timestamp), 32 INDEX (object_id, start, end) 33 ) ENGINE=INNODB; 34 35 DELETE FROM cachedata WHERE 36 timestamp < DATE_SUB(@timestamp, INTERVAL 15 SECOND); 37 38 SELECT count(*) INTO cache_count FROM cachedata WHERE 39 object_id = obj_id 40 AND start = start 41 AND end = end; 42 43 IF cache_count > 0 THEN LEAVE cachedata; 44 END IF; 45 46 INSERT INTO cachedata (timestamp, object_id, start, end, value) VALUES 47 (@timestamp, obj_id, start, end, 1234), 48 (@timestamp, obj_id, start, end, 4567), 49 (@timestamp, obj_id, start, end, 8901), 50 (@timestamp, obj_id, start, end, 1234), 51 (@timestamp, obj_id, start, end, 4567), 52 (@timestamp, obj_id, start, end, 8901), 53 (@timestamp, obj_id, start, end, 1234), 54 (@timestamp, obj_id, start, end, 4567), 55 (@timestamp, obj_id, start, end, 8901), 56 (@timestamp, obj_id, start, end, 1234), 57 (@timestamp, obj_id, start, end, 4567), 58 (@timestamp, obj_id, start, end, 8901), 59 (@timestamp, obj_id, start, end, 2345), 60 (@timestamp, obj_id, start, end, 1234), 61 (@timestamp, obj_id, start, end, 4567), 62 (@timestamp, obj_id, start, end, 8901), 63 (@timestamp, obj_id, start, end, 2345), 64 (@timestamp, obj_id, start, end, 1234), 65 (@timestamp, obj_id, start, end, 4567), 66 (@timestamp, obj_id, start, end, 8901), 67 (@timestamp, obj_id, start, end, 2345); 68 69END$$ 70 71 72CREATE FUNCTION get_cache( 73 obj_id BIGINT UNSIGNED, 74 start DATETIME, 75 end DATETIME 76) 77 RETURNS FLOAT 78 READS SQL DATA 79BEGIN 80 DECLARE result FLOAT; 81 82 CALL cachedata(obj_id, start, end); 83 84 SELECT SUM(value) INTO result FROM cachedata WHERE 85 object_id = obj_id 86 AND start = start 87 AND end = end; 88 89 RETURN result; 90END$$ 91 92DELIMITER ;$$ 93 94let $i = 30; 95while ($i) 96{ 97 SELECT get_cache(1, '2014-01-01', '2014-02-01'); 98 select sleep(1); 99 dec $i; 100} 101 102DROP FUNCTION get_cache; 103DROP PROCEDURE cachedata; 104