Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-34812

Storage engine set by enforce_storage_engine is not enforced for partioned tables

Details

    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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-12080 usage of enforce_storage_engine may cause ALTER .. PARTITION BY statements to fail

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Confirmed

          Task - A task that needs to be done. MDEV-6858 enforce_storage_engine option

          • Major - Major loss of function.
          • Closed

          Activity

            mg MG added a comment -

            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_ci
            		 
             PARTITION 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'`

            mg MG added a comment - 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_ci PARTITION 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'`

            People

              rucha174 Rucha Deodhar
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.