Bug #96994 Changing default encryption for a schema will reset default collation

• Submitted: 24 Sep 2019 12:08
• Modified: 9 Oct 2019 14:48
• Reporter: Sivert Sørumgård
• Status: Closed
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.19
• OS: Any
• CPU Architecture: Any

[24 Sep 2019 12:08] Sivert Sørumgård

Description:
Changing default encryption for a schema will reset default collation.

How to repeat:
mysql> create schema s default character set ascii;

mysql> show create schema s;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| s        | CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET ascii */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+

mysql> alter schema s default encryption = 'y';

mysql> show create schema s;
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                             |
+----------+-----------------------------------------------------------------------------------------------------------------------------+
| s        | CREATE DATABASE `s` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='Y' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------+

Suggested fix:
diff --git a/sql/sql_db.cc b/sql/sql_db.cc
index 582fc081eb1..39499760e6f 100644
--- a/sql/sql_db.cc
+++ b/sql/sql_db.cc
@@ -436,8 +436,6 @@ bool mysql_alter_db(THD *thd, const char *db, HA_CREATE_INFO *create_info) {
 
   if (lock_schema_name(thd, db)) return true;
 
-  set_db_default_charset(thd, create_info);
-
   dd::cache::Dictionary_client::Auto_releaser releaser(thd->dd_client());
   dd::Schema *schema = nullptr;
   if (thd->dd_client()->acquire_for_modification(db, &schema)) return true;
@@ -447,8 +445,12 @@ bool mysql_alter_db(THD *thd, const char *db, HA_CREATE_INFO *create_info) {
     return true;
   }
 
-  // Set new collation ID.
-  schema->set_default_collation_id(create_info->default_table_charset->number);
+  // Set new collation ID if submitted in the statement.
+  if (create_info->used_fields & HA_CREATE_USED_DEFAULT_CHARSET) {
+    set_db_default_charset(thd, create_info);
+    schema->set_default_collation_id(
+        create_info->default_table_charset->number);
+  }

[9 Oct 2019 14:48] Paul Dubois

Fixed in 8.0.19.

Altering only the default encryption in an ALTER SCHEMA statement
caused the schema default character set and collation to be reset to
the system defaults.