MySQL Bugs: #27684: SHOW ENGINES and I_S.ENGINES output not the same

Bug #27684	SHOW ENGINES and I_S.ENGINES output not the same
Submitted:	6 Apr 2007 10:12	Modified:	23 Jun 2007 7:31
Reporter:	Roland Bouman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	5.1.18-BK, 5.1.16	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The output from SHOW ENGINES is not equivalent to the contents of information_schema.ENGINES.

For example, values for the Support column in the SHOW ENGINES output can have the values YES, DISABLED, NO, DEFAULT.
The information_schema.ENGINES table contains ENABLED, DISABLED instead of YES and DISABLED. 

The value DEFAULT does not seem to be present at all. Sadly I have been unable to check the value that would be returned for the NO value.

However, the documentation for http://dev.mysql.com/doc/refman/5.1/en/engines-table.html refers to http://dev.mysql.com/doc/refman/5.1/en/show-engines.html without further comment.

How to repeat:
mysql> show engines;
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support  | Comment                                                        | Transactions | XA  | Savepoints |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| ndbcluster | DISABLED | Clustered, fault-tolerant tables                               | YES          | NO  | NO         |
| MRG_MYISAM | YES      | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| CSV        | YES      | CSV storage engine                                             | NO           | NO  | NO         |
| Falcon     | YES      | Falcon storage engine                                          | YES          | NO  | YES        |
| BLACKHOLE  | YES      | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| MEMORY     | YES      | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| ARCHIVE    | YES      | Archive storage engine                                         | NO           | NO  | NO         |
| InnoDB     | DEFAULT  | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MyISAM     | YES      | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
| FEDERATED  | YES      | Federated MySQL storage engine                                 | YES          | NO  | NO         |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
10 rows in set (0.02 sec)

mysql> select * from information_schema.engines;
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| ENGINE     | SUPPORT  | COMMENT                                                        | TRANSACTIONS | XA  | SAVEPOINTS |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
| ndbcluster | DISABLED | Clustered, fault-tolerant tables                               | YES          | NO  | NO         |
| MRG_MYISAM | ENABLED  | Collection of identical MyISAM tables                          | NO           | NO  | NO         |
| CSV        | ENABLED  | CSV storage engine                                             | NO           | NO  | NO         |
| Falcon     | ENABLED  | Falcon storage engine                                          | YES          | NO  | YES        |
| BLACKHOLE  | ENABLED  | /dev/null storage engine (anything you write to it disappears) | NO           | NO  | NO         |
| MEMORY     | ENABLED  | Hash based, stored in memory, useful for temporary tables      | NO           | NO  | NO         |
| ARCHIVE    | ENABLED  | Archive storage engine                                         | NO           | NO  | NO         |
| InnoDB     | ENABLED  | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MyISAM     | ENABLED  | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
| FEDERATED  | ENABLED  | Federated MySQL storage engine                                 | YES          | NO  | NO         |
+------------+----------+----------------------------------------------------------------+--------------+-----+------------+
10 rows in set (0.00 sec)

Suggested fix:
Please, align the output of the SHOW statement and the ENGINES table. There seems to be no justification for this difference. It is reasonable expect identical output in this case.

If there is such a justification, it should be documented.

Thank you for a problem report. Verified just as described with latest 5.1.18-BK on Linux.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/24584

ChangeSet@1.2570, 2007-04-16 16:20:18+05:00, gluh@mysql.com +2 -0
  Bug#27684 undocumented difference between SHOW ENGINES and I_S.ENGINES
  changed:'show engines' works via I_S.engines table now

Corresponding rows should have the same values,
according to the original specification (WL#3008).
But they don't. And WL#3008 isn't marked "complete".
And the MySQL Reference Manual
15.7.5. Quick Reference: MySQL Cluster SQL Statements
http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-sql-statements.html
says information_schema.engines.support can be 'ENABLED'.
So the obvious fix, changing information_schema, is hard.

On the other hand, changing SHOW ENGINES is also hard.
It is older, and its values are already documented
(although there are a few minor errors in the manual).

Therefore, instead of accepting the patch, I suggest
that the documentation should be fixed, as follows.

The MySQL Reference Manual
13.5.4.13. SHOW ENGINES Syntax
says possible values are 'YES', 'NO', 'DISABLED'.
This is a documentation error. It should say
possible values are 'YES', 'DISABLED', 'DEFAULT'.

The MySQL Reference Manual
22.18. The INFORMATION_SCHEMA ENGINES Table
says "The PLUGINS table provides information about
storage engines." This is a documentation error.
It should say "The ENGINES table provides information
about storage engines."

The MySQL Reference Manual
22.18. The INFORMATION_SCHEMA ENGINES Table
should have an additional sentence:
"The possible values in the SUPPORT column
are 'DISABLED', 'ENABLED'. The Support field
from SHOW ENGINES has different possible
values: 'DISABLED', 'YES', 'DEFAULT'."

I would like to know if the complainer
(Roland Bouman), or anyone else, objects
to these suggestions.

Hi Peter,

I'm not sure what it means that the WL#3008 is not marked complete, or what consequences that should have. 

You seem to suggest that it is hard to change the behaviour of information_schema.ENGINES because it is already documented. I think that it is unfortunate but that we now still have the chance to fix it as 5.1 (which introduces information_schema.ENGINES) is not yet GA.

I agree that the behaviour of SHOW ENGINES should not be changed.

I'm a bit unhappy about changing only the documentation. I'd much rather see the more recent introduction information_schema.ENGINES conform to the older existing SHOW ENGINES functionality. Even though you could document the difference, it will still be confusing. I fear it will still be perceived as a bug, - it will just be a documented bug.

Roland Bouman expressed unhappiness about changing only the documentation,
and a patch exists for changing information_schema.engines, and it is
acknowledged in this case that there was a slipup somewhere. I hope
that Gluh will make this change, and that the documentation people
will read the comment of April 16 about other errors in the manual.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28400

ChangeSet@1.2552, 2007-06-08 18:17:51+05:00, gluh@mysql.com +2 -0
  Bug#27684 undocumented difference between SHOW ENGINES and I_S.ENGINES
  Changed SHOW ENGINES to work in the same way as I_S.ENGINES.
  For this: removed the functions mysqld_show_storage_engines and show_handlerton, and
  made SHOW ENGINES work via the common function iter_schema_engines.
  There in no test case because an engine (except of MyISAM) may be not compiled or disabled
  which may affect the test result.

Pushed into 5.1.20-beta

This is from a fresh pull, build using compile-amd64-debug-max:

mysql> SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 5.1.20-beta-debug |
+-------------------+
1 row in set (0.00 sec)

mysql> SHOW ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| Engine     | Support | Comment                                                        | Transactions | XA  | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 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                                 | YES          | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| ENGINE     | SUPPORT | COMMENT                                                        | TRANSACTIONS | XA  | SAVEPOINTS |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     | YES          | YES | YES        |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | 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                                 | YES          | NO  | NO         |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO  | NO         |
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO  | NO         |
+------------+---------+----------------------------------------------------------------+--------------+-----+------------+
8 rows in set (0.00 sec)

I have verified that all the NDB executables were built, but where are the columns for NDB? They should be present, and have the value DISABLED.

Were the server built without NDB support at all, then the columns should also be there, but have the value NO.

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

this is a separate bug, moved to bug#29263

Sure, but this means that storage engines present but disabled aren't being reported as DISABLED. So this bug still isn't really closed.

Also I've changed the synopsis of this bug to reflect the actual problem with the server's behaviour (rather than implying that it's a docs issue when it isn't).

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Forget that, looking at it sideways...

Documented in 5.1.20 changelog as shown here:

        <para>
          <literal>SHOW ENGINES</literal> and queries on
          <literal>INFORMATION_SCHEMA.ENGINES</literal> did not use the
          same values for representing the same storage engine states.
          (Bug #27684) 
        </para>

After this patch I think 5.1 and 6.0 are inconsistent?