Error in MSSQL db when deleting migrated service provider

[malshaniS]

Description

Getting the below error when deleting migrated service provider in a MSSQL 2016 setup.

TID: [-1234] [api/openbanking/dynamic-client-registration] [2025-02-26 04:25:58,659] [25e48fb8-a3a8-4486-a61c-76f7830173cd] ERROR {com.wso2.openbanking.accelerator.identity.dcr.endpoint.impl.api.ClientRegistrationApiImpl} - Error while deleting the application org.wso2.carbon.identity.oauth.dcr.exception.DCRMServerException: Error occurred while deleting service provider jFQuQ4eQbNCMSqdCog21nF
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at org.wso2.carbon.identity.base.IdentityException.error(IdentityException.java:100)
at org.wso2.carbon.identity.oauth.dcr.util.DCRMUtils.generateServerException(DCRMUtils.java:128)
at org.wso2.carbon.identity.oauth.dcr.service.DCRMService.deleteServiceProvider(DCRMService.java:547)
at org.wso2.carbon.identity.oauth.dcr.service.DCRMService.deleteApplication(DCRMService.java:159)
at com.wso2.openbanking.accelerator.identity.dcr.endpoint.impl.service.RegistrationServiceHandler.deleteRegistration(RegistrationServiceHandler.java:232)
at com.wso2.openbanking.accelerator.identity.dcr.endpoint.impl.api.ClientRegistrationApiImpl.registerClientIdDelete(ClientRegistrationApiImpl.java:80)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:179)
at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:96)
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:201)
at org.apache.cxf.jaxrs.JAXRSInvoker.invoke(JAXRSInvoker.java:104)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:59)
at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:96)
at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:307)
at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:265)
at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:234)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:208)
at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:160)
at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:225)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:304)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doDelete(AbstractHTTPServlet.java:228)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:561)
at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:279)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:199)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.filters.HttpHeaderSecurityFilter.doFilter(HttpHeaderSecurityFilter.java:129)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:168)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:144)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:168)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:482)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93)
at org.wso2.carbon.identity.context.rewrite.valve.TenantContextRewriteValve.invoke(TenantContextRewriteValve.java:119)
at org.wso2.carbon.identity.context.rewrite.valve.OrganizationContextRewriteValve.invoke(OrganizationContextRewriteValve.java:115)
at org.wso2.carbon.tomcat.ext.valves.SameSiteCookieValve.invoke(SameSiteCookieValve.java:38)
at org.wso2.carbon.identity.cors.valve.CORSValve.invoke(CORSValve.java:83)
at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:154)
at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:142)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:114)
at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49)
at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:75)
at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:152)
at org.wso2.carbon.extension.identity.x509Certificate.valve.X509CertificateAuthenticationValve.invoke(X509CertificateAuthenticationValve.java:59)
at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:660)
at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:63)
at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49)
at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:137)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:346)
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:396)
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:937)
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791)
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1190)
at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:63)
at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: org.wso2.carbon.identity.application.common.IdentityApplicationManagementException: Error while cleaning up oauth application data associated with service provider: jFQuQ4eQbNCMSqdCog21nF of tenantDomain: carbon.super
at org.wso2.carbon.identity.oauth2.internal.OAuthApplicationMgtListener.doPreDeleteApplication(OAuthApplicationMgtListener.java:178)
at org.wso2.carbon.identity.application.mgt.ApplicationManagementServiceImpl.deleteApplication(ApplicationManagementServiceImpl.java:746)
at org.wso2.carbon.identity.oauth.dcr.service.DCRMService.deleteServiceProvider(DCRMService.java:544)
... 63 more
Caused by: org.wso2.carbon.identity.oauth.IdentityOAuthAdminException: Error occurred while deleting OAuth2 application data for application with consumer key: 8x0LWXBn15hkv9RtSO5KuUr4Dv8a
at org.wso2.carbon.identity.oauth.OAuthAdminServiceImpl.removeOAuthApplicationData(OAuthAdminServiceImpl.java:970)
at org.wso2.carbon.identity.oauth2.internal.OAuthApplicationMgtListener.deleteAssociatedOAuthApps(OAuthApplicationMgtListener.java:219)
at org.wso2.carbon.identity.oauth2.internal.OAuthApplicationMgtListener.doPreDeleteApplication(OAuthApplicationMgtListener.java:176)
... 65 more
Caused by: org.wso2.carbon.identity.oauth.IdentityOAuthAdminException: Error when executing the SQL : DELETE FROM IDN_OAUTH_CONSUMER_APPS WHERE CONSUMER_KEY=?
at org.wso2.carbon.identity.oauth.OAuthUtil.handleError(OAuthUtil.java:390)
at org.wso2.carbon.identity.oauth.dao.OAuthAppDAO.removeConsumerApplication(OAuthAppDAO.java:749)
at org.wso2.carbon.identity.oauth.OAuthAdminServiceImpl.removeOAuthApplicationData(OAuthAdminServiceImpl.java:947)
... 67 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The DELETE statement conflicted with the REFERENCE constraint "FK__IDN_OIDC___CODE___5006DFF2". The conflict occurred in database "openbank_apimgtdb", table "dbo.IDN_OIDC_REQ_OBJECT_REFERENCE", column 'CODE_ID'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:505)
at jdk.internal.reflect.GeneratedMethodAccessor156.invoke(Unknown Source)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:566)
at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:118)
at com.sun.proxy.$Proxy50.execute(Unknown Source)
at org.wso2.carbon.identity.oauth.dao.OAuthAppDAO.removeConsumerApplication(OAuthAppDAO.java:741)
... 68 more

Further Analysis:
"on delete cascade" is only available with the IDN_OAUTH2_ACCESS_TOKEN(TOKEN_ID) reference in IDN_OIDC_REQ_OBJECT_REFERENCE table in MSSQL script. (CONSUMER_KEY_ID) REFERENCES IDN_OAUTH_CONSUMER_APPS(ID), (CODE_ID) REFERENCES IDN_OAUTH2_AUTHORIZATION_CODE(CODE_ID) doesn't have "on delete cascade".

Also, there are some NULL values for CODE_ID and TOKEN_ID.

But in MySQL and Oracle DB scripts, "on delete cascade" is added for all the 3 references.

Steps to Reproduce

1. Setup IS-5.11.0 deployment with MSSQL 2016.
2. Create Service provider and try out consent authorisation flows with the created SP.
3. Try out Consent authorisation, Consent Deny flows to populate different kind of data.
4. Setup IS-6.1.0 by pointing to the same database.
5. Migrate IS-5.11.0 to IS-6.1.0. Follow the documentation.
6. Try to delete the migrated SP.

Version

IS-5.111.0 to IS 6.1.0 Migration

Environment Details (with versions)

MSSQL 2016