[MDEV-21618] CREATE UNIQUE INDEX fails with "ERROR 1286 (42000): Unknown storage engine 'partition'" Created: 2020-01-31  Updated: 2023-11-22  Resolved: 2023-11-18

Status: Closed
Project: MariaDB Server
Component/s: Partitioning
Affects Version/s: 10.1, 10.3.11, 10.2, 10.3, 10.4
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Alexey Botchkov
Resolution: Fixed Votes: 1
Labels: None


 Description   

This fails:
===========

CREATE UNIQUE INDEX MyIndexName on MyTableName (
 
UserId ASC,
 
RepId ASC,
 
Field03,
 
Field04
 
);
 
 
 
"ERROR 1286 (42000): Unknown storage engine 'partition'"

This works:
============

ALTER TABLE MyTableName ADD UNIQUE MyIndexName (UserId ASC,
 
RepId ASC,
 
Field03,
 
Field04
 
);

Table definition:

 
 
 CREATE TABLE `MyTableName` (
 
  `Field01` bigint(20) NOT NULL,
 
  `UserId` bigint(20) NOT NULL,
 
  `RepId` bigint(20) NOT NULL,
 
  `Field03` varchar(32) NOT NULL,
 
  `Field04` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
 
  PRIMARY KEY (`UserId`,`RepId`,`Field01`)
 
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
 
 PARTITION BY HASH (`UserId`)
 
PARTITIONS 10



 Comments   
Comment by Alice Sherepa [ 2020-02-03 ]

could not reproduce:

MariaDB [test]> CREATE TABLE `MyTableName` ( `Field01` bigint(20) NOT NULL, `UserId` bigint(20) NOT NULL, `RepId` bigint(20) NOT NULL, `Field03` varchar(32) NOT NULL, `Field04` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`UserId`,`RepId`,`Field01`) 
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED PARTITION BY HASH (`UserId`)
 
    -> PARTITIONS 10;
 
Query OK, 0 rows affected (0.063 sec)
 
 
 
MariaDB [test]> CREATE UNIQUE INDEX MyIndexName ON MyTableName ( UserId ASC, RepId ASC, Field03, Field04 );
 
Query OK, 0 rows affected (0.038 sec)
 
Records: 0  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select version();
 
+-----------------+
 
| version()       |
 
+-----------------+
 
| 10.3.11-MariaDB |
 
+-----------------+
 
1 row in set (0.000 sec)

Comment by Elena Stepanova [ 2020-03-07 ]

The description misses the essential bit of information, the server had enforce_storage_engine configured.

--source include/have_partition.inc
 
 
 
set enforce_storage_engine= MyISAM;
 
 
 
CREATE TABLE t1 (a INT) ENGINE=MyISAM PARTITION BY HASH(a);
 
CREATE INDEX x on t1 (a);
 
 
 
# Cleanup
 
DROP TABLE t1;

10.3 a662cb9b

 
query 'CREATE INDEX x on t1 (a)' failed: 1286: Unknown storage engine 'partition'

Reproducible on 10.1-10.5.

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-31 ]

The error is raised here:

bool check_engine(THD *thd, const char *db_name,
 
                  const char *table_name, HA_CREATE_INFO *create_info)
 
{
 
  DBUG_ENTER("check_engine");
 
  ...
 
  bool no_substitution= thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION;
 
  *new_engine= ha_checktype(thd, req_engine, no_substitution);
 
  ...
 
 
 
  /* Enforced storage engine should not be used in
 
  ALTER TABLE that does not use explicit ENGINE = x to
 
  avoid unwanted unrelated changes.*/
 
  if (!(thd->lex->sql_command == SQLCOM_ALTER_TABLE &&
 
        !(create_info->used_fields & HA_CREATE_USED_ENGINE)))
 
    enf_engine= thd->variables.enforced_table_plugin ?
 
       plugin_hton(thd->variables.enforced_table_plugin) : NULL;
 
 
 
  if (enf_engine && enf_engine != *new_engine)
 
  {
 
    if (no_substitution)
 
    {
 
      const char *engine_name= ha_resolve_storage_engine_name(req_engine);
 
      my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(0), engine_name);
 
      DBUG_RETURN(TRUE);
 
    }
 
    *new_engine= enf_engine;
 
  }
 
  ...
 
  DBUG_RETURN(false);
 
}

Comment by Nayuta Yanagisawa (Inactive) [ 2022-03-31 ]

holyfoot please review: https://github.com/MariaDB/server/commit/8ffbaf59add999b70a2a51d8872ed3b1b9fb6614

Comment by Alexey Botchkov [ 2023-11-15 ]

ok to push.

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