How to fix Error Code: 1478. InnoDB : A partitioned table is not allowed in a shared tablespace while migrating to MySQL Flexible Server from MySQL Single Server

Sachin Vettiyattil-FT 41

We are planning to migrate mysql single server to flexible server, while doing so using data migration service in offline mode we encountered the below error for few the tables which had partition. The error message is

The pipeline failed to prepare table for bulk import.
	An error occurred while preparing the table  in database  on server  for migration during activity. As a result, this table will not be migrated.
		The pipeline was unable to create the schema of object for activity using strategy MySqlSchemaMigrationTableStrategy because of a query failure.

While executing the same query manually the below error message is displayed.
Error Code: 1478. InnoDB : A partitioned table is not allowed in a shared tablespace.

What are the possible way to fix the issue and proceed with the migration. I have referred the below articles for migration.
https://video2.skills-academy.com/en-us/azure/dms/tutorial-mysql-azure-single-to-flex-offline-portal?bc=%2Fazure%2Fmysql%2Fbreadcrumb%2Ftoc.json&toc=%2Fazure%2Fmysql%2Ftoc.json

ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-10-10T12:26:37.27+00:00

Hi
Sachin Vettiyattil-FT •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you are getting error when trying to do Single to Flexible Server migration of Azure MySQL.

Placing InnoDB table partitions in shared InnoDB tablespaces is not supported. Shared tablespaces include the InnoDB system tablespace and general tablespaces

Refer to the solution provided here: https://stackoverflow.com/questions/68974656/updating-mysql-configuration-to-avid-error-a-partitioned-table-is-not-allowed and see if this works in your case.

Let us know your result. Thanks
ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-10-11T12:45:39.4766667+00:00

Hi
Sachin Vettiyattil-FT •,

We haven't heard back from you. In case you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Sachin Vettiyattil-FT 41 Reputation points

2023-10-11T14:32:51.8466667+00:00

ShaktiSingh-MSFT I did have a look at the solution, however the solution provided is to set the value for TABLESPACE to innodb_file_per_table. I believe this can be done if i manually create the table in the database. And even i do create it manually, since the table that needs to be migrated contains huge volume of data, how do i restore them in the newly created table and the database.
ShaktiSingh-MSFT 14,201 Reputation points Microsoft Employee

2023-10-12T05:00:53.49+00:00

Hi
Sachin Vettiyattil-FT •,

I would recommend you to please file a support ticket for deeper analysis and investigation on handling of huge volume data in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you.

Thanks

Share via

How to fix Error Code: 1478. InnoDB : A partitioned table is not allowed in a shared tablespace while migrating to MySQL Flexible Server from MySQL Single Server