1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
# This is the test for Information Schema System Table View
# that displays the InnoDB system table content through
# information schema tables.
--source include/have_innodb.inc
--disable_query_log
SET default_storage_engine=InnoDB;
let $MYSQLD_DATADIR= `select @@datadir`;
# These values can change during the test
LET $innodb_file_format_orig=`select @@innodb_file_format`;
LET $innodb_file_per_table_orig=`select @@innodb_file_per_table`;
--enable_query_log
# The IDs of mysql.innodb_table_stats and mysql.innodb_index_stats are
# unpredictable, probably they depend on whether mtr has created the
# database for this test from scratch or is using a previously created
# database where those tables have been dropped and recreated. If we can
# force mtr to use a freshly created database for this test then the following
# complications can be removed and the test be reverted to the version
# it was before the patch that adds this comment.
--let $table_stats_id = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/innodb_table_stats'`
--let $index_stats_id = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/innodb_index_stats'`
--let $rep_table_1 = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/slave_master_info'`
--let $rep_table_2 = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/slave_relay_log_info'`
--let $rep_table_3 = `SELECT table_id FROM information_schema.innodb_sys_tables WHERE name = 'mysql/slave_worker_info'`
--disable_query_log
--eval SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE table_id NOT IN ($table_stats_id, $index_stats_id, $rep_table_1, $rep_table_2, $rep_table_3) ORDER BY table_id;
--eval SELECT table_id,pos,mtype,prtype,len,name FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS WHERE table_id NOT IN ($table_stats_id, $index_stats_id, $rep_table_1, $rep_table_2, $rep_table_3) ORDER BY table_id, pos;
# The SELECT * version of the query below has been moved to innodb_4k,
# innodb_8k & innodb_16k since the root page number changes with page size.
--eval SELECT index_id,table_id,type,n_fields,space,name FROM INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE table_id NOT IN ($table_stats_id, $index_stats_id, $rep_table_1, $rep_table_2, $rep_table_3) ORDER BY index_id;
--enable_query_log
SELECT index_id,pos,name FROM INFORMATION_SCHEMA.INNODB_SYS_FIELDS
WHERE name NOT IN ('database_name', 'table_name', 'index_name', 'stat_name', 'id', 'host', 'port')
ORDER BY index_id, pos;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
SET GLOBAL innodb_file_format=`Barracuda`;
SET GLOBAL innodb_file_per_table=ON;
--disable_warnings
DROP TABLE IF EXISTS t_redundant, t_compact, t_compressed, t_dynamic;
--enable_warnings
CREATE TABLE t_redundant (a INT KEY, b TEXT) ROW_FORMAT=REDUNDANT ENGINE=innodb;
CREATE TABLE t_compact (a INT KEY, b TEXT) ROW_FORMAT=COMPACT ENGINE=innodb;
CREATE TABLE t_compressed (a INT KEY, b TEXT) ROW_FORMAT=COMPRESSED ENGINE=innodb;
CREATE TABLE t_dynamic (a INT KEY, b TEXT) ROW_FORMAT=DYNAMIC ENGINE=innodb;
--sorted_result
SELECT name, n_cols, file_format, row_format
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE space > 0;
--sorted_result
SELECT name, file_format, row_format
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESPACES
WHERE name LIKE 'test%';
--replace_result ./ MYSQLD_DATADIR/ $MYSQLD_DATADIR MYSQLD_DATADIR
--sorted_result
SELECT path FROM INFORMATION_SCHEMA.INNODB_SYS_DATAFILES
WHERE path LIKE '%test%';
DROP TABLE t_redundant, t_compact, t_compressed, t_dynamic;
--disable_query_log
EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig;
EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
--enable_query_log
SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS;
# Create a foreign key constraint, and verify the information
# in INFORMATION_SCHEMA.INNODB_SYS_FOREIGN and
# INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS
CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE) ENGINE=INNODB;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
# Insert a row in the table "parent", and see whether that reflected in
# INNODB_SYS_TABLESTATS
INSERT INTO parent VALUES(1);
--sorted_result
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
--sorted_result
SELECT NAME, FLAG, N_COLS FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;
--sorted_result
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%parent%");
--sorted_result
SELECT name, n_fields
from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
--sorted_result
SELECT name, pos, mtype, len
from INFORMATION_SCHEMA.INNODB_SYS_COLUMNS
WHERE table_id In (SELECT table_id from
INFORMATION_SCHEMA.INNODB_SYS_TABLES
WHERE name LIKE "%child%");
DROP TABLE child;
DROP TABLE parent;
# Create table with 2 columns in the foreign key constraint
CREATE TABLE parent (id INT NOT NULL, newid INT NOT NULL,
PRIMARY KEY (id, newid)) ENGINE=INNODB;
CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
CONSTRAINT constraint_test
FOREIGN KEY (id, parent_id) REFERENCES parent(id, newid)
ON DELETE CASCADE) ENGINE=INNODB;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN;
--sorted_result
SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN_COLS;
INSERT INTO parent VALUES(1, 9);
# Nested query will open the table handle twice
--sorted_result
SELECT * FROM parent WHERE id IN (SELECT id FROM parent);
--sorted_result
SELECT name, num_rows, ref_count
FROM INFORMATION_SCHEMA.INNODB_SYS_TABLESTATS
WHERE name LIKE "%parent";
DROP TABLE child;
DROP TABLE parent;
--disable_query_log
EVAL SET GLOBAL innodb_file_format=$innodb_file_format_orig;
EVAL SET GLOBAL innodb_file_per_table=$innodb_file_per_table_orig;
--enable_query_log
|
