MySQL has had pluggable storage engine support for decades now. Their capabilities vary wildly. Some of those storage engines are not even storage engine-like, for example BLACKHOLE does not store data at all. For OLTP, this herd thins very quickly if we only consider ACID-supporting ones: InnoDB, MyRocks, NDB, (up until recently) TokuDB, and (up until ten years ago) PBXT. I am not too familiar with OLAP, but I know there's Warp.
The core server layer has accumulated dozens of engine feature flags over those decades to accommodate those varying capabilities, let's take a look at what they can be. To make this post look less like a source code annotation dump, I'll try to sort them by feature area. If I classify something as an "implementation detail", it still could be more than that, there could be e.g. performance implications or something user-visible like forced closing of a table, but I'll be not figuring that out at this time.
To recap, a "handlerton" is a "handler singleton", and a "handler" applies to a single opened table. So a handlerton is a singleton for all the tables in a particular storage engine, the capabilities for it as the whole, although later we'll see that some of the table flags are engine-level too. Perhaps that happened because handlerton concept was introduced later, in 5.0 series AFAIK, and it was all handler earlier.
The list in the source code starts with
#define and ends with
its evolution as the coding standards evolve. Although it all should be somehow made
enum class bitset, I think, with a healthy part of handler flags moved over
HTON_ALTER_NOT_SUPPORTED: cannot do
ALTER TABLE, i.e. performance_schema.
HTON_TEMPORARY_NOT_SUPPORTED: cannot do
CREATE TEMPORARY TABLE, i.e. performance_schema again.
HTON_SUPPORT_LOG_TABLES: can host
slow_logtables. CSV and MyISAM!
HTON_NO_PARTITION: tables cannot be partitioned. performance_schema, Warp.
HTON_SUPPORTS_EXTENDED_KEYS: all the secondary keys include the primary key columns in the end. This is a feature for the query optimizer. InnoDB, MyRocks, TokuDB.
HTON_SUPPORTS_FOREIGN_KEYS: self-descriptive. InnoDB, NDB.
HTON_SUPPORTS_ATOMIC_DDL: DDL statements are atomic, nice. InnoDB, NDB.
HTON_SUPPORTS_PACKED_KEYS: MyISAM something something.
HTON_SECONDARY_ENGINES_DDL: too early to tell, the secondary engine feature is under development, and even then it might not be public, i.e. something for HeatWave.
HTON_SUPPORTS_TABLE_ENCRYPTION: self-descriptive. InnoDB.
ENGINE_ATTRIBUTEfor engine-specific attributes provided by user.
HTON_SUPPORTS_GENERATED_INVISIBLE_PK: will create a hidden primary key if you don't provide one. InnoDB.
Hidden Storage Engines
HTON_HIDDEN: do not advertise as a storage engine at all, do not allow
CREATE TABLEin it etc. Binary log is such storage engine!
HTON_NOT_USER_SELECTABLE: do not allow user access any tables in this engine. Binlog again. Not sure why it cannot be merged with
HTON_CLOSE_CURSORS_AT_COMMIT: last in-tree engine that used it was BDB in 5.0, which I am not familiar with. MariaDB docs (here goes Planet MySQL) say this was BerkeleyDB. But also TokuDB.
CREATEinternally. MyRocks, Warp.
HTON_FLUSH_AFTER_RENAME: introduced somewhere between 5.0 and 5.1, removed in 8.0. An implementation detail for the BDB storage engine.
HTON_NO_BINLOG_ROW_OPT: if using row-based binlog format, do not do anything about before/after row images for this engine, meaning all the columns will be always included. NDB.
Handlerton Flags for Foreign Keys
Foreign key capabilities are finer-grained. Archeologically we are above the
#define layer, but below the
constexpr layer. These are
static const, so they
are emitted in every single compilation unit including
handler.h, unless the
compilers manage to clean it up.
HTON_FKS_WITH_PREFIX_PARENT_KEYS: whether the foreign key can have arbitrary columns with resulting non-uniqueness etc. as long as the unique parent key columns are in its prefix. InnoDB.
HTON_FKS_WITH_SUPPORTING_HASH_KEYS: the foreign key can be a hash key. NDB.
HTON_FKS_NEED_DIFFERENT_PARENT_AND_SUPPORTING_KEY: can't have a self-referring foreign key. InnoDB.
HTON_FKS_WITH_EXTENDED_PARENT_KEYS: if the keys have primary key columns in its suffix, use that. InnoDB.
Optional Handlerton Function Pointers
We are done with handlerton flags, but we are not done with handlerton. Turns out, some of its function pointers are optional, and capabilities are inferred from them being non-null. A lot of them are transaction-related. These capabilities are:
savepoint_set: transaction savepoints. InnoDB, MyRocks, TokuDB.
savepoint_rollback_can_release_mdl: ability to rollback MDL locks taken after a savepoint on rollback there. InnoDB, MyRocks
set_prepared_in_tc_by_xid: XA. InnoDB, MyRocks, TokuDB.
recover: can recover XA transactions on startup. InnoDB, MyRocks.
recover_prepared_in_tc: can recover prepared XA transactions on startup. InnoDB.
set_prepared_in_tc: 2PC support. InnoDB, MyRocks.
START TRANSACTION WITH CONSISTENT SNAPSHOT. InnoDB, MyRocks.
flush_logs: self-descriptive. InnoDB, MyRocks, TokuDB.
get_tablespace_type_by_name: supports tablespaces. InnoDB.
is_supported_system_table: can host at least some of the
mysqlschema tables. InnoDB.
compare_secondary_engine_cost: self-descriptive. InnoDB, MyRocks.
clone_interface: can clone. InnoDB. Watch this space for more exciting announcements!
collect_hton_log_info: can participate in
performance_schema.LOG_STATUStable. InnoDB, MyRocks
dict_...: native data dictionary support. InnoDB.
page_track: can be used with the page tracking service.
drop_database: wants to participate in
DROP DATABASEimplementation. NDB.
panic: can be shutdown, even if it's statically compiled in.
fill_is_schema: wants to fill in some of INFORMATION_SCHEMA tables by itself.
se_before_rollback: just let's say it's all implementation details.
Handler Table Flags
These flags are per-table, but before handlerton was a thing, a lot of per-engine flags were added too.
HA_NO_TRANSACTIONS: this is not a real OLTP database storage engine. MyISAM.
HA_CAN_SQL_HANDLER: supports HANDLER interface. InnoDB.
HA_NO_AUTO_INCREMENT: does not support auto increment fields. performance_schema, Warp.
REPAIR TABLE. MyISAM, Warp.
FLUSH TABLE FOR EXPORT. InnoDB.
DEFAULTcolumn clauses may be expressions. InnoDB, MyRocks, MyISAM.
HA_UPDATE_NOT_SUPPORTED: self-descriptive. ARCHIVE.
Supported Column and Index Types
HA_NO_PREFIX_CHAR_KEYS: does not support indexing
CHARcolumn prefixes. NDB.
HA_NO_VARCHAR: does not support
VARCHARtype. No engine sets this.
HA_CAN_FULLTEXT: supports fulltext indexes. InnoDB.
HA_NO_BLOBS: does not support BLOBs.
HA_CAN_INDEX_BLOBS: can index BLOBs. InnoDB, MyRocks, TokuDB.
HA_BLOB_PARTIAL_UPDATE: BLOBs can be updated partially. InnoDB.
HA_CAN_GEOMETRY: can handle spatial data. InnoDB.
HA_CAN_RTREEKEYS: supports R-Tree indexes.
HA_SUPPORTS_GEOGRAPHIC_GEOMETRY_COLUMN: geometry supports can do not only coordinates on a flat surface, but geographic coordinates too. InnoDB.
HA_GENERATED_COLUMNS: supports generated columns. InnoDB, MyRocks, MyISAM.
HA_CAN_INDEX_VIRTUAL_GENERATED_COLUMN: can index them. InnoDB, MyRocks.
HA_ANY_INDEX_MAY_BE_UNIQUE: self-descriptive. MRG_MyISAM.
HA_DESCENDING_INDEX: supports descending indexes. InnoDB.
HA_MULTI_VALUED_KEY_SUPPORT: can index JSON arrays. InnoDB.
Query Optimizer Features
HA_PARTIAL_COLUMN_READ: can return a subset of row columns. InnoDB, MyRocks.
HA_TABLE_SCAN_ON_INDEX: a clustered index always exists and should be used for table scans. InnoDB, TokuDB.
HA_FAST_KEY_READ: random key order is as fast as sequential. Set for in-memory engines, but probably should be set for everything on SSD?
HA_NULL_IN_KEY: can index
NULL. InnoDB, MyRocks, TokuDB.
HA_STATS_RECORDS_IS_EXACT: table record count in statistics is exact. InnoDB.
HA_PRIMARY_KEY_IN_READ_INDEX: when doing an index-only read on a secondary key, primary key columns will be returned too. InnoDB, MyRocks, TokuDB.
position()a handler, primary key is needed. Otherwise, position works as a row counter. InnoDB, MyRocks, TokuDB.
HA_COUNT_ROWS_INSTANT: not sure what the difference from
HA_STATS_RECORDS_IS_EXACTis. The name suggests that counting records is instant, and the comment suggests that it's exact. MyISAM.
HA_READ_BEFORE_WRITE_REMOVAL: supports blind writes, i.e. to write a row you don't have to read it first. NDB. But, this has no relation to a similar optimization in MyRocks and TokuDB.
HA_BLOCK_CONST_TABLE: disables const-table optimization. NDB.
HA_CAN_FULLTEXT_HINTS: can provide extra FULLTEXT-related info in
EXPLAIN SELECT? InnoDB.
HA_UNUSED3: used to be
HA_REC_NOT_IN_SEQbefore 8.0. Implementation detail.
HA_REQUIRES_KEY_COLUMNS_FOR_DELETE: need to read all the key columns before deleting a row by that key. Unused in MySQL, used by MariaDB MyISAM and InnoDB.
HA_DUPLICATE_POS: on duplicate key error, provide the position of the existing key.
HA_AUTO_PART_KEY: multi-part keys can include auto increment columns. TokuDB.
HA_REQUIRE_PRIMARY_KEY: a user-provided primary key must exist. No SE uses this.
HA_UNUSED14: until 5.7 it was
HA_PRIMARY_KEY_REQUIRED_FOR_DELETE: DELETE and UPDATE work by primary key. performance_schema.
HA_FILE_BASED: each table is stored in a separate file. MyISAM, TokuDB.
HA_NO_COPY_ON_ALTER: the name suggests that "
ALTER TABLEdoes not copy data", but this is different from online
HA_BINLOG_STMT_CAPABLE: what binary log formats are supported.
HA_DUPLICATE_KEY_NOT_IN_ORDER: multiple key conflicts in a multiple value-
REPLACEstatements are reported not in the ascending key name order. Unused in MySQL, used by CONNECT storage engine in MariaDB.
HA_CAN_FULLTEXT_EXT: supports "extended fulltext API", i.e. it is InnoDB and not MyISAM.
HA_READ_OUT_OF_SYNC: the storage engine is BLACKHOLE. I cannot make more sense out of the description: "what you read will not be what is expected to be in the table".
HA_NO_READ_LOCAL_LOCK: does not support
LOCK TABLE READ LOCAL, and does not want them to be upgraded to
LOCK TABLE READby the server. InnoDB.
HA_ATTACHABLE_TRX_COMPATIBLE: the storage engine is "compatible" with attachable transactions. It is a superset of "supporting" them. InnoDB and MyISAM.
HA_NO_INDEX_ACCESS: the indexes are not really indexes, because they don't support access through them. MOCK (that's a storage engine name, not a suggestion).
These are capabilities for individual indexes.
HA_READ_NEXT: the index can go from one record to the next. Not used, always assumed.
HA_READ_PREV: the index can go to the previous record.
HA_READ_ORDER: the index is ordered. Usually might mean not a hash index.
HA_READ_RANGE: the index supports ranges, again not a hash index, usually.
HA_ONLY_WHOLE_INDEX: the index does not support key prefix search. Hash indexes again.
HA_TABLE_SCAN_ON_NULL: the index does not store NULLs even if the underlying column does, forcing a switch to table scan. NDB.
HA_KEYREAD_ONLY: index-only scans are supported. InnoDB, MyRocks, TokuDB.
HA_KEY_SCAN_NOT_ROR: Not sure. Will update if I figure it out. InnoDB.
HA_DO_INDEX_COND_PUSHDOWN: supports Index Condition Pushdown. InnoDB, MyRocks, TokuDB.
Handler Partitioning Flags
Storage engines can implement partitioning with different levels of capabilities.
HA_CAN_UPDATE_PARTITION_KEY: can do
UPDATEinvolving the partitioning key. NDB.
HA_CAN_PARTITION_UNIQUE: can handle unique indexes across partitions correctly. NDB.
HA_USE_AUTO_PARTITION: not sure what "automatic partitioning" is, but this is something NDB supports.
ALTER TABLE EXCHANGE PARTITIONwith a non-partitioned table. InnoDB.
HA_CANNOT_PARTITION_FK: a foreign key cannot be the partition key. MyRocks, TokuDB.
TRUNCATE PARTITIONrequires closing the involved tables. InnoDB.