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 and innodb_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 the ENCRYPTED=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 while innodb_encrypt_tables=OFF was set, and innodb_default_encryption_key_id or the ENCRYPTION_KEY_ID table option was not set to 1.

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.