Debian Codegraph @

mysql-test/suite/funcs_1/t/is_statistics_cs.test

raw
1 # suite/funcs_1/t/is_statistics.test
2 #
3 # Check the layout of information_schema.statistics and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA ... on its content.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing tables
9 # within the databases information_schema and mysql
10 # - for checking storage engine properties
11 # Therefore please do not alter $engine_type and $other_engine_type.
12 #
13 # Author:
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 # Create this script based on older scripts and new code.
17 #
18
19
20 # Result differences depending on FS case sensitivity.
21 if (!$require_case_insensitive_file_system)
22 {
23 --source include/have_case_sensitive_file_system.inc
24 }
25
26 let $engine_type = InnoDB;
27 let $other_engine_type = InnoDB;
28
29 let $is_table = STATISTICS;
30
31 # The table INFORMATION_SCHEMA.STATISTICS must exist
32 eval SHOW TABLES FROM information_schema LIKE '$is_table';
33
34 --echo #######################################################################
35 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
36 --echo #######################################################################
37 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
38 # statement, just as if it were an ordinary user-defined table.
39 #
40 --source suite/funcs_1/datadict/is_table_query.inc
41
42
43 --echo #########################################################################
44 --echo # Testcase 3.2.14.1: INFORMATION_SCHEMA.STATISTICS layout
45 --echo #########################################################################
46 # Ensure that the INFORMATION_SCHEMA.STATISTICS table has the following columns,
47 # in the following order:
48 #
49 # TABLE_CATALOG (always shows NULL),
50 # TABLE_SCHEMA (shows the database, or schema, in which a table indexed by
51 # an accessible index resides),
52 # TABLE_NAME (shows the name of the indexed table),
53 # NON_UNIQUE (shows whether the index may contain duplicate values;
54 # 0 if it cannot, 1 if it can),
55 # INDEX_SCHEMA (shows the database, or schema, in which an accessible
56 # index resides),
57 # INDEX_NAME (shows the name of an index which the current user may access),
58 # SEQ_IN_INDEX (shows the ordinal position of an indexed column within
59 # the index),
60 # COLUMN_NAME (shows the name of a column that comprises some, or all, of an
61 # index key),
62 # COLLATION (shows how the column is sorted in the index; either A for ascending
63 # or NULL for unsorted columns),
64 # CARDINALITY (shows the number of unique values in the index),
65 # SUB_PART (shows the number of indexed characters if the index is
66 # a prefix index),
67 # PACKED (shows how the index key is packed),
68 # NULLABLE (shows whether the index column may contain NULL values),
69 # INDEX_TYPE (shows the index type; either BTREE, FULLTEXT, HASH or RTREE),
70 # COMMENT (shows a comment on the index, if any).
71 #
72 --source suite/funcs_1/datadict/datadict_bug_12777.inc
73 eval DESCRIBE information_schema.$is_table;
74 --source suite/funcs_1/datadict/datadict_bug_12777.inc
75 eval SHOW CREATE TABLE information_schema.$is_table;
76 --source suite/funcs_1/datadict/datadict_bug_12777.inc
77 eval SHOW COLUMNS FROM information_schema.$is_table;
78
79 # Note: Retrieval of information within information_schema.columns about
80 # information_schema.statistics is in is_columns_is.test.
81
82 # Show that TABLE_CATALOG is always NULL.
83 --sorted_result
84 SELECT table_catalog, table_schema, table_name, index_schema, index_name
85 FROM information_schema.statistics WHERE table_catalog IS NOT NULL AND
86 table_name != 'ndb_binlog_index'
87 ORDER BY table_schema, table_name, index_schema, index_name;
88
89
90 --echo ####################################################################################
91 --echo # Testcase 3.2.14.2 + 3.2.14.3: INFORMATION_SCHEMA.STATISTICS accessible information
92 --echo ####################################################################################
93 # 3.2.14.2 Ensure that the table shows the relevant information on every index
94 # which the current user or PUBLIC may access (usually because
95 # privileges on the indexed table have been granted).
96 # 3.2.14.3 Ensure that the table does not show any information on any indexes
97 # which the current user and PUBLIC may not access.
98 #
99 # Note: Check of content within information_schema.statistics about
100 # database is in
101 # mysql is_statistics_mysql.test
102 # information_schema is_statistics_is.test
103 #
104 --disable_warnings
105 DROP DATABASE IF EXISTS db_datadict;
106 DROP DATABASE IF EXISTS db_datadict_2;
107 --enable_warnings
108 CREATE DATABASE db_datadict;
109 CREATE DATABASE db_datadict_2;
110
111 --error 0,ER_CANNOT_USER
112 DROP USER 'testuser1'@'localhost';
113 CREATE USER 'testuser1'@'localhost';
114 --error 0,ER_CANNOT_USER
115 DROP USER 'testuser2'@'localhost';
116 CREATE USER 'testuser2'@'localhost';
117
118 --replace_result $engine_type <engine_type>
119 eval
120 CREATE TABLE db_datadict.t1
121 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
122 ENGINE = $engine_type;
123 --replace_result $engine_type <engine_type>
124 eval
125 CREATE TABLE db_datadict.t2
126 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
127 ENGINE = $engine_type;
128
129 eval
130 CREATE TABLE db_datadict_2.t3
131 (f1 INT NOT NULL, f2 INT, f5 DATE,
132 PRIMARY KEY(f1), INDEX f2f1_ind(f2,f1), UNIQUE(f5))
133 ENGINE = $engine_type;
134 eval
135 CREATE TABLE db_datadict_2.t4
136 (f1 INT NOT NULL, PRIMARY KEY(f1), f2 INT, INDEX f2_ind(f2))
137 ENGINE = $engine_type;
138
139 let $my_select = SELECT * FROM information_schema.statistics
140 WHERE table_schema LIKE 'db_datadict%'
141 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
142 let $my_show1 = SHOW GRANTS FOR 'testuser1'@'localhost';
143 let $my_show2 = SHOW GRANTS FOR 'testuser2'@'localhost';
144 eval $my_select;
145 --sorted_result
146 eval $my_show1;
147 --sorted_result
148 eval $my_show2;
149
150 --echo # Establish connection testuser1 (user=testuser1)
151 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
152 connect (testuser1, localhost, testuser1, , test);
153 # nothing visible for testuser1
154 eval $my_select;
155 --sorted_result
156 eval $my_show1;
157 --error ER_TABLEACCESS_DENIED_ERROR
158 eval $my_show2;
159
160 --echo # Establish connection testuser2 (user=testuser2)
161 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
162 connect (testuser2, localhost, testuser2, , test);
163 # nothing visible for testuser2
164 eval $my_select;
165 --error ER_TABLEACCESS_DENIED_ERROR
166 eval $my_show1;
167 --sorted_result
168 eval $my_show2;
169
170 --echo # Switch to connection default
171 connection default;
172 GRANT SELECT ON db_datadict.t1 TO 'testuser1'@'localhost' WITH GRANT OPTION;
173 GRANT SELECT(f1,f5) ON db_datadict_2.t3 TO 'testuser1'@'localhost';
174 eval $my_select;
175 --sorted_result
176 eval $my_show1;
177 --sorted_result
178 eval $my_show2;
179
180 --echo # Switch to connection testuser1
181 connection testuser1;
182 eval $my_select;
183 --sorted_result
184 eval $my_show1;
185 --error ER_TABLEACCESS_DENIED_ERROR
186 eval $my_show2;
187
188 --echo # Switch to connection testuser2
189 connection testuser2;
190 eval $my_select;
191 --error ER_TABLEACCESS_DENIED_ERROR
192 eval $my_show1;
193 --sorted_result
194 eval $my_show2;
195
196 --echo # Switch to connection default
197 connection default;
198 REVOKE SELECT,GRANT OPTION ON db_datadict.t1 FROM 'testuser1'@'localhost';
199 --sorted_result
200 eval $my_show1;
201
202 --echo # Switch to connection testuser1
203 # nothing visible for testuser1
204 connection testuser1;
205 eval $my_select;
206 --sorted_result
207 eval $my_show1;
208
209 # Cleanup
210 --echo # Switch to connection default and close connections testuser1, testuser2
211 connection default;
212 disconnect testuser1;
213 disconnect testuser2;
214 DROP USER 'testuser1'@'localhost';
215 DROP USER 'testuser2'@'localhost';
216 DROP DATABASE db_datadict;
217 DROP DATABASE db_datadict_2;
218
219
220 --echo #########################################################################
221 --echo # 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.STATISTICS modifications
222 --echo #########################################################################
223 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
224 # column) automatically inserts all relevant information on that
225 # object into every appropriate INFORMATION_SCHEMA table.
226 # 3.2.1.14: Ensure that the alteration of any existing database object
227 # automatically updates all relevant information on that object in
228 # every appropriate INFORMATION_SCHEMA table.
229 # 3.2.1.15: Ensure that the dropping of any existing database object
230 # automatically deletes all relevant information on that object from
231 # every appropriate INFORMATION_SCHEMA table.
232 #
233 --disable_warnings
234 DROP TABLE IF EXISTS test.t1_my_table;
235 DROP DATABASE IF EXISTS db_datadict;
236 --enable_warnings
237 CREATE DATABASE db_datadict;
238 --replace_result $other_engine_type <other_engine_type>
239 eval
240 CREATE TABLE test.t1_1 (f1 BIGINT,
241 f2 TEXT, f2x TEXT, f3 CHAR(10), f3x CHAR(10), f4 BIGINT, f4x BIGINT,
242 f5 POINT, f5x POINT NOT NULL)
243 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
244 ENGINE = $other_engine_type;
245 --replace_result $engine_type <engine_type>
246 eval
247 CREATE TABLE test.t1_2 (f1 BIGINT, f2 BIGINT)
248 ENGINE = $engine_type;
249
250 # Tables without primary key or index do not show up
251 # in information_schema.statistics.
252 SELECT table_name FROM information_schema.statistics
253 WHERE table_name LIKE 't1_%';
254 # Check ADD PRIMARY KEY (two columns)
255 ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1,f3);
256 SELECT * FROM information_schema.statistics
257 WHERE table_name LIKE 't1_%'
258 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
259 # Check DROP PRIMARY KEY
260 ALTER TABLE test.t1_1 DROP PRIMARY KEY;
261 SELECT table_name FROM information_schema.statistics
262 WHERE table_name LIKE 't1_%';
263 # Check ADD PRIMARY KEY (one column)
264 ALTER TABLE test.t1_1 ADD PRIMARY KEY (f1);
265 SELECT * FROM information_schema.statistics
266 WHERE table_name LIKE 't1_%';
267 # Some variations on index definitions
268 # 1. No name assigned, one column
269 ALTER TABLE test.t1_1 ADD INDEX (f4);
270 # 2. Name assigned, two columns
271 CREATE INDEX f3_f1 ON test.t1_1 (f3,f1);
272 # 3. Unique index
273 CREATE UNIQUE INDEX f4x_uni ON test.t1_1 (f4x);
274 # 4. Index using HASH
275 CREATE INDEX f2_hash USING HASH ON test.t1_2 (f2);
276 # 5. Index with comment (feature introduced in 5.2)
277 --error ER_PARSE_ERROR
278 CREATE INDEX f1_idx ON test.t1_2 (f1) COMMENT = 'COMMENT';
279 # 6. NOT NULL
280 CREATE INDEX not_null ON test.t1_1 (f3x);
281 # 7. Prefix index
282 CREATE INDEX f2_prefix ON test.t1_1 (f2(20));
283 #
284 SELECT * FROM information_schema.statistics
285 WHERE table_name LIKE 't1_%' AND index_name <> 'PRIMARY'
286 ORDER BY table_schema,table_name,index_name,seq_in_index,column_name;
287 --horizontal_results
288 DROP TABLE test.t1_2;
289 #
290 # Check modification of TABLE_NAME
291 SELECT DISTINCT table_name FROM information_schema.statistics
292 WHERE table_name = 't1_1';
293 RENAME TABLE test.t1_1 TO test.t1_1x;
294 SELECT DISTINCT table_name FROM information_schema.statistics
295 WHERE table_name = 't1_1x';
296 #
297 # Check modification of TABLE_SCHEMA
298 SELECT DISTINCT table_schema,table_name FROM information_schema.statistics
299 WHERE table_name LIKE 't1_1%';
300 RENAME TABLE test.t1_1x TO db_datadict.t1_1x;
301 SELECT DISTINCT table_schema,table_name FROM information_schema.statistics
302 WHERE table_name LIKE 't1_1%';
303 #
304 # Check impact of DROP TABLE
305 SELECT DISTINCT table_name FROM information_schema.statistics
306 WHERE table_name = 't1_1x';
307 DROP TABLE db_datadict.t1_1x;
308 SELECT DISTINCT table_name FROM information_schema.statistics
309 WHERE table_name = 't1_1x';
310 #
311 # Check a temporary table (not visible)
312 --replace_result $engine_type <engine_type>
313 eval
314 CREATE TEMPORARY TABLE test.t1_1x (PRIMARY KEY(f1,f2))
315 ENGINE = $engine_type
316 AS SELECT 1 AS f1, 2 AS f2;
317 --vertical_results
318 SELECT * FROM information_schema.statistics
319 WHERE table_name = 't1_1x';
320 --horizontal_results
321 DROP TEMPORARY TABLE test.t1_1x;
322 #
323 # Check impact of DROP SCHEMA
324 --replace_result $engine_type <engine_type>
325 eval
326 CREATE TABLE db_datadict.t1_1x (PRIMARY KEY(f1))
327 ENGINE = $engine_type
328 AS SELECT 1 AS f1, 2 AS f2;
329 SELECT table_name FROM information_schema.statistics
330 WHERE table_name = 't1_1x';
331 DROP DATABASE db_datadict;
332 SELECT table_name FROM information_schema.statistics
333 WHERE table_name = 't1_1x';
334
335
336 --echo ########################################################################
337 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
338 --echo # DDL on INFORMATION_SCHEMA tables are not supported
339 --echo ########################################################################
340 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
341 # INFORMATION_SCHEMA table.
342 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
343 # INFORMATION_SCHEMA table.
344 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
345 # INFORMATION_SCHEMA table.
346 # 3.2.1.8: Ensure that no user may create an index on an
347 # INFORMATION_SCHEMA table.
348 # 3.2.1.9: Ensure that no user may alter the definition of an
349 # INFORMATION_SCHEMA table.
350 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
351 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
352 # other database.
353 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
354 # in an INFORMATION_SCHEMA table.
355 #
356 --disable_warnings
357 DROP DATABASE IF EXISTS db_datadict;
358 --enable_warnings
359 CREATE DATABASE db_datadict;
360 --replace_result $engine_type <engine_type>
361 eval
362 CREATE TABLE db_datadict.t1 (f1 BIGINT)
363 ENGINE = $engine_type;
364
365 --error ER_DBACCESS_DENIED_ERROR
366 INSERT INTO information_schema.statistics
367 SELECT * FROM information_schema.statistics;
368
369 --error ER_DBACCESS_DENIED_ERROR
370 UPDATE information_schema.statistics SET table_schema = 'test'
371 WHERE table_name = 't1';
372
373 --error ER_DBACCESS_DENIED_ERROR
374 DELETE FROM information_schema.statistics WHERE table_name = 't1';
375 --error ER_DBACCESS_DENIED_ERROR
376 TRUNCATE information_schema.statistics;
377
378 --error ER_DBACCESS_DENIED_ERROR
379 CREATE INDEX my_idx_on_statistics
380 ON information_schema.statistics(table_schema);
381
382 --error ER_DBACCESS_DENIED_ERROR
383 ALTER TABLE information_schema.statistics DROP PRIMARY KEY;
384 --error ER_DBACCESS_DENIED_ERROR
385 ALTER TABLE information_schema.statistics ADD f1 INT;
386
387 --error ER_DBACCESS_DENIED_ERROR
388 DROP TABLE information_schema.statistics;
389
390 --error ER_DBACCESS_DENIED_ERROR
391 ALTER TABLE information_schema.statistics RENAME db_datadict.statistics;
392 --error ER_DBACCESS_DENIED_ERROR
393 ALTER TABLE information_schema.statistics RENAME information_schema.xstatistics;
394
395 # Cleanup
396 DROP DATABASE db_datadict;
397