sap-commerce-db-sync icon indicating copy to clipboard operation
sap-commerce-db-sync copied to clipboard

Published 20 hours ago • verified publisher icon SAP

Staged copy approach is broken

Open thendricks0 opened this issue 1 year ago • 12 comments

We are still trying to get a migration from a HANA2 DB to the CCv2 both systems are on 2205. For easier debugging I am currently trying to migrate on a local development environment since the CCv2 breaks easily.

I am trying to use the staged approach just like the guide describes.

db.tableprefix = cc
migration.ds.target.db.tableprefix = cmt

Initializing the local system first, then migrating the schemas and then migrating the data which is successful. Afterwards I switch the properties just like the docs describe:

db.tableprefix = cmt
migration.ds.target.db.tableprefix = cc

If I then try to run ant updatesystem the system fails to start:

     [java] 16/04/2024 14:56:52                      ERROR [main]  [DeploymentMigrationUtil] Error while migrating deployments of extension basecommerce
     [java]  de.hybris.platform.jalo.JaloSystemException: Invalid object name 'cmtcmtcomposedtypes'.
     [java]     at de.hybris.platform.persistence.type.TypeManagerEJB.findByCodeExact(TypeManagerEJB.java:286) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.persistence.type.TypeManagerEJB.getComposedType(TypeManagerEJB.java:458) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.type.TypeManager.getComposedType(TypeManager.java:363) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.util.migration.DeploymentMigrationUtil.migrateGeneralizedDuplicatedDeployments(DeploymentMigrationUtil.java:151) ~[coreserver.jar:?]
...

For some reason it looks for tables called cmtcmtcomposedtypes instead of cmtcomposedtypes which makes the cmt prefix unusable. Switching back to the cc prefix does work fine though!

If I change the prefix to an empty value it looks for composedtypes and fails because it doesn't exist. For some reason the db.tableprefix is doubled whenever I try to switch to the migrated tables!

I'm not sure if there is a bug in the db-sync extensions or if the documentation is missing something crucial.

The problem with the direct copy approach is that the migration fails at some point breaking the database for the d1 system completely with it being irrecoverable so the support has to reset it. The direct copy approach also doesn't seem to ignore the audit tables (migration.data.tables.audit.enabled is set to false!) while the staged approach is 100 times faster because it properly ignores the audit tables. But the staged copy approach comes with the problem I described above.

thendricks0 avatar Apr 16 '24 13:04 thendricks0

Try this instead:

  1. Configure: db.tableprefix=cmt migration.ds.target.db.tableprefix= (this must be set explicitly to empty string value)
  2. Run init and then migration with target schema populated by DB Sync schema migrator
  3. Revert configuration: db.tableprefix=
  4. Run update system

lnowakowski avatar Apr 16 '24 13:04 lnowakowski

Great idea! This seemed to work, the data migration was successful and the updatesystem also went through without errors, so we can finally continue with the migration!

But I still wonder why the table prefixes are doubled. I debugged into EntityContext in Hybris and the ItemDeployment Object does contain the cmtcmtcomposedtypes table which is causing the exceptions and it also contains the prefix, I think the HJMP Wrapper classes use the prefix + table from the ItemDeployment.

And I think the UpdateYDeploymentsPostProcessor is the culprit here, it adjusts table names in the ydeployments table and seems to add table names with a prefix, but when I look at my ccydeploymentstable all the tablenames in there are without the prefix. So the step of migrating the ydeployments table is indeed unnecessary, at least for Commerce v2205 and higher it seems.

I'll adjust it locally and then retry it with the documented approach of using a prefix for both properties, if it works I'll add a PR here.

thendricks0 avatar Apr 17 '24 09:04 thendricks0

Yes I agree that recently introduced UpdateYDeploymentsPostProcessor might be a root cause of this problem. Will need to review it again. However usage of empty table prefix, for one tables set, is more common use case for actual migration process.

lnowakowski avatar Apr 17 '24 09:04 lnowakowski

Yeah it indeed makes sense to have an empty table prefix for the final database so I guess I can definitely continue working on the cloud move. Setting db.tableprefix in hcs_common does apply on initialization, right? It would be quite inconvenient to create a new build every time I want to switch prefixes..

Thanks for your quick help!

thendricks0 avatar Apr 17 '24 09:04 thendricks0

Yes, use hcs_common for any runtime properties, build is not required to apply this

lnowakowski avatar Apr 17 '24 09:04 lnowakowski

Not sure why but for some reason the system is broken again when using the staged copy approach. When running ant updatesystem the log is spammed with exceptions when importing essential data from all extensions:

    [java] 18/04/2024 11:53:50                      ERROR [main]  [Initialization] null
     [java]  de.hybris.platform.jalo.JaloSystemException: null
     [java]     at de.hybris.platform.persistence.GenericItemEJBImpl.createGenericItem(GenericItemEJBImpl.java:82) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.GenericItem.createItem(GenericItem.java:109) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.media.GeneratedMedia.createItem(GeneratedMedia.java:177) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.media.Media.createItem(Media.java:114) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.impex.jalo.ImpExMedia.createItem(ImpExMedia.java:75) ~[impexserver.jar:?]
     [java]     at de.hybris.platform.jalo.Item.newInstanceInternal(Item.java:4089) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.Item$19.execute(Item.java:4008) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.tx.Transaction.execute(Transaction.java:1266) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.tx.Transaction.execute(Transaction.java:1184) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.jalo.Item.newInstance(Item.java:4003) ~[coreserver.jar:?]

Importing any impex files results in ModelSavingExceptions:

     [java] 18/04/2024 13:05:33                      ERROR [main]  [DefaultSetupImpexService] Importing [/releasedata/releases/39.0.0/01_solr.impex]... FAILED
     [java]  de.hybris.platform.servicelayer.exceptions.ModelSavingException: null
     [java]     at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.createNewItem(ItemModelConverter.java:2344) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.lambda$save$1(ItemModelConverter.java:848) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.polyglot.UnitOfWorkInterceptor.execute(UnitOfWorkInterceptor.java:57) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.polyglot.UnitOfWorkInterceptor.createFromServiceLayer(UnitOfWorkInterceptor.java:34) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.save(ItemModelConverter.java:848) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.model.impl.wrapper.ModelWrapper.save(ModelWrapper.java:364) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.saveNewOnes(ResolvingModelPersister.java:78) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.persist(ResolvingModelPersister.java:46) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.saveViaJalo(DefaultModelService.java:1173) ~[coreserver.jar:?]
     [java]     at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.doJaloPersistence(DefaultModelService.java:732) ~[coreserver.jar:?]
...

thendricks0 avatar Apr 18 '24 10:04 thendricks0

I'm guessing that there is an NullPointerException, but I don't see origin of it on provided here exception stacks, can you see/share more details?

lnowakowski avatar Apr 18 '24 11:04 lnowakowski

Here the full log message:

18/04/2024 15:26:37                      ERROR [hybrisHTTP12]  [AbstractRuleEngineSystemSetup] Importing [/ruleengineservices/import/essentialdata-coredefinitions_de.impex]... FAILED
 de.hybris.platform.servicelayer.exceptions.ModelSavingException: null
        at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.createNewItem(ItemModelConverter.java:2344) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.lambda$save$1(ItemModelConverter.java:848) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.polyglot.UnitOfWorkInterceptor.execute(UnitOfWorkInterceptor.java:57) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.polyglot.UnitOfWorkInterceptor.createFromServiceLayer(UnitOfWorkInterceptor.java:34) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.save(ItemModelConverter.java:848) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.wrapper.ModelWrapper.save(ModelWrapper.java:364) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.saveNewOnes(ResolvingModelPersister.java:78) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.ResolvingModelPersister.persist(ResolvingModelPersister.java:46) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.saveViaJalo(DefaultModelService.java:1173) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.doJaloPersistence(DefaultModelService.java:732) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.persistWrappers(DefaultModelService.java:1117) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.performPersistenceOperations(DefaultModelService.java:709) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.saveAllInternal(DefaultModelService.java:703) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.saveAll(DefaultModelService.java:683) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.model.impl.DefaultModelService.save(DefaultModelService.java:630) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.impex.impl.StreamBasedImpExResource.<init>(StreamBasedImpExResource.java:63) ~[impexserver.jar:?]
        at de.hybris.platform.servicelayer.impex.impl.StreamBasedImpExResource.<init>(StreamBasedImpExResource.java:32) ~[impexserver.jar:?]
        at de.hybris.platform.ruleengine.setup.AbstractRuleEngineSystemSetup.importImpexFile(AbstractRuleEngineSystemSetup.java:99) ~[ruleengineserver.jar:?]
        at de.hybris.platform.ruleengine.setup.AbstractRuleEngineSystemSetup.importLanguageSpecificImpexFiles(AbstractRuleEngineSystemSetup.java:84) ~[ruleengineserver.jar:?]
        at de.hybris.platform.ruleengine.setup.AbstractRuleEngineSystemSetup.importImpexFile(AbstractRuleEngineSystemSetup.java:60) ~[ruleengineserver.jar:?]
        at de.hybris.platform.ruleengineservices.setup.impl.RuleEngineServicesSystemSetup.createEssentialData(RuleEngineServicesSystemSetup.java:26) ~[ruleengineservicesserver.jar:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
        at org.springframework.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:282) ~[spring-core-5.3.19.jar:5.3.19]
        at de.hybris.platform.core.initialization.impl.SystemSetupCollectorImpl.invokeSystemSetupMethod(SystemSetupCollectorImpl.java:327) ~[coreserver.jar:?]
        at de.hybris.platform.core.initialization.impl.SystemSetupCollectorImpl.executeMethods(SystemSetupCollectorImpl.java:279) ~[coreserver.jar:?]
        at de.hybris.platform.core.Initialization.createEssentialData(Initialization.java:1089) ~[coreserver.jar:?]
        at de.hybris.platform.core.Initialization.doInitializeImpl(Initialization.java:630) ~[coreserver.jar:?]
        at de.hybris.platform.core.Initialization$5.call(Initialization.java:885) ~[coreserver.jar:?]
        at de.hybris.platform.core.Initialization$5.call(Initialization.java:876) ~[coreserver.jar:?]
        at de.hybris.platform.core.system.InitializationLockHandler.performLocked(InitializationLockHandler.java:68) ~[coreserver.jar:?]
        at de.hybris.platform.core.Initialization.doInitialize(Initialization.java:917) ~[coreserver.jar:?]
        at de.hybris.platform.hac.facade.HacInitUpdateFacade.executeInitUpdate(HacInitUpdateFacade.java:60) ~[hacserver.jar:?]
        at de.hybris.platform.hac.controller.platform.InitUpdateController.initExecuteWrap(InitUpdateController.java:114) ~[classes/:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
        at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1067) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:963) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:682) ~[servlet-api.jar:?]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.19.jar:5.3.19]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:765) ~[servlet-api.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52) ~[tomcat-websocket.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.sitemesh.webapp.contentfilter.ContentBufferingFilter.bufferAndPostProcess(ContentBufferingFilter.java:169) ~[sitemesh-3.0-alpha-2.jar:?]
        at org.sitemesh.webapp.contentfilter.ContentBufferingFilter.doFilter(ContentBufferingFilter.java:126) ~[sitemesh-3.0-alpha-2.jar:?]
        at org.sitemesh.config.ConfigurableSiteMeshFilter.doFilter(ConfigurableSiteMeshFilter.java:163) ~[sitemesh-3.0-alpha-2.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.springframework.web.multipart.support.MultipartFilter.doFilterInternal(MultipartFilter.java:125) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:337) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:115) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:81) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:122) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:116) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:126) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:81) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:109) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:102) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:93) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:149) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:150) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:219) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:213) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:103) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:89) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:117) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.header.HeaderWriterFilter.doHeadersAfter(HeaderWriterFilter.java:90) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:75) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:55) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:110) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:80) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.access.channel.ChannelProcessingFilter.doFilter(ChannelProcessingFilter.java:133) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:346) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:221) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:186) ~[spring-security-web-5.6.9.jar:5.6.9]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267) ~[spring-web-5.3.19.jar:5.3.19]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:94) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:338) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$StatisticsGatewayFilter.doFilter(AbstractPlatformFilterChain.java:426) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.SecureMediaFilter.doFilter(SecureMediaFilter.java:100) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.DataSourceSwitchingFilter.doFilter(DataSourceSwitchingFilter.java:59) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.session.stale.impl.DefaultStaleSessionCheckingFilterChain.filterWithStaleSessionChecking(DefaultStaleSessionCheckingFilterChain.java:92) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.session.stale.impl.DefaultStaleSessionCheckingFilterChain.doFilter(DefaultStaleSessionCheckingFilterChain.java:58) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.SessionFilter.doFilterInternal(SessionFilter.java:122) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.SessionFilter.doFilter(SessionFilter.java:84) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.ResourcesGuardFilter.doFilter(ResourcesGuardFilter.java:41) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.RedirectWhenSystemIsNotInitializedFilter.doFilter(RedirectWhenSystemIsNotInitializedFilter.java:94) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.Log4JFilter.doFilter(Log4JFilter.java:37) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.session.HybrisSpringSessionFilter.doFilter(HybrisSpringSessionFilter.java:67) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain$InternalFilterChain.doFilter(AbstractPlatformFilterChain.java:308) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain.processStandardFilterChain(AbstractPlatformFilterChain.java:216) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.AbstractPlatformFilterChain.doFilterInternal(AbstractPlatformFilterChain.java:193) ~[coreserver.jar:?]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:354) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:267) ~[spring-web-5.3.19.jar:5.3.19]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at de.hybris.platform.servicelayer.web.XSSFilter.processPatternsAndDoFilter(XSSFilter.java:351) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.web.XSSFilter.doFilter(XSSFilter.java:299) ~[coreserver.jar:?]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.19.jar:5.3.19]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.19.jar:5.3.19]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:177) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:97) ~[catalina.jar:8.5.85]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:543) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) ~[catalina.jar:8.5.85]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[catalina.jar:8.5.85]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) ~[catalina.jar:8.5.85]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:698) ~[catalina.jar:8.5.85]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:367) ~[catalina.jar:8.5.85]
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:639) ~[tomcat-coyote.jar:8.5.85]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-coyote.jar:8.5.85]
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:885) ~[tomcat-coyote.jar:8.5.85]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1688) ~[tomcat-coyote.jar:8.5.85]
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-coyote.jar:8.5.85]
        at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) ~[tomcat-util.jar:8.5.85]
        at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) ~[tomcat-util.jar:8.5.85]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-util.jar:8.5.85]
        at java.lang.Thread.run(Thread.java:840) ~[?:?]
Caused by: de.hybris.platform.jalo.JaloSystemException
        at de.hybris.platform.persistence.GenericItemEJBImpl.createGenericItem(GenericItemEJBImpl.java:82) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.GenericItem.createItem(GenericItem.java:109) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.media.GeneratedMedia.createItem(GeneratedMedia.java:177) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.media.Media.createItem(Media.java:114) ~[coreserver.jar:?]
        at de.hybris.platform.impex.jalo.ImpExMedia.createItem(ImpExMedia.java:75) ~[impexserver.jar:?]
        at de.hybris.platform.jalo.Item.newInstanceInternal(Item.java:4089) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.Item$19.execute(Item.java:4008) ~[coreserver.jar:?]
        at de.hybris.platform.tx.Transaction.execute(Transaction.java:1266) ~[coreserver.jar:?]
        at de.hybris.platform.tx.Transaction.execute(Transaction.java:1184) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.Item.newInstance(Item.java:4003) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1272) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1197) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.createNewItem(ItemModelConverter.java:2331) ~[coreserver.jar:?]
        ... 171 more
Caused by: de.hybris.platform.persistence.hjmp.HJMPException
        at de.hybris.platform.persistence.GenericBMPBean$GenericItemEntityState.createEntity(GenericBMPBean.java:1872) ~[coreserver.jar:?]
        at de.hybris.platform.persistence.GenericBMPBean.ejbCreate(GenericBMPBean.java:394) ~[coreserver.jar:?]
        at jdk.internal.reflect.GeneratedMethodAccessor629.invoke(Unknown Source) ~[?:?]
        at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
        at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
        at de.hybris.platform.util.Utilities.callMethod(Utilities.java:1021) ~[coreserver.jar:?]
        at de.hybris.platform.util.Utilities.callMethod(Utilities.java:1011) ~[coreserver.jar:?]
        at de.hybris.platform.persistence.framework.HomeInvocationHandler.invokeMethod(HomeInvocationHandler.java:75) ~[coreserver.jar:?]
        at de.hybris.platform.persistence.framework.HomeInvocationHandler.invoke(HomeInvocationHandler.java:51) ~[coreserver.jar:?]
        at jdk.proxy2.$Proxy185.create(Unknown Source) ~[?:?]
        at de.hybris.platform.persistence.GenericItemEJBImpl.createGenericItem(GenericItemEJBImpl.java:72) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.GenericItem.createItem(GenericItem.java:109) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.media.GeneratedMedia.createItem(GeneratedMedia.java:177) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.media.Media.createItem(Media.java:114) ~[coreserver.jar:?]
        at de.hybris.platform.impex.jalo.ImpExMedia.createItem(ImpExMedia.java:75) ~[impexserver.jar:?]
        at de.hybris.platform.jalo.Item.newInstanceInternal(Item.java:4089) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.Item$19.execute(Item.java:4008) ~[coreserver.jar:?]
        at de.hybris.platform.tx.Transaction.execute(Transaction.java:1266) ~[coreserver.jar:?]
        at de.hybris.platform.tx.Transaction.execute(Transaction.java:1184) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.Item.newInstance(Item.java:4003) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1272) ~[coreserver.jar:?]
        at de.hybris.platform.jalo.type.ComposedType.newInstance(ComposedType.java:1197) ~[coreserver.jar:?]
        at de.hybris.platform.servicelayer.internal.converter.impl.ItemModelConverter.createNewItem(ItemModelConverter.java:2331) ~[coreserver.jar:?]
        ... 171 more

I've been debugging down the stacktrace and the Exception is thrown in GenericBMPBean$$GenericItemEntityState.createEntity(prc, typeInfoMap) which is actually an SQLException that is thrown when modifiedLines = statement.executeUpdate() is called. The statement that is executed is INSERT INTO medias ( hjmpTS,PK,createdTS,modifiedTS,TypePkString,p_catalogversion,p_code,p_commentcharacter,p_encoding,p_fieldseparator,p_folder,p_linestoskip,p_quotecharacter,p_realfilename,p_removable,p_removeonsuccess ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) for creating an ImpExMedia which is created when an Impex is imported.

The actual exception message which is lost in the catch block is actually Conversion failed when converting the nvarchar value '#' to data type int. I've then compared the schemas of the pre-migration medias table and the newly migrated one and for some reason the new medias table has different column types:

P_FIELDSEPARATOR	int NULL	
P_QUOTECHARACTER	int NULL	
P_COMMENTCHARACTER	int NULL

while the one created from initializing had:

p_fieldseparator	char(4) NULL	
p_quotecharacter	char(4) NULL	
p_commentcharacter	char(4) NULL

So apparently the schema migration is broken in this case? Never had this issue with the previous CMT extensions, but I also never migrated from a HANA DB, is this a HANA problem or a problem with schema generation?

Edit: Yeah looking at the generated schema script the column types are indeed set to INTEGER. This is kind of problematic since I now can't trust the whole process that my schema migration script is actually representing the database correctly..

Edit2: Yep the columns in the HANA DB are of type SMALLINT which gets converted to INTEGER when using the staged copy approach, so basically I cannot use the staged copy approach with HANA to MSSQL, or I'd have to manually edit the generated schema SQL file, go over 12k of lines and figure out the correct type for the target db. This is a real setback now...

thendricks0 avatar Apr 18 '24 13:04 thendricks0

This is rather old known issue with storing single char value for ImpEx separators configuration. It usually gets handled by this custom approach of data adaptation in DB Sync: https://github.com/SAP/sap-commerce-db-sync/blob/main/commercedbsync/src/com/sap/cx/boosters/commercedbsync/dataset/impl/DefaultDataSet.java#L62-L77

Also to handle such problem "manually" following SQL was used:

EXEC sp_RENAME 'medias.p_fieldseparator', 'p_fieldseparator_', 'COLUMN';
EXEC sp_RENAME 'medias.p_quotecharacter', 'p_quotecharacter_', 'COLUMN';
EXEC sp_RENAME 'medias.p_commentcharacter', 'p_commentcharacter_', 'COLUMN';

ALTER TABLE dbo.medias ADD p_fieldseparator CHAR(4) NULL, p_quotecharacter CHAR(4) NULL, p_commentcharacter CHAR(4) NULL;

UPDATE medias SET p_fieldseparator = CHAR(p_fieldseparator_) WHERE p_fieldseparator_ IS NOT NULL;
UPDATE medias SET p_quotecharacter = CHAR(p_quotecharacter_) WHERE p_quotecharacter_ IS NOT NULL;
UPDATE medias SET p_commentcharacter = CHAR(p_commentcharacter_) WHERE p_commentcharacter_ IS NOT NULL;

ALTER TABLE dbo.medias DROP COLUMN p_fieldseparator_, p_quotecharacter_, p_commentcharacter_;

lnowakowski avatar Apr 18 '24 15:04 lnowakowski

https://github.com/SAP/sap-commerce-db-sync/blob/main/commercedbsync/src/com/sap/cx/boosters/commercedbsync/dataset/impl/DefaultDataSet.java#L62-L77

But that code wouldn't really work for staged copying, since thar target column type is not CHAR but already set to INTEGER on schema migration, right?

But this means the SMALLINT to CHAR conversion is the only col type conversion issue? Because if that is the only problem it would actually not be that much of a problem, there are only 16 INTEGER type columns in the schema migration SQL script, I can quickly validate which ones are actually CHAR(4) types and change the SQL script.

thendricks0 avatar Apr 19 '24 06:04 thendricks0

Usually this issue limits to cronjobs and medias tables if there are no other custom attributes of impacted type.

Still it would be good to understand why in your case source/target schema attributes are of different types than expected here java.lang.Character: image as per deployments specification for in HANA and MSSQL databases: image

lnowakowski avatar Apr 19 '24 07:04 lnowakowski

Yeah I understand why the types are different, in my opinion schema migration should also respect type mappings from the database-schema defined in the deployment.xml. Since source and target DB are expected to be mostly the same, I think the db-sync extension should also parse items.xmls to get the correct typings. Then we would not have these typing problems, but I also get that that is a more complex task.

Funny thing is, on my local system I just executed a direct copy and it finished in 5 minutes without copying any audit tables. Not sure why direct copy suddenly works fine now but it does. If it also works on d1 I might actually prefer direct copy since I know for sure that the target db already has the correct column types and I won't have any headaches with incompatible col types.

Edit: Ok direct copy worked on d1 as well, not sure entirely why, the only thing that changed was that I fixed some duplicate unique identifiers in the source DB that weren't unique anymore in the target DB because of case insensitivity. But now the db copy works fine, ignoring auditing tables and no errors at all. 🤷

thendricks0 avatar Apr 19 '24 07:04 thendricks0

No further actions for now

lnowakowski avatar Jul 09 '24 06:07 lnowakowski