Bug #36068 SHOW ENGINES not showing engine as disabled when it's available
Submitted: 14 Apr 2008 18:44 Modified: 14 Apr 2008 21:58
Reporter: Geert Vanderkelen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.23 OS:Any
Assigned to: CPU Architecture:Any

[14 Apr 2008 18:44] Geert Vanderkelen
Description:
SHOW ENGINES is not showing the MySQL Cluster NDB storage engine as 'DISABLED' when it's actually available.

Manual says:
 http://dev.mysql.com/doc/refman/5.1/en/show-engines.html

"For the NDB Cluster storage engine, DISABLED means the server was compiled with support for MySQL Cluster, but was not started with the --ndb-cluster option."

(Note that the --ndb-cluster option is wrong to there, should be --ndbcluster)

Using --skip-ndbcluster option doesn't also make the 'ndbcluster' engine show up.

How to repeat:
MySQL 5.1.23 configured without NDBCluster storage engine:

mysql> SHOW ENGINES;
+------------+---------+---------------..
| Engine     | Support | Comment       ..
+------------+---------+---------------..
| InnoDB     | YES     | Supports trans.. 
| MRG_MYISAM | YES     | Collection of ..
| BLACKHOLE  | YES     | /dev/null stor..
| CSV        | YES     | CSV storage en.. 
| MEMORY     | YES     | Hash based, st..
| FEDERATED  | YES     | Federated MySQ..
| ARCHIVE    | YES     | Archive storag..
| MyISAM     | DEFAULT | Default engine..
+------------+---------+---------------..

It should actually say 'DISABLED' according to the manual.

Enabling the NDB storage engine would give this:

mysql> SHOW ENGINES;
+------------+---------+---------------..
| Engine     | Support | Comment       ..
+------------+---------+---------------..
| ndbcluster | YES     | Clustered, fau..
| InnoDB     | YES     | Supports trans.. 
| MRG_MYISAM | YES     | Collection of ..
| BLACKHOLE  | YES     | /dev/null stor..
| CSV        | YES     | CSV storage en.. 
| MEMORY     | YES     | Hash based, st..
| FEDERATED  | YES     | Federated MySQ..
| ARCHIVE    | YES     | Archive storag..
| MyISAM     | DEFAULT | Default engine..
+------------+---------+---------------..
[14 Apr 2008 18:45] Geert Vanderkelen
Verified using MySQL 5.1.23 (and consequently also in CGE 6.3)
[14 Apr 2008 19:05] Jon Stephens
Same thing happens if you start the server with --skip-innodb or --skip-archive. The engine simply doesn't appear in SHOW ENGINES output, as shown here:

jon@whalegate:~/bin/mysql/64/5.1/bin$ ./mysqld_safe --skip-innodb &
[1] 7622
jon@whalegate:~/bin/mysql/64/5.1/bin$ 080414 21:12:32 mysqld_safe Logging to '/home/jon/bin/mysql/64/5.1/var/whalegate.err'.
080414 21:12:32 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/64/5.1/var

jon@whalegate:~/bin/mysql/64/5.1/bin$ ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | 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 temporary tables      | NO           | NO | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO | NO         |
+------------+---------+----------------------------------------------------------------+--------------+----+------------+
7 rows in set (0.01 sec)

mysql> exit
Bye
jon@whalegate:~/bin/mysql/64/5.1/bin$ ./mysqladmin -uroot shutdown
jon@whalegate:~/bin/mysql/64/5.1/bin$ 080414 21:15:40 mysqld_safe mysqld from pid file /home/jon/bin/mysql/64/5.1/var/whalegate.pid ended

[1]+  Done                    ./mysqld_safe --skip-innodb
jon@whalegate:~/bin/mysql/64/5.1/bin$ ./mysqld_safe --skip-archive &
[1] 7874
jon@whalegate:~/bin/mysql/64/5.1/bin$ 080414 21:16:11 mysqld_safe Logging to '/home/jon/bin/mysql/64/5.1/var/whalegate.err'.
080414 21:16:11 mysqld_safe Starting mysqld daemon with databases from /home/jon/bin/mysql/64/5.1/var

jon@whalegate:~/bin/mysql/64/5.1/bin$ ./mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.25-rc-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| CSV        | YES     | CSV storage engine                                             | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| FEDERATED  | YES     | Federated MySQL storage engine                                 | NO           | NO  | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
7 rows in set (0.00 sec)
[14 Apr 2008 19:35] Jon Stephens
jon@whalegate:~/bin/mysql/64/6.0/libexec$ ./mysqld --verbose --help | grep skip
  --archive           Enable ARCHIVE plugin. Disable with --skip-archive (will
                      (Defaults to on; use --skip-archive to disable.)
                      Disable with --skip-automatic-sp-privileges.
                      (Defaults to on; use --skip-automatic-sp-privileges to disable.)
                      (Defaults to on; use --skip-character-set-client-handshake to disable.)
                      (Defaults to on; use --skip-engine-condition-pushdown to disable.)
                      Disable with --skip-external-locking.
  --falcon            Enable Falcon plugin. Disable with --skip-falcon (will
                      (Defaults to on; use --skip-falcon to disable.)
                      (Defaults to on; use --skip-falcon-consistent-read to disable.)
  --innodb            Enable InnoDB plugin. Disable with --skip-innodb (will
                      (Defaults to on; use --skip-innodb to disable.)
                      Disable with --skip-innodb-checksums.
                      (Defaults to on; use --skip-innodb-checksums to disable.)
                      Disable with --skip-innodb-doublewrite.
                      (Defaults to on; use --skip-innodb-doublewrite to disable.)
                      (Defaults to on; use --skip-innodb-stats-on-metadata to disable.)
                      (Defaults to on; use --skip-innodb-support-xa to disable.)
                      (Defaults to on; use --skip-innodb-table-locks to disable.)
                      (Defaults to on; use --skip-innodb-use-adaptive-hash-indexes to disable.)
                      --skip-large-pages.
                      (Defaults to on; use --skip-local-infile to disable.)
                      (Defaults to on; use --skip-ndb-force-send to disable.)
                      (Defaults to on; use --skip-ndb-log-update-as-write to disable.)
                      (Defaults to on; use --skip-ndb-log-updated-only to disable.)
                      (Defaults to on; use --skip-ndb-optimized-node-selection to disable.)
                      (Defaults to on; use --skip-ndb-use-exact-count to disable.)
                      (Defaults to on; use --skip-ndb-use-transactions to disable.)
                      (Defaults to on; use --skip-ndb_force_send to disable.)
                      (Defaults to on; use --skip-ndb_use_exact_count to disable.)
                      (Defaults to on; use --skip-ndb_use_transactions to disable.)
                      (Defaults to on; use --skip-relay_log_purge to disable.)
                      In replication, if set to 1, do not skip events having
  --skip-grant-tables Start without grant tables. This gives all users FULL
  --skip-host-cache   Don't cache host names.
  --skip-locking      Deprecated option, use --skip-external-locking instead.
  --skip-name-resolve Don't resolve hostnames. All hostnames are IP's or
  --skip-networking   Don't allow connection with TCP/IP.
  --skip-new          Don't use new, possible wrong routines.
  --skip-safemalloc   Don't use the memory allocation checking.
  --skip-show-database
  --skip-slave-start  If set, slave is not autostarted.
  --skip-stack-trace  Don't print a stack trace on failure.
  --skip-symlink      Don't allow symlinking of tables. Deprecated option.  Use
                      --skip-symbolic-links instead.
  --skip-thread-priority
  --slave-skip-errors=name
                      other flags). Disable with --skip-ssl.
                      (Defaults to on; use --skip-symbolic-links to disable.)
                      (Defaults to on; use --skip-sync-frm to disable.)
                      (Defaults to on; use --skip-temp-pool to disable.)
                      (Defaults to on; use --skip-use-symbolic-links to disable.)
skip-grant-tables                 FALSE
skip-slave-start                  FALSE

However, the following options are also allowed and appear to work:

--skip-federated
--skip-blackhole
--skip-csv
--skip-mrg_myisam

In addition, --skip-memory and --skip-ndbcluster are allowed but don't appear to do anything.
[14 Apr 2008 19:41] Jon Stephens
I've fixed Geert's typo, and removed the statement "Only the InnoDB, [sic] and MERGE engines can be disabled in this way [using --skip-engine_name]" which appeared in this section in some versions of the Manual and which was easily demonstrated to be false.
[14 Apr 2008 21:42] Peter Laursen
it would be interesting to know if/how

SELECT * FROM INFORMATION_SCHEMA.ENGINES 

... is affected by this!
[14 Apr 2008 21:58] Sergei Golubchik
Seems to be a duplicate of bug#29263