1--source include/have_innodb.inc 2--source include/have_file_key_management_plugin.inc 3 4# 5# MDEV-11974: MariaDB 10.2 encryption does not support spatial indexes 6# 7 8# 9# (1) Do not allow creating table with ENCRYPTED=YES 10# 11# 12--error ER_CANT_CREATE_TABLE 13CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, 14c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB 15ENCRYPTED=YES; 16 17# 18# (2) Alter table 19# 20CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, 21c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB; 22--error ER_ILLEGAL_HA_CREATE_OPTION 23ALTER TABLE t1 ENCRYPTED=YES; 24DROP TABLE t1; 25 26# 27# Index creation 28# 29CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, 30c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=YES ENGINE=INNODB; 31# FIXME: MDEV-13851 Encrypted table refuses some form of ALGORITHM=COPY, 32# but allows rebuild by FORCE 33--error ER_CANT_CREATE_TABLE 34ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), ALGORITHM=COPY; 35--error ER_ILLEGAL_HA_CREATE_OPTION 36ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate), FORCE, ALGORITHM=INPLACE; 37--error ER_ILLEGAL_HA_CREATE_OPTION 38ALTER TABLE t1 ADD SPATIAL INDEX(coordinate); 39--error ER_ILLEGAL_HA_CREATE_OPTION 40CREATE SPATIAL INDEX b on t1(coordinate); 41DROP TABLE t1; 42 43CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, 44c VARCHAR(256), coordinate POINT NOT NULL) ENCRYPTED=DEFAULT ENGINE=INNODB; 45CREATE SPATIAL INDEX b on t1(coordinate); 46INSERT INTO t1 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); 47ALTER TABLE t1 DROP INDEX b; 48INSERT INTO t1 values(2, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); 49ALTER TABLE t1 ADD SPATIAL INDEX b(coordinate); 50INSERT INTO t1 values(3, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); 51DROP TABLE t1; 52# 53# (3) Default encryption should still work 54# 55 56CREATE TABLE t1 (pk INT PRIMARY KEY AUTO_INCREMENT, 57c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB; 58CREATE TABLE t2 (pk INT PRIMARY KEY AUTO_INCREMENT, 59c VARCHAR(256), coordinate POINT NOT NULL, SPATIAL index(coordinate)) ENGINE=INNODB PAGE_COMPRESSED=YES; 60 61INSERT INTO t1 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); 62INSERT INTO t2 values(1, 'secret', ST_GeomFromText('POINT(903994614 180726515)')); 63 64--let $wait_timeout=600 65--let $wait_condition=SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; 66--source include/wait_condition.inc 67 68--echo # Success! 69 70--sorted_result 71SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION > 0; 72--sorted_result 73SELECT NAME FROM INFORMATION_SCHEMA.INNODB_TABLESPACES_ENCRYPTION WHERE MIN_KEY_VERSION = 0; 74 75DROP TABLE t1, t2; 76