[MDEV-11974] Disable encryption of spatial indexes in MariaDB 10.2 Created: 2017-02-02  Updated: 2017-09-20  Resolved: 2017-02-08

Status: Closed
Project: MariaDB Server
Component/s: Encryption, Storage Engine - InnoDB
Affects Version/s: 10.2
Fix Version/s: 10.2.4

Type: Bug Priority: Critical
Reporter: Jan Lindström (Inactive) Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: 10.2-rc

Issue Links:
Problem/Incident
causes MDEV-12026 Support encrypted SPATIAL INDEX Closed
causes MDEV-13851 Always check table options in ALTER T... Closed
Relates
relates to MDEV-11759 Encryption code in MariaDB 10.1/10.2 ... Closed
relates to MDEV-12711 backup may show corruption with custo... Closed

 Description   

Encryption must be properly tested with SPATIAL INDEX before 10.2 goes GA. I do not think that it can work in its current form. Note that MySQL 5.7 contains this change (to prepare for the repurposing of the field as FIL_RTREE_SPLIT_SEQ_NUM), but it does not retroactively clear the field to 0 on old files:

commit 8406d6aac3be6353bf693159ea0a5163edae0179
 
Author: Marko Mäkelä <marko.makela@oracle.com>
 
Date:   Wed Jul 2 10:26:42 2014 +0300
 
 
    WL#7990 Repurpose FIL_PAGE_FLUSH_LSN
 
    
    The field FIL_PAGE_FLUSH_LSN is consulted during InnoDB startup.
 
    
    At startup, InnoDB reads the FIL_PAGE_FLUSH_LSN from the first page of
 
    each file in the InnoDB system tablespace. If there are multiple
 
    files, the minimum and maximum LSN can differ. These numbers are
 
    passed to InnoDB startup.
 
    
    Having the number in other files than the first file of the InnoDB
 
    system tablespace is not providing much additional value. It is
 
    conflicting with other use of the field, such as on InnoDB R-tree
 
    index pages.
 
    
    The FIL_PAGE_FLUSH_LSN was also being written to InnoDB undo
 
    tablespace files, even though the fields are not going to be consulted
 
    on crash recovery.
 
    
    This worklog will stop writing FIL_PAGE_FLUSH_LSN to other files
 
    than the first file of the InnoDB system tablespace (page number 0:0).



 Comments   
Comment by Jan Lindström (Inactive) [ 2017-02-03 ]

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.
Comment by Jan Lindström (Inactive) [ 2017-02-07 ]

bb-10.2-MDEV-11974

Comment by Marko Mäkelä [ 2017-02-07 ]

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

Comment by Jan Lindström (Inactive) [ 2017-02-07 ]

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

Comment by Marko Mäkelä [ 2017-02-07 ]

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?

Comment by Jan Lindström (Inactive) [ 2017-02-07 ]

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

Comment by Jan Lindström (Inactive) [ 2017-02-07 ]

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'.

Comment by Jan Lindström (Inactive) [ 2017-02-07 ]

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

Comment by Marko Mäkelä [ 2017-02-07 ]

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.

Comment by Jan Lindström (Inactive) [ 2017-02-08 ]

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.

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