Bug #71978 Server silently allows to set PERFORMANCE_SCHEMA as default_storage_engine
Submitted: 8 Mar 2014 16:57 Modified: 12 Apr 2014 18:46
Reporter: Valeriy Kravchuk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Storage Engine API Severity:S4 (Feature request)
Version:5.5, 5.6.16 OS:Any
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: default_storage_engine

[8 Mar 2014 16:57] Valeriy Kravchuk
Description:
It seems any storage engine that is compiled in and supported according to SHOW ENGINES may be set as default_storage_engine (and default_tmp_storage_engine). It does NOT make sense to allow setting PERFORMANCE_SCHEMA as default storage engine, as one actually can NOT create table with this storage engine.

How to repeat:
mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine             | Support | Comment
                | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine
                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine
                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to
 it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine
                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and f
oreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> set session default_storage_engine=PERFORMANCE_SCHEMA;
Query OK, 0 rows affected (0.00 sec)

mysql> show engines;
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| Engine             | Support | Comment
                | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine
                | NULL         | NULL | NULL       |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables
                | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine
                | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to
 it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine
                | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for tempor
ary tables      | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine
                | NO           | NO   | NO         |
| InnoDB             | YES     | Supports transactions, row-level locking, and f
oreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | DEFAULT | Performance Schema
                | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------
----------------+--------------+------+------------+
9 rows in set (0.00 sec)

mysql> create table tps(id int);
ERROR 1683 (HY000): Invalid performance_schema usage.
mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.07 sec)

Suggested fix:
Return error or at least a warning when PERFORMANCE_SCHEMA is used as a value for default_storage_engine or default_tmp_storage_engine.
[9 Mar 2014 23:12] MySQL Verification Team
Thank you for the bug report. Verified as described.
[10 Mar 2014 7:48] MySQL Verification Team
Sure, but what if the only goal is to prevent less sophisticated users creating tables :)
[31 Mar 2014 10:32] Georgi Kodinov
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Valeriy,

Storage engines are considered equal. And dynamic. I know some of them are compiled together with the server, but this doesn't mean that they should somehow be a special case. 
So if we are to discriminate and limit the choice of a default storage engine we need to do it based on the preferences specified in each storage engine. 
Currently the SE API doesn't have a capability flag for this. 
So your request is at best a feature request (and feel free to re-open as such).
But I see little value in implementing this, specially since you're getting a meaningful error message.
[2 Apr 2014 9:44] Valeriy Kravchuk
Thank you for clarifications.

Yes, I'd really want to see storage engine API to be extended in way to communicate the ability to use as default storage engine/create tables with this storage engine set explicitly, via SQL statements.