Debian Codegraph @

mysql-test/suite/innodb/r/innodb_bug84958.result

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