MySQL Bugs: #102199: Data dictionary upgrading fails with encrypted tablespace

Bug #102199	Data dictionary upgrading fails with encrypted tablespace
Submitted:	8 Jan 2021 14:04	Modified:	17 Mar 2023 7:36
Reporter:	Cristi Coteț	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

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.

Thank you for the bug report. Please check for duplicate/related with https://bugs.mysql.com/bug.php?id=98530.

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.

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

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.

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>

Hi,

I cannot reproduce this with latest 8.0.23

all best
Bogdan

I have the same problem. I'm upgrading 5.7.31 to latest community version using yum on red hat 7.9.

All my dba are encrypted on keyring

Error:
[System] [MY-013381] [Server] Server upgrade from '50700' to '80032' started.
[ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE mysql.plugin TABLESPACE = mysql; ' failed with error code = 3825, error message = 'Request to create 'encrypted' table while using an 'unencrypted' tablespace.'.
[ERROR] [MY-013380] [Server] Failed to upgrade server.
[ERROR] [MY-010119] [Server] Aborting

Apparently, the error "Request to create 'unencrypted' table while using an 'encrypted' tablespace" occurs when mysql_upfrade runs on encrypted system tables. 
To fix this issue, I ran:

use mysql;
alter table engine_cost ENCRYPTION='N';
alter table gtid_executed ENCRYPTION='N';
alter table help_category ENCRYPTION='N';
alter table help_keyword ENCRYPTION='N';
alter table help_relation ENCRYPTION='N';
alter table help_topic ENCRYPTION='N';
alter table innodb_index_stats ENCRYPTION='N';
alter table innodb_table_stats ENCRYPTION='N';
alter table plugin ENCRYPTION='N';
alter table server_cost ENCRYPTION='N';
alter table servers ENCRYPTION='N';
alter table slave_master_info ENCRYPTION='N';
alter table slave_relay_log_info ENCRYPTION='N';
alter table slave_worker_info ENCRYPTION='N';
alter table time_zone ENCRYPTION='N';
alter table time_zone_leap_second ENCRYPTION='N';
alter table time_zone_name ENCRYPTION='N';
alter table time_zone_transition ENCRYPTION='N';
alter table time_zone_transition_type ENCRYPTION='N';

Then I triggered the upgrade again and it worked fine!

You can get past this bug like this:

- rollback to previous version
- decrypt MySQL schema or innodb temp schema, or undo schema or something like this (don't remember exactly). My advice is to decrypt everything except actual data schemas.
- upgrade successfully