I got this idea from a Valerii Kravchuk’s MySQL bug report:
In theory, I completely agree that MySQL and forks should not allow us to set a default storage engine which cannot be used to create a table. You can see the same with MariaDB’s
SEQUENCE. The MySQL & forks philosophy seems to be: ignore your mistakes, so you can repeat them forever. Which can turn a mistype into a major data loss.
Unless you only use InnoDB and your magic powers tell you that this will never change, the
ENGINE clause should be mandatory in your MySQL installation. Since there is no clean way to make it mandatory, setting a “weird” storage engine as default seems to be a decent workaround. I don’t like it, but it can prevent human mistakes.
MariaDB [test]> SET SESSION default_storage_engine = 'performance_schema';
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> CREATE TABLE x (id INT) ;
ERROR 1005 (HY000): Can't create table `test`.`x` (errno: 131 "Command not supported by database")
Error (Code 1005): Can't create table `test`.`x` (errno: 131 "Command not supported by database")
Error (Code 1683): Invalid performance_schema usage.
This is a list of MariaDB storage engines that are not distributed with MySQL. I think that most of them will work with MySQL, but not all – at least CassandraSE doesn’t.
||A fully-compatible fork of InnoDB, mantained by Percona
||A crash-safe MyISAM, also used for internal temptables
||A transactional engine with innovative buffers and high compression, by TokuTek
||Supports fulltext searches with Chinese, Japanese and Korean languages
||Shards tables through multiple servers
||Used to implement tree structures
||Returns a sequence of numbers as a resultset
||Support several external data sources (data files, DBMS’s…)
||A bridge to Apache Cassandra
||A bridge to Sphinx
||A richer fork of Federated
- XtraDB is the default and should be preferred to InnoDB, unless it has a bug which affects you (but XtraDB fixes several InnoDB bugs).
- OQGRAPH was introduced during the Big Bang or soon after, but disabled in 5.5 and reintroduced in 10.0. See this post.
- FederatedX is called Federated, so it’s possible that you are already using it and you don’t know.
- CONNECT will hopefully obsolete CSV and FederatedX, but some features are still missing. For example, CONNECT cannot store NULL values in flat files.
- With MariaDB you need to specify
SQL_MODE='NO_ENGINE_SUBSTITUTION'. If you mistype the engine name, or try to use a disabled engine, you want to get an error, rather than silently switch to XtraDB.
Obsolete storage engines:
PBXT (Big Bang – 5.3) – A transactional engine which aimed to be “somewhere between InnoDB and MyISAM”. Seemed to support foreign keys better than InnoDB (I couldn’t reproduce a tirgger+FK bug and it had more options), but alas it’s dead.