[MDEV-26372] enforce-storage-engine=InnoDB has no usability as an option to mysqld-install-db Created: 2021-08-16  Updated: 2022-12-19  Resolved: 2021-08-16

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.7
Fix Version/s: 10.4.22, 10.5.13, 10.6.5

Type: Bug Priority: Minor
Reporter: Roel Van de Paar Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-30065 "mariadb-install-db --enforce-storage... Stalled

 Description   

Starting mysqld-install-db server setup with --enforce-storage-engine=InnoDB gives:

2021-08-16 11:45:47 23042769061632 [ERROR] InnoDB: Trying to create a MySQL system table `mysql`.`db` of type InnoDB. MySQL system tables must be of the MyISAM type!
 
ERROR: 1005  Can't create table `mysql`.`db` (errno: 168 "Unknown (generic) error from engine")
 
2021-08-16 11:45:47 23042925725632 [ERROR] Aborting

Interesting is also the 168 unknown error from InnoDB.

Perhaps a better way to handle this would be to automatically make system tables MyISAM (without any overwrite possibility). This would also make sense given that a server which already has MyISAM system tables can be started fine with --enforce-storage-engine=InnoDB thereafter, i.e. the "enforce" naming can be misleading in any case.

Another option is to disable the enforce-storage-engine option (or the InnoDB setting specifically) for mysqld-install-db only.

 Comments   
Comment by Marko Mäkelä [ 2021-08-16 ]

Creation of tables by the three names mysql.user, mysql.host, mysql.db is being blocked in the function row_mysql_is_system_table(). Since MDEV-17658 in MariaDB Server 10.4.1, mysql.user is a view, not a table. Since MDEV-15851 in MariaDB Server 10.4.0, mysql.host is not being created at all.

So, when it comes to MariaDB Server 10.4 or later, the question is whether we can allow mysql.db to be created in InnoDB. With the following patch, a test run is encountering other problems, because any use of explicit ENGINE=MyISAM would lead to a failure:

./mtr --mysqld=--enforce-storage-engine=InnoDB --force --parallel=auto --big-test --suite=innodb

The most prominent failure is this:

mysqltest: At line 28: query 'create temporary table error_log (
 
row int auto_increment primary key,
 
suspicious int default 1,
 
file_name varchar(255),
 
line varchar(1024) default null
 
) engine=myisam' failed: ER_UNKNOWN_STORAGE_ENGINE (1286): Unknown storage engine 'MyISAM'

The following would remove the special handling of the table names when creating or renaming InnoDB tables:

diff --git a/storage/innobase/row/row0mysql.cc b/storage/innobase/row/row0mysql.cc
 
index f84755a5c4a..7694c6bdef2 100644
 
--- a/storage/innobase/row/row0mysql.cc
 
+++ b/storage/innobase/row/row0mysql.cc
 
@@ -72,25 +72,6 @@ Created 9/17/2000 Heikki Tuuri
 
 #include "wsrep_mysqld.h"
 
 #endif
 
 
-/*******************************************************************//**
 
-Determine if the given name is a name reserved for MySQL system tables.
 
-@return TRUE if name is a MySQL system table name */
 
-static
 
-ibool
 
-row_mysql_is_system_table(
 
-/*======================*/
 
-	const char*	name)
 
-{
 
-	if (strncmp(name, "mysql/", 6) != 0) {
 
-
 
-		return(FALSE);
 
-	}
 
-
 
-	return(0 == strcmp(name + 6, "host")
 
-	       || 0 == strcmp(name + 6, "user")
 
-	       || 0 == strcmp(name + 6, "db"));
 
-}
 
-
 
 /*******************************************************************//**
 
 Delays an INSERT, DELETE or UPDATE operation if the purge is lagging. */
 
 static
 
@@ -2212,12 +2193,8 @@ row_create_table_for_mysql(
 
 
 	trx->op_info = "creating table";
 
 
-	if (row_mysql_is_system_table(table->name.m_name)) {
 
-
 
-		ib::error() << "Trying to create a MariaDB system table "
 
-			<< table->name << " of type InnoDB. MariaDB system"
 
-			" tables must be of the MyISAM type!";
 
-
 
+	if (!dict_sys.sys_tables_exist()) {
 
+		ib::error() << "Some InnoDB system tables are missing";
 
 err_exit:
 
 		dict_mem_table_free(table);
 
 
@@ -2226,11 +2203,6 @@ row_create_table_for_mysql(
 
 		return(DB_ERROR);
 
 	}
 
 
-	if (!dict_sys.sys_tables_exist()) {
 
-		ib::error() << "Some InnoDB system tables are missing";
 
-		goto err_exit;
 
-	}
 
-
 
 	trx_start_if_not_started_xa(trx, true);
 
 
 	heap = mem_heap_create(512);
 
@@ -2770,14 +2742,6 @@ row_rename_table_for_mysql(
 
 
 	if (high_level_read_only) {
 
 		return(DB_READ_ONLY);
 
-
 
-	} else if (row_mysql_is_system_table(new_name)) {
 
-
 
-		ib::error() << "Trying to create a MariaDB system table "
 
-			<< new_name << " of type InnoDB. MariaDB system tables"
 
-			" must be of the MyISAM type!";
 
-
 
-		goto funct_exit;
 
 	}
 
 
 	trx->op_info = "renaming table";

An additional patch would allow 88% of --suite=innodb to pass, including some recovery tests. Many tests would fail due to ENGINE=MyISAM or similar.

diff --git a/mysql-test/include/check-warnings.test b/mysql-test/include/check-warnings.test
 
index be347ba46ec..06335a28fe3 100644
 
--- a/mysql-test/include/check-warnings.test
 
+++ b/mysql-test/include/check-warnings.test
 
@@ -30,7 +30,7 @@ create temporary table error_log (
 
   suspicious int default 1,
 
   file_name varchar(255),
 
   line varchar(1024) default null
 
-) engine=myisam;
 
+) engine=innodb;
 
 
 # Get the name of servers error log
 
 let $log_error= $MTR_LOG_ERROR;

Generated at Thu Feb 08 09:44:48 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.