Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
Description
See attached test case that creates table and exports it, this should be run on 10.1 (attached also resulting t0.ibd). Second test case should be run on 10.2 to try import file created above but it fails. Note that final test case should test also row_format=compressed,
encrypted and encrypted+compressed cases.
set global innodb_file_format = 'Barracuda';
|
set global innodb_file_per_table=ON;
|
create table t0 (c1 int not null auto_increment primary key, b char(200)) engine=innodb;
|
alter table t0 discard tablespace;
|
alter table t0 import tablespace;
|
encryption.jan2 'innodb' [ fail ]
|
Test ended at 2018-01-24 11:19:34
|
|
|
CURRENT_TEST: encryption.jan2
|
mysqltest: At line 12: query 'alter table t0 import tablespace' failed: 1808: Schema mismatch (Expected FSP_SPACE_FLAGS=0x21, .ibd file contains 0x0.)
|
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
MDEV-18762 Support easy restore of partial backup
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Closed
-
-
MDEV-20974 Don't require .cfg files to import InnoDB tablespaces
-
- Closed
-
Activity
I added a note about this here:
https://mariadb.com/kb/en/library/innodb-file-per-table-tablespaces/#differing-row_format-values
Geoff, Kenneth and I extensively talked about the coverage of this topic within the existing documentation. Our conclusion is that this topic is covered through the refactor Geoff did (linked above), as well as the COMPRESSED section of https://mariadb.com/kb/en/library/innodb-storage-formats/#compressed
It is our shared perception that the ROW_FORMAT section of the CREATE TABLE page, and the InnoDB Storage Formats page at https://mariadb.com/kb/en/library/innodb-storage-formats/ both need some general cleanup. It is further our concern that the ALTER TABLE page does not cover ROW_FORMAT, https://mariadb.com/kb/en/library/alter-table
Additional follow-up by Documentation team is planned.
In MDEV-15225, I got an idea that we could streamline the import by making the CREATE TABLE and ALTER TABLE…DISCARD TABLESPACE steps optional, and letting InnoDB construct the tablespace metadata based on the contents of the .frm and .ibd (and optional .cfg) files.
I refactored the row format page a bit to better cover the things pointed out by Marko.
For example, this warning was made clearer:
In MariaDB 10.1 and before, InnoDB can silently ignore and override some row format choices if you do not have the innodb_file_format system variable set to Barracuda and the innodb_file_per_table system variable set to ON.
https://mariadb.com/kb/en/library/innodb-storage-formats/#setting-a-tables-row-format
And this tip was made clearer:
The SHOW TABLE STATUS statement can be used to see the row format used by a table.
In MariaDB 10.0 and later, the information_schema.INNODB_SYS_TABLES table can also be queried to see the row format used by a table.
https://mariadb.com/kb/en/library/innodb-storage-formats/#checking-a-tables-row-format
It didn't seem valuable to duplicate all of the information on the specific section about this schema mismatch problem, so these sections are linked from that section, which can be found here:
https://mariadb.com/kb/en/library/innodb-file-per-table-tablespaces/#differing-row_format-values
I've re-assigned this to marko, since he thinks that he can fix the problem by changing how ALTER TABLE ... IMPORT TABLESPACE is done.
My suggested fix in the now updated MDEV-15225 Description could help address also this case. The idea is that you would copy both the .frm and .ibd file, and execute ALTER TABLE…IMPORT TABLESPACE. The CREATE TABLE…; ALTER TABLE…DISCARD TABLESPACE; steps would become optional.
I think that we would need additional logic to validate and detect the InnoDB ROW_FORMAT from the FSP_SPACE_FLAGS and the page_is_comp() on the B-tree pages, because that information would not be present in the .frm file. It will likely be present in the .cfg file, but that file is optional.
In MDEV-11658 we could streamline this further by making the ALTER TABLE…IMPORT TABLESPACE optional, but that would require file format changes and is not applicable to any GA release branch.
Because MDEV-15225 (which was expected to address this issue) was closed as "Won’t Fix", then also this one needs to be closed.
Starting with MariaDB 10.2.17, we do display a more user-friendly message about this:
MDEV-16851On schema mismatch in IMPORT TABLESPACE, display ROW_FORMAT in clear textThe FSP_SPACE_FLAGS is 0 for ROW_FORMAT=REDUNDANT (which was the only option before MySQL 5.0.3) and ROW_FORMAT=COMPACT (which was introduced by me and made the default in MySQL 5.0.3) when using the default innodb_page_size=16k.
In MariaDB 10.2 (and MySQL 5.7), the parameter innodb_default_row_format was introduced, and it was made dynamic by default.
The flags 0x21 are for ROW_FORMAT=DYNAMIC when using the default innodb_page_size=16k.
Ultimately, MDEV-11658 should replace the current clumsy and error-prone mechanism of importing InnoDB data files.
Perhaps the documentation could be clarified about this. SHOW TABLE STATUS can display the actual ROW_FORMAT that is in effect inside InnoDB. It is also worth noting that even if a ROW_FORMAT was specified in a CREATE TABLE or ALTER TABLE statement, it could be overridden by InnoDB unless innodb_file_format=barracuda and innodb_file_per_table=1. This logic was removed in MariaDB 10.2 or 10.3.