[MDEV-14571] mysql_install_db does not encrypt system tablespace when innodb_encrypt_tables is set Created: 2017-12-04  Updated: 2020-08-25  Resolved: 2017-12-14

Status: Closed
Project: MariaDB Server
Component/s: Encryption, Storage Engine - InnoDB
Affects Version/s: 10.1, 10.2.9
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Geoff Montee (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: encryption, innodb

Issue Links:
Relates
relates to MDEV-14157 Improve documentation of data at rest... Closed
relates to MDEV-14610 Add syntax to manually encrypt/decryp... Closed
relates to MDEV-14398 When innodb_encryption_rotate_key_age... Closed
Sprint: 10.1.30

 Description   

When mysql_install_db initializes a new InnoDB system tablespace and innodb_encrypt_tables is set to ON or FORCE, should the newly created InnoDB tablespace be encrypted? That does not appear to happen with the current implementation.

Let's say that we have the following configuration:

plugin-load-add=file_key_management
 
file-key-management
 
file_key_management_encryption_algorithm=aes_cbc
 
file_key_management_filename = /etc/my.cnf.d//keys.enc
 
file_key_management_filekey = secret
 
innodb-encrypt-tables
 
innodb-encrypt-log
 
innodb-encryption-threads=4
 
encrypt-tmp-disk-tables=1
 
encrypt-tmp-files=1
 
#encrypt-binlog=1
 
innodb_encryption_rotate_key_age = 0

I would expect the system tablespace (where space_id=0) to be encrypted, but it is not:

[ec2-user@ip-172-30-0-58 ~]$ sudo mysql_install_db --user=mysql
 
Installing MariaDB/MySQL system tables in '/var/lib/mysql' ...
 
OK
 
 
 
To start mysqld at boot time you have to copy
 
support-files/mysql.server to the right place for your system
 
 
 
PLEASE REMEMBER TO SET A PASSWORD FOR THE MariaDB root USER !
 
To do so, start the server, then issue the following commands:
 
 
 
'/usr/bin/mysqladmin' -u root password 'new-password'
 
'/usr/bin/mysqladmin' -u root -h ip-172-30-0-58.us-west-2.compute.internal passw                                                                             ord 'new-password'
 
 
 
Alternatively you can run:
 
'/usr/bin/mysql_secure_installation'
 
 
 
which will also give you the option of removing the test
 
databases and anonymous user created by default.  This is
 
strongly recommended for production servers.
 
 
 
See the MariaDB Knowledgebase at http://mariadb.com/kb or the
 
MySQL manual for more instructions.
 
 
 
You can start the MariaDB daemon with:
 
cd '/usr' ; /usr/bin/mysqld_safe --datadir='/var/lib/mysql'
 
 
 
You can test the MariaDB daemon with mysql-test-run.pl
 
cd '/usr/mysql-test' ; perl mysql-test-run.pl
 
 
 
Please report any problems at http://mariadb.org/jira
 
 
 
The latest information about MariaDB is available at http://mariadb.org/.
 
You can find additional information about the MySQL part at:
 
http://dev.mysql.com
 
Consider joining MariaDB's strong and vibrant community:
 
https://mariadb.org/get-involved/
 
 
 
[ec2-user@ip-172-30-0-58 ~]$ sudo galera_new_cluster
 
[ec2-user@ip-172-30-0-58 ~]$ mysql -u root
 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 11
 
Server version: 10.2.9-MariaDB-debug-log Source distribution
 
 
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION                                                                             \G
 
*************************** 1. row ***************************
 
                       SPACE: 1
 
                        NAME: mysql/innodb_table_stats
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
*************************** 2. row ***************************
 
                       SPACE: 2
 
                        NAME: mysql/innodb_index_stats
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
*************************** 3. row ***************************
 
                       SPACE: 3
 
                        NAME: mysql/gtid_slave_pos
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
3 rows in set (0.00 sec)

If I change innodb_encryption_rotate_key_age to the default value of 1 (see also MDEV-14398 about that), then the system tablespace is encrypted in the background the next time mysqld is restarted, which suggests that it probably should have been encrypted from the start.

MariaDB [(none)]> SELECT @@global.innodb_encryption_rotate_key_age;
 
+-------------------------------------------+
 
| @@global.innodb_encryption_rotate_key_age |
 
+-------------------------------------------+
 
|                                         1 |
 
+-------------------------------------------+
 
1 row in set (0.00 sec)
 
 
 
MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION\G
 
*************************** 1. row ***************************
 
                       SPACE: 1
 
                        NAME: mysql/innodb_table_stats
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
*************************** 2. row ***************************
 
                       SPACE: 2
 
                        NAME: mysql/innodb_index_stats
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
*************************** 3. row ***************************
 
                       SPACE: 3
 
                        NAME: mysql/gtid_slave_pos
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
*************************** 4. row ***************************
 
                       SPACE: 0
 
                        NAME: innodb_system
 
           ENCRYPTION_SCHEME: 1
 
          KEYSERVER_REQUESTS: 1
 
             MIN_KEY_VERSION: 1
 
         CURRENT_KEY_VERSION: 1
 
    KEY_ROTATION_PAGE_NUMBER: NULL
 
KEY_ROTATION_MAX_PAGE_NUMBER: NULL
 
              CURRENT_KEY_ID: 1
 
        ROTATING_OR_FLUSHING: 0
 
4 rows in set (0.00 sec)



 Comments   
Comment by Elena Stepanova [ 2017-12-05 ]

Isn't it essentially the same problem as MDEV-14398?

In any case, it's easily reproducible, I'm passing it over to jplindst for further handling.

Comment by Geoff Montee (Inactive) [ 2017-12-05 ]

This isn't exactly the same as MDEV-14398, but it is similar.

The problem in MDEV-14398 is that mysqld's background encryption threads do not encrypt unencrypted tables when innodb_encryption_rotate_key_age=0 is set. The variable innodb_encryption_rotate_key_age affects how the background encryption threads (configured by innodb_encryption_threads) handle key rotations. Setting innodb_encryption_rotate_key_age=0 disabled key rotations, so it seems like the current encryption implementation treats the conversion of an unencrypted table to an encrypted table in the background as a key rotation. I believe this was confirmed by jplindst, who said that unencrypted tables are considered to have key_version=0. I guess whether we consider MDEV-14398 to be a bug or not would depend on whether we like the current behavior. At least one user does not like it. It does seem counterintuitive to me to continue to allow unencrypted tables to exist when innodb_encrypt_tables is changed to ON or FORCE. Note that this problem would affect all InnoDB tablespaces that were unencrypted before innodb_encrypt_tables was enabled--not just the system tablespace.

In contrast, this issue (MDEV-14571) only affects InnoDB's system tablespace, and it is unrelated to mysqld's background encryption threads. Instead, it is about the behavior of mysql_install_db, which as far as I know, does not have background encryption threads. If you look at the output above, you can see that when innodb_encrypt_tables is enabled, mysql_install_db uses encryption when it initializes the file-per-table tablespaces for mysql.innodb_table_stats, mysql.innodb_index_stats, and mysql.gtid_slave_pos. However, for some reason, it does not use encryption when it initializes InnoDB's system tablespace. InnoDB's system tablespace can only be encrypted by mysqld's background encryption threads if the server is started when innodb_encryption_rotate_key_age>0 is set. I guess whether we consider this to be a bug or not would depend on whether we want mysql_install_db to initialize the InnoDB system tablespace as an encrypted tablespace, similar to how it initializes the file-per-table tablespaces.

Comment by Jan Lindström (Inactive) [ 2017-12-05 ]

Your analysis is correct. Problem is that there is conflicting desires here. Do you want to use CPU to do background encryption or not. You can't have background encryption with no CPU usage. Normal tablespaces (i.e. even mysql.xxx if they are innodb) can be encrypted even on mysql_install_db as at that point they are new tables. However, system tablespace is not really a table and can't be encrypted any other method except background encryption. Currently, we do not have feature where we would at startup iterate all tablespaces and do encrypt/decrypt for those that require key rotation (here both unencrypted to encrypted and encrypted to unencrypted are key rotation) and after that only for new tables that really require it. So this behavior currently is currently correct and as designed. Naturally, if customers really require another approach we could provide that to them. But before I would do it I would want to know exact expectations. Note that any change on current approach will require CPU.

Comment by Geoff Montee (Inactive) [ 2017-12-06 ]

Hi jplindst,

Thanks for the feedback!

However, system tablespace is not really a table and can't be encrypted any other method except background encryption.

Could the need for key rotation be solved if we made the following changes?:

  • For brand new databases, change mysql_install_db to encrypt the system tablespace if innodb_encrypt_tables is set.
  • For existing databases, add some command or variable that the user could run to tell the server to encrypt the system tablespace. e.g. maybe something like:

SET GLOBAL innodb_encrypt_sys_tablespace=ON;

Would that allow users to encrypt the system tablespace, or are there other implementation details that would prevent this from working properly?

If users want to disable key rotation to save CPU usage, they already have ways to force unencrypted tables to become encrypted with ALTER TABLE even when key rotation is disabled (i.e. ALTER TABLE tab ENCRYPTED=YES). The system tablespace seems to be the only object that needs key rotation enabled for it to be encrypted. Is that correct?

Comment by Geoff Montee (Inactive) [ 2017-12-13 ]

Should this be closed as "Not a Bug" and the implementation details added to the documentation (e.g. see MDEV-14157)?

Comment by Jan Lindström (Inactive) [ 2017-12-13 ]

Confirmed, mysql_install_db does not encrypt system tablespace if innodb-encryption-rotate-key-age=0, this means e.g.

MariaDB [test]> set global innodb_file_per_table=off;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [test]> create table t1(a char(200)) engine=innodb;
 
Query OK, 0 rows affected (0.02 sec)
 
 
 
MariaDB [test]> insert into t1 values ('secretsecret');
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [test]> exit

And above data is visible:

jan@jan-laptop-asus:~/testdb$ strings ibdata1 | grep secret
 
secretsecret                                                                                                                                                                                            
secretsecret

Comment by Jan Lindström (Inactive) [ 2017-12-13 ]

However, if innodb-encryption-rotate-key-age=1 on configuration when mysql-install-db is executed and

MariaDB [(none)]> set global innodb_file_per_table=off;
 
Query OK, 0 rows affected (0.00 sec)
 
 
 
MariaDB [(none)]> use test;
 
Database changed
 
MariaDB [test]> create table t1(a char(200)) engine=innodb;
 
Query OK, 0 rows affected (0.01 sec)
 
 
 
MariaDB [test]> insert into t1 values ('secretsecret');
 
Query OK, 1 row affected (0.01 sec)
 
 
 
MariaDB [test]> exit
 
Bye

Then data inside a system tablespace is not visible:

jan@jan-laptop-asus:~/testdb$ strings ibdata1 | grep secret

Comment by Jan Lindström (Inactive) [ 2017-12-14 ]

When you start server with innodb-encryption-rotate-key-age=1 you can monitor progress of system tablespace encryption from
information_schema.innodb-tablespaces-encryption table. Note that this table is available only if your my.cnf has innodb-tablespaces-encryption set. See example below where key_rotation_page_number is increasing as the background key rotation progess. Furthermore,
note the field rotating_or_flushing it value remains on 1 until whole tablespace is rotated and all the changes are flushed to disk.

	
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  0 |               0 |                   1 |                       61 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                      601 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     1201 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     1381 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     1501 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     1561 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     1681 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               0 |                   1 |                     2521 |                        12800 |              1 |                    1 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
jan@jan-laptop-asus:~$ /usr/local/mysql/bin/mysql -u root -S /tmp/mysql.sock -e "select * from information_schema.innodb_tablespaces_encryption"
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
| SPACE | NAME                     | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER | CURRENT_KEY_ID | ROTATING_OR_FLUSHING |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+
 
|     1 | mysql/innodb_table_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     2 | mysql/innodb_index_stats |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     3 | mysql/gtid_slave_pos     |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
|     0 | ./ibdata1                |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |              1 |                    0 |
 
+-------+--------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+----------------+----------------------+

Comment by Jan Lindström (Inactive) [ 2017-12-14 ]

In this contents I do not agree that current behavior would be a bug. Default value for innodb-encryption-key-age=1 would do the expected results. In this contents server is misconfigured similarly if you would not set encryption key location correctly. When we first time create a database using value = 0 is not really meaningful because server will not stay up.

We will continue discussion on MDEV-14398. Any change there will actually have effect on mysql_install_db also.

Comment by Geoff Montee (Inactive) [ 2017-12-14 ]

Ah, thanks for the explanation. So rotating_or_flushing is the column in INNODB_TABLESPACES_ENCRYPTION that can be used to determine if the table still has unencrypted pages. That column is not documented at all right now, so I've added a note about that to MDEV-14157 too.

Generated at Thu Feb 08 08:14:37 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.