| 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: | |
| Category: | MySQL Server: Storage Engine API | Severity: | S4 (Feature request) |
| Version: | 5.5, 5.6.16 | OS: | Any |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
| Tags: | default_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.
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.