Debian Codegraph @

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

raw
1 # This test upgrades schema from 5.7, 8.0 to 8.0.15 where we
2 # introduce serveral ENCRYPTION control capabilities in MySQL
3 # (WL#12261). Specifically this test upgrades from,
4 #
5 # a) 5.7.25 where, we support file-per-tablespace
6 # encryption and not general tablespace encryption. We upgrade from
7 # FRM to DD.
8 # b) 8.0.12 where, we support file-per-tablespace
9 # encryption and not general tablespace encryption. We upgrade from
10 # DD to DD.
11 # c) 8.0.13 where, we support file-per-tablespace
12 # encryption and general tablespace encryption. We upgrade from
13 # DD to DD.
14 #
15 # The schema used is during upgrade is as described in
16 # upgrade_schema.test placed in respective *.zip file. The expectation
17 # is that the encryption clause for tables and tablespaces are
18 # stored as designed by wl12261 after the upgrade is completed.. The
19 # results are verified by querying the mysql.tables.options and
20 # mysql.tablespaces.options.
21 ########################################################################
22 # Stop the running server.
23 ########################################################################
24 ########################################################################
25 # Unzip a 5.7.25 datadir with bug data, start the server against it
26 ########################################################################
27 ########################################################################
28 # Copy and unzip the datadir.
29 ########################################################################
30 ########################################################################
31 # Restart the server against the unzipped datadir.
32 ########################################################################
33 SET DEBUG='+d,skip_dd_table_access_check';
34 SELECT schema_id, t.name, t.options, t.tablespace_id
35 FROM mysql.tables t JOIN mysql.schemata sch
36 ON t.schema_id = sch.id WHERE sch.name LIKE 'db1' ORDER BY t.name;
37 schema_id name options tablespace_id
38 4 c1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
39 4 c2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
40 4 c3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
41 4 h1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
42 4 h2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
43 4 h3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
44 4 i1 avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
45 4 i2 avg_row_length=0;encrypt_type=y;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
46 4 i3 avg_row_length=0;encrypt_type=n;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
47 4 i4 avg_row_length=0;encrypt_type=N;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
48 4 i_part1 avg_row_length=0;encrypt_type=N;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
49 4 i_part2 avg_row_length=0;encrypt_type=y;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
50 4 i_part3 avg_row_length=0;encrypt_type=n;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
51 4 i_part4 avg_row_length=0;encrypt_type=N;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
52 4 i_ts1 avg_row_length=0;encrypt_type=N;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; 27
53 4 i_ts2 avg_row_length=0;encrypt_type=n;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; 27
54 4 i_ts3 avg_row_length=0;encrypt_type=N;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; 27
55 SELECT id, name, options FROM mysql.tablespaces
56 WHERE name LIKE 'ts%' OR name LIKE '%db1%';
57 id name options
58 26 ts0 encryption=N;
59 27 ts1 encryption=N;
60 28 db1/i1 encryption=N;
61 29 db1/i2 encryption=Y;
62 30 db1/i3 encryption=N;
63 31 db1/i4 encryption=N;
64 32 db1/i_part1#p#p0 encryption=N;
65 33 db1/i_part1#p#p1 encryption=N;
66 34 db1/i_part1#p#p2 encryption=N;
67 35 db1/i_part2#p#p0 encryption=Y;
68 36 db1/i_part2#p#p1 encryption=Y;
69 37 db1/i_part2#p#p2 encryption=Y;
70 38 db1/i_part3#p#p0 encryption=N;
71 39 db1/i_part3#p#p1 encryption=N;
72 40 db1/i_part3#p#p2 encryption=N;
73 41 db1/i_part4#p#p0 encryption=N;
74 42 db1/i_part4#p#p1 encryption=N;
75 43 db1/i_part4#p#p2 encryption=N;
76 SET DEBUG='-d,skip_dd_table_access_check';
77 Check if upgrade root gets TABLE_ENCRYPTION_ADMIN privilege.
78 SHOW GRANTS FOR 'root'@'localhost';
79 Grants for root@localhost
80 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
81 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
82 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
83 ########################################################################
84 # Remove copied files and stop the server.
85 ########################################################################
86 ########################################################################
87 # Unzip a 8.0.12 datadir with bug data, start the server against it
88 ########################################################################
89 ########################################################################
90 # Copy and unzip the datadir.
91 ########################################################################
92 ########################################################################
93 # Restart the server against the unzipped datadir.
94 ########################################################################
95 SET DEBUG='+d,skip_dd_table_access_check';
96 SELECT schema_id, t.name, t.options, t.tablespace_id
97 FROM mysql.tables t JOIN mysql.schemata sch
98 ON t.schema_id = sch.id WHERE sch.name LIKE 'db1' ORDER BY t.name;
99 schema_id name options tablespace_id
100 7 c1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
101 7 c2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
102 7 c3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
103 7 h1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
104 7 h2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
105 7 h3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
106 7 i1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; NULL
107 7 i2 avg_row_length=0;encrypt_type=y;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
108 7 i3 avg_row_length=0;encrypt_type=n;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
109 7 i4 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; NULL
110 7 i_part1 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table;encrypt_type=N; NULL
111 7 i_part2 avg_row_length=0;encrypt_type=y;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
112 7 i_part3 avg_row_length=0;encrypt_type=n;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
113 7 i_part4 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table;encrypt_type=N; NULL
114 7 i_ts1 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; 8
115 7 i_ts2 avg_row_length=0;encrypt_type=n;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; 8
116 7 i_ts3 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; 8
117 SELECT id, name, options FROM mysql.tablespaces
118 WHERE name LIKE 'ts%' OR name LIKE '%db1%';
119 id name options
120 7 ts0 encryption=N;
121 8 ts1 encryption=N;
122 9 db1/i1 encryption=N;
123 10 db1/i2 encryption=Y;
124 11 db1/i3 encryption=N;
125 12 db1/i4 encryption=N;
126 13 db1/i_part1#p#p0 encryption=N;
127 14 db1/i_part1#p#p1 encryption=N;
128 15 db1/i_part1#p#p2 encryption=N;
129 16 db1/i_part2#p#p0 encryption=Y;
130 17 db1/i_part2#p#p1 encryption=Y;
131 18 db1/i_part2#p#p2 encryption=Y;
132 19 db1/i_part3#p#p0 encryption=N;
133 20 db1/i_part3#p#p1 encryption=N;
134 21 db1/i_part3#p#p2 encryption=N;
135 22 db1/i_part4#p#p0 encryption=N;
136 23 db1/i_part4#p#p1 encryption=N;
137 24 db1/i_part4#p#p2 encryption=N;
138 SET DEBUG='-d,skip_dd_table_access_check';
139 Check if upgrade root gets TABLE_ENCRYPTION_ADMIN privilege.
140 SHOW GRANTS FOR 'root'@'localhost';
141 Grants for root@localhost
142 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
143 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
144 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
145 ########################################################################
146 # Remove copied files and stop the server.
147 ########################################################################
148 ########################################################################
149 # Unzip a 8.0.13 datadir with bug data, start the server against it
150 ########################################################################
151 ########################################################################
152 # Copy and unzip the datadir.
153 ########################################################################
154 ########################################################################
155 # Restart the server against the unzipped datadir.
156 ########################################################################
157 SET DEBUG='+d,skip_dd_table_access_check';
158 SELECT schema_id, t.name, t.options, t.tablespace_id
159 FROM mysql.tables t JOIN mysql.schemata sch
160 ON t.schema_id = sch.id WHERE sch.name LIKE 'db1' ORDER BY t.name;
161 schema_id name options tablespace_id
162 7 c1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
163 7 c2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
164 7 c3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
165 7 h1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
166 7 h2 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
167 7 h3 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
168 7 i1 avg_row_length=0;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; NULL
169 7 i2 avg_row_length=0;encrypt_type=y;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
170 7 i3 avg_row_length=0;encrypt_type=n;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0; NULL
171 7 i_part1 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table;encrypt_type=N; NULL
172 7 i_part2 avg_row_length=0;encrypt_type=y;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
173 7 i_part3 avg_row_length=0;encrypt_type=n;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=1;stats_auto_recalc=0;stats_sample_pages=0;tablespace=innodb_file_per_table; NULL
174 7 i_ts1 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; 8
175 7 i_ts2 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; 9
176 7 i_ts3 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=Y; 10
177 7 i_ts4 avg_row_length=0;explicit_tablespace=1;key_block_size=0;keys_disabled=0;pack_record=0;stats_auto_recalc=0;stats_sample_pages=0;encrypt_type=N; 11
178 SELECT id, name, options FROM mysql.tablespaces
179 WHERE name LIKE 'ts%' OR name LIKE '%db1%';
180 id name options
181 7 ts0 encryption=N;
182 8 ts1 encryption=N;
183 9 ts2 encryption=n;
184 10 ts3 encryption=y;
185 11 ts4 encryption=N;
186 12 ts5 encryption=N;
187 13 db1/i1 encryption=N;
188 14 db1/i2 encryption=Y;
189 15 db1/i3 encryption=N;
190 16 db1/i_part1#p#p0 encryption=N;
191 17 db1/i_part1#p#p1 encryption=N;
192 18 db1/i_part1#p#p2 encryption=N;
193 19 db1/i_part2#p#p0 encryption=Y;
194 20 db1/i_part2#p#p1 encryption=Y;
195 21 db1/i_part2#p#p2 encryption=Y;
196 22 db1/i_part3#p#p0 encryption=N;
197 23 db1/i_part3#p#p1 encryption=N;
198 24 db1/i_part3#p#p2 encryption=N;
199 SET DEBUG='-d,skip_dd_table_access_check';
200 Check if upgrade root gets TABLE_ENCRYPTION_ADMIN privilege.
201 SHOW GRANTS FOR 'root'@'localhost';
202 Grants for root@localhost
203 GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION
204 GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION
205 GRANT PROXY ON ``@`` TO `root`@`localhost` WITH GRANT OPTION
206 ########################################################################
207 # Remove copied files and stop the server.
208 ########################################################################
209 ########################################################################
210 # Cleanup: Restart with default options.
211 ########################################################################
212 # restart
213 ########################################################################
214 # Bug #29447073: UPGRADE FROM 8.0.16 TO TRUNK FAILS WHEN MYSQL TABLESPACE IS ENCRYPTED
215 ########################################################################
216 alter tablespace mysql encryption='Y';
217 # restart: --upgrade=FORCE --early-plugin-load=keyring_file=keyring_file.so KEYRING_PLUGIN_OPT
218 alter tablespace mysql encryption='N';
219 ########################################################################
220 # Cleanup: Restart with default options.
221 ########################################################################
222 # restart