Debian Codegraph @

mysql-test/suite/innodb/t/innodb-system-table-view.test

raw
1 # This is the test for Information Schema System Table View
2 # that displays the InnoDB system table content through
3 # information schema tables.
4
5 --source include/innodb_page_size_small.inc
6
7 SET @save_stats_persistent = @@GLOBAL.innodb_stats_persistent;
8 SET GLOBAL innodb_stats_persistent = 0;
9
10 LET $MYSQLD_DATADIR = `select @@datadir`;
11 LET $INNODB_PAGE_SIZE = `select @@innodb_page_size`;
12
13 # The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats may
14 # vary depending on whether the tables have been rebuilt
15 # by previously run tests.
16 --disable_cursor_protocol
17 SELECT table_id INTO @table_stats_id FROM information_schema.innodb_sys_tables
18 WHERE name = 'mysql/innodb_table_stats';
19 SELECT table_id INTO @index_stats_id FROM information_schema.innodb_sys_tables
20 WHERE name = 'mysql/innodb_index_stats';
21 --enable_cursor_protocol
22
23 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
24 WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY table_id;
25
26 SELECT table_id,pos,mtype,prtype,len,name
27 FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
28 WHERE table_id NOT IN (@table_stats_id, @index_stats_id)
29 ORDER BY table_id, pos;
30
31 --replace_column 1 # 3 # 6 # 7 #
32 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES
33 WHERE table_id NOT IN (@table_stats_id, @index_stats_id) ORDER BY index_id;
34
35 SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS
36 WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name')
37 ORDER BY index_id, pos;
38
39 --sorted_result
40 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
41 --sorted_result
42 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
43
44 CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
45 CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
46 CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb KEY_BLOCK_SIZE=2;
47 CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
48
49 --source suite/innodb/include/show_i_s_tables.inc
50 --source suite/innodb/include/show_i_s_tablespaces.inc
51
52 DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
53
54 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
55
56 # Create a foreign key constraint, and verify the information
57 # in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN and
58 # INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
59 CREATE TABLE parent (id INT NOT NULL,
60 PRIMARY KEY (id)) ENGINE=INNODB;
61
62 CREATE TABLE child (id INT, parent_id INT,
63 INDEX par_ind (parent_id),
64 CONSTRAINT constraint_test
65 FOREIGN KEY (parent_id) REFERENCES parent(id)
66 ON DELETE CASCADE) ENGINE=INNODB;
67
68 --sorted_result
69 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
70
71 --sorted_result
72 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
73
74 # Insert a row in the table "parent", and see whether that reflected in
75 # INNODB_SYS_TABLESTATS
76 INSERT INTO parent VALUES(1);
77
78 --source include/wait_all_purged.inc
79
80 --sorted_result
81 SELECT name, num_rows, ref_count
82 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
83 WHERE name LIKE "%parent";
84
85 --sorted_result
86 SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
87 WHERE name NOT LIKE 'sys/%';
88
89 --sorted_result
90 SELECT name, n_fields
91 from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
92 WHERE table_id In (SELECT table_id from
93 INFORMATION_SCHEMA.INNODB_SYS_TABLES
94 WHERE name LIKE "%parent%");
95
96 --sorted_result
97 SELECT name, n_fields
98 from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
99 WHERE table_id In (SELECT table_id from
100 INFORMATION_SCHEMA.INNODB_SYS_TABLES
101 WHERE name LIKE "%child%");
102
103 --sorted_result
104 SELECT name, pos, mtype, len
105 from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
106 WHERE table_id In (SELECT table_id from
107 INFORMATION_SCHEMA.INNODB_SYS_TABLES
108 WHERE name LIKE "%child%");
109
110 DROP TABLE child;
111
112 DROP TABLE parent;
113
114 # Create table with 2 columns in the foreign key constraint
115 CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
116 PRIMARY KEY (id, newid)) ENGINE=INNODB;
117
118 CREATE TABLE child (id INT, parent_id INT,
119 INDEX par_ind (parent_id),
120 CONSTRAINT constraint_test
121 FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
122 ON DELETE CASCADE) ENGINE=INNODB;
123
124 --sorted_result
125 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
126
127 --sorted_result
128 SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
129
130 INSERT INTO parent VALUES(1, 9);
131
132 # Nested query will open the table handle twice
133 --sorted_result
134 SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
135
136 --source include/wait_all_purged.inc
137
138 --sorted_result
139 SELECT name, num_rows, ref_count
140 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
141 WHERE name LIKE "%parent";
142
143 DROP TABLE child;
144
145 DROP TABLE parent;
146
147 --echo #
148 --echo # MDEV-29479 I_S.INNODB_SYS_TABLESPACES doesn't have
149 --echo # temporary tablespace information
150 --echo #
151 SELECT SPACE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES WHERE name like 'innodb_temporary';
152
153 SET GLOBAL innodb_stats_persistent = @save_stats_persistent;