Add MTR tests for MySQL (5.7) to MariaDB migration

[PhysicsTing]

Description

Currently upgrades from MySQL 5.7 to MariaDB fail on certain edge cases. This commit adds MTR tests to reproduce those issues. The purpose of this is to make the problems visible and facilitate the development of the proper upgrade code in MariaDB. The MTR tests are added to skip list as they will fail for all branches until these incompatibilities are addressed.

Populated a MySQL 5.7 data directory from official MySQL docker image. The scripts are stored in mysql-test/lib, and the data directory is stored in std_data to be consumed by MTR directly.

Add MTR tests for the usual migration procedure from MySQL to MariaDB.

1. Start MariaDB from the MySQL data directory
2. Run mariadb-upgrade
3. Verify that the upgrade succeed

Also highlight the incompatibilities that are either not documented before or documented but the detailed scenario are not clarified. These tests will fail for now but can be used as references for future to improve these incompatibilities.

Focus on MySQL 5.7 migration for now.

How can this PR be tested?

Newly created MTR tests failed but that is intentional. Existing MTR tests succeed as expected.

Basing the PR against the correct MariaDB version

• [x] This is a bug fix and the PR is based against the earliest maintained branch in which the bug can be reproduced: 10.5

Backward compatibility

Backward compatibility is not compromised, and will be improved based on the newly added tests.

Copyright

All new code of the whole pull request, including one or several files that are either new files or modified ones, are contributed under the BSD-new license. I am contributing on behalf of my employer Amazon Web Services, Inc.

[CLAassistant]

Thank you for your submission! We really appreciate it. Like many open source projects, we ask that you sign our Contributor License Agreement before we can accept your contribution.
_{You have signed the CLA already but the status is still pending? Let us recheck it.}

[grooverdan]

@PhysicsTing I suspect you're still after feedback on this approach.

We could also leave this to a non-mtr test in bb. Are you committed to a MTR approach?

[dlenski]

We could also leave this to a non-mtr test in bb. Are you committed to a MTR approach?

@grooverdan, what do you see as the problem with having this as an MTR test?

@PhysicsTing's approach here provides very concise documentation of all the known cases where a MySQL 5.7 data directory is incompatible with MariaDB, which would result in data loss/corruption if the engine is replaced with MariaDB.

In this form, it's also a roadmap and an automated framework for testing solutions to each of these incompatibilities.

For example, if a developer is trying to implement loading of InnoDB "native partitioning" tables in MariaDB, it can be quickly tested by uncommenting the --error ER_NO_SUCH_TABLE_IN_ENGINE here, and checking the .result for select * from idb_partition_t1:

https://github.com/MariaDB/server/blob/7bd148adb2706ffaec6fe5116478d8d4d1d3d6d6/mysql-test/suite/mysql_migration/mysql_migration.test#L65-L77

[PhysicsTing]

Thank you both for the feedback and reply.

I used MTR mainly because at this point the actual test result matters less than highlighting to people these compatibilities in an explicit way (e.g. how to reproduce), and MTR framework empowers this well. So I agree with what @dlenski pointed out, but would love to hear if a non-MTR approach would potentially benefit more.

[ottok]

#2555 fixes at least on testable MySQL 5.7 to MariaDB upgrade issue. This PR should be finalized so we can catch all of those issues.

[PhysicsTing]

Previous PR is in draft state and therefore is not a good state for discussion. Refresh this PR after a long while. The changes are:

• Instead of spinning up a docker during MTR which won't work in most of CI system, use a pre-generated data directory.
• Instead of expect failures in MTR test, add the MTR to skip list.
• Rebase on 10.5 so the improvement of incompatibilities can be observed as version progresses.

[PhysicsTing]

Pushed again. In the latest update I addressed comments from Marko Mäkelä:

• Tested that the specific stored procedure does not change after migration. I did not cover other types of stored procedure which I think is outside of the scope and requires more knowledge on the topic. That can be expanded later. I have commented about this in code.
• Created temp table in MySQL 5.7 to fill in the first 32 undo segments. I commented that this does not cover cases 5.6 -> 5.7 -> MariaDB.
• Explained that the MySQL compression format is unlikely to be supported in MariaDB.

[dr-m]

@PhysicsTing, it would be nice if you could update this to address my review comments.