1--echo #
2--echo # Bug #84958 InnoDB's MVCC has O(N^2) behaviors
3--echo # https://bugs.mysql.com/bug.php?id=84958
4--echo #
5--echo # Set up the test with a procedure and a function.
6--echo #
7
8--source include/have_innodb.inc
9SET @saved_frequency= @@GLOBAL.innodb_purge_rseg_truncate_frequency;
10SET GLOBAL innodb_purge_rseg_truncate_frequency= 1;
11
12DELIMITER ~~;
13CREATE PROCEDURE insert_n(start int, end int)
14BEGIN
15    DECLARE i INT DEFAULT start;
16    START TRANSACTION;
17    WHILE i <= end do
18        INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = i;
19        SET i = i + 1;
20    END WHILE;
21    COMMIT;
22END~~
23
24CREATE FUNCTION num_pages_get()
25RETURNS INT
26BEGIN
27    DECLARE ret INT;
28    SELECT variable_value INTO ret
29    FROM information_schema.global_status
30    WHERE variable_name = 'innodb_buffer_pool_read_requests';
31    RETURN ret;
32END~~
33DELIMITER ;~~
34
35--echo #
36--echo # Create a table with one record in it and start an RR transaction
37--echo #
38CREATE TABLE t1 (a INT, b INT, c INT, PRIMARY KEY(a,b), KEY (b,c))
39ENGINE=InnoDB;
40BEGIN;
41SELECT * FROM t1;
42
43--echo #
44--echo # Create 100 newer record versions in con2 and con3
45--echo #
46connect (con2, localhost, root,,);
47connection con2;
48INSERT INTO t1 VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE c = NULL;
49--send CALL insert_n(1, 50);
50
51connect (con3, localhost, root,,);
52connection con3;
53--send CALL insert_n(51, 100);
54
55connection con2;
56reap;
57connection con3;
58reap;
59INSERT INTO t1 VALUES (1, 2, 1) ON DUPLICATE KEY UPDATE c = NULL;
60
61connection default;
62
63--echo #
64--echo # Connect to default and record how many pages were accessed
65--echo # when selecting the record using the secondary key.
66--echo #
67--let $wait_all_purged=4
68--source include/wait_all_purged.inc
69SET @num_pages_1 = num_pages_get();
70SELECT * FROM t1 force index (b);
71SET @num_pages_2= num_pages_get();
72
73SELECT IF(@num_pages_2 - @num_pages_1 < 5000, 'OK', @num_pages_2 - @num_pages_1) num_pages_diff;
74
75--echo #
76--echo # Commit and show the final record.
77--echo #
78SELECT * FROM t1;
79SELECT * FROM t1 force index (b);
80COMMIT;
81SELECT * FROM t1 force index (b);
82SELECT * FROM t1;
83CHECK TABLE t1;
84
85--echo #
86--echo # Cleanup
87--echo #
88disconnect con2;
89disconnect con3;
90SET GLOBAL innodb_purge_rseg_truncate_frequency= @saved_frequency;
91DROP TABLE t1;
92DROP PROCEDURE insert_n;
93DROP FUNCTION num_pages_get;
94