MySQL Bugs: #95834: Reject upgrade 8.0.14-16 -> 8.0.17 for lctn=1 and partitioned tables

Bug #95834	Reject upgrade 8.0.14-16 -> 8.0.17 for lctn=1 and partitioned tables
Submitted:	17 Jun 2019 8:44	Modified:	29 Jun 2019 12:19
Reporter:	Sivert Sørumgård	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Dictionary	Severity:	S3 (Non-critical)
Version:	8.0.17	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As reported in bug#29823032, after patch for bug#26925260, there are issues regarding upgrade from previous versions. If bug#26925260 is reverted, we will need to warn when upgrading from 8.0.14, 8.0.15 or 8.0.16 to 8.0.17, if this is done with lctn=1 on a case sensitive file system and there are partitioned tables in the database.

How to repeat:
.

Suggested fix:
.

Posted by developer:
 
Fixed as of the upcoming 8.0.17 release, and here's the changelog entry:

Due to a regression introduced in MySQL 8.0.14, in-place upgrade on a
case sensitive file system from MySQL 5.7 or a MySQL 8.0 release prior to
MySQL 8.0.14 to MySQL 8.0.16 failed for instances with partitioned tables
and lower_case_table_names=1. The failure was caused by a case mismatch
issue related to partitioned table file names. The fix that introduced the
regression was reverted, which permits upgrades to MySQL 8.0.17 from MySQL
5.7 or MySQL 8.0 releases prior to MySQL 8.0.14 to function as normal.
However, the regression is still present in the MySQL 8.0.14, 8.0.15, and
8.0.16 releases. 

In-place upgrade on a case sensitive file
system from MySQL 8.0.14, 8.0.15, or 8.0.16 to MySQL 8.0.17 fails with the
following error when starting the server after upgrading binaries or
packages to MySQL 8.0.17 if partitioned tables are present and
lower_case_table_names=1: 

Upgrading from server version
version_number with partitioned tables and lower_case_table_names == 1 on
a case sensitive file system may cause issues, and is therefore
prohibited. To upgrade anyway, restart the new server version with the
command line option 'upgrade=FORCE'. When upgrade is completed, please
execute 'RENAME TABLE part_table_name TO new_table_name; RENAME TABLE
new_table_name TO part_table_name;' for each of the partitioned tables.
Please see the documentation for further information. If you encounter
this error when upgrading to MySQL 8.0.17, perform the following
workaround: 

1. Restart the server with mysqld --upgrade=force
to force the upgrade operation to proceed. 

2. Identify partitioned table file names with lowercase suffixes: 

mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME
LIKE '%#p#%' OR FILE_LIKE '%#sp#%'; 

3. For each file identified, rename the associated table using a 
temporary name, then rename the table back to its original name. 

mysql> RENAME TABLE table_name TO temporary_table_name; 
mysql> RENAME TABLE temporary_table_name TO table_name; 

4. Verify that there are no partitioned table file names with
lowercase suffixes (an empty result set should be returned).

mysql> SELECT FILE_NAME FROM INFORMATION_SCHEMA.FILES
WHERE FILE_NAME LIKE '%#p#%' OR FILE_NAME LIKE '%#sp#%'; 
Empty set (0.00sec)

Because of the regression still present in the MySQL 8.0.14, 8.0.15,
and 8.0.16 releases, importing partitioned tables from MySQL 8.0.14,
8.0.15, or 8.0.16 to MySQL 8.0.17 is not supported on case sensitive file
systems where lower_case_table_names=1. Attempting to do so results in a
"Tablespace is missing for table" error.