Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2(EOL), 11.4, 11.5(EOL), 11.6(EOL)
Description
The kb for enforce_storage_engine states:
- Force the use of a particular storage engine for new tables. Used to avoid unwanted creation of tables using another engine.
- For example, setting to InnoDB will prevent any MyISAM tables from being created.
It also discusses "if another engine is specified in a CREATE TABLE statement" in connection with NO_ENGINE_SUBSTITUTION, however, this is not the case here as no other engine is specified in the CREATE TABLE.
When the following SQL is executed is the CLI:
SET sql_mode=''; # Disables NO_ENGINE_SUBSTITUTION |
SET SESSION enforce_storage_engine=MyISAM; |
CREATE TABLE t (c INT) PARTITION BY LIST (c) (PARTITION p VALUES IN (0)); |
SHOW WARNINGS;
|
SHOW CREATE TABLE t; |
We expect the table to MyISAM. However, it is InnoDB (CLI default):
|
10.5.27 0e27351028a4888b0da271e2089bf1f847620396 (Optimized) |
10.5.27-opt>SET sql_mode=''; # Disable NO_ENGINE_SUBSTITUTION
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
10.5.27-opt>SET SESSION enforce_storage_engine=MyISAM;
|
Query OK, 0 rows affected (0.001 sec)
|
|
|
10.5.27-opt>CREATE TABLE t (c INT) PARTITION BY LIST (c) (PARTITION p VALUES IN (0));
|
Query OK, 0 rows affected, 1 warning (0.010 sec)
|
|
|
10.5.27-opt>SHOW WARNINGS;
|
+-------+------+-------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------+
|
| Note | 1266 | Using storage engine MyISAM for table 't' |
|
+-------+------+-------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
10.5.27-opt>SHOW CREATE TABLE t;
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t | CREATE TABLE `t` (
|
`c` int(11) DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
|
PARTITION BY LIST (`c`)
|
(PARTITION `p` VALUES IN (0) ENGINE = InnoDB) |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
We also see the misleading note that MyISAM will be used for the table.
The issue does not happen when no partitioning is used:
|
10.5.27 0e27351028a4888b0da271e2089bf1f847620396 (Optimized) |
10.5.27-opt>SET sql_mode=''; # Disable NO_ENGINE_SUBSTITUTION
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
10.5.27-opt>SET SESSION enforce_storage_engine=MyISAM;
|
Query OK, 0 rows affected (0.000 sec)
|
|
|
10.5.27-opt>CREATE TABLE t (c INT);
|
Query OK, 0 rows affected, 1 warning (0.002 sec)
|
|
|
10.5.27-opt>SHOW WARNINGS;
|
+-------+------+-------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------+
|
| Note | 1266 | Using storage engine MyISAM for table 't' |
|
+-------+------+-------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
10.5.27-opt>SHOW CREATE TABLE t;
|
+-------+----------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+----------------------------------------------------------------------------------------------------------------+
|
| t | CREATE TABLE `t` (
|
`c` int(11) DEFAULT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
+-------+----------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
Present in 10.5+ dbg+opt. The same issue exists in Percona Server; ref MDEV-6858 re: implementation.
Attachments
Issue Links
- relates to
-
-
- Confirmed
-
-
MDEV-6858 enforce_storage_engine option
-
- Closed
-
I am opting to report a similar issue in this MDEV rather than a new one.
Export an existing table with `mariadb-dump` as:
DROP TABLE IF EXISTS `t`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `t` (`month` smallint(6) DEFAULT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ciPARTITION BY LIST (`month`)(PARTITION `p01` VALUES IN (1) ENGINE = MyISAM,PARTITION `p02` VALUES IN (2) ENGINE = MyISAM,PARTITION `p03` VALUES IN (3) ENGINE = MyISAM,PARTITION `p04` VALUES IN (4) ENGINE = MyISAM,PARTITION `p05` VALUES IN (5) ENGINE = MyISAM,PARTITION `p06` VALUES IN (6) ENGINE = MyISAM,PARTITION `p07` VALUES IN (7) ENGINE = MyISAM,PARTITION `p08` VALUES IN (8) ENGINE = MyISAM,PARTITION `p09` VALUES IN (9) ENGINE = MyISAM,PARTITION `p10` VALUES IN (10) ENGINE = MyISAM,PARTITION `p11` VALUES IN (11) ENGINE = MyISAM,PARTITION `p12` VALUES IN (12) ENGINE = MyISAM);/*!40101 SET character_set_client = @saved_cs_client */;Attempt to import as InnoDB:
set session sql_mode='';set session enforce_storage_engine=innodb;CREATE TABLE ...You will encounter:
> ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MariaDB
I was able to work around this thanks to spaces in PARTITION specification of ENGINE not present in base table definition, eg:
`sed 's/ENGINE = MyISAM/ENGINE=INNODB/g'`