| Bug #19558 | Information_schema.engines differs from show variable output | ||
|---|---|---|---|
| Submitted: | 5 May 2006 10:26 | Modified: | 5 May 2006 14:30 |
| Reporter: | [ name withheld ] (Basic Quality Contributor) | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.9 | OS: | Windows (Windows XP sp2) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[5 May 2006 14:26]
MySQL Verification Team
Thank you for the bug report. Disabled means for example like showed
below:
miguel@hegel:~/dbs/5.1> libexec/mysqld --skip-innodb
libexec/mysqld: Table 'mysql.general_log' doesn't exist
060506 10:00:31 [ERROR] Can't open the mysql.plugin table. Please run the mysql_upgrade script to create it.
060506 10:00:31 [Note] libexec/mysqld: ready for connections.
Version: '5.1.10-beta-debug' socket: '/tmp/mysql.sock' port: 3306 Source distribution
miguel@hegel:~/dbs/5.1> bin/mysql -uroot information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> select * from engines where engine='innodb'\G
*************************** 1. row ***************************
ENGINE: InnoDB
SUPPORT: DISABLED
COMMENT: Supports transactions, row-level locking, and foreign keys
TRANSACTIONS: YES
XA: YES
SAVEPOINTS: YES
1 row in set (0.06 sec)
mysql> show variables like "%have_inn%";
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| have_innodb | DISABLED |
+---------------+----------+
1 row in set (0.00 sec)
mysql>
[5 May 2006 14:30]
[ name withheld ]
I don't see your point. I said that there are more engines (either disabled or not) in the show variables (like csv engine in my example) than those listed in show engines or information_schema.engines table.
Description: Hi, there is a huge difference between a search on database engines through a show variables and a select from information_schema.engines, the latter seem to show only enabled engines even if the table contains a specific column to show if the engine is enabled or disabled. Note that the table's content is the same as the result of a "show engines". I think that the information_schema table should hold infos about all engines (diabled or not) like a show variables, and that show engines should be deprecated on 5.1.10 and later. How to repeat: mysql> select version(); +------------+ | version() | +------------+ | 5.1.9-beta | +------------+ 1 row in set (0.00 sec) mysql> show variables like 'have%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | have_archive | YES | | have_bdb | YES | | have_blackhole_engine | YES | | have_compress | YES | | have_crypt | NO | | have_csv | NO | You can see that it shows CSV engine (not available, but ...) Now the other search methods output: mysql> select * from engines; +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ | ENGINE | SUPPORT | COMMENT | TRANSACTIONS | XA | SAVEPOINTS | +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ | MEMORY | ENABLED | Hash based, stored in memory, useful for temporary tabl es | NO | NO | NO | | MyISAM | ENABLED | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | ARCHIVE | ENABLED | Archive storage engine | NO | NO | NO | | BLACKHOLE | ENABLED | /dev/null storage engine (anything you write to it disa ppears) | NO | NO | NO | | EXAMPLE | ENABLED | Example storage engine | NO | NO | NO | | InnoDB | ENABLED | Supports transactions, row-level locking, and foreign k eys | YES | YES | YES | | FEDERATED | ENABLED | Federated MySQL storage engine | YES | NO | NO | | BerkeleyDB | ENABLED | Supports transactions and page-level locking | YES | NO | YES | | MRG_MYISAM | ENABLED | Collection of identical MyISAM tables | NO | NO | NO | +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ and: mysql> show engines; +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ | EXAMPLE | YES | Example storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tabl es | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disa ppears) | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign k eys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BerkeleyDB | YES | Supports transactions and page-level locking | YES | NO | YES | | FEDERATED | YES | Federated MySQL storage engine | YES | NO | NO | +------------+---------+-------------------------------------------------------- --------+--------------+-----+------------+ Suggested fix: Fix information_schema.engines to show all engines and their status. Deprecate "show engines" no need for "another way to skin the cat"