MDEV-22726: Add check that one can't change general or slow log to a transactional enginebb-10.2-MDEV-22726-aria-non-trans-log

This is post-push fix.

It is possible to change log tables engine to transactional one if
enforce_storage_engine is used.

create_info->db_type is set to the value of enforce_storage_engine in
check_engine() function, which is invoked after log tables engine check
in mysql_alter_table().

The fix is in moving log tables engine check code to the place where
check_engine() is already invoked and changed create_info->db_type.

Also log table engine check was added for CREATE TABLE.

4 files changed, 178 insertions, 48 deletions

diff --git a/mysql-test/r/log_tables.result b/mysql-test/r/log_tables.result
index 6b5a2ad1577..409cc358a34 100644
--- a/mysql-test/r/log_tables.result
+++ b/mysql-test/r/log_tables.result
@@ -265,8 +265,57 @@ ALTER TABLE mysql.general_log ENGINE=Aria transactional = 0;
 ALTER TABLE mysql.slow_log ENGINE=Aria;
 ERROR HY000: Only non-transactional Aria table can be used for logging
 ALTER TABLE mysql.slow_log ENGINE=Aria transactional = 0;
+SET SESSION enforce_storage_engine=InnoDB;
+ALTER TABLE mysql.slow_log ENGINE=MyISAM;
+ERROR HY000: Storage engine InnoDB cannot be used for log tables
+SET SESSION enforce_storage_engine=Aria;
+ALTER TABLE mysql.slow_log ENGINE=Aria;
+ERROR HY000: Only non-transactional Aria table can be used for logging
+SET SESSION enforce_storage_engine=NULL;
 drop table mysql.slow_log;
 drop table mysql.general_log;
+CREATE TABLE `general_log` (
+`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+ON UPDATE CURRENT_TIMESTAMP,
+`user_host` mediumtext NOT NULL,
+`thread_id` BIGINT(21) UNSIGNED NOT NULL,
+`server_id` int(10) unsigned NOT NULL,
+`command_type` varchar(64) NOT NULL,
+`argument` mediumtext NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='General log';
+ERROR HY000: Storage engine InnoDB cannot be used for log tables
+CREATE TABLE `general_log` (
+`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+ON UPDATE CURRENT_TIMESTAMP,
+`user_host` mediumtext NOT NULL,
+`thread_id` BIGINT(21) UNSIGNED NOT NULL,
+`server_id` int(10) unsigned NOT NULL,
+`command_type` varchar(64) NOT NULL,
+`argument` mediumtext NOT NULL
+) ENGINE=Aria DEFAULT CHARSET=utf8 COMMENT='General log';
+ERROR HY000: Only non-transactional Aria table can be used for logging
+SET SESSION enforce_storage_engine=InnoDB;
+CREATE TABLE `general_log` (
+`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+ON UPDATE CURRENT_TIMESTAMP,
+`user_host` mediumtext NOT NULL,
+`thread_id` BIGINT(21) UNSIGNED NOT NULL,
+`server_id` int(10) unsigned NOT NULL,
+`command_type` varchar(64) NOT NULL,
+`argument` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
+ERROR HY000: Storage engine InnoDB cannot be used for log tables
+SET SESSION enforce_storage_engine=NULL;
+CREATE TABLE `general_log` (
+`event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+ON UPDATE CURRENT_TIMESTAMP,
+`user_host` mediumtext NOT NULL,
+`thread_id` BIGINT(21) UNSIGNED NOT NULL,
+`server_id` int(10) unsigned NOT NULL,
+`command_type` varchar(64) NOT NULL,
+`argument` mediumtext NOT NULL
+) ENGINE=Aria transactional=0 DEFAULT CHARSET=utf8 COMMENT='General log';
+drop table mysql.general_log;
 drop table mysql.general_log;
 ERROR 42S02: Unknown table 'mysql.general_log'
 drop table mysql.slow_log;
diff --git a/mysql-test/t/log_tables.test b/mysql-test/t/log_tables.test
index 1146611ac6c..b8e7773ef5b 100644
--- a/mysql-test/t/log_tables.test
+++ b/mysql-test/t/log_tables.test
@@ -1,5 +1,6 @@
 # this test needs multithreaded mysqltest
 -- source include/not_embedded.inc
+-- source include/have_innodb.inc
 #
 # Basic log tables test
 #
@@ -270,16 +271,71 @@ alter table mysql.slow_log engine=memory;
 set storage_engine= @save_storage_engine;
 
 # Make sure only non-transactional Aria table can be used for logging
---error ER_TRANSACTIONAL_ARIA_LOG_ENGINE
+--error ER_UNSUPORTED_LOG_ENGINE
 ALTER TABLE mysql.general_log ENGINE=Aria;
 ALTER TABLE mysql.general_log ENGINE=Aria transactional = 0;
---error ER_TRANSACTIONAL_ARIA_LOG_ENGINE
+--error ER_UNSUPORTED_LOG_ENGINE
 ALTER TABLE mysql.slow_log ENGINE=Aria;
 ALTER TABLE mysql.slow_log ENGINE=Aria transactional = 0;
 
+SET SESSION enforce_storage_engine=InnoDB;
+--error ER_UNSUPORTED_LOG_ENGINE
+ALTER TABLE mysql.slow_log ENGINE=MyISAM;
+SET SESSION enforce_storage_engine=Aria;
+--error ER_UNSUPORTED_LOG_ENGINE
+ALTER TABLE mysql.slow_log ENGINE=Aria;
+SET SESSION enforce_storage_engine=NULL;
+
 drop table mysql.slow_log;
 drop table mysql.general_log;
 
+--error ER_UNSUPORTED_LOG_ENGINE
+CREATE TABLE `general_log` (
+  `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+                         ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `command_type` varchar(64) NOT NULL,
+  `argument` mediumtext NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='General log';
+
+--error ER_UNSUPORTED_LOG_ENGINE
+CREATE TABLE `general_log` (
+  `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+                         ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `command_type` varchar(64) NOT NULL,
+  `argument` mediumtext NOT NULL
+) ENGINE=Aria DEFAULT CHARSET=utf8 COMMENT='General log';
+
+SET SESSION enforce_storage_engine=InnoDB;
+--error ER_UNSUPORTED_LOG_ENGINE
+CREATE TABLE `general_log` (
+  `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+                         ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `command_type` varchar(64) NOT NULL,
+  `argument` mediumtext NOT NULL
+) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log';
+SET SESSION enforce_storage_engine=NULL;
+
+CREATE TABLE `general_log` (
+  `event_time` TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP 
+                         ON UPDATE CURRENT_TIMESTAMP,
+  `user_host` mediumtext NOT NULL,
+  `thread_id` BIGINT(21) UNSIGNED NOT NULL,
+  `server_id` int(10) unsigned NOT NULL,
+  `command_type` varchar(64) NOT NULL,
+  `argument` mediumtext NOT NULL
+) ENGINE=Aria transactional=0 DEFAULT CHARSET=utf8 COMMENT='General log';
+
+drop table mysql.general_log;
+
 # check that table share cleanup is performed correctly (double drop)
 
 --error ER_BAD_TABLE_ERROR
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 8db992c7501..ff495f69a4f 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7731,5 +7731,3 @@ ER_GEOJSON_EMPTY_COORDINATES
 
 ER_MYROCKS_CANT_NOPAD_COLLATION
   eng "MyRocks doesn't currently support collations with \"No pad\" attribute."
-ER_TRANSACTIONAL_ARIA_LOG_ENGINE
-        eng "Only non-transactional Aria table can be used for logging"
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index b3a600eec36..24a0fb14ee2 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -4770,6 +4770,8 @@ int create_table_impl(THD *thd,
   int		error= 1;
   bool          frm_only= create_table_mode == C_ALTER_TABLE_FRM_ONLY;
   bool          internal_tmp_table= create_table_mode == C_ALTER_TABLE || frm_only;
+  TABLE_LIST table_list;
+
   DBUG_ENTER("mysql_create_table_no_lock");
   DBUG_PRINT("enter", ("db: '%s'  table: '%s'  tmp: %d  path: %s",
                        db, table_name, internal_tmp_table, path));
@@ -4849,7 +4851,6 @@ int create_table_impl(THD *thd,
         LEX_STRING tab_name= {(char *) table_name, strlen(table_name)};
         (void) delete_statistics_for_table(thd, &db_name, &tab_name);
 
-        TABLE_LIST table_list;
         table_list.init_one_table(db, strlen(db), table_name,
                                   strlen(table_name), table_name,
                                   TL_WRITE_ALLOW_WRITE);
@@ -4898,6 +4899,29 @@ int create_table_impl(THD *thd,
   if (check_engine(thd, orig_db, orig_table_name, create_info))
     goto err;
 
+  table_list.init_one_table(db, strlen(db), table_name, strlen(table_name),
+                            table_name, TL_WRITE_ALLOW_WRITE);
+  table_list.table= create_info->table;
+  if (check_if_log_table(&table_list, FALSE, NullS))
+  {
+    if (!create_info->db_type || /* unknown engine */
+        !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES))
+    {
+      my_error(ER_UNSUPORTED_LOG_ENGINE, MYF(0),
+               hton_name(create_info->db_type)->str);
+      goto err;
+    }
+
+    if (create_info->db_type == maria_hton &&
+        create_info->transactional != HA_CHOICE_NO)
+    {
+      my_printf_error(
+          ER_UNSUPORTED_LOG_ENGINE,
+          "Only non-transactional Aria table can be used for logging", MYF(0));
+      goto err;
+    }
+  }
+
   if (create_table_mode == C_ASSISTED_DISCOVERY)
   {
     /* check that it's used correctly */
@@ -8898,49 +8922,6 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
 {
   DBUG_ENTER("mysql_alter_table");
 
-  /*
-    Check if we attempt to alter mysql.slow_log or
-    mysql.general_log table and return an error if
-    it is the case.
-    TODO: this design is obsolete and will be removed.
-  */
-  int table_kind= check_if_log_table(table_list, FALSE, NullS);
-
-  if (table_kind)
-  {
-    /* Disable alter of enabled log tables */
-    if (logger.is_log_table_enabled(table_kind))
-    {
-      my_error(ER_BAD_LOG_STATEMENT, MYF(0), "ALTER");
-      DBUG_RETURN(true);
-    }
-
-    /* Disable alter of log tables to unsupported engine */
-    if ((create_info->used_fields & HA_CREATE_USED_ENGINE) &&
-        (!create_info->db_type || /* unknown engine */
-         !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
-    {
-      my_error(ER_UNSUPORTED_LOG_ENGINE, MYF(0),
-               hton_name(create_info->db_type)->str);
-      DBUG_RETURN(true);
-    }
-
-    if (create_info->db_type == maria_hton &&
-        create_info->transactional != HA_CHOICE_NO)
-    {
-      my_error(ER_TRANSACTIONAL_ARIA_LOG_ENGINE, MYF(0));
-      DBUG_RETURN(true);
-    }
-
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-    if (alter_info->flags & Alter_info::ALTER_PARTITION)
-    {
-      my_error(ER_WRONG_USAGE, MYF(0), "PARTITION", "log table");
-      DBUG_RETURN(true);
-    }
-#endif
-  }
-
   THD_STAGE_INFO(thd, stage_init);
 
   /*
@@ -9096,6 +9077,52 @@ bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
   }
 
   /*
+    Check if we attempt to alter mysql.slow_log or
+    mysql.general_log table and return an error if
+    it is the case.
+    TODO: this design is obsolete and will be removed.
+  */
+  int table_kind= check_if_log_table(table_list, FALSE, NullS);
+
+  if (table_kind)
+  {
+    /* Disable alter of enabled log tables */
+    if (logger.is_log_table_enabled(table_kind))
+    {
+      my_error(ER_BAD_LOG_STATEMENT, MYF(0), "ALTER");
+      DBUG_RETURN(true);
+    }
+
+    /* Disable alter of log tables to unsupported engine */
+    if ((create_info->used_fields & HA_CREATE_USED_ENGINE) &&
+        (!create_info->db_type || /* unknown engine */
+         !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
+    {
+      my_error(ER_UNSUPORTED_LOG_ENGINE, MYF(0),
+               hton_name(create_info->db_type)->str);
+      DBUG_RETURN(true);
+    }
+
+    if (create_info->used_fields & HA_CREATE_USED_ENGINE &&
+        create_info->db_type == maria_hton &&
+        create_info->transactional != HA_CHOICE_NO)
+    {
+      my_printf_error(
+          ER_UNSUPORTED_LOG_ENGINE,
+          "Only non-transactional Aria table can be used for logging", MYF(0));
+      DBUG_RETURN(true);
+    }
+
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+    if (alter_info->flags & Alter_info::ALTER_PARTITION)
+    {
+      my_error(ER_WRONG_USAGE, MYF(0), "PARTITION", "log table");
+      DBUG_RETURN(true);
+    }
+#endif
+  }
+
+  /*
    If foreign key is added then check permission to access parent table.
 
    In function "check_fk_parent_table_access", create_info->db_type is used