Debian Codegraph @

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

raw
1 # Exercise the code path for INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS
2 # and INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
3
4
5 -- disable_result_log
6 SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
7
8 # How many buffer pools we have
9 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_POOL_STATS;
10
11 SELECT * FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
12
13 # This gives the over all buffer pool size
14 SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE;
15
16 -- enable_result_log
17
18 # Create a table and check its page info behave correctly in the pool
19 CREATE TABLE infoschema_buffer_test (col1 INT) ENGINE = INNODB;
20
21 INSERT INTO infoschema_buffer_test VALUES(9);
22
23 # We should be able to see this table in the buffer pool if we check
24 # right away
25 SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
26 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
27 WHERE TABLE_NAME like "%infoschema_buffer_test%"
28 and PAGE_STATE="file_page" and PAGE_TYPE="index";
29
30 # The NUMBER_RECORDS and DATA_SIZE should check with each insertion
31 INSERT INTO infoschema_buffer_test VALUES(19);
32
33 SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
34 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
35 WHERE TABLE_NAME like "%infoschema_buffer_test%"
36 and PAGE_STATE="file_page" and PAGE_TYPE="index";
37
38 CREATE INDEX idx ON infoschema_buffer_test(col1);
39
40 SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
41 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
42 WHERE TABLE_NAME like "%infoschema_buffer_test%"
43 and PAGE_STATE="file_page" and INDEX_NAME = "idx" and PAGE_TYPE="index";
44
45 let $space_id = `SELECT space FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
46 WHERE TABLE_NAME like "%infoschema_buffer_test%" GROUP BY space`;
47
48 let $query = SELECT space, is_stale FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
49 WHERE space = $space_id GROUP BY space, is_stale;
50 # There should be no stale pages.
51 --replace_result $space_id {space_id}
52 --eval $query;
53
54 # Check the buffer after dropping the table
55 DROP TABLE infoschema_buffer_test;
56
57 SELECT TABLE_NAME, INDEX_NAME, NUMBER_RECORDS, DATA_SIZE, PAGE_STATE, PAGE_TYPE
58 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
59 WHERE TABLE_NAME like "%infoschema_buffer_test%";
60
61 # There should be only stale pages.
62 --replace_result $space_id {space_id}
63 --eval $query;
64
65 --source suite/innodb/include/evict_everything_from_buffer_pool.inc
66
67
68 # There should no space pages now, neither stale nor not.
69 --replace_result $space_id {space_id}
70 --eval $query;
71
72 # Do one more test
73 #--replace_regex /'*[0-9]*'/'NUM'/
74 CREATE TABLE infoschema_parent (id INT NOT NULL, PRIMARY KEY (id))
75 ENGINE=INNODB;
76
77 CREATE TABLE infoschema_child (id INT, parent_id INT, INDEX par_ind (parent_id),
78 FOREIGN KEY (parent_id)
79 REFERENCES infoschema_parent(id)
80 ON DELETE CASCADE)
81 ENGINE=INNODB;
82
83 SELECT count(*)
84 FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
85 WHERE TABLE_NAME like "%infoschema_child%" and PAGE_STATE="file_page"
86 and PAGE_TYPE="index";
87
88 DROP TABLE infoschema_child;
89 DROP TABLE infoschema_parent;
90