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,
When this occurs, you can usually get more information about the cause of the error by following it with a SHOW WARNINGS statement.
This error is known to occur in the following cases:
Encrypting a table by setting the table option to YES when the is set to OFF.In this case, would return the following:
Encrypting a table by setting the table option to YES, and the system variable or the table option refers to a non-existent key identifier. In this case, would return the following:
In some versions, this could happen while creating a table with the table option set to DEFAULT while the system variable is set to OFF, and the system variable or the table option are not set to 1. In this case, would return the following:
Creating a table with the table option set to DEFAULT while the system variable is set to OFF, and the system variable or the table option are not set to 1 no longer fail, and it no longer throws a warning.
For more information, see .
If you set the table option for a table that is unencrypted because the system variable is set to OFF and the table option set to DEFAULT, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
As a side effect, with the current encryption design, if the system variable is later set to ON, and InnoDB goes to encrypt the table, then the will not read this encryption key ID from the .frm file. Instead, the threads may encrypt the table with the encryption key with ID 1, which is internally considered the default encryption key when no key is specified. For example:
A similar problem is that, if you set the table option for a table that is unencrypted because the table option is set to NO, then this encryption key ID will be saved in the table's .frm file, but the encryption key will not be saved to the table's .ibd file.
Recent versions of MariaDB will throw warnings in the case where the table option is set to NO, but they will allow the operation to succeed. For example:
However, in this case, if you change the table option to YES or DEFAULT with , then it will actually use the proper key. For example:
For more information, see , , and .
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 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.
If is set to full_crc32 or strict_full_crc32, and if the table does not use , then data files are guaranteed to be zero-initialized.
For more information, see .
Support for encrypting . To enable, set the to full_crc32 or to strict_full_crc32. Note that MariaDB only encrypts spatial indexes when the table option is not set to .
For more information, see .
This page is licensed: CC BY-SA / Gnu FDL
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")SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED requires innodb_file_per_table |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTION_KEY_ID 500 not available |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)SHOW WARNINGS;
+---------+------+---------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------+
| Warning | 140 | InnoDB: innodb_encrypt_tables=OFF only allows ENCRYPTION_KEY_ID=1 |
| Error | 1005 | Can't create table `db1`.`tab3` (errno: 140 "Wrong create options") |
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB |
+---------+------+---------------------------------------------------------------------+
3 rows in set (0.00 sec)SET GLOBAL innodb_encrypt_tables=OFF;
CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTION_KEY_ID=100;
SET GLOBAL innodb_encrypt_tables=ON;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME='db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 1 |
+----------+-------------------+----------------+CREATE TABLE tab1 (
id INT PRIMARY KEY,
str VARCHAR(50)
) ENCRYPTED=NO ENCRYPTION_KEY_ID=100;
Query OK, 0 rows affected, 1 warning (0.01 sec)
SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------+
| Warning | 140 | InnoDB: ENCRYPTED=NO implies ENCRYPTION_KEY_ID=1 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)SET GLOBAL innodb_encrypt_tables=ON;
ALTER TABLE tab1 ENCRYPTED=DEFAULT;
SELECT NAME, ENCRYPTION_SCHEME, CURRENT_KEY_ID
FROM information_schema.INNODB_TABLESPACES_ENCRYPTION
WHERE NAME = 'db1/tab1';
+----------+-------------------+----------------+
| NAME | ENCRYPTION_SCHEME | CURRENT_KEY_ID |
+----------+-------------------+----------------+
| db1/tab1 | 1 | 100 |
+----------+-------------------+----------------+