[MDEV-8150] Random values in I_S.INNODB_TABLESPACES_ENCRYPTION Created: 2015-05-12  Updated: 2015-11-18  Resolved: 2015-11-18

Status: Closed
Project: MariaDB Server
Component/s: Documentation, Encryption
Affects Version/s: 10.1.4
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Ian Gilfillan
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-8148 Document INFORMATION_SCHEMA table(s) ... Closed
Sprint: 10.1.9-3

 Description   

Note: I don't really know what is right and what is wrong here (see MDEV-8148 – request to document it). I am only trying to guess, and it seems I can never guess right

server started with --plugin-load-add=file_key_management.so --file_key_management_filename=/home/elenst/git/10.1/mysql-test/std_data/keys.txt

+------------------------------------------+---------+
 
| Variable_name                            | Value   |
 
+------------------------------------------+---------+
 
| aria_encrypt_tables                      | OFF     |
 
| encrypt_tmp_disk_tables                  | OFF     |
 
| file_key_management_encryption_algorithm | aes_cbc |
 
| innodb_default_encryption_key_id         | 1       |
 
| innodb_encrypt_log                       | OFF     |
 
| innodb_encrypt_tables                    | OFF     |
 
| innodb_encryption_rotate_key_age         | 1       |
 
| innodb_encryption_rotation_iops          | 100     |
 
| innodb_encryption_threads                | 0       |
 
+------------------------------------------+---------+

DDL that will be executed

 
drop database if exists db_encrypt;
 
create database db_encrypt;
 
use db_encrypt;
 
create table t_encrypted_existing_key (i int) encrypted=yes encryption_key_id = 2;
 
create table t_not_encrypted_existing_key (i int) encrypted=no encryption_key_id = 2;
 
create table t_encrypted_non_existing_key (i int) encrypted=yes encryption_key_id = 9;
 
create table t_not_encrypted_non_existing_key (i int) encrypted=no encryption_key_id = 9;
 
create table t_default_encryption_existing_key (i int) encryption_key_id = 2;
 
create table t_default_encryption_non_existing_key (i int) encryption_key_id = 9;
 
create table t_encrypted_default_key (i int) encrypted=yes;
 
create table t_not_encrypted_default_key (i int) encrypted=no;
 
create table t_defaults (i int);

Note: t_encrypted_non_existing_key will not be created

set global innodb_encrypt_tables = 0;
 
 
 
# (Re-)create all the tables as above
 

MariaDB [db_encrypt]> select * from information_schema.innodb_tablespaces_encryption where name like 'db_encrypt%';
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                             | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    10 | db_encrypt/t_encrypted_existing_key              |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    11 | db_encrypt/t_not_encrypted_existing_key          |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    12 | db_encrypt/t_not_encrypted_non_existing_key      |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    13 | db_encrypt/t_default_encryption_existing_key     |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    14 | db_encrypt/t_default_encryption_non_existing_key |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    15 | db_encrypt/t_encrypted_default_key               |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    16 | db_encrypt/t_not_encrypted_default_key           |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    17 | db_encrypt/t_defaults                            |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
8 rows in set (0.00 sec)

It does not seem to change with time.
It is already weird: as I understand from the description, tables with ENCRYPTED=YES should be encrypted regardless of innodb_encrypt_tables.
However, this part might be a duplicate of MDEV-8138.

set global innodb_encrypt_tables = 1;
 
# Wait a bit?
 

MariaDB [db_encrypt]> select * from information_schema.innodb_tablespaces_encryption where name like 'db_encrypt%';
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                             | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    10 | db_encrypt/t_encrypted_existing_key              |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    11 | db_encrypt/t_not_encrypted_existing_key          |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    12 | db_encrypt/t_not_encrypted_non_existing_key      |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    13 | db_encrypt/t_default_encryption_existing_key     |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    14 | db_encrypt/t_default_encryption_non_existing_key |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    15 | db_encrypt/t_encrypted_default_key               |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    16 | db_encrypt/t_not_encrypted_default_key           |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
|    17 | db_encrypt/t_defaults                            |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
+-------+--------------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
8 rows in set (0.00 sec)

I can't event start guessing why it is the way it is. It also does not change with time.

Now, with innodb_encrypt_tables=1 re-create the tables again.
This time t_encrypted_non_existing_key and t_default_encryption_non_existing_key are not created. I suppose that's okay.

MariaDB [db_encrypt]> select * from information_schema.innodb_tablespaces_encryption where name like 'db_encrypt%';
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                         | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    18 | db_encrypt/t_encrypted_existing_key          |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|    19 | db_encrypt/t_not_encrypted_existing_key      |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
 
|    20 | db_encrypt/t_not_encrypted_non_existing_key  |                 1 |                  0 |      4294967295 |          4294967295 |                     NULL |                         NULL |
 
|    21 | db_encrypt/t_default_encryption_existing_key |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|    22 | db_encrypt/t_encrypted_default_key           |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|    23 | db_encrypt/t_not_encrypted_default_key       |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
 
|    24 | db_encrypt/t_defaults                        |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
7 rows in set (0.00 sec)

4294967295 for t_not_encrypted_non_existing_key looks ugly.
Non-zero values for not_encrypted tables are strange. 

set global innodb_encrypt_tables = 0;
 
# Wait a bit?
 

MariaDB [db_encrypt]> select * from information_schema.innodb_tablespaces_encryption where name like 'db_encrypt%';
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                         | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    18 | db_encrypt/t_encrypted_existing_key          |                 1 |                  1 |               1 |                   0 |                     NULL |                         NULL |
 
|    19 | db_encrypt/t_not_encrypted_existing_key      |                 1 |                  0 |               1 |                   0 |                     NULL |                         NULL |
 
|    20 | db_encrypt/t_not_encrypted_non_existing_key  |                 1 |                  0 |      4294967295 |                   0 |                     NULL |                         NULL |
 
|    21 | db_encrypt/t_default_encryption_existing_key |                 1 |                  1 |               1 |                   0 |                     NULL |                         NULL |
 
|    22 | db_encrypt/t_encrypted_default_key           |                 1 |                  1 |               1 |                   0 |                     NULL |                         NULL |
 
|    23 | db_encrypt/t_not_encrypted_default_key       |                 1 |                  0 |               1 |                   0 |                     NULL |                         NULL |
 
|    24 | db_encrypt/t_defaults                        |                 1 |                  1 |               1 |                   0 |                     NULL |                         NULL |
 
+-------+----------------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
7 rows in set (0.00 sec)

Nothing else changes, even for tables with default encryption. I don't know if anything should.



 Comments   
Comment by Sergei Golubchik [ 2015-11-13 ]

I cannot repeat it, here's my test:

cat suite/encryption/t/a.test

 
--source include/have_innodb.inc
 
--source include/have_file_key_management_plugin.inc
 
set storage_engine=innodb;
 
 
 
create table t_encrypted_existing_key (i int) encrypted=yes encryption_key_id = 2;
 
create table t_not_encrypted_existing_key (i int) encrypted=no encryption_key_id = 2;
 
--error 1005
 
create table t_encrypted_non_existing_key (i int) encrypted=yes encryption_key_id = 9;
 
create table t_not_encrypted_non_existing_key (i int) encrypted=no encryption_key_id = 9;
 
create table t_default_encryption_existing_key (i int) encryption_key_id = 2;
 
--error 1005
 
create table t_default_encryption_non_existing_key (i int) encryption_key_id = 9;
 
create table t_encrypted_default_key (i int) encrypted=yes;
 
create table t_not_encrypted_default_key (i int) encrypted=no;
 
create table t_defaults (i int);
 
 
 
# use $MYSQL for a nicely formatted output
 
--system $MYSQL -te "select variable_name,session_value,global_value from information_schema.system_variables where variable_name like '%encrypt%'"
 
--system $MYSQL -te "select * from information_schema.innodb_tablespaces_encryption where name like 'test/%'"
 
 
 
drop table t_encrypted_existing_key;
 
drop table t_not_encrypted_existing_key;
 
drop table t_not_encrypted_non_existing_key;
 
drop table t_default_encryption_existing_key;
 
drop table t_encrypted_default_key;
 
drop table t_not_encrypted_default_key;
 
drop table t_defaults;

And this is the output:

./mtr encryption.a,cbc,innodb_plugin

 
Logging: ./mtr  encryption.a,cbc,innodb_plugin
 
vardir: /usr/home/serg/Abk/maria/mysql-test/var
 
Checking leftover processes...
 
Removing old var directory...
 
Creating var directory '/usr/home/serg/Abk/maria/mysql-test/var'...
 
Checking supported features...
 
MariaDB Version 10.1.8-MariaDB-debug
 
 - SSL connections supported
 
 - binaries are debug compiled
 
Collecting tests...
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
+------------------------------------------+---------------+--------------+
 
| variable_name                            | session_value | global_value |
 
+------------------------------------------+---------------+--------------+
 
| FILE_KEY_MANAGEMENT_ENCRYPTION_ALGORITHM | NULL          | aes_cbc      |
 
| INNODB_ENCRYPTION_THREADS                | NULL          | 0            |
 
| INNODB_ENCRYPT_LOG                       | NULL          | OFF          |
 
| INNODB_ENCRYPTION_ROTATION_IOPS          | NULL          | 100          |
 
| INNODB_DEFAULT_ENCRYPTION_KEY_ID         | 1             | 1            |
 
| INNODB_ENCRYPT_TABLES                    | NULL          | OFF          |
 
| ENCRYPT_BINLOG                           | NULL          | OFF          |
 
| INNODB_ENCRYPTION_ROTATE_KEY_AGE         | NULL          | 1            |
 
| ENCRYPT_TMP_DISK_TABLES                  | NULL          | OFF          |
 
| ENCRYPT_TMP_FILES                        | NULL          | OFF          |
 
| ARIA_ENCRYPT_TABLES                      | NULL          | OFF          |
 
+------------------------------------------+---------------+--------------+
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                   | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|     4 | test/t_encrypted_existing_key          |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
 
|     5 | test/t_not_encrypted_existing_key      |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     6 | test/t_not_encrypted_non_existing_key  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     7 | test/t_default_encryption_existing_key |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     8 | test/t_encrypted_default_key           |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
 
|     9 | test/t_not_encrypted_default_key       |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|    10 | test/t_defaults                        |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
set storage_engine=innodb;
 
create table t_encrypted_existing_key (i int) encrypted=yes encryption_key_id = 2;
 
create table t_not_encrypted_existing_key (i int) encrypted=no encryption_key_id = 2;
 
Warnings:
 
Warning	140	InnoDB: Ignored ENCRYPTION_KEY_ID 2 when encryption is disabled
 
create table t_encrypted_non_existing_key (i int) encrypted=yes encryption_key_id = 9;
 
ERROR HY000: Can't create table `test`.`t_encrypted_non_existing_key` (errno: 140 "Wrong create options")
 
create table t_not_encrypted_non_existing_key (i int) encrypted=no encryption_key_id = 9;
 
Warnings:
 
Warning	140	InnoDB: Ignored ENCRYPTION_KEY_ID 9 when encryption is disabled
 
create table t_default_encryption_existing_key (i int) encryption_key_id = 2;
 
create table t_default_encryption_non_existing_key (i int) encryption_key_id = 9;
 
ERROR HY000: Can't create table `test`.`t_default_encryption_non_existing_key` (errno: 140 "Wrong create options")
 
create table t_encrypted_default_key (i int) encrypted=yes;
 
create table t_not_encrypted_default_key (i int) encrypted=no;
 
create table t_defaults (i int);
 
drop table t_encrypted_existing_key;
 
drop table t_not_encrypted_existing_key;
 
drop table t_not_encrypted_non_existing_key;
 
drop table t_default_encryption_existing_key;
 
drop table t_encrypted_default_key;
 
drop table t_not_encrypted_default_key;
 
drop table t_defaults;
 
encryption.a 'cbc,innodb_plugin'         [ pass ]    193
 
--------------------------------------------------------------------------
 
The servers were restarted 0 times
 
Spent 0.193 of 7 seconds executing testcases
 
 
 
Completed: All 1 tests were successful.

Comment by Elena Stepanova [ 2015-11-15 ]

Indeed, there have been some pushes over this time that changed the picture. Still, there is the lack of documentation – at least I can't find "INNODB_TABLESPACES_ENCRYPTION" in the KB, and maybe because of that some questions remain (not necessarily bugs, but things to check).

First, with your test above, the result is non-deterministic. Sometimes it's as you pasted (with all zeros in KEYSERVER_REQUESTS), sometimes it has a "1" in KEYSERVER_REQUESTS:

+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                   | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    10 | test/t_defaults                        |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     7 | test/t_default_encryption_existing_key |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     8 | test/t_encrypted_default_key           |                 1 |                  0 |               1 |                   1 |                     NULL |                         NULL |
 
|     4 | test/t_encrypted_existing_key          |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|     9 | test/t_not_encrypted_default_key       |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     5 | test/t_not_encrypted_existing_key      |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     6 | test/t_not_encrypted_non_existing_key  |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+

I don't know what this field means, exactly, so cannot figure if it's supposed to be so.

Further, my initial complaint had more workflow.

If I add to your test set global innodb_encrypt_tables = 1 and wait (I used a sleep 5),

  • I still don't see any changes for tables with default encryption;
  • for non-encrypted table with an invalid key, CURRENT_KEY_VERSION changes to 4294967295, which is maybe harmless, just somewhat ugly;
  • for non-encrypted table with a default key, CURRENT_KEY_VERSION changes to 1, which is also a bit strange:

set global innodb_encrypt_tables = 1;
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
| SPACE | NAME                                   | ENCRYPTION_SCHEME | KEYSERVER_REQUESTS | MIN_KEY_VERSION | CURRENT_KEY_VERSION | KEY_ROTATION_PAGE_NUMBER | KEY_ROTATION_MAX_PAGE_NUMBER |
 
++-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+
 
|    10 | test/t_defaults                        |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     7 | test/t_default_encryption_existing_key |                 0 |                  0 |               0 |                   0 |                     NULL |                         NULL |
 
|     8 | test/t_encrypted_default_key           |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|     4 | test/t_encrypted_existing_key          |                 1 |                  1 |               1 |                   1 |                     NULL |                         NULL |
 
|     9 | test/t_not_encrypted_default_key       |                 0 |                  0 |               0 |                   1 |                     NULL |                         NULL |
 
|     5 | test/t_not_encrypted_existing_key      |                 0 |                  0 |               0 |                   1 |                     NULL |                         NULL |
 
|     6 | test/t_not_encrypted_non_existing_key  |                 0 |                  0 |               0 |          4294967295 |                     NULL |                         NULL |
 
+-------+----------------------------------------+-------------------+--------------------+-----------------+---------------------+--------------------------+------------------------------+

The other way round, starting with innodb_encrypt_tables = 1 and then switching to 0, works in a similar fashion.

Could you please take a look whether these results are expected, and if they are, switch the task to Documentation so that the I_S table is properly described somewhere.

Note: results above are of 10.1 commit 0dfa0eef596ee677b55976793d632dc9b36928c7

Comment by Sergei Golubchik [ 2015-11-17 ]

Yes, everything works as expected. Note that your set global innodb_encrypt_tables = 1 doesn't change anything because you don't have background rotation threads (innodb_rotation_threads=0) so tables are not encrypted or decrypted in background. But sleep 5 affects the value in the keyserver_requests column. This column shows (per tablespace) how many times InnoDB has requested an encryption key from the encryption plugin. It needs a key when it writes the to-be-encrypted page on disk. First SELECT happens immediately after the table is created, all pages are still in the buffer pool, not flushed. During the 5-sec pause InnoDB flushes modified pages, it requests the key to encrypt pages before writing them to disk. You can remove set global innodb_encrypt_tables = 1 and only keep sleep 5, the effect will be the same (I tried).

CURRENT_KEY_VERSION changes to 4294967295

I agree that this is harmless and ugly, let's fix it, but in a separate MDEV.

Comment by Ian Gilfillan [ 2015-11-18 ]

This has been documented at https://mariadb.com/kb/en/mariadb/information-schema-innodb_tablespaces_encryption-table/ - the current key version issue mentioned above will be a separate task.

Generated at Thu Feb 08 07:25:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.