Leftover tables with data when system was initialized with different typesystem/extensions

[thendricks0]

Description

There is an issue where there is leftover data in tables when these tables are not existent on the source db. The problem is those entries in those tables cause duplicate key exceptions and they are not visible to the commerce system, its basically hidden data that causes a system to be in an unusable state.

This can happen when the target system has different extensions than the source system, which could happen in the future when 2205 is blocked in the CCv2.
To clarify this issue, the db-sync documentation's basic steps to perform a db-sync is: Adjust onpremise source code to make it compatible with CCv2 -> build and deploy with "initialize" -> migrate db using db-sync -> migrate media using SAS token -> re-deploy using "migrate data". At least this is true for the direct copy approach.

This becomes an issue when the code base contains extensions or rather items that are not present in the onpremise database.
To give an example, our code base for the CCv2 contained the integrationservices and SCPI extensions but the onpremise system did not. The reason for this is that we already had to continue migration of the synchronized order management while we had to wait for the customer to test the CCv2 environment.

Explanation

When the ccv2 environment was initialized with extensions that were not present in the onpremise environment, it created ComposedType items for IntegrationObject and other items that are created from the integrationservices extensions.
This means the initialize created the deployment table "integrationobjs" (typecode 8400) and assigned the pk for this type to "1234" (as example).

But when migrating the database it obviously overrides the composedtypes table with the composedtypes from the source database. But the source db did not have this type. So when the db migration was done and we did a re-deployment with "migrate data" the composed type "IntegrationObject" was created with a NEW pk "5678". But the old table "integrationobjs" still had all the IntegrationObjects that were created with the TypePKString "1234".

This caused the system to be have irregular, because re-importing the impex files to create or update all the IntegrationObjects failed with a duplicate key exception. Since the objects DID exist, but they weren't visible in the Backoffice or via FlexibleSearch since the new TypePK was "5678", but the existing entries still had the TypePkString "1234".

And this was just an example, of course this could be prevented by making sure the source and target system use the same deployment tables.

But IMO this will become an future issue when 2205 support will be completely dropped by the CCv2 build system. As soon as 2211 contains any deployment tables that are not present in a 2205 source system, the bug I described above will likely cause issues down the line.

Solution

Implementing migration.schema.target.tables.remove.enabled is a major requirement to prevent this, because left over tables have to be cleared out before or after the db migration.

The alternative might be using the staged copy approach but in our case staged copy from HANA to Azure SQL is broken and the issue was closed: #17

Workaround

For now to get around this issue we have to hack together a groovy script that will gather all ComposedTypes of the current system.
Then gather all the tables and map the "valid" type pks for all tables.
Afterwards use the jdbcTemplate to remove every entry in all tables that does not have a "valid" type pk.

The other workaround would be to modify the Schema Script created by the schema migration tab by identifying all tables not existing in the source system and then truncating these tables.