InnoDB / XtraDb Encryption Troubleshooting
Wrong Create Options
With InnoDB tables using encryption, there are several cases where a CREATE TABLE
or ALTER TABLE
statement can throw Error 1005, due to the InnoDB error 140, Wrong create options
. For instance,
CREATE TABLE `test`.`table1` ( `id` int(4) primary key , `name` varchar(50)); ERROR 1005 (HY000): Can't create table `test`.`table1` (errno: 140 "Wrong create options")
When this occurs, you can usually get more information about the cause of the error by following it with a SHOW WARNINGS
statement.
SHOW WARNINGS; +---------+------+----------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------------------------------+ | Warning | 140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 | | Error | 1005 | Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options") | | Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB | +---------+------+----------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
This error is known to occur in the following cases:
- Encrypting a table by setting the
ENCRYPTED
table option toYES
when theinnodb_file_per_table
is set toOFF
. - Encrypting a table with the
ENCRYPTION_KEY_ID
table option using an invalid key identifier. - Encrypting a table by setting
ENCRYPTION_KEY_ID
table option without also setting theENCRYPTED
table option toYES
. For more information, see MDEV-17230. - Encrypting a table with the
ENCRYPTED
table option set toYES
while theinnodb_encrypt_tables
system variable is set toOFF
, and theinnodb_default_encryption_key_id
system variable or theENCRYPTION_KEY_ID
table option are not set to1
.
Tablespaces Created on MySQL 5.1.47 or Earlier
MariaDB's data-at-rest encryption implementation re-used previously unused fields in InnoDB's buffer pool pages to identify the encryption key version and the post-encryption checksum. Prior to MySQL 5.1.48, these unused fields were not initialized in memory due to performance concerns. These fields still had zero values most of the time, but since they were not explicitly initialized, that means that these fields could have occasionally had non-zero values that could have been written into InnoDB's tablespace files. If MariaDB were to encounter an unencrypted page from a tablespace file that was created on an early version of MySQL that also had non-zero values in these fields, then it would mistakenly think that the page was encrypted.
The fix for MDEV-12112 that was included in MariaDB 10.1.38, MariaDB 10.2.20, and MariaDB 10.3.12 changed the way that MariaDB distinguishes between encrypted and unencrypted pages, so that it is less likely to mistake an unencrypted page for an encrypted page.
In MariaDB 10.4.3 and later, if innodb_checksum_algorithm
is set to full_crc32
or strict_full_crc32
, and if the table does not use ROW_FORMAT=COMPRESSED
, then data files will be guaranteed to be zero-initialized.
For more information, see MDEV-18097.
Spatial Indexes
MariaDB 10.4.3 introduces support for encrypting spatial indexes. To enable, set the innodb_checksum_algorithm
to full_crc32
or to strict_full_crc32
. Note that MariaDB only encrypts spatial indexes when the ROW_FORMAT
table option is not set to COMPRESSED
.
In older versions of MariaDB, spatial index encryption is unsupported. Tables that contain spatial indexes store them unencrypted.
For more information, see MDEV-12026.