MySQL Bugs: #104413: Downgrading MySQL 5.7 from 5.7.32 to 5.7.21 in-place causes ALTERs to rebuild

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:	None
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

Description:
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;
 }

+#define IGNORE_BINARY_PACK_KEY_MASK (HA_BINARY_PACK_KEY|HA_VAR_LENGTH_KEY)

 /**
   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 &&
+        (new_key->flags & IGNORE_BINARY_PACK_KEY_MASK) == IGNORE_BINARY_PACK_KEY_MASK &&
+        (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;
 }

+#undef IGNORE_BINARY_PACK_KEY_MASK

 static int compare_uint(const uint *s, const uint *t)
 {
--

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:

---------------------------------------------------------

https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html#downgrade-inno...

"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.

Hi

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.

Hi,

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.

Hi,

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.

In principle, we do not update Release Notes for the old releases.

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