Possible solutions:

• Spatial indexes has page type FIL_PAGE_RTREE, using that we can easily left those pages as they are in encryption. Secondly we should not allow to create table with encrypted=ON if table contains spatial index. OFF and DEFAULT we need to support.
• Totally new page contents design for FIL_PAGE_RTREE in case we use encryption.

bb-10.2-MDEV-11974

Some changes are needed. Most notably, compression should work on spatial indexes.
Please address my comments on GitHub.

Addressed comments except about new error message, server team
is not that happy about new engine specific errors.

http://lists.askmonty.org/pipermail/commits/2017-February/010630.html

Thanks, it looks almost OK now.

Please demonstrate in some way that page compression actually works with SPATIAL INDEX. Can you for example run -suite=innodb_gis with some --mysqld=-innodb… option that will cause all tables to be compressed by default?

Test1: --suite=innodb_gis --big-test --force

Contains result set differences like:

-) ENGINE=InnoDB DEFAULT CHARSET=latin1

+) ENGINE=InnoDB DEFAULT CHARSET=latin1 `PAGE_COMPRESSED`='ON'

Completed: Failed 7/9 tests, 22.22% were successful.

Test2: --suite=innodb --big-test --force: Too many failed: Failed 10/25 tests, 60.00% were successful. Again above kind of result differences and failures to create table as compression requires innodb-file-per-table

Using:.

/mtr --vardir=/dev/shm --mysqld=--innodb-encrypt-tables=ON --mysqld=--loose-file-key-management --mysqld=--plugin-load-add=file_key_management.so --mysqld=--loose-file-key-management-filename=/dev/shm/std_data/keys.txt --mysqld=--file-key-management-encryption-algorithm=aes_ctr --suite=innodb_gis --force

Completed: Failed 1/9 tests, 88.89% were successful, and that failure was because Unknown table 'mysql.innodb_table_stats'.

http://lists.askmonty.org/pipermail/commits/2017-February/010635.html

OK to push. Please explain in the commit message why we cannot support encryption in SPATIAL INDEX pages. And add an --echo #FIXME comment to the test that successfully creates a spatial index with ENCRYPTION=DEFAULT, without actually encrypting the index.

I think that the title of this MDEV must be changed to ‘Disable encryption in spatial indexes’ and a follow-up MDEV must be filed for actually implementing an encryption that works with spatial indexes.

commit 7b27465e10c0bad648d193af8062b78b10c2b1c6
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Tue Feb 7 15:55:01 2017 +0200

MDEV-11974: MariaDB 10.2 encryption does not support spatial indexes

Encryption stores used key_version to
FIL_PAGE_FILE_FLUSH_LSN_OR_KEY_VERSION (offset 26)
field. Spatial indexes store RTREE Split Sequence Number
(FIL_RTREE_SPLIT_SEQ_NUM) in the same field. Both values
can't be stored in same field. Thus, current encryption
implementation does not support encrypting spatial indexes.

fil_space_encrypt(): Do not encrypt page if page type is
FIL_PAGE_RTREE (this is required for background
encryption innodb-encrypt-tables=ON).

create_table_info_t::check_table_options() Do not allow creating
table with ENCRYPTED=YES if table contains spatial index.