A CREATE TABLE or ALTER TABLE of an encrypted table with SPATIAL INDEX was prevented in MDEV-11974, at least in some tested cases. There was a refinement for ALTER TABLE.

Surprises with unencrypted data (minimum bounding rectangles and PRIMARY KEY values) might be possible with innodb_encrypt_tables=ON.

I think that we can do the following:

1. Introduce a new page type code for encrypted spatial index.
2. Before encryption, store the Split Sequence Number (SSN) in the space that was reserved for the strings "infimum" and "supremum". Zero-fill the rest of the strings, similar to how MDEV-15562 did it for the clustered index root page.
3. When decrypting, restore the SSN to the correct place, and fill in the strings "infimum" and "supremum".
4. On the encrypted page, the encryption parameters will be in the familiar location (the same location that was repurposed for the SSN in the unencrypted spatial index page).

Thanks to the new page type code, the index pages should be unaccessible in earlier versions of MariaDB.

As part of this fix, the creation of encrypted tables with SPATIAL INDEX must be re-enabled.

CREATE TABLE t1 (pk VARCHAR(100), c VARCHAR(256), coordinate POINT NOT NULL, primary key (pk),SPATIAL index(coordinate)) ENGINE=INNODB;

INSERT INTO t1 values('mysql', 'secret', ST_GeomFromText('POINT(903994614 180726515)'));

# Success!

SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0;

NAME

innodb_system

mysql/innodb_index_stats

mysql/innodb_table_stats

mysql/transaction_registry

test/t1

Open t1.ibd and check 4th page (root page of spatial index)

00010000: 9402 925b 0000 0004 ffff ffff ffff ffff  ...[............

00010010: 0000 0000 0002 6878 45be 0000 0000 0000  ......hxE.......

00010020: 0000 0000 0005 0002 00a4 8003 0000 0000  ................

00010030: 007f 0005 0000 0001 0000 0000 0000 0024  ...............$

00010040: 0000 0000 0000 0000 001a 0000 0005 0000  ................

00010050: 0002 0272 0000 0005 0000 0002 01b2 0100  ...r............

00010060: 0200 1c69 6e66 696d 756d 0002 000b 0000  ...infimum......

00010070: 7375 7072 656d 756d 0520 0000 10ff f100  supremum. ......

00010080: 0000 7bee f0ca 4100 0000 7bee f0ca 4100  ..{...A...{...A.

00010090: 0000 e655 8ba5 4100 0000 e655 8ba5 416d  ...U..A....U..Am

000100a0: 7973 716c 0000 0000 0000 0000 0000 0000  ysql............

000100b0: 0000 0000 0000 0000 0000 0000 0000 0000  ................

Primary key value "mysql" is visible to read even though encryption is enabled.

My idea of repurposing the fields "infimum" and "supremum" will not work with ROW_FORMAT=COMPRESSED tables, because those pages omit these records.

But, the following fields are unused in all index pages except the root pages:

#define PAGE_BTR_SEG_LEAF 36	/* file segment header for the leaf pages in

				a B-tree: defined only on the root page of a

				B-tree, but not in the root of an ibuf tree */

#define PAGE_BTR_IBUF_FREE_LIST	PAGE_BTR_SEG_LEAF

#define PAGE_BTR_IBUF_FREE_LIST_NODE PAGE_BTR_SEG_LEAF

				/* in the place of PAGE_BTR_SEG_LEAF and _TOP

				there is a free list base node if the page is

				the root page of an ibuf tree, and at the same

				place is the free list node if the page is in

				a free list */

#define PAGE_BTR_SEG_TOP (36 + FSEG_HEADER_SIZE)

				/* file segment header for the non-leaf pages

				in a B-tree: defined only on the root page of

				a B-tree, but not in the root of an ibuf

				tree */

It turns out that even in root pages, the first 4 bytes of each field are redundant:

#define FSEG_HDR_SPACE		0	/*!< space id of the inode */

#define FSEG_HDR_PAGE_NO	4	/*!< page number of the inode */

#define FSEG_HDR_OFFSET		8	/*!< byte offset of the inode */

#define FSEG_HEADER_SIZE	10	/*!< Length of the file system

					header, in bytes */

Let us repurpose the first 4 bytes of PAGE_BTR_SEG_TOP in the encrypted copy of the page, to store the encrypted 32-bit split sequence number (SSN). The encryption algorithm should be like this:

1. encrypt up to PAGE_BTR_SEG_TOP
2. encrypt a buffer that holds the SSN in big-endian format
3. encrypt from PAGE_BTR_SEG_TOP + 4 onwards

When decrypting, we would copy the SSN into the right place in the unencrypted page, and also restore the original contents:

memcpy(block->frame + PAGE_BTR_SEG_TOP, block->frame + PAGE_BTR_SEG_LEAF, 4);

It would be good to add

ut_ad(!memcmp(block->frame + PAGE_BTR_SEG_TOP, block->frame + PAGE_BTR_SEG_LEAF, 4));

to the encryption code path.

We now have a patch that implements my suggestion above. This patch could theoretically backported to 10.2 and 10.3. If that patch were applied, an IMPORT TABLESPACE or a downgrade to an earlier version would seemingly succeed, but we would get crashes somewhere later when the encrypted spatial index is being accessed.

During the recent work on MDEV-12112 and MDEV-17957, an alternative (and in my opinion better) approach occurred to me.

Instead of shuffling bytes in multiple higher-level places of InnoDB, I think that the clean solution is to introduce a new encrypted file format, identified by a new flag in FSP_SPACE_FLAGS that would also prevent downgrading to earlier versions of MariaDB. This flag would indicate the following:

• Unused bytes on data pages are initialized to zero. (As noted in MDEV-18097, files created before this change in MySQL 5.1.48 could contain any garbage values, limiting the ability to repurpose pages.)
• A new page checksum algorithm is always being used, and it cannot be overridden by innodb_checksum_algorithm.
• Only one page_compressed algorithm is allowed, and it cannot be overridden. The page header will include the checksum and the compressed size of the page.
• Only one checksum will be computed on the page payload (after any compression or encryption).
• The key_version will be stored in the first 4 bytes of each page. So, there is no collision with SPATIAL INDEX.

We’d introduce a new (and default) configuration parameter value
innodb_checksum_algorithm=full_crc32 (or a better name) with a strict_ counterpart, which would refuse to open old data files.

The non-strict variant would open old tablespaces fine, but it would create new files with the new bit in FSP_SPACE_FLAGS set, preventing a downgrade.

If compatibility with older MariaDB or MySQL versions is desired, then innodb_checksum_algorithm=crc32 can be set. In this case, any pre-existing new-checksum files would be opened fine, but they would keep using the new format. Any new created files would use the old format and the configured innodb_checksum_algorithm.

Setting innodb_checksum_algorithm to something else than the default value (or its strict_ counterpart) would issue a deprecation warning, because we would want to remove the old algorithms and formats in some future release of MariaDB.

To implement this, we’d have to do a few things differently when decrypting or encrypting pages, based on fil_space_t::flags. A member function could be introduced for those checks, something like this:

bool use_full_checksum() const {return flags & FSP_FLAGS_FULL_CHECKSUM; }

This was pushed to MariaDB 10.4.3.
By default, encryption will not be supported on SPATIAL INDEX pages. To do that, you will have to do one of the following:

-- create new files in the new format; allow any checksum for old files

SET GLOBAL innodb_checksum_algorithm=full_crc32;

-- equivalent to strict_crc32 for old files

SET GLOBAL innodb_checksum_algorithm=strict_full_crc32;

The follow-up task MDEV-18644 was filed for implementing support for page_compressed pages with this format.

ROW_FORMAT=COMPRESSED tables will not use the new format.