Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 11.2, 11.4, 11.6, 11.5(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
-