When I discussed this issue with MySQL development back in days, I was told that SHOW CREATE TABLE isn't supposed to show all properties of the table, but instead what was actually provided at the time of table creation. Obvious deviations from this rule were pointed out, but discarded as exceptions.

So, I guess it should be a task, and then we'll see if MariaDB sees things differently (now).

Was this before or after ALTER...IMPORT was added?

For most use cases the current approach should be fine, but for the transportable tablespace case it isn't.

In my specific case at hand this is actually about "mariabackup --prepare --export" for partial restores, where taking "mysqldump --no-data" schema dumps along with mariabackup is the only way right now to create tables for partial restore with IMPORT.

IMPORT requires the source and destination table structures inside InnoDB to be exactly the same though, which is not possible if mysqldump does not dump all CREATE options.

In my ideal world the mysqldump step would not even be necessary as mariabackup would already taking care of writing proper CREATE sql files along with preparing the .ibd files and writing .cfg files for IMPORT.

So right now I'm wondering what task route to take here:

• request extended SHOW command, like e.g. SHOW FULL CREATE TABLE, that would include all implicitly set table options?
• request extended option for mysqldump to perform the same?
• request mariabackup to write CREATE statements on --export?

The last seems like the right route to take, as it would not require any syntax changes, due to mariabackup actually running a full private server instance on --prepare, so having direct access to table schema internals without having to take the SHOW detour?

I now created this (more general) task:

https://jira.mariadb.org/browse/MDEV-20932

Generally, SHOW CREATE TABLE only reflects the contents of the .frm file. The only exception that I am aware of is the FOREIGN KEY metadata, which will be reported by the storage engine (at least until MDEV-16417 has been implemented).

Fixing this bug should not need any changes to InnoDB. There already is an implementation ha_innobase::get_row_type() of the virtual member function handler::get_row_type(). It should be reflected in the ROW_FORMAT column reported by SHOW TABLE STATUS.

hholzgra asked:

Was this before or after ALTER...IMPORT was added?

An example of how ROW_FORMAT can get out of sync between the .frm file and the InnoDB data dictionary is that the table is created with a ROW_FORMAT that is not supported by InnoDB. The incorrect attribute could be ignored by InnoDB. An easy way to guarantee this is to avoid setting innodb_file_format=barracuda in MariaDB 10.0 or 10.1, or MySQL 5.6, and to try to create a table with a disallowed ROW_FORMAT (DYNAMIC or COMPRESSED). The ROW_FORMAT attribute was first implemented for InnoDB in MySQL 5.0.3 by me, by the introduction of ROW_FORMAT=COMPACT. The original format was retroactively named REDUNDANT.

So, to answer the question, I think that if you upgraded from MySQL 4.0 to 5.0.3 or later, you would definitely have some other ROW_FORMAT for your tables than what the InnoDB data dictionary would indicate. The answer is thus inconclusive because IMPORT TABLESPACE was implemented in MySQL 4.1.

serg says that he does not see anything wrong in the current behaviour. Only SHOW TABLE STATUS and the corresponding INFORMATION_SCHEMA view would display the actual ROW_FORMAT used by the data files of the storage engine, while SHOW CREATE TABLE would reflect the contents of the .frm file.

Maybe this could be addressed in our documentation?

Well, that makes it even harder to properly create tables on another server for doing IMPORT TABLESPACE. For this it is necessary to create the table in exact the same way, and that's what I somehow expect SHOW CREATE TABLE to provide ...

Maybe we need a

SHOW CREATE TABLE ... FOR IMPORT

variant then, if we don't want to change regular SHOW CREATE output?