1 SHOW TABLES FROM information_schema LIKE 'COLLATIONS';
2 Tables_in_information_schema (COLLATIONS)
4 #######################################################################
5 # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6 #######################################################################
7 DROP VIEW IF EXISTS test.v1;
8 DROP PROCEDURE IF EXISTS test.p1;
9 DROP FUNCTION IF EXISTS test.f1;
10 CREATE VIEW test.v1 AS SELECT * FROM information_schema.COLLATIONS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLLATIONS;
12 CREATE FUNCTION test.f1() returns BIGINT
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.COLLATIONS;
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.COLLATIONS;
20 SELECT * FROM test.v1;
24 DROP PROCEDURE test.p1;
25 DROP FUNCTION test.f1;
26 #########################################################################
27 # Testcase 3.2.3.1: INFORMATION_SCHEMA.COLLATIONS layout
28 #########################################################################
29 DESCRIBE information_schema.COLLATIONS;
30 Field Type Null Key Default Extra
31 COLLATION_NAME varchar(32) NO
32 CHARACTER_SET_NAME varchar(32) NO
34 IS_DEFAULT varchar(3) NO
35 IS_COMPILED varchar(3) NO
36 SORTLEN bigint(3) NO 0
37 SHOW CREATE TABLE information_schema.COLLATIONS;
39 COLLATIONS CREATE TEMPORARY TABLE `COLLATIONS` (
40 `COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',
41 `CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',
42 `ID` bigint(11) NOT NULL DEFAULT '0',
43 `IS_DEFAULT` varchar(3) NOT NULL DEFAULT '',
44 `IS_COMPILED` varchar(3) NOT NULL DEFAULT '',
45 `SORTLEN` bigint(3) NOT NULL DEFAULT '0'
46 ) ENGINE=MEMORY DEFAULT CHARSET=utf8
47 SHOW COLUMNS FROM information_schema.COLLATIONS;
48 Field Type Null Key Default Extra
49 COLLATION_NAME varchar(32) NO
50 CHARACTER_SET_NAME varchar(32) NO
52 IS_DEFAULT varchar(3) NO
53 IS_COMPILED varchar(3) NO
54 SORTLEN bigint(3) NO 0
55 # Testcases 3.2.3.2 and 3.2.3.3 are checked in suite/funcs_1/t/charset_collation*.test
56 ########################################################################
57 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
58 # DDL on INFORMATION_SCHEMA tables are not supported
59 ########################################################################
60 DROP DATABASE IF EXISTS db_datadict;
61 CREATE DATABASE db_datadict;
62 INSERT INTO information_schema.collations
63 SELECT * FROM information_schema.collations;
64 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
65 INSERT INTO information_schema.collations
66 (collation_name,character_set_name,id,is_default,is_compiled,sortlen)
67 VALUES ( 'cp1251_bin', 'cp1251',50, '', '',0);
68 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
69 UPDATE information_schema.collations SET description = 'just updated';
70 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
71 DELETE FROM information_schema.collations WHERE table_name = 't1';
72 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
73 TRUNCATE information_schema.collations;
74 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
75 CREATE INDEX my_idx ON information_schema.collations(character_set_name);
76 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
77 ALTER TABLE information_schema.collations DROP PRIMARY KEY;
78 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
79 ALTER TABLE information_schema.collations ADD f1 INT;
80 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
81 ALTER TABLE information_schema.collations ENABLE KEYS;
82 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
83 DROP TABLE information_schema.collations;
84 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
85 ALTER TABLE information_schema.collations RENAME db_datadict.collations;
86 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
87 ALTER TABLE information_schema.collations
88 RENAME information_schema.xcollations;
89 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
90 DROP DATABASE db_datadict;