Let me start with a short summary and then proceed with a long story, code snippets, hexdumps, links and
awk functions converted from the source code of MariaDB server. This blog post can be summarized as follows:
- One can find row_format used to create table explicitly in the .frm file (or the outputs of SHOW CREATE TABLE or SHOW TABLE STATUS). Internals manual may help to find out where is it stored and source code reading helps to find the way to interpret the values.
- For InnoDB tables created without specifying the row_format explicitly neither logical backup nor .frm file itself contains the information about the row format used. There are 4 of them (Redundant, Compact, Dynamic and Compressed). The one used implicitly is defined by current value of the innodb_default_row_format that may change dynamically.
- At the .ibd file level there is no (easy) way to distinguish Redundant from Compact, this detail should come from elsewhere. If the source table's row format had NOT changed you can find it from the information_schema.innodb_sys_tables (or innodb_tables in case of MySQL 8), or from the output of SHOW TABL STATUS.
- There is an easy enough way to check tablespace level flags in the .ibd file (sample awk functions/script are presented below) and this helps to find out that the row format was Compressed or Dynamic.
- So far in basic cases (encryption etc aside) individual .ibd files for InnoDB tables from MariaDB (even 10.3.x) and MySQL 8.0.x are compatible enough.
- You have to take all the above into account while importing individual tables to do partial restore or copy/move tablespaces from one database to the other.
- Some useful additional reading and links may be found in MariaDB bug reports MDEV-19523 and MDEV-15049. Yes, reading MariaDB MDEVs may help MySQL DBAs to understand some things better!
Now the real story.
 |
| I miss London, so I am going to be there on June 13 to partcipate in Open Databases Meetup. Should I speak about importing InnoDB tablespaces there? |
* * *
This is a long enough blog post about a "research" I had to make while working in Support recently. It all started with a question like this in a support issue earlier in May:
"Is it somehow possible to extract ROW_FORMAT used from a table in a backup in XtraBackup format?"
The context was importing tablespace for InnoDB table and error 1808, "Schema mismatch", and customer had a hope to find out proper format without attempts to import, in some way that can be scripted easily. When one tries to import
.ibd file with a format that does not match
.frm file or data dictionary content, she gets a very clear message in MariaDB (that still presents all thee details) due to the fix in
MDEV-16851, but the idea was to avoid trial and error path entirely.
There were several ideas on how to proceed. Given the
.frm, one could
use mysqlfrm utility (you can still find MySQL Utilities that are only under Sustaining Support by Oracle
here) to get full
CREATE TABLE from the
.frm. But I was sure that just checking
ROW_FORMAT should be easier than that. (Later test of latest
mysqlfrm I could get running on Fedora 29 proved that it was a good idea to avoid it due to some problems I may write about one day.) Fine MySQL Internals Manual
clearly describes .frm file format and shows that at offset 0x28 in the header section we have
row_type encoded as one byte:
| 0028
|
1
|
00
|
create_info->row_type
|
Quick search in source code ended up with the following
defined in sql/handler.h (links refer to MariaDB code, but the idea is clear and same for MySQL as well):
enum row_type { ROW_TYPE_NOT_USED=-1, ROW_TYPE_DEFAULT, ROW_TYPE_FIXED,
ROW_TYPE_DYNAMIC, ROW_TYPE_COMPRESSED,
ROW_TYPE_REDUNDANT, ROW_TYPE_COMPACT, ROW_TYPE_PAGE };
The rest looked clear at the moment. We should see decimal values from 2 to 5 at offset 0x28 (decimal 40) from the beginning of the
.frm file representing row formats supported by InnoDB. I quickly created a set of tables with different row formats:
MariaDB [test]> create table ti1(id int primary key, c1 int) engine=InnoDB row_format=redundant;
Query OK, 0 rows affected (0.147 sec)
MariaDB [test]> create table ti2(id int primary key, c1 int) engine=InnoDB row_format=compact;
Query OK, 0 rows affected (0.145 sec)
MariaDB [test]> create table ti3(id int primary key, c1 int) engine=InnoDB row_format=dynamic;
Query OK, 0 rows affected (0.149 sec)
MariaDB [test]> create table ti4(id int primary key, c1 int) engine=InnoDB row_format=compressed;
Query OK, 0 rows affected (0.130 sec)
MariaDB [test]> create table ti5(id int primary key, c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.144 sec)
MariaDB [test]> insert into ti5 values(5,5);
Query OK, 1 row affected (0.027 sec)
and checked the content of the
.frm files with
hexdump:
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti1.frm | more00000000 fe 01 0a 0c 12 00 56 00 01 00 b2 03 00 00 f9 01 |......V.........|
00000010 09 00 00 00 00 00 00 00 00 00 00 02 21 00 08 00 |............!...|
00000020 00 05 00 00 00 00 08 00 04 00 00 00 00 00 00 f9 |................|
...
As you can see, we see expected value
04 for
ROW_TYPE_REDUNDANT of the table
ti1. After that it's easy to come up with some command line to just show numeric row format, like this:
[openxs@fc29 server]$ hexdump --skip 40 --length=1 ~/dbs/maria10.3/data/test/ti1.frm | awk '{print $2}'
0004
or even better:
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti1.frm | awk '/00000020/ {print $10}'
04
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti2.frm | awk '/00000020/ {print $10}'
05
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti3.frm | awk '/00000020/ {print $10}'
02
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti4.frm | awk '/00000020/ {print $10}'
03
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti5.frm | awk '/00000020/ {print $10}'
00
But in real customer case there was no problem with tables created with explicit
row_format set (assuming the correct
.frm was in place). The problem was with table like
ti5 above, those created with the default row format:
MariaDB [test]> show variables like 'innodb%format';
+---------------------------+---------+
| Variable_name | Value |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
| innodb_file_format | |
+---------------------------+---------+
2 rows in set (0.001 sec)
In
.frm file (and in
SHOW CREATE TABLE output) the format is NOT set, it's default, 0 (or 0x00 in hex). The problem happens when we try to import such a table into an instance with different
innodb_default_row_format. Consider the following test case:
[openxs@fc29 maria10.3]$ bin/mysql --socket=/tmp/mariadb.sock -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.15-MariaDB Source distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> create database test2;
Query OK, 1 row affected (0.000 sec)
MariaDB [test]> use test2
Database changed
MariaDB [test2]> set global innodb_default_row_format=compact;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test2]> create table ti0(id int primary key, c1 int) engine=InnoDB; Query OK, 0 rows affected (0.165 sec)
MariaDB [test2]> show create table ti0\G
*************************** 1. row ***************************
Table: ti0
Create Table: CREATE TABLE `ti0` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
MariaDB [test2]> \! hexdump -C data/test2/ti0.frm | awk '/00000020/ {print $10}'
00
In this test we create a new table, ti0, in other database, test2, that has ROW_TYPE_DEFAULT (0) in the .frm file, same as the test.ti5 table created above. But if we try to import t5 tablespace by first exporting it properly in another session:
MariaDB [test]> flush tables ti5 for export;
Query OK, 0 rows affected (0.001 sec)
and then discarding original test2.t0 tablespace, copying .ibd and .cfg files (with proper renaming) and running ALTER TABLE ... IMPORT TABLESPACE:
MariaDB [test2]> alter table ti0 discard tablespace;Query OK, 0 rows affected (0.058 sec)
MariaDB [test2]> \! cp data/test/ti5.cfg data/test2/ti0.cfg
MariaDB [test2]> \! cp data/test/ti5.ibd data/test2/ti0.ibd
MariaDB [test2]> alter table ti0 import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC)
we fail with error 1808 (that has all the details about the original's table row format, DYNAMIC, and hex information about some flags in hex that are different). We failed because now innodb_default_row_format is different, it's COMPACT!
We can not fool the target server by removing (or not copying) non-mandatory
.cfg file:
MariaDB [test2]> \! rm data/test2/ti0.cfg
MariaDB [test2]> alter table ti0 import tablespace;
ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x0, .ibd file contains 0x21.)
Now we see a bit different text, but the same error 1808. Real row format of InnoDB table is stored somewhere in
.ibd file. As you can guess, copying
.frm file (as it may when we copy back files from Xtrabackup- or mariabackup-based backup to do
partial restore) also does not help - the files had the same row_format anyway and we verified that. So, real row format of InnoDB table is stored somewhere in InnoDB (data dictionary). When it does not match the one we see in
.ibd file we get error 1808.
How to resolve this error? There are two ideas to explore (assuming we found the real format in
.ibd file somehow):
- Try to create target table with proper row_format and then import.
- Set innodb_default_row_format properly and create target table without explicit row format set, and then import.
The first one works, as one can find out (but will end up with different
.frm file than the original table had, surely). Check these:
MariaDB [test2]> select * from test.ti5;
+----+------+
| id | c1 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set (0,001 sec)
MariaDB [test2]> alter table ti0 discard tablespace;
Query OK, 0 rows affected (0,066 sec)
MariaDB [test2]> \! cp data/test/ti5.ibd data/test2/ti0.ibd
MariaDB [test2]> alter table ti0 import tablespace;
ERROR 1808 (HY000): Schema mismatch (Expected FSP_SPACE_FLAGS=0x0, .ibd file contains 0x21.)
MariaDB [test2]> \! cp data/test/ti5.cfg data/test2/ti0.cfg
MariaDB [test2]> alter table ti0 import tablespace;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x1 and the meta-data file has 0x21; .cfg file uses ROW_FORMAT=DYNAMIC)
MariaDB [test2]> drop table ti0;
Query OK, 0 rows affected (0,168 sec)
So, if you care to understand the flags (we'll work on that below) or care to copy
.cfg file as well, you surely can get the row format of the table. Now let's re-create
ti0 with explicitly defined Dynamic row format and try to import again:
MariaDB [test2]> create table ti0(id int primary key, c1 int) engine=InnoDB row_format=Dynamic;
Query OK, 0 rows affected (0,241 sec)
MariaDB [test2]> alter table ti0 discard tablespace;
Query OK, 0 rows affected (0,071 sec)
MariaDB [test2]> \! cp data/test/ti5.ibd data/test2/ti0.ibd
MariaDB [test2]> alter table ti0 import tablespace;
Query OK, 0 rows affected, 1 warning (0,407 sec)
MariaDB [test2]> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 1810
Message: IO Read error: (2, No such file or directory) Error opening './test2/ti0.cfg', will attempt to import without schema verification
1 row in set (0,000 sec)
MariaDB [test2]> select * from ti0;
+----+------+
| id | c1 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set (0,001 sec)
We see that copying
.cfg file is not really mandatory and that explicit setting of
ROW_FORMAT (assuming that
.frm file is NOT copied) works.
The second idea also surely works (and customer in his trial and error attempts just tried with all possible formats until import was successful). Lucky from the first error we'll know the original format used for sure:
MariaDB [test2]> drop table ti0;
Query OK, 0 rows affected (0.084 sec)
MariaDB [test2]> set global innodb_default_row_format=dynamic;
Query OK, 0 rows affected (0.000 sec)
MariaDB [test2]> create table ti0(id int primary key, c1 int) engine=InnoDB; Query OK, 0 rows affected (0.171 sec)
MariaDB [test2]> alter table ti0 discard tablespace;
Query OK, 0 rows affected (0.049 sec)
MariaDB [test2]> \! cp data/test/ti5.cfg data/test2/ti0.cfg
MariaDB [test2]> \! cp data/test/ti5.ibd data/test2/ti0.ibd
MariaDB [test2]> alter table ti0 import tablespace;
Query OK, 0 rows affected (0.307 sec)
MariaDB [test2]> select * from ti0;
+----+------+
| id | c1 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set (0.000 sec)
MariaDB [test2]> show create table ti0\G
*************************** 1. row ***************************
Table: ti0
Create Table: CREATE TABLE `ti0` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.000 sec)
Now we can proceed with
UNLOCK TABLES in that another session where we flushed
test.ti5 for export.
How could we find out the row format to use without trial and error, now that we know in one specific case
.frm file (or even
CREATE TABLE statement shown by server or
mysqldump) misses it?
First of all we could try to save this information (
select @@innodb_default_file_format) alone with the backup. But that would show the value of this variable at the moment of asking, and it could be different when specific table was created. Does not work in general case.
We could use
SHOW TABLE STATUS also, as follows:
MariaDB [test]> show create table ti5\G
*************************** 1. row ***************************
Table: ti5
Create Table: CREATE TABLE `ti5` (
`id` int(11) NOT NULL,
`c1` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.001 sec)
MariaDB [test]> show table status like 'ti5'\G
*************************** 1. row ***************************
Name: ti5
Engine: InnoDB
Version: 10
Row_format: Dynamic
...
In the example above that table was created without setting row_format explicitly, but we see the real one used in the output of
SHOW TABLE STATUS. So, if we cared enough, this kind of output could be saved when the data were backed up or exported.
Then we could try to get it for each table from the InnoDB data dictionary of the system we get
.ibd files from. In older MySQL versions we'd have to
dig into the real data dictionary tables on disk probably, but in any recent MySQL (up to 5.7, 8.0 may be somewhat different due to a new data dictionary) or MariaDB we have a convenient, SQL-based way to get this information. There are two
INFORMATION_SCHEMA tables to consider:
INNODB_SYS_TABLESPACES and
INNODB_SYS_TABLES. The first one is not good enough, as it considers Compact and Redundant row formats the same (even though fine MySQL Manual does not say this):
MariaDB [test]> select * from information_schema.innodb_sys_tablespaces where name like '%ti%';
+-------+----------------------------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
| SPACE | NAME | FLAG | ROW_FORMAT | PAGE_SIZE | ZIP_PAGE_SIZE | SPACE_TYPE | FS_BLOCK_SIZE | FILE_SIZE | ALLOCATED_SIZE |
+-------+----------------------------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
| 3 | mysql/transaction_registry | 33 | Dynamic | 16384 | 16384 | Single | 4096 | 147456 | 147456 |
| 4 | mysql/gtid_slave_pos | 33 | Dynamic | 16384 | 16384 | Single | 4096 | 98304 | 98304 |
| 6 | test/ti1 | 0 | Compact or Redundant | 16384 | 16384 | Single | 4096 | 98304 | 98304 |
| 7 | test/ti2 | 0 | Compact or Redundant | 16384 | 16384 | Single | 4096 | 98304 | 98304 || 8 | test/ti3 | 33 | Dynamic | 16384 | 16384 | Single | 4096 | 98304 | 98304 |
| 9 | test/ti4 | 41 | Compressed | 16384 | 8192 | Single | 4096 | 65536 | 65536 |
| 10 | test/ti5 | 33 | Dynamic | 16384 | 16384 | Single | 4096 | 98304 | 98304 |
+-------+----------------------------+------+----------------------+-----------+---------------+------------+---------------+-----------+----------------+
7 rows in set (0.000 sec)
The second one works perfectly:
MariaDB [test2]> select * from information_schema.innodb_sys_tables where name like '%ti%';
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE |
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
| 19 | mysql/gtid_slave_pos | 33 | 7 | 4 | Dynamic | 0 | Single |
| 18 | mysql/transaction_registry | 33 | 8 | 3 | Dynamic | 0 | Single |
| 21 | test/ti1 | 0 | 5 | 6 | Redundant | 0 | Single |
| 22 | test/ti2 | 1 | 5 | 7 | Compact | 0 | Single || 23 | test/ti3 | 33 | 5 | 8 | Dynamic | 0 | Single |
| 24 | test/ti4 | 41 | 5 | 9 | Compressed | 8192 | Single |
| 25 | test/ti5 | 33 | 5 | 10 | Dynamic | 0 | Single |
| 26 | test2/ti0 | 1 | 5 | 11 | Compact | 0 | Single |
+----------+----------------------------+------+--------+-------+------------+---------------+------------+
8 rows in set (0.000 sec)
In the table above I was wondering about the exact values in
FLAG column (note 33, 0x21 in hex, looks familiar from the error message in previous examples). MySQL Manual says just this:
"A numeric value that represents bit-level information about
tablespace format and storage characteristics."
MariaDB's KB page is now way more detailed after my bug report,
MDEV-19523, was closed. See
the link for the details, or check the code of the
i_s_dict_fill_sys_tables() function if you want to interpret the data properly:
/**********************************************************************//**
Populate information_schema.innodb_sys_tables table with information
from SYS_TABLES.
@return 0 on success */
static
int
i_s_dict_fill_sys_tables(
/*=====================*/
THD* thd, /*!< in: thread */
dict_table_t* table, /*!< in: table */
TABLE* table_to_fill) /*!< in/out: fill this table */
{
Field** fields;
ulint compact = DICT_TF_GET_COMPACT(table->flags);
ulint atomic_blobs = DICT_TF_HAS_ATOMIC_BLOBS(
table->flags);
const ulint zip_size = dict_tf_get_zip_size(table->flags);
const char* row_format;
if (!compact) {
row_format = "Redundant";
} else if (!atomic_blobs) {
row_format = "Compact";
} else if (DICT_TF_GET_ZIP_SSIZE(table->flags)) {
row_format = "Compressed";
} else {
row_format = "Dynamic";
}
...
Another part of the code shows how the checks above are performed:
| #define DICT_TF_GET_COMPACT(flags) \ |
| ((flags & DICT_TF_MASK_COMPACT) \ |
| /** Return the value of the ZIP_SSIZE field */ |
| #define DICT_TF_GET_ZIP_SSIZE(flags) \ |
| ((flags & DICT_TF_MASK_ZIP_SSIZE) \ |
| >> DICT_TF_POS_ZIP_SSIZE) |
| /** Return the value of the ATOMIC_BLOBS field */ |
| #define DICT_TF_HAS_ATOMIC_BLOBS(flags) \ |
| ((flags & DICT_TF_MASK_ATOMIC_BLOBS) \ |
| >> DICT_TF_POS_ATOMIC_BLOBS) |
We miss masks and flags to double check (in the same
storage/innobase/include/dict0mem.h file):
/** Width of the COMPACT flag */
#define DICT_TF_WIDTH_COMPACT 1
/** Width of the ZIP_SSIZE flag */
#define DICT_TF_WIDTH_ZIP_SSIZE 4
/** Width of the ATOMIC_BLOBS flag. The ROW_FORMAT=REDUNDANT and
ROW_FORMAT=COMPACT broke up BLOB and TEXT fields, storing the first 768 bytes
in the clustered index. ROW_FORMAT=DYNAMIC and ROW_FORMAT=COMPRESSED
store the whole blob or text field off-page atomically.
Secondary indexes are created from this external data using row_ext_t
to cache the BLOB prefixes. */
#define DICT_TF_WIDTH_ATOMIC_BLOBS 1
...
/** Zero relative shift position of the COMPACT field */
#define DICT_TF_POS_COMPACT 0
/** Zero relative shift position of the ZIP_SSIZE field */
#define DICT_TF_POS_ZIP_SSIZE (DICT_TF_POS_COMPACT \
+ DICT_TF_WIDTH_COMPACT)
/** Zero relative shift position of the ATOMIC_BLOBS field */
#define DICT_TF_POS_ATOMIC_BLOBS (DICT_TF_POS_ZIP_SSIZE \
+ DICT_TF_WIDTH_ZIP_SSIZE)
If we make some basic math we can find out that
DICT_TF_POS_ZIP_SSIZE is 1 and
DICT_TF_POS_ATOMIC_BLOBS is 5, etc. The masks are defined as:
/** Bit mask of the COMPACT field */
#define DICT_TF_MASK_COMPACT \
((~(~0U << DICT_TF_WIDTH_COMPACT)) \
<< DICT_TF_POS_COMPACT)
/** Bit mask of the ZIP_SSIZE field */
#define DICT_TF_MASK_ZIP_SSIZE \
((~(~0U << DICT_TF_WIDTH_ZIP_SSIZE)) \
<< DICT_TF_POS_ZIP_SSIZE)
/** Bit mask of the ATOMIC_BLOBS field */
#define DICT_TF_MASK_ATOMIC_BLOBS \
((~(~0U << DICT_TF_WIDTH_ATOMIC_BLOBS)) \
<< DICT_TF_POS_ATOMIC_BLOBS)
Basically we have what we need now, bit positions and masks. We can create a function to return a row format based on decimal value of falgs. Consider this primitive
awk example:
openxs@ao756:~/dbs/maria10.3$ awk '
> function DICT_TF_GET_COMPACT(flags) {
> return rshift(and(flags, DICT_TF_MASK_COMPACT), DICT_TF_POS_COMPACT);
> }
>
> function DICT_TF_GET_ZIP_SSIZE(flags)
> {
> return rshift(and(flags, DICT_TF_MASK_ZIP_SSIZE), DICT_TF_POS_ZIP_SSIZE);
> }
>
> function DICT_TF_HAS_ATOMIC_BLOBS(flags)
> {
> return rshift(and(flags, DICT_TF_MASK_ATOMIC_BLOBS), DICT_TF_POS_ATOMIC_BLOBS);
> }
>
> function innodb_row_format(flags)
> {
> compact = DICT_TF_GET_COMPACT(flags);
> atomic_blobs = DICT_TF_HAS_ATOMIC_BLOBS(flags);
>
> if (!compact) {
> row_format = "Redundant";
> } else if (!atomic_blobs) {
> row_format = "Compact";
> } else if (DICT_TF_GET_ZIP_SSIZE(flags)) {
> row_format = "Compressed";
> } else {
> row_format = "Dynamic";
> }
> return row_format;
> }
>
> BEGIN {
> DICT_TF_WIDTH_COMPACT=1;
> DICT_TF_WIDTH_ZIP_SSIZE=4;
> DICT_TF_WIDTH_ATOMIC_BLOBS=1;
>
> DICT_TF_POS_COMPACT=0;
> DICT_TF_POS_ZIP_SSIZE=DICT_TF_POS_COMPACT + DICT_TF_WIDTH_COMPACT;
> DICT_TF_POS_ATOMIC_BLOBS=DICT_TF_POS_ZIP_SSIZE + DICT_TF_WIDTH_ZIP_SSIZE;
>
> DICT_TF_MASK_COMPACT=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_COMPACT)),DICT_TF_POS_COMPACT);
> DICT_TF_MASK_ZIP_SSIZE=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_ZIP_SSIZE)),DICT_TF_POS_ZIP_SSIZE);
> DICT_TF_MASK_ATOMIC_BLOBS=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_ATOMIC_BLOBS)),DICT_TF_POS_ATOMIC_BLOBS);
>
> print innodb_row_format(0), innodb_row_format(1), innodb_row_format(33), innodb_row_format(41);
> }'
Redundant Compact Dynamic Compressed
openxs@ao756:~/dbs/maria10.3$
So, we know how to get format based on decimal values of flags. The remaining subtask is to find out where are the flags in the
.ibd file. Instead of digging into the code (
server/storage/innobase/include/fsp0fsp.h etc) one can just check this great blog post by Jeremy Cole to find out that flags are at bytes 54-57, 16 bytes offset after
FIL header that is 38 bytes long (4 bytes starting from hex offset 0x36) in the
.ibd file. These bytes are highlighted with bold below:
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti2.ibd | more
00000000 5d 4f 09 aa 00 00 00 00 00 00 00 00 00 00 00 00 |]O..............|
00000010 00 00 00 00 00 19 11 ee 00 08 00 00 00 00 00 00 |................|
00000020 00 00 00 00 00 07 00 00 00 07 00 00 00 00 00 00 |................|
00000030 00 06 00 00 00 40 00 00 00 00 00 00 00 04 00 00 |.....@..........|
...
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti4.ibd | more
00000000 6c cd 19 15 00 00 00 00 00 00 00 00 00 00 00 00 |l...............|
00000010 00 00 00 00 00 19 44 9f 00 08 00 00 00 00 00 00 |......D.........|
00000020 00 00 00 00 00 09 00 00 00 09 00 00 00 00 00 00 |................|
00000030 00 06 00 00 00 40 00 00 00 29 00 00 00 04 00 00 |.....@...)......|
...
[openxs@fc29 maria10.3]$ hexdump -C data/test/ti5.ibd | more00000000 d8 21 6d 2e 00 00 00 00 00 00 00 00 00 00 00 00 |.!m.............|
00000010 00 00 00 00 00 19 62 9d 00 08 00 00 00 00 00 00 |......b.........|
00000020 00 00 00 00 00 0a 00 00 00 0a 00 00 00 00 00 00 |................|
00000030 00 06 00 00 00 40 00 00 00 21 00 00 00 04 00 00 |.....@...!......|
...
As you can see we have hex values 0x00, 0x29 (41 decimal), 0x21 (33 decimal) etc, and, theoretically, we can find out the exact
row_format used (and other details) from that, based on the information presented above. For row format we need just one byte and we can get it as follows in hex:
openxs@ao756:~/dbs/maria10.3$ hexdump -C data/test/t*.ibd | awk '/00000030/ {print $11}'
21
openxs@ao756:~/dbs/maria10.3$ hexdump -C data/test/t*.ibd | awk '/00000030/ {flags=strtonum("0x"$11); print flags;}'
33
To use the
awk function defined above we need to convert hex to decimal, hence a small trick with
strtonum() function. Now, let me put it all together and show that we can apply this MySQL as well (I checked MariaDB code mostly in the process). Let me create same tables
ti1 ...
ti5 in MySQL 8.0.x:
openxs@ao756:~/dbs/8.0$ bin/mysqld_safe --no-defaults --basedir=/home/openxs/dbs/8.0 --datadir=/home/openxs/dbs/8.0/data --port=3308 --socket=/tmp/mysql8.sock &
[1] 31790
openxs@ao756:~/dbs/8.0$ 2019-05-26T10:55:18.274601Z mysqld_safe Logging to '/home/openxs/dbs/8.0/data/ao756.err'.
2019-05-26T10:55:18.353458Z mysqld_safe Starting mysqld daemon with databases from /home/openxs/dbs/8.0/data
openxs@ao756:~/dbs/8.0$ bin/mysql --socket=/tmp/mysql8.sock -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 Source distribution
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select @@innodb_default_row_format;
+-----------------------------+
| @@innodb_default_row_format |
+-----------------------------+
| dynamic |
+-----------------------------+
1 row in set (0,00 sec)
mysql> create table ti1(id int primary key, c1 int) engine=InnoDB row_format=redundant;
Query OK, 0 rows affected (0,65 sec)
mysql> create table ti2(id int primary key, c1 int) engine=InnoDB row_format=compact;
Query OK, 0 rows affected (0,44 sec)
mysql> create table ti3(id int primary key, c1 int) engine=InnoDB row_format=dynamic;
Query OK, 0 rows affected (0,51 sec)
mysql> create table ti4(id int primary key, c1 int) engine=InnoDB row_format=compressed;
Query OK, 0 rows affected (0,68 sec)
mysql> create table ti5(id int primary key, c1 int) engine=InnoDB;
Query OK, 0 rows affected (0,59 sec)
mysql> select * from information_schema.innodb_sys_tables where name like 'test/ti%';
ERROR 1109 (42S02): Unknown table 'INNODB_SYS_TABLES' in information_schema
mysql> select * from information_schema.innodb_tables where name like 'test/ti%';
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+
| 1158 | test/ti1 | 0 | 5 | 6 | Redundant | 0 | Single | 0 |
| 1159 | test/ti2 | 1 | 5 | 7 | Compact | 0 | Single | 0 |
| 1160 | test/ti3 | 33 | 5 | 8 | Dynamic | 0 | Single | 0 |
| 1161 | test/ti4 | 41 | 5 | 9 | Compressed | 8192 | Single | 0 |
| 1162 | test/ti5 | 33 | 5 | 10 | Dynamic | 0 | Single | 0 |
+----------+----------+------+--------+-------+------------+---------------+------------+--------------+
5 rows in set (0,03 sec)
Now let's combine some shell and
awk together:
openxs@ao756:~/dbs/8.0$ for file in `ls data/test/ti*.ibd`
> do
> echo $file
> hexdump -C $file | awk '
> function DICT_TF_GET_COMPACT(flags) {
> return rshift(and(flags, DICT_TF_MASK_COMPACT), DICT_TF_POS_COMPACT);
> }
>
> function DICT_TF_GET_ZIP_SSIZE(flags)
> {
> return rshift(and(flags, DICT_TF_MASK_ZIP_SSIZE), DICT_TF_POS_ZIP_SSIZE);
> }
>
> function DICT_TF_HAS_ATOMIC_BLOBS(flags)
> {
> return rshift(and(flags, DICT_TF_MASK_ATOMIC_BLOBS), DICT_TF_POS_ATOMIC_BLOBS);
> }
>
> function innodb_row_format(flags)
> {
> compact = DICT_TF_GET_COMPACT(flags);
> atomic_blobs = DICT_TF_HAS_ATOMIC_BLOBS(flags);
>
> if (!compact) {
> row_format = "Redundant";
> } else if (!atomic_blobs) {
> row_format = "Compact";
> } else if (DICT_TF_GET_ZIP_SSIZE(flags)) {
> row_format = "Compressed";
> } else {
> row_format = "Dynamic";
> }
> return row_format;
> }
>
> BEGIN {
> DICT_TF_WIDTH_COMPACT=1;
> DICT_TF_WIDTH_ZIP_SSIZE=4;
> DICT_TF_WIDTH_ATOMIC_BLOBS=1;
>
> DICT_TF_POS_COMPACT=0;
> DICT_TF_POS_ZIP_SSIZE=DICT_TF_POS_COMPACT + DICT_TF_WIDTH_COMPACT;
> DICT_TF_POS_ATOMIC_BLOBS=DICT_TF_POS_ZIP_SSIZE + DICT_TF_WIDTH_ZIP_SSIZE;
>
> DICT_TF_MASK_COMPACT=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_COMPACT)),DICT_TF_POS_COMPACT);
> DICT_TF_MASK_ZIP_SSIZE=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_ZIP_SSIZE)),DICT_TF_POS_ZIP_SSIZE);
> DICT_TF_MASK_ATOMIC_BLOBS=lshift(compl(lshift(compl(0), DICT_TF_WIDTH_ATOMIC_BLOBS)),DICT_TF_POS_ATOMIC_BLOBS);
> }
> /00000030/ {flags=strtonum("0x"$11); print innodb_row_format(flags);}'
> done
data/test/ti1.ibd
Redundant
data/test/ti2.ibd
Redundant
data/test/ti3.ibd
Dynamic
data/test/ti4.ibd
Compressed
data/test/ti5.ibd
Dynamic
openxs@ao756:~/dbs/8.0$
So, we proved that there is a way (based on some code analysis and scripting) to find out the exact row format that was used to create InnoDB table based solely on the
.ibd file and nothing else, in all cases but one! If you are reading carefully you noted Redundant printed for
ti2.ibd as well, we've seen the same in the
INNODB_SYS_TABLESPACES table. Flags in the tablespace are same for both Redundant and Compact row formats, see
this part of the code also. It seems to be one of the reasons why
.cfg file may be needed when we export tablespace is exactly this.
One day I'll find out and create a followup post. Too much core reading for my limited abilities today...
