MariaDB/MySQL: Making ENGINE clause mandatory

I got this idea from a Valerii Kravchuk’s MySQL bug report:

http://bugs.mysql.com/bug.php?id=71978

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.

Enjoy!

About these ads

3 thoughts on “MariaDB/MySQL: Making ENGINE clause mandatory

  1. Pingback: MariaDB/MySQL: Making ENGINE clause mandatory | FALSE IS … « SSHlab.com

  2. I can see two issues with making ENGINE= mandatory:
    1. This might be a violation of the SQL:2003 and other standards. (but it might be if “OF” is used for table types.)
    See also “ISO/IEC 9075-2:2003 (E) 11.3 table definition>”
    2. This might make it more difficult for people who just started to learn SQL. I don’t think they should be bothered with implementation details.

    Maybe a engine should report if it might be used as default or not.

    A related issue is that you can specify ENGINE=InnoDB on a MySQL Cluster (NDB) system.

    Maybe add a global variable for allowed storage engines and allowed default storage engines?

    • Hi Daniël,
      I must agree with your points. The variable you talk about sounds like an enhancement, but I’m afraid it could cause other problems. If I can suggest it, I would prefer to have a non-dynamic @@mandatory_engine_clause.

Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s