[#MDEV-8888] Redo log encrypion does not seam to work when enabling after installing

Comment by Elena Stepanova [ 2015-10-03 ]

Please provide your configuration (cnf files), data structure, INSERT that you are running and the string that you find in the InnoDB logs.

Here is an example of my results (could not reproduce the problem so far):

New server started with innodb_encrypt_log=1
MariaDB [test]> select @@innodb_encrypt_log;
+----------------------+
\| @@innodb_encrypt_log \|
+----------------------+
\| 1 \|
+----------------------+
1 row in set (0.00 sec)

MariaDB [test]> create table t1 (pk int primary key, c varchar(256)) engine=InnoDB;
Query OK, 0 rows affected (0.58 sec)

MariaDB [test]> insert into t1 values (1,'foobar1');
Query OK, 1 row affected (0.10 sec)

MariaDB [test]> system strings data/ib_logfile* \| grep foobar1

Server is restarted with innodb_encrypt_log=0
MariaDB [test]> select @@innodb_encrypt_log;
+----------------------+
\| @@innodb_encrypt_log \|
+----------------------+
\| 0 \|
+----------------------+
1 row in set (0.01 sec)

MariaDB [test]> create table t2 (pk int primary key, c varchar(256)) engine=InnoDB;
Query OK, 0 rows affected (0.61 sec)

MariaDB [test]> insert into t2 values (1,'foobar2');
Query OK, 1 row affected (0.10 sec)

MariaDB [test]> system strings data/ib_logfile* \| grep foobar2
foobar2

Server is restarted again with innodb_encrypt_log=1
MariaDB [test]> select @@innodb_encrypt_log;
+----------------------+
\| @@innodb_encrypt_log \|
+----------------------+
\| 1 \|
+----------------------+
1 row in set (0.00 sec)

MariaDB [test]> create table t3 (pk int primary key, c varchar(256)) engine=InnoDB;
Query OK, 0 rows affected (0.57 sec)

MariaDB [test]> insert into t3 values (1,'foobar3');
Query OK, 1 row affected (0.14 sec)

MariaDB [test]> system strings data/ib_logfile* \| grep foobar3

Comment by Petri Rautiainen [ 2015-10-24 ]

I was able to reproduce this on 10.1.8 and 10.1.7 with quite random results. I made small php script that ran in iterations of 50 inserted rows after changing innodb_encrypt_log on/off, also restarted the server always with the new setting and had 5 second sleep after restart and also had 1 second sleep after each insert, just in case. Test run starts with encryption on.
Schema and queries below (this was in 10.1.8).
Table:

CREATE TABLE `php_enc` (

  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,

  `str` varchar(255) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=501 DEFAULT CHARSET=utf8

Insert statement:

INSERT INTO php_enc (str) VALUES

    ( CONCAT('php_enc_', (SELECT @@innodb_encrypt_log),'_', ?));

As I was using pdo for prepared statements I set i from loop which is same value with auto_increment. This is how I wanted to distinguish if there will be log entries that were not encrypted when @@innodb_encrypt_log was on. Here are rows that did not get encrypted:

mysql [localhost] {msandbox} (enctest) > system strings data/ib_logfile* | grep 'php_enc_ON'

php_enc_ON_50

php_enc_ON_148

php_enc_ON_149

php_enc_ON_150

php_enc_ON_249

php_enc_ON_250

php_enc_ON_350

php_enc_ON_448

php_enc_ON_449

php_enc_ON_450

The results actually vary a lot, here is same script run second time on 10.1.8, oddly enough it shares the same rows but not all of them. And did clear out the ib_logfiles before doing second run:

mysql [localhost] {msandbox} (enctest) > system strings data/ib_logfile* | grep 'php_enc_ON'

php_enc_ON_150

php_enc_ON_249

php_enc_ON_250

php_enc_ON_350

php_enc_ON_450

my.cnf (my.sandbox.cnf)

[mysql]

prompt='mysql [\h] {\u} (\d) > '

[client]

user               = msandbox

password           = msandbox

port               = 10108

socket             = /tmp/mysql_sandbox10108.sock

[mysqld]

user               = dunnock

port               = 10108

socket             = /tmp/mysql_sandbox10108.sock

basedir            = /home/dunnock/mysql/./10.1.8

datadir            = /home/dunnock/sandboxes/msb_10_1_8/data

tmpdir             = /home/dunnock/sandboxes/msb_10_1_8/tmp

lower_case_table_names = 0

pid-file           = /home/dunnock/sandboxes/msb_10_1_8/data/mysql_sandbox10108.pid

bind-address       = 127.0.0.1

innodb_file_per_table          = 1

expire-logs-days               = 14

sync-binlog                    = 1

log-error=msandbox.err

character-set-server = utf8

collation-server = utf8_general_ci

file_key_management_filename = /home/dunnock/sandboxes/msb_10_1_8/enc.in

plugin-load-add=file_key_management.so

innodb-encrypt-log = 1

No errors in the error log, all was fine for that. Same run without restarts goes without problems.

Comment by Jan Lindström (Inactive) [ 2015-10-29 ]

Do you mean that you inserted some data when innodb-encrypt-log=OFF ? If that is true, then they remain naturally unencrypted, there is no background encryption for redo-logs. Log is encrypted if and only if innodb-encrypt-log=ON, changing the value does not effect old data only new data after the setting is enabled.

Comment by Petri Rautiainen [ 2015-10-29 ]

I did do the run in iterations and like you see from the results when innodb-encrypt-log was on some of the inserts were not encrypted. Most of it was encrypted but not all, but that was the extreme test and most likely does not reflect real world at all.

Comment by Michaël de groot [ 2015-10-29 ]

This issue is a duplicate to ~~MDEV-9011~~ - I thought this issue was closed already; when creating this issue I didn't have the time to do much more then create it, in #9011 elenst found that 128bit encryption does not work in the redo log encryption.

For both JIRA issues I used your blog post as tutorial: https://blog.mariadb.org/table-and-tablespace-encryption-on-mariadb-10-1-3/ so the encryption is the same in #9011.

Slightly off topic: The blog post has old configuration parameters. I think easiest is to change the configuration parameters in the blog post or create a note about it?

Comment by Jan Lindström (Inactive) [ 2015-10-30 ]

Up to datte documentation is on https://mariadb.com/kb/en/mariadb/data-at-rest-encryption/

[MDEV-8888] Redo log encrypion does not seam to work when enabling after installing Created: 2015-10-02 Updated: 2015-10-30 Resolved: 2015-10-30
Status:	Closed
Project:	MariaDB Server
Component/s:	Data Definition - Alter Table, Documentation, Encryption
Affects Version/s:	10.1.7
Fix Version/s:	10.1.9

[MDEV-8888] Redo log encrypion does not seam to work when enabling after installing Created: 2015-10-02 Updated: 2015-10-30 Resolved: 2015-10-30