where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA

Quote from MariaDB 10.2 CHECK and DEFAULT clauses:

Another limitation is that CHECK metadata are not accessible via the INFORMATION_SCHEMA. The only way to find out if a table has CHECK clauses is parsing the output of SHOW CREATE TABLE.

I also found in MariaDB KB

Note that SHOW CREATE TABLE is not meant to provide metadata about a table. It provides information about how the table was declared, but the real table structure could differ a bit. For example, if an index has been declared as HASH, the CREATE TABLE statement returned by SHOW CREATE TABLE will declare that index as HASH; however, it is possible that the index is in fact a BTREE, because the storage engine does not support HASH.

So from where does SHOW CREATE TABLE collect its output?

Answers 1

  • Ever since long, long, ago the info has come from the .frm file. The I_S is relatively new. In the future (5.8 maybe) the .frm will be replaced by an InnoDB-based table.

    CHECK (and a few other things) are syntactically allowed but are not handled anywhere in the code. (Another example is a DESC index.) It could be that the .frm contains these strings, but there is no need for such unused information to be replicated in the I_S.

Related Questions