Debian Codegraph @

mysql-test/suite/innodb/t/sp_temp_table.test

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