Moving table partitions to different tablespace location

[SUJITH NAIR]

I am trying to move partitions to a different location because we have reached the maximum limit at the host level, and it cannot be extended.

I created a new tablespace and tried to move the partitioned tables.

SQL

mysql> ALTER TABLE dummy_table TABLESPACE new_ts3;
ERROR 1478 (HY000): InnoDB : A partitioned table is not allowed in a shared tablespace.
mysql>


I created separate tablespaces for each partition:

SQL

mysql> CREATE TABLESPACE ts2 ADD DATAFILE '/u01/data/datafile2.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLESPACE ts3 ADD DATAFILE '/u01/data/datafile3.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLESPACE ts4 ADD DATAFILE '/u01/data/datafile4.ibd' ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)

However, when I tried to move the partitions, I encountered a syntax error:

SQL

mysql> ALTER TABLE dummy_table
-> PARTITION p20250212 TABLESPACE ts1,
-> PARTITION p20250213 TABLESPACE ts2,
-> PARTITION p20250214 TABLESPACE ts3,
-> PARTITION p20250215 TABLESPACE ts4,
-> PARTITION p20250216 TABLESPACE ts5,
-> PARTITION p20250217 TABLESPACE ts6,
-> PARTITION p20250218 TABLESPACE ts7,
-> PARTITION p20250219 TABLESPACE ts8,
-> PARTITION p20250220 TABLESPACE ts9;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PARTITION p20250212 TABLESPACE ts1,
PARTITION p20250213 TABLESPACE ts2,
PARTITION p20250214 TABLESPACE ts3' at line 2
mysql>

Any other way to go forward?