Getting errors when starting up with innodb_encrypt_tables=ON on MySQL8.0

MySQL & MariaDB Percona Distribution for MySQL
1

I’m on mysql 8.0.21-12 and i’m getting an error when starting up:

2023-10-12T18:44:35.121528-04:00 0 [ERROR] [MY-000067] [Server] unknown variable ‘innodb_encrypt_tables=ON’.

I tried using the variable default_table_encryption = ON
but that doesnt seem to work.
I want to have a table automatically set to encrypted if a user runs a create table statement.

Is there a different variable that replaced that variable in mysql 8.0?

2

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_table_encryption

default_table_encryption=ON

3

Thanks.
I did try using that but it didn’t seem to work and I had to explicitly create a table with encryption.
Let me check again as it might be user error.
Thanks

4

After reading and testing I found what might be your issue as I was unable to get this working as well.

The MySQL manual says that default_table_encryption applies only to CREATE [DATABASE|SCHEMA] and CREATE TABLESPACE commands. It does not apply to CREATE TABLE. When using CREATE TABLE the new table will follow the schema’s default.

mysql [localhost:8035] {msandbox} (enctest) > SELECT @@default_table_encryption;
+----------------------------+
| @@default_table_encryption |
+----------------------------+
| ON                         |
+----------------------------+
1 row in set (0.00 sec)

mysql [localhost:8035] {msandbox} ((none)) > CREATE DATABASE enctest;
Query OK, 1 row affected (0.01 sec)

mysql [localhost:8035] {msandbox} ((none)) > SHOW CREATE DATABASE enctest;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                   |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| enctest  | CREATE DATABASE `enctest` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8035] {msandbox} ((none)) > use enctest;
Database changed
mysql [localhost:8035] {msandbox} (enctest) > CREATE TABLE enc (id int unsigned primary key);
Query OK, 0 rows affected (0.04 sec)

mysql [localhost:8035] {msandbox} (enctest) > show create table enc\G
*************************** 1. row ***************************
       Table: enc
Create Table: CREATE TABLE `enc` (
  `id` int unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ENCRYPTION='Y'
1 row in set (0.01 sec)

Notice how I did not specify an encryption default for the database. It was added automatically due to the variable. Then I created a table, again, not specifying encryption and it was added automatically because the database defaults on.

You can prevent users from creating tables without encryption by setting another parameter, table_encryption_privilege_check=1:

mysql [localhost:8035] {foo} (enctest) > SELECT @@table_encryption_privilege_check;
+------------------------------------+
| @@table_encryption_privilege_check |
+------------------------------------+
|                                  1 |
+------------------------------------+
1 row in set (0.00 sec)

mysql [localhost:8035] {foo} (enctest) > CREATE TABLE notenc (
  id int unsigned primary key) ENCRYPTION='N';
ERROR 3826 (HY000): Table encryption differ from its database default encryption, and user doesn't have enough privilege.
5

thanks!
i thought there was no way to do it and i would have to set databases to encryption first.
thank you so much!