Description:
A table with sub-partitions can not add a column after upgrade, if the upgrade is done from 8.0.11-16 to MySQL 8.0.17 or 8.0.18 (latest) version.
This failure does not occur if the upgrade is done from 8.0.17 to 8.0.18.
How to repeat:
Steps:
1. Initialize MySQL 8.0.11-16.
2. Execute:
create schema db1;
CREATE TABLE db1.Two (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased)) (partition CUST_ID_LESS_2010 values less than (2010));
CREATE TABLE db1.TsT (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased))
SUBPARTITION BY HASH( TO_DAYS(purchased) )
SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE);
INSERT INTO db1.Two values (1,'1970-01-01');
INSERT INTO db1.TsT values (1,'1970-01-01');
3. Live-upgrade in to MySQL 8.0.17 or MySQL 8.0.18.
4. Execute:
use db1;
ALTER TABLE Two ADD COLUMN col2 VARCHAR(15); ==> Works
ALTER TABLE TsT ADD COLUMN col2 VARCHAR(15); ==> Fails: ERROR 3664 (HY000) at line 4 in file: 'SQLIN.sql': Failed to delete SDI 'db1.TsT' in tablespace 'db1/TsT#P#p0#SP#p0sp0'.
So, if the upgrade is done TO MySQL 8.0.17 the failure is present but if MySQL 8.0.17 is used to initialize, the error is not present in MySQL 8.0.18.
Suggested fix:
ALTER TABLE ADD COLUMN should work after upgrade.
Description: A table with sub-partitions can not add a column after upgrade, if the upgrade is done from 8.0.11-16 to MySQL 8.0.17 or 8.0.18 (latest) version. This failure does not occur if the upgrade is done from 8.0.17 to 8.0.18. How to repeat: Steps: 1. Initialize MySQL 8.0.11-16. 2. Execute: create schema db1; CREATE TABLE db1.Two (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased)) (partition CUST_ID_LESS_2010 values less than (2010)); CREATE TABLE db1.TsT (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased)) SUBPARTITION BY HASH( TO_DAYS(purchased) ) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE); INSERT INTO db1.Two values (1,'1970-01-01'); INSERT INTO db1.TsT values (1,'1970-01-01'); 3. Live-upgrade in to MySQL 8.0.17 or MySQL 8.0.18. 4. Execute: use db1; ALTER TABLE Two ADD COLUMN col2 VARCHAR(15); ==> Works ALTER TABLE TsT ADD COLUMN col2 VARCHAR(15); ==> Fails: ERROR 3664 (HY000) at line 4 in file: 'SQLIN.sql': Failed to delete SDI 'db1.TsT' in tablespace 'db1/TsT#P#p0#SP#p0sp0'. So, if the upgrade is done TO MySQL 8.0.17 the failure is present but if MySQL 8.0.17 is used to initialize, the error is not present in MySQL 8.0.18. Suggested fix: ALTER TABLE ADD COLUMN should work after upgrade.