MySQL Bugs: #37107: INFORMATION_SCHEMA needs an ENGINES_CAPABILITIES table (or something similar)

Bug #37107	INFORMATION_SCHEMA needs an ENGINES_CAPABILITIES table (or something similar)
Submitted:	30 May 2008 17:22	Modified:	24 Sep 2010 19:07
Reporter:	Justin Swanhart	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Currently there are many tools that allow client to create schemas via a GUI.  A number of these tools only allow the creation of referential constraints on InnoDB tables.  These tools need to be able to identify which capabilities are available for which engines.

I suggest something like ENGINE_CAPABILITIES or ENGINE_FEATURES.  To remain backward compat, leave TRANSACTIONS, XA and SAVEPOINTS in ENGINES, but also represent them in the new table.

TABLE ENGINE_CAPABILITIES
  ENGINE VARCHAR(64)
  CAPABILITY VARCHAR(64)
  ENABLED BOOLEAN

sample
MYISAM  TRANSACTIONS FALSE
INNODB  TRANSACTIONS TRUE
KDB     TRANSACTIONS TRUE
FALCON  TRANSACTIONS TRUE
INNODB  XA           TRUE
MYISAM  XA           FALSE
KDB     XA           FALSE
INNODB  REFERENTIAL  TRUE
KDB     REFERENTIAL  TRUE
MYISAM  REFERENTIAL  FALSE

This way, engines can add new capabilities in a supported way, and tools will be able to detect those capabilities.

The table could be much more sophisticated, but this is meant to be a simple example.

How to repeat:
-

Many thanks for writing a feature request.

You already will get this information by using SHOW ENGINES.

I will give this to our Workbench team so that they will be able to think about implementing a nice layout for an engine list with all these informations.

I think you miss the point.  There is no way to determine what engines support FOREIGN KEYS for example.  I don't think that extending the ENGINES table to include additional columns is the right thing to do, which is why I suggest that a new table should be provided.

You are right. I wasn't sure what exactly you want to get listed.

Any plans on incorporating this into 5.6?  This is still a problem with different storage engines not having any way to enumerate their feature sets.