Debian Codegraph @

mysql-test/suite/encryption/r/engine.result

raw
1 #
2 # WL#12261 Control (enforce and disable) table encryption
3 #
4 # Check for use of ENCRYPTION clause on tables using engines that
5 # does not support encryption.
6 #
7 # 1. Requesting encryption on SE that does not support encryption.
8 #
9 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=HEAP ENCRYPTION='y';
10 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
11 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=MyISAM ENCRYPTION='y';
12 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
13 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=MyISAM ENCRYPTION='w';
14 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
15 # 2.
16 # Requesting unencrypted tables on SE that does not support
17 # encryption. This is allowed. Verify that we do not store
18 # ENCRYPTION clause.
19 #
20 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=HEAP ENCRYPTION='N';
21 SHOW CREATE TABLE t1;
22 Table Create Table
23 t1 CREATE TABLE `t1` (
24 `f1` int NOT NULL
25 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
26 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
27 WHERE TABLE_NAME='t1';
28 TABLE_NAME CREATE_OPTIONS
29 t1
30 DROP TABLE t1;
31 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=HEAP ENCRYPTION='n';
32 SHOW CREATE TABLE t1;
33 Table Create Table
34 t1 CREATE TABLE `t1` (
35 `f1` int NOT NULL
36 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
37 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
38 WHERE TABLE_NAME='t1';
39 TABLE_NAME CREATE_OPTIONS
40 t1
41 DROP TABLE t1;
42 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=MyISAM ENCRYPTION='';
43 SHOW CREATE TABLE t1;
44 Table Create Table
45 t1 CREATE TABLE `t1` (
46 `f1` int NOT NULL
47 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
48 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
49 WHERE TABLE_NAME='t1';
50 TABLE_NAME CREATE_OPTIONS
51 t1
52 DROP TABLE t1;
53 # 3.
54 # Move tables to and from different storage engine.
55 #
56 CREATE TABLE t1 (f1 INT NOT NULL) ENGINE=InnoDB ENCRYPTION='y';
57 SHOW CREATE TABLE t1;
58 Table Create Table
59 t1 CREATE TABLE `t1` (
60 `f1` int NOT NULL
61 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
62 # 4.
63 # Moving encrypted table to SE that doesn't support encryption.
64 ALTER TABLE t1 ENGINE=MyISAM;
65 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
66 ALTER TABLE t1 ENGINE=HEAP;
67 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
68 # 5.
69 # Moving encrypted table to SE that doesn't support encryption.
70 # with a explicit request to decrypt the table is allowed.
71 ALTER TABLE t1 ENGINE=MyISAM ENCRYPTION='n';
72 SHOW CREATE TABLE t1;
73 Table Create Table
74 t1 CREATE TABLE `t1` (
75 `f1` int NOT NULL
76 ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
77 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
78 WHERE TABLE_NAME='t1';
79 TABLE_NAME CREATE_OPTIONS
80 t1
81 ALTER TABLE t1 ENGINE=Heap ENCRYPTION='N';
82 SHOW CREATE TABLE t1;
83 Table Create Table
84 t1 CREATE TABLE `t1` (
85 `f1` int NOT NULL
86 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
87 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
88 WHERE TABLE_NAME='t1';
89 TABLE_NAME CREATE_OPTIONS
90 t1
91 ALTER TABLE t1 ENGINE=CSV ENCRYPTION='';
92 SHOW CREATE TABLE t1;
93 Table Create Table
94 t1 CREATE TABLE `t1` (
95 `f1` int NOT NULL
96 ) ENGINE=CSV DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
97 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
98 WHERE TABLE_NAME='t1';
99 TABLE_NAME CREATE_OPTIONS
100 t1
101 # 6.
102 # Moving unencrypted table from SE that doesn't support encryption
103 # to SE that does support encryption is allowed.
104 ALTER TABLE t1 ENGINE=InnoDB ENCRYPTION='y';
105 SHOW CREATE TABLE t1;
106 Table Create Table
107 t1 CREATE TABLE `t1` (
108 `f1` int NOT NULL
109 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
110 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
111 WHERE TABLE_NAME='t1';
112 TABLE_NAME CREATE_OPTIONS
113 t1 ENCRYPTION='y'
114 DROP TABLE t1;
115 # Same as 4/5/6 using general tablespace:
116 #
117 CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENCRYPTION='y';
118 CREATE TABLE t1 (f1 INT NOT NULL) TABLESPACE=ts1 ENCRYPTION='y';
119 # 4.
120 # Moving encrypted table to SE that doesn't support encryption.
121 ALTER TABLE t1 ENGINE=MyISAM;
122 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
123 ALTER TABLE t1 ENGINE=HEAP;
124 ERROR 42000: The storage engine for the table doesn't support ENCRYPTION
125 # 5.
126 # Moving encrypted table to SE that doesn't support encryption.
127 # with a explicit request to decrypt the table is allowed.
128 ALTER TABLE t1 ENGINE=MyISAM ENCRYPTION='n';
129 SHOW CREATE TABLE t1;
130 Table Create Table
131 t1 CREATE TABLE `t1` (
132 `f1` int NOT NULL
133 ) /*!50100 TABLESPACE `ts1` */ ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
134 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
135 WHERE TABLE_NAME='t1';
136 TABLE_NAME CREATE_OPTIONS
137 t1
138 # 6.
139 # Moving unencrypted table from SE that doesn't support encryption
140 # to SE that does support encryption is allowed.
141 ALTER TABLE t1 ENGINE=InnoDB TABLESPACE=innodb_file_per_table ENCRYPTION='n';
142 SHOW CREATE TABLE t1;
143 Table Create Table
144 t1 CREATE TABLE `t1` (
145 `f1` int NOT NULL
146 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
147 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
148 WHERE TABLE_NAME='t1';
149 TABLE_NAME CREATE_OPTIONS
150 t1
151 ALTER TABLE t1 ENCRYPTION='y';
152 SHOW CREATE TABLE t1;
153 Table Create Table
154 t1 CREATE TABLE `t1` (
155 `f1` int NOT NULL
156 ) /*!50100 TABLESPACE `innodb_file_per_table` */ ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='y'
157 SELECT TABLE_NAME, CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES
158 WHERE TABLE_NAME='t1';
159 TABLE_NAME CREATE_OPTIONS
160 t1 ENCRYPTION='y'
161 DROP TABLE t1;
162 DROP TABLESPACE ts1;