1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
|
--source include/have_innodb.inc
--source include/big_test.inc
--echo #
--echo # Bug #19306524 FAILING ASSERTION WITH TEMP TABLE FOR A PROCEDURE
--echo # CALLED FROM A FUNCTION
--echo #
call mtr.add_suppression("MySQL is trying to drop table");
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;
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$$
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);
SELECT SUM(value) INTO result FROM cachedata WHERE
object_id = obj_id
AND start = start
AND end = end;
RETURN result;
END$$
DELIMITER ;$$
let $i = 30;
while ($i)
{
SELECT get_cache(1, '2014-01-01', '2014-02-01');
select sleep(1);
dec $i;
}
DROP FUNCTION get_cache;
DROP PROCEDURE cachedata;
|
