Bug #104413 Downgrading MySQL 5.7 from 5.7.32 to 5.7.21 in-place causes ALTERs to rebuild
Submitted: 25 Jul 2021 19:20 Modified: 26 Jul 2021 14:22
Reporter: Akshay Suryawanshi (OCA) Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.7 OS:Linux
Assigned to: Daniel Price CPU Architecture:x86
Tags: 5.7, alter, downgrade, rebuild, upgrade

[25 Jul 2021 19:20] Akshay Suryawanshi
While downgrading MySQL Server versions 5.7.32 to 5.7.21 in-place, we have observed that a metadata change ALTER statement causes a full online rebuild in older versions.

After some code examination and debugging with gdb, we have found out that the breaking change was introduced in the following series of commits:

1. The first change was introduced in MySQL Server 5.7.23 https://github.com/mysql/mysql-server/commit/005ef9f0ed06df640503afaf65491205c422104b,
more specifically with this block https://github.com/mysql/mysql-server/commit/005ef9f0ed06df640503afaf65491205c422104b#diff..., which broke metadata change ALTERs.

2. This resulted in a bug that was reported already https://bugs.mysql.com/bug.php?id=94383. A further fix was introduced in MySQL Server 5.7.27 https://github.com/mysql/mysql-server/commit/9d2a5d8a95c which resolved the bug.

The eventual fix in version 5.7.27 breaks downgrades to a server version <= 5.7.26 (we tested version 5.7.21).

How to repeat:
This bug can be reproduced as follows:
1. Install and start a MySQL server with version >= 5.7.32
2. Perform any type of ALTER on an InnoDB table, for eg: ALTER TABLE <table-name> AUTO_INCREMENT=<some-value>;
3. Shutdown the MySQL server
4. Perform the in-place MySQL downgrade steps
	1. Uninstall currently installed 5.7.32 packages
	2. Install packages for version 5.7.21
	3. Start the MySQL Server on version 5.7.21
	4. Run the mysql_upgrade script
	5. Restart the MySQL Server
5. Perform a metadata change ALTER on the same InnoDB table as step 2, for eg: ALTER TABLE <table-name> AUTO_INCREMENT=<some-value>;

Suggested fix:
This behavior is introduced due to a code change in InnoDB where the use of HA_BINARY_PACK_KEY was removed from InnoDB tables.

Since InnoDB doesn't support packed keys, we came up with a patch where the decision to do a table rebuild is skipped if the table structure shows changes related to HA_BINARY_PACK_KEY (coming from versions >= 5.7.27) only.

 sql/sql_table.cc | 10 +++++++++-
 1 file changed, 9 insertions(+), 1 deletion(-)

diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index de053ba63af..681491b8df9 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -6467,6 +6467,7 @@ static KEY* find_key_cs(const char *key_name, KEY *key_start, KEY *key_end)
   return NULL;


   Check if index has changed in a new version of table (ignore
@@ -6494,8 +6495,14 @@ static bool has_index_def_changed(Alter_inplace_info *ha_alter_info,
   if ((table_key->algorithm != new_key->algorithm) ||
       ((table_key->flags & HA_KEYFLAG_MASK) !=
        (new_key->flags & HA_KEYFLAG_MASK)) ||
-      (table_key->user_defined_key_parts != new_key->user_defined_key_parts))
+      (table_key->user_defined_key_parts != new_key->user_defined_key_parts))
+  {
+    if (table_key->table->s->db_type()->db_type == DB_TYPE_INNODB &&
+        (table_key->flags & IGNORE_BINARY_PACK_KEY_MASK) == 0)
+      return false;
     return true;
+  }

     If an index comment is added/dropped/changed, then mark it for a
@@ -6560,6 +6567,7 @@ static bool has_index_def_changed(Alter_inplace_info *ha_alter_info,
   return false;


 static int compare_uint(const uint *s, const uint *t)
[26 Jul 2021 12:57] MySQL Verification Team
Hi Mr. Suryawanshi,

Thank you for your bug report.

However, what you are reporting is a well known change in 5.7 , when it comes to InnoDB compression algorithms .....

My colleague was good enough to provide me with a link to our documentation, as well as with the relevant part with the text:



"A new compression version used by the InnoDB page compression feature was added in MySQL 5.7.32. The new compression version is not compatible with earlier MySQL releases. Creating a page compressed table in MySQL 5.7.32 or higher and accessing the table after downgrading to a release earlier than MySQL 5.7.32 causes a failure. As a workaround, uncompress such tables before downgrading. To uncompress a table, run ALTER TABLE tbl_name COMPRESSION='None' and OPTIMIZE TABLE. For information about the InnoDB page compression feature, see Section 14.9.2, “InnoDB Page Compression”. "


We do hope that this explanation will suffice .......

Not a bug.
[26 Jul 2021 13:43] Akshay Suryawanshi

Thank you for the response.

Are InnoDB tables by default compressed tables? I don't think we use compressed tables in our environment and the test tables used for reproducing the bug were also not compressed tables.

Are we sure this is not a bug? Also "OPTIMIZE TABLE" for large tables will take hours to complete, which is also what we observed happen in our case due to online rebuilds.
[26 Jul 2021 14:15] MySQL Verification Team

There are a number of metadata changes between 5.7.21 and 5.7.32 that will force rebuild of InnoDB tablespaces.

Full list of those you will find in our Release Notes on dev.mysql.com.
[26 Jul 2021 14:22] Akshay Suryawanshi

Yes you are right about the breaking changes being introduced, however, in this specific changelog https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-27.html, the documentation fails to inform that downgrading will cause a table rebuild to happen.

The specific entry I am referring to is,
For InnoDB tables that contained an index on a VARCHAR column and were created prior to MySQL 5.7.23, some simple ALTER TABLE statements that should have been done in place were performed with a table rebuild after an upgrade to MySQL 5.7.23 or higher. (Bug #29375764, Bug #94383)

I wonder if this lack of documentation explaining that downgrading below this version will require rebuild of innodb tablespaces which were changed in version 5.7.27 is worthy of the bug report.
[27 Jul 2021 11:51] MySQL Verification Team
In principle, we do not update Release Notes for the old releases.

However, it is on the relevant team to make a decision.