Bug #102199 Data dictionary upgrading fails with encrypted tablespace
Submitted: 8 Jan 14:04 Modified: 18 Jan 15:31
Reporter: shoty netop Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:8.0.22 OS:CentOS (7.9)
Assigned to: MySQL Verification Team CPU Architecture:x86
Tags: encryption, upgrade

[8 Jan 14:04] shoty netop
Description:
I've enabled and encrypted evrything I could (data at rest encryptions).

When I've tried upgrading from 8.0.21 to 8.0.22 the following error occurs:

2021-01-08T12:38:44.535880Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80021' to '80022'.
2021-01-08T12:38:46.498850Z 1 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'CREATE TABLE events(   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,   schema_id BIGINT UNSIGNED NOT NULL,   name VARCHAR(64) NOT NULL COLLATE utf8_general_ci,   definer VARCHAR(288) NOT NULL,   time_zone VARCHAR(64) NOT NULL,   definition LONGBLOB NOT NULL,   definition_utf8 LONGTEXT NOT NULL,   execute_at DATETIME,   interval_value INT,   interval_field ENUM('YEAR','QUARTER','MONTH','DAY','HOUR','MINUTE','WEEK','SECOND','MICROSECOND','YEAR_MONTH','DAY_HOUR','DAY_MINUTE','DAY_SECOND','HOUR_MINUTE','HOUR_SECOND','MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND','MINUTE_MICROSECOND','SECOND_MICROSECOND'),   sql_mode SET('REAL_AS_FLOAT','PIPES_AS_CONCAT','ANSI_QUOTES','IGNORE_SPACE','NOT_USED','ONLY_FULL_GROUP_BY','NO_UNSIGNED_SUBTRACTION','NO_DIR_IN_CREATE','NOT_USED_9','NOT_USED_10','NOT_USED_11','NOT_USED_12','NOT_USED_13','NOT_USED_14','NOT_USED_15','NOT_USED_16','NOT_USED_17','NOT_USED_18','ANSI','NO_AUTO_VALUE_ON_ZERO','NO_BACKSLASH_ESCAPES','STRICT_TRANS_TABLES','STRICT_ALL_TABLES','NO_ZERO_IN_DATE','NO_ZERO_DATE','ALLOW_INVALID_DATES','ERROR_FOR_DIVISION_BY_ZERO','TRADITIONAL','NOT_USED_29','HIGH_NOT_PRECEDENCE','NO_ENGINE_SUBSTITUTION','PAD_CHAR_TO_FULL_LENGTH','TIME_TRUNCATE_FRACTIONAL') NOT NULL,   starts DATETIME,   ends DATETIME,   status ENUM('ENABLED', 'DISABLED', 'SLAVESIDE_DISABLED') NOT NULL,   on_completion ENUM('DROP', 'PRESERVE') NOT NULL,   created TIMESTAMP NOT NULL,   last_altered TIMESTAMP NOT NULL,   last_executed DATETIME,   comment VARCHAR(2048) NOT NULL,   originator INT UNSIGNED NOT NULL,   client_collation_id BIGINT UNSIGNED NOT NULL,   connection_collation_id BIGINT UNSIGNED NOT NULL,   schema_collation_id BIGINT UNSIGNED NOT NULL,   options MEDIUMTEXT,   PRIMARY KEY(id),   UNIQUE KEY(schema_id, name),   KEY(client_collation_id),   KEY(connection_collation_id),   KEY(schema_collation_id),   KEY(definer),   FOREIGN KEY (schema_id) REFERENCES schemata(id),   FOREIGN KEY (client_collation_id) REFERENCES collations(id),   FOREIGN KEY (connection_collation_id) REFERENCES collations(id),   FOREIGN KEY (schema_collation_id) REFERENCES collations(id) ) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=DYNAMIC STATS_PERSISTENT=0 TABLESPACE=mysql' failed with error code = 3825, error message = 'Request to create 'unencrypted' table while using an 'encrypted' tablespace.'.
2021-01-08T12:38:46.535912Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.

How to repeat:
Enable all available encryptions and encrypt all tablespaces.

my.cnf:
default_table_encryption=on
innodb_redo_log_encrypt=on
innodb_undo_log_encrypt=on

# for all tablespaces:
ALTER TABLESPACE [...] ENCRYPTION = 'Y';
# for all tables: 
ALTER TABLE [...] ENCRYPTION = 'Y';

ALTER TABLESPACE mysql ENCRYPTION = 'Y';

# CHECK:
SELECT SPACE, NAME, SPACE_TYPE, ENCRYPTION FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE ENCRYPTION='N';
-- should only contain one line like this:
-- 4294967293	innodb_temporary	System	N
-- as it's the only one that cannot be encrypted.

- Upgrade to MySQL 8.0.22 and restart server.
The server will fail when trying to migrate data dictionary

Suggested fix:
Table should be automatically encrypted if tablespace is encrypted. From what I can tell the SQL doesn't have an ENCRYPTION=N option. Maybe when upgrading data dictionary some options have other default values.
[8 Jan 14:27] MySQL Verification Team
Thank you for the bug report. Please check for duplicate/related with https://bugs.mysql.com/bug.php?id=98530.
[8 Jan 15:12] shoty netop
It's a different problem from https://bugs.mysql.com/bug.php?id=98530.

This bug is an SQL error while executing data dictionary upgrade. Error is:  Request to create 'unencrypted' table while using an 'encrypted' tablespace
Also for current bug encryption is complete and server is stopped gracefully for upgrade.

Bug 98530 seems to be datafile corruption if server crashes while encryption is in progress.
[13 Jan 15:37] MySQL Verification Team
Hi,

I will need some more details.

What binaries are you using on this CentOS7? The original ones that come with CentOS? Some 3rd party repo? Oracle YUM repo or you are using archive from Oracle?

How are you performing the upgrade? Are you shutdown, replace binaries, start, or you are yum upgrade or ? With what binaries are you upgrading?

Thanks
Bogdan
[13 Jan 22:25] shoty netop
I'm using Oracle official yum repository.
Upgrade is done using " yum update ".
Everything works as expected except database doesn't start any more, due to the error message that occurs when it tries to start and upgrade internal tables.
I've update other instances that are not using data at rest encryption without any problems.
[18 Jan 15:31] MySQL Verification Team
Hi,
Testing 8.0.19 -> 8.0.23 (latest MySQL Server)

mysql> select * from INNODB_TABLESPACES;
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
| SPACE      | NAME             | FLAG  | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE | SERVER_VERSION | SPACE_VERSION | ENCRYPTION | STATE  |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
| 4294967294 | mysql            | 26624 | Any                  |     16384 |             0 | General    |          4096 |  25165824 |       25169920 | 8.0.19         |             1 | Y          | normal |
| 4294967293 | innodb_temporary |  4096 | Compact or Redundant |     16384 |             0 | System     |             0 |         0 |              0 | 8.0.19         |             1 | N          | normal |
| 4294967279 | innodb_undo_001  |     0 | Undo                 |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.19         |             1 | Y          | active |
| 4294967278 | innodb_undo_002  |     0 | Undo                 |     16384 |             0 | Undo       |             0 |         0 |              0 | 8.0.19         |             1 | Y          | active |
|          1 | sys/sys_config   | 16417 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |          81920 | 8.0.19         |             1 | N          | normal |
|          2 | test/t1          | 24609 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |          81920 | 8.0.19         |             1 | Y          | normal |
|          3 | test/t2          | 24609 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |          81920 | 8.0.19         |             1 | Y          | normal |
|          4 | test/t3          | 24609 | Dynamic              |     16384 |             0 | Single     |          4096 |    114688 |          81920 | 8.0.19         |             1 | Y          | normal |
+------------+------------------+-------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+----------------+---------------+------------+--------+
8 rows in set (0.00 sec)

2021-01-18T15:16:42.016121Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.19)  MySQL Community Server - GPL.
2021-01-18T15:16:44.515588Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.23) starting as process 26766
2021-01-18T15:16:45.163369Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-18T15:16:48.048735Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-18T15:16:48.315259Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80017' to '80023'.
2021-01-18T15:16:51.842448Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80017' to '80023' completed.
2021-01-18T15:16:52.593964Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2021-01-18T15:17:13.807495Z 4 [System] [MY-013381] [Server] Server upgrade from '80019' to '80023' started.
2021-01-18T15:18:22.873285Z 4 [System] [MY-013381] [Server] Server upgrade from '80019' to '80023' completed.
2021-01-18T15:18:44.012384Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-01-18T15:18:44.012710Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-01-18T15:18:44.187928Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.23'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
[root@centos7Template ~]#
mysql> select * from t1;
+----+------------------+----------------------+
| id | a                | b                    |
+----+------------------+----------------------+
|  1 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  2 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  3 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  4 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  5 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  6 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  7 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  8 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
|  9 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 10 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 11 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 12 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 13 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 14 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 15 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 16 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 17 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 18 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
| 19 | xxxxxxxxxxxxxxxx | yyyyyyyyyyyyyyyyyyyy |
+----+------------------+----------------------+
19 rows in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` char(100) DEFAULT NULL,
  `b` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.00 sec)

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

mysql>
[18 Jan 15:31] MySQL Verification Team
Hi,

I cannot reproduce this with latest 8.0.23

all best
Bogdan