InnoDB / XtraDb Encryption Troubleshooting
Wrong create options
There are several cases where a CREATE TABLE
or ALTER TABLE
statement that involves encryption on an InnoDB table can throw the following error:
Error: 140 Wrong create options.
You can usually execute SHOW WARNINGS
to get more details about the cause of the error. For example:
create table `table1` ( `id` int(4) primary key , `name` varchar(50)); ERROR 1005 (HY000): Can't create table `dba_test`.`table1` (errno: 140 "Wrong create options") 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 at least the following cases:
- You attempted to encrypt a table with
ENCRYPTED=YES
set as a table option andinnodb_file_per_table=OFF
is set. - You attempted to encrypt a table with the
ENCRYPTION_KEY_ID
table option set to a key that cannot be found. - You attempted to set the
ENCRYPTION_KEY_ID
table option for a table, but theENCRYPTED=YES
table option was not also set for that table. See MDEV-17230 about that. - You attempted to encrypt a table with
ENCRYPTED=YES
set as a table option whileinnodb_encrypt_tables=OFF
was set, andinnodb_default_encryption_key_id
or theENCRYPTION_KEY_ID
table option was 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.
See MDEV-18097 about plans for permanently fixing this issue.