Description:
Issue 1:
--------
5.7 to 8.0/8.1/8.2 upgrade fails when partitions are based on range(date) with arbitrary delimiters which got deprecated in 8.0.29
Upgrade fails with ERROR 1654 (HY000): Partition column values of incorrect type
Issue 2:
-------
Upgrade between minor versions of 8.0 ( from <=8.0.28 to >=8.0.29 ) and to innovation release makes the table inaccessible after upgrade when partitions are based on range(date) with arbitrary delimiters
ERROR 1654 (HY000): Partition column values of incorrect type
Rebuilding the table to supported delimiters work.
How to repeat:
Issue 1:
########
5.7 to 8.0/8.1/8.2 upgrade fails when partitions are based on range(date) with arbitrary delimiters which got deprecated in 8.0.29
Create the following in mysql 5.7 version
USE test;
CREATE TABLE `i_am_gonna_cause_upgrade_failures` (
`id` date DEFAULT '2012_12_12'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(id)
(PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */;
-- Upgrade to any version >=8.0.29. I tried with 8.0.35 ----
Upgrade doesn't complete:
2023-11-10T19:12:07.539726Z 0 [Note] /home/ec2-user/opt/mysql/5.7.44/bin/mysqld: Shutdown complete
2023-11-10T19:12:07.562019Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_5_7_44/data/mysql_sandbox5744.pid ended
2023-11-10T19:12:12.547141Z mysqld_safe Logging to '/home/ec2-user/sandboxes/msb_8_0_35/data/msandbox.err'.
2023-11-10T19:12:12.565330Z mysqld_safe Starting mysqld daemon with databases from /home/ec2-user/sandboxes/msb_8_0_35/data
2023-11-10T19:12:12.761708Z 0 [System] [MY-010116] [Server] /home/ec2-user/opt/mysql/8.0.35/bin/mysqld (mysqld 8.0.35) starting as process 1313818
2023-11-10T19:12:12.768263Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2023-11-10T19:12:12.768283Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2023-11-10T19:12:13.131837Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2023-11-10T19:12:13.941658Z 2 [ERROR] [MY-013140] [Server] Partition column values of incorrect type
2023-11-10T19:12:14.257251Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables.
2023-11-10T19:12:14.257273Z 0 [ERROR] [MY-010119] [Server] Aborting
2023-11-10T19:12:15.408608Z 0 [System] [MY-010910] [Server] /home/ec2-user/opt/mysql/8.0.35/bin/mysqld: Shutdown complete (mysqld 8.0.35) MySQL Community Server - GPL.
2023-11-10T19:12:15.434488Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_8_0_35/data/mysql_sandbox8035.pid ended
Issue 2:
########
Create the following in mysql version <= 8.0.28
USE test;
CREATE TABLE `i_am_not_going_to_open_after_upgrade` (
`id` date DEFAULT '2012_12_12'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50500 PARTITION BY RANGE COLUMNS(id)
(PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */;
Upgrade to 8.0.35
====
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8028] {msandbox} ((none)) > USE test;
Database changed
mysql [localhost:8028] {msandbox} (test) > CREATE TABLE `i_am_not_going_to_open_after_upgrade` (
-> `id` date DEFAULT '2012_12_12'
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> /*!50500 PARTITION BY RANGE COLUMNS(id)
-> (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.05 sec)
mysql [localhost:8028] {msandbox} (test) > \q
Bye
[ec2-user@ip-172-31-54-9 ~]$ dbdeployer admin upgrade msb_8_0_28 msb_8_0_35
stop /home/ec2-user/sandboxes/msb_8_0_28
stop /home/ec2-user/sandboxes/msb_8_0_35
Data directory msb_8_0_28/data moved to msb_8_0_35/data
........ sandbox server started
The data directory from msb_8_0_35/data is preserved in msb_8_0_35/data-msb_8_0_35
The data directory from msb_8_0_28/data is now used in msb_8_0_35/data
msb_8_0_28 is not operational and can be deleted
[ec2-user@ip-172-31-54-9 ~]$ dbdeployer use msb_8_0_35
running /home/ec2-user/sandboxes/msb_8_0_35/ use
/home/ec2-user/sandboxes/msb_8_0_35/use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.35 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8035] {msandbox} ((none)) > show create table test.i_am_not_going_to_open_after_upgrade\G
ERROR 1654 (HY000): Partition column values of incorrect type
=====
Upgrade to 8.2.0
=========
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8028] {msandbox} ((none)) > USE test;
Database changed
mysql [localhost:8028] {msandbox} (test) > CREATE TABLE `i_am_not_going_to_open_after_upgrade` (
-> `id` date DEFAULT '2012_12_12'
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1
-> /*!50500 PARTITION BY RANGE COLUMNS(id)
-> (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.04 sec)
mysql [localhost:8028] {msandbox} (test) > \q
Bye
[ec2-user@ip-172-31-54-9 ~]$ dbdeployer admin upgrade msb_8_0_28 msb_8_2_0
stop /home/ec2-user/sandboxes/msb_8_0_28
stop /home/ec2-user/sandboxes/msb_8_2_0
Data directory msb_8_0_28/data moved to msb_8_2_0/data
........ sandbox server started
The data directory from msb_8_2_0/data is preserved in msb_8_2_0/data-msb_8_2_0
The data directory from msb_8_0_28/data is now used in msb_8_2_0/data
msb_8_0_28 is not operational and can be deleted
[ec2-user@ip-172-31-54-9 ~]$ dbdeployer use msb_8_2_0
running /home/ec2-user/sandboxes/msb_8_2_0/ use
/home/ec2-user/sandboxes/msb_8_2_0/use
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql [localhost:8200] {msandbox} ((none)) > show create table test.i_am_not_going_to_open_after_upgrade\G
ERROR 1654 (HY000): Partition column values of incorrect type
mysql [localhost:8200] {msandbox} ((none)) >
Suggested fix:
Fix to allow this as the support for arbitrary delimiter is deprecated but not removed yet.
Also, create a check in mysql shell upgrade checker for this.
Description: Issue 1: -------- 5.7 to 8.0/8.1/8.2 upgrade fails when partitions are based on range(date) with arbitrary delimiters which got deprecated in 8.0.29 Upgrade fails with ERROR 1654 (HY000): Partition column values of incorrect type Issue 2: ------- Upgrade between minor versions of 8.0 ( from <=8.0.28 to >=8.0.29 ) and to innovation release makes the table inaccessible after upgrade when partitions are based on range(date) with arbitrary delimiters ERROR 1654 (HY000): Partition column values of incorrect type Rebuilding the table to supported delimiters work. How to repeat: Issue 1: ######## 5.7 to 8.0/8.1/8.2 upgrade fails when partitions are based on range(date) with arbitrary delimiters which got deprecated in 8.0.29 Create the following in mysql 5.7 version USE test; CREATE TABLE `i_am_gonna_cause_upgrade_failures` ( `id` date DEFAULT '2012_12_12' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE COLUMNS(id) (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */; -- Upgrade to any version >=8.0.29. I tried with 8.0.35 ---- Upgrade doesn't complete: 2023-11-10T19:12:07.539726Z 0 [Note] /home/ec2-user/opt/mysql/5.7.44/bin/mysqld: Shutdown complete 2023-11-10T19:12:07.562019Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_5_7_44/data/mysql_sandbox5744.pid ended 2023-11-10T19:12:12.547141Z mysqld_safe Logging to '/home/ec2-user/sandboxes/msb_8_0_35/data/msandbox.err'. 2023-11-10T19:12:12.565330Z mysqld_safe Starting mysqld daemon with databases from /home/ec2-user/sandboxes/msb_8_0_35/data 2023-11-10T19:12:12.761708Z 0 [System] [MY-010116] [Server] /home/ec2-user/opt/mysql/8.0.35/bin/mysqld (mysqld 8.0.35) starting as process 1313818 2023-11-10T19:12:12.768263Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2023-11-10T19:12:12.768283Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2023-11-10T19:12:13.131837Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2023-11-10T19:12:13.941658Z 2 [ERROR] [MY-013140] [Server] Partition column values of incorrect type 2023-11-10T19:12:14.257251Z 0 [ERROR] [MY-010022] [Server] Failed to Populate DD tables. 2023-11-10T19:12:14.257273Z 0 [ERROR] [MY-010119] [Server] Aborting 2023-11-10T19:12:15.408608Z 0 [System] [MY-010910] [Server] /home/ec2-user/opt/mysql/8.0.35/bin/mysqld: Shutdown complete (mysqld 8.0.35) MySQL Community Server - GPL. 2023-11-10T19:12:15.434488Z mysqld_safe mysqld from pid file /home/ec2-user/sandboxes/msb_8_0_35/data/mysql_sandbox8035.pid ended Issue 2: ######## Create the following in mysql version <= 8.0.28 USE test; CREATE TABLE `i_am_not_going_to_open_after_upgrade` ( `id` date DEFAULT '2012_12_12' ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50500 PARTITION BY RANGE COLUMNS(id) (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */; Upgrade to 8.0.35 ==== Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:8028] {msandbox} ((none)) > USE test; Database changed mysql [localhost:8028] {msandbox} (test) > CREATE TABLE `i_am_not_going_to_open_after_upgrade` ( -> `id` date DEFAULT '2012_12_12' -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50500 PARTITION BY RANGE COLUMNS(id) -> (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */; Query OK, 0 rows affected (0.05 sec) mysql [localhost:8028] {msandbox} (test) > \q Bye [ec2-user@ip-172-31-54-9 ~]$ dbdeployer admin upgrade msb_8_0_28 msb_8_0_35 stop /home/ec2-user/sandboxes/msb_8_0_28 stop /home/ec2-user/sandboxes/msb_8_0_35 Data directory msb_8_0_28/data moved to msb_8_0_35/data ........ sandbox server started The data directory from msb_8_0_35/data is preserved in msb_8_0_35/data-msb_8_0_35 The data directory from msb_8_0_28/data is now used in msb_8_0_35/data msb_8_0_28 is not operational and can be deleted [ec2-user@ip-172-31-54-9 ~]$ dbdeployer use msb_8_0_35 running /home/ec2-user/sandboxes/msb_8_0_35/ use /home/ec2-user/sandboxes/msb_8_0_35/use Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.0.35 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:8035] {msandbox} ((none)) > show create table test.i_am_not_going_to_open_after_upgrade\G ERROR 1654 (HY000): Partition column values of incorrect type ===== Upgrade to 8.2.0 ========= Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.28 MySQL Community Server - GPL Copyright (c) 2000, 2022, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:8028] {msandbox} ((none)) > USE test; Database changed mysql [localhost:8028] {msandbox} (test) > CREATE TABLE `i_am_not_going_to_open_after_upgrade` ( -> `id` date DEFAULT '2012_12_12' -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -> /*!50500 PARTITION BY RANGE COLUMNS(id) -> (PARTITION p_2022_01 VALUES LESS THAN ('2022_02_01') ENGINE = InnoDB) */; Query OK, 0 rows affected (0.04 sec) mysql [localhost:8028] {msandbox} (test) > \q Bye [ec2-user@ip-172-31-54-9 ~]$ dbdeployer admin upgrade msb_8_0_28 msb_8_2_0 stop /home/ec2-user/sandboxes/msb_8_0_28 stop /home/ec2-user/sandboxes/msb_8_2_0 Data directory msb_8_0_28/data moved to msb_8_2_0/data ........ sandbox server started The data directory from msb_8_2_0/data is preserved in msb_8_2_0/data-msb_8_2_0 The data directory from msb_8_0_28/data is now used in msb_8_2_0/data msb_8_0_28 is not operational and can be deleted [ec2-user@ip-172-31-54-9 ~]$ dbdeployer use msb_8_2_0 running /home/ec2-user/sandboxes/msb_8_2_0/ use /home/ec2-user/sandboxes/msb_8_2_0/use Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 8.2.0 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql [localhost:8200] {msandbox} ((none)) > show create table test.i_am_not_going_to_open_after_upgrade\G ERROR 1654 (HY000): Partition column values of incorrect type mysql [localhost:8200] {msandbox} ((none)) > Suggested fix: Fix to allow this as the support for arbitrary delimiter is deprecated but not removed yet. Also, create a check in mysql shell upgrade checker for this.