Problematic dictionary/tablespace options:

• DATA_DIR
• innodb-page-size != 16K
• PAGE_COMPRESSED=1
• ENCRYPTED=1
• ATOMIC_WRITES (will be removed)

Remaining test failure after SYS_TABLE_OPTIONS system table change: innodb.innodb_bug12400341 not yet sure how to fix that.

• Fixed above test failure
• Oracle 5.6 with DATA_DIR -> 10.2 pass
• Oracle 5.6 with page_size != 16K and DATA_DIR pass
• 10.1 -> 10.2 pass

http://lists.askmonty.org/pipermail/commits/2016-November/010124.html

I can explain more why this is needed if you want.

An idea for ensuring future-proofness:
When we adjust the InnoDB FSP_SPACE_FLAGS, could we introduce

#define FSP_MARIADB_FORMAT (1U << 31)

I hope this position is unused in MariaDB. In MySQL it is, up to and including 8.0.
If this bit is set, we would repurpose the low-order flag bits as needed and possibly introduce new FSP_MARIADB_* access macros for them. (We can remove all flags that were added in MySQL 5.7 and the DATA_DIRECTORY flag that was added in MySQL 5.6.) We would also zero-initialize some unused space in page 0 (and possibly pages 1 and 2) to have guaranteed garbage-free bytes for introducing further flags in the future. See if the MySQL 8.0 source code or http://dev.mysql.com/worklog/task/?id=7053 contains some notes on which bytes are available.

branch bb-10.2-jan

commit 5dcfee7c0c40a8c0ac07b909649e58f5e667ce39
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Nov 30 16:07:54 2016 +0200

MDEV-11202: InnoDB 10.1 -> 10.2 migration does not work

Remove MariaDB extended tablespace and dictionary flags
from tablespace flags and dictionary flags (SYS_TABLES.TYPE).
Adds a new system table SYS_TABLEOPTIONS where MariaDB
table options are stored and information_schema table
to query them. Automatically fixes dictionary flags
and tablespace flags so that MariaDB extended
are removed. This patch also removes atomic writes
option. Atomic writes will be transparently used
with patch submitted later.

branch bb-10.2-jan

commit 5dcfee7c0c40a8c0ac07b909649e58f5e667ce39
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Nov 30 16:07:54 2016 +0200

MDEV-11202: InnoDB 10.1 -> 10.2 migration does not work

Remove MariaDB extended tablespace and dictionary flags
from tablespace flags and dictionary flags (SYS_TABLES.TYPE).
Adds a new system table SYS_TABLEOPTIONS where MariaDB
table options are stored and information_schema table
to query them. Automatically fixes dictionary flags
and tablespace flags so that MariaDB extended
are removed. This patch also removes atomic writes
option. Atomic writes will be transparently used
with patch submitted later.

http://lists.askmonty.org/pipermail/commits/2016-November/010140.html

Instead of adding a new table SYS_TABLEOPTIONS, could we try to keep the InnoDB data files self-contained? This would simplify upgrade, partial backup, and export/import.
I think that we should be able to use some unused bytes in pages 0 and 1. Because old MySQL versions did not initialize the full page when creating a page, unused sections could contain garbage. But, luckily to us, the field that has since then been repurposed as FSP_SPACE_FLAGS always contained 0 in MySQL 4.1 which introduced innodb_file_per_table. And also luckily to us, the high-order bit of FSP_SPACE_FLAGS is never set.

My suggestion is as follows:

1. Introduce the FSP_MARIADB_FORMAT flag (1<<32).
2. Repurpose some previously unused space in pages 0 and 1 for the attributes. (Be sure to test with each innodb_page_size from 4k to 64k.)
3. When opening a file, if the FSP_MARIADB_FORMAT flag is set, read the attributes from the above-mentioned locations.
4. If the FSP_MARIADB_FORMAT flag is clean, convert the flags to new format, zero-initialize all previously unused bytes in pages 0 and 1 (for easing future repurposing) and update FSP_SPACE_FLAGS and the new attributes.
5. If SYS_TABLES or SYS_TABLESPACES need adjustment, adjust those in a transaction, and do the above mentioned page-update in the same mini-transaction that commits the transaction. (See how trx_commit_low() is used in handler0alter.cc.)

Also, show some test results what happens when 10.2 startup is attempted after MariaDB 10.1 or MySQL 5.6 or MySQL 5.7 is killed. (That should be refused.) Show that startup works after a clean shutdown of MariaDB 10.1 or MySQL 5.7, but is refused on a data set that was created with MySQL 8.0.

Side note: If we absolutely must add some table-level attributes to the transactional InnoDB dictionary tables, we could follow the example of MySQL 5.7 and append a column to the hard-coded table SYS_TABLES. MySQL 5.7 appended the MERGE_THRESHOLD column to SYS_INDEXES. Existing records are not updated on upgrade, but instead a default value is being used when a record contains a smaller number of columns. (ROW_FORMAT=REDUNDANT allows this.)

Please try to see if we can avoid adding an InnoDB system table.
And please include all --suite=innodb tests related to crash recovery, such as innodb.log_*.

http://lists.askmonty.org/pipermail/commits/2016-December/010292.html

The revised patch looks much simpler. I think that the patch could be simplified a little further.
I expect to pass it in the next round of review.

http://lists.askmonty.org/pipermail/commits/2016-December/010299.html

The commit seems to include some changes for MDEV-11600, which I think should be done separately.

I think that we need some more code removal and also more testing. I got the impression that already the upgrade from MariaDB 10.0 to MariaDB 10.1 might not work when a non-default innodb_page_size is being used.

I believe that this issue will be fixed when I merge MDEV-11623 from 10.1.
For that, some preparation is needed to reduce our dependency on FSP_SPACE_FLAGS stored in page 0. The patch to reduce that dependency is waiting for review.

There is unrelated changes (space_id -> fil_space_t*) but in my opinion they are good. Ok to push.

Thank you. Replacing space_id with fil_space_t* is not an unrelated change. It is necessary in order to avoid performing more tablespace ID lookups, worsening the contention on fil_system->mutex. Because we cannot trust the FSP_SPACE_FLAGS from page 0, we must access fil_space_t::flags.