[MDEV-14178] can't encrypt table innodb - get errno: 140 Wrong Create Options Created: 2017-10-27  Updated: 2017-11-29  Resolved: 2017-11-29

Status: Closed
Project: MariaDB Server
Component/s: Encryption
Affects Version/s: 10.1.23
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Richard Whitney Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: encryption, innodb, need_feedback


 Description   

Given the following command (mariadb 10.1.23 on Debian Stretch (Raspbian))

ALTER TABLE TBL_ACCOUNT ENCRYPTED=YES;

I get:

ERROR 1005 (HY000): Can't create table `#sql-2c3_15` (errno: 140 "Wrong create options")

and no other error.



 Comments   
Comment by Richard Whitney [ 2017-10-27 ]

Will this help?

MariaDB [KTC2016]> show variables like '%encrypt%';
 
Connection id:    2
 
Current database: KTC2016
 
 
 
+----------------------------------+-------+
 
| Variable_name                    | Value |
 
+----------------------------------+-------+
 
| aria_encrypt_tables              | OFF   |
 
| encrypt_binlog                   | OFF   |
 
| encrypt_tmp_disk_tables          | OFF   |
 
| encrypt_tmp_files                | OFF   |
 
| innodb_default_encryption_key_id | 1     |
 
| innodb_encrypt_log               | OFF   |
 
| innodb_encrypt_tables            | OFF   |
 
| innodb_encryption_rotate_key_age | 1     |
 
| innodb_encryption_rotation_iops  | 100   |
 
| innodb_encryption_threads        | 0     |
 
+----------------------------------+-------+

Comment by Elena Stepanova [ 2017-10-30 ]

By far the most likely reason is that you don't have an encryption plugin, or it failed to start, or it is misconfigured. From the variables above it appears that at least file_key_management plugin is not installed; of course, it's possible that you are using some other plugin of your own.

If you think that you do have the plugin, right after failing ALTER TABLE, run SHOW WARNINGS, like so:

MariaDB [test]> ALTER TABLE TBL_ACCOUNT ENCRYPTED=YES;
 
ERROR 1005 (HY000): Can't create table `test`.`#sql-34b0_3` (errno: 140 "Wrong create options")
 
MariaDB [test]> SHOW WARNINGS;
 
+---------+------+-----------------------------------------------------------------------------+
 
| Level   | Code | Message                                                                     |
 
+---------+------+-----------------------------------------------------------------------------+
 
| Warning |  140 | InnoDB: ENCRYPTION_KEY_ID 1 not available                                   |
 
| Error   | 1005 | Can't create table `test`.`#sql-34b0_3` (errno: 140 "Wrong create options") |
 
| Warning | 1030 | Got error 140 "Wrong create options" from storage engine InnoDB             |
 
+---------+------+-----------------------------------------------------------------------------+

It might give you some additional information about what went wrong. Also check the error log.

Generated at Thu Feb 08 08:11:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.