Constraint violation issue in REST Authentication Connector when trying to generate OTP multiple times

[deshankoswatte]

Description

The REST Authentication Connector's init-authenticator endpoint fails with an internal server error when attempting to generate an OTP multiple times with a newly generated flowId. This occurs due to a constraint violation error at the database level.

The issue arises because, with the tokenRenewalInterval configuration, the system attempts to extract the same session at [1]. This session contains a previously generated transactionId, which is returned at [2] and then used as the flowId at [3]. This flowId is subsequently saved to the IDN_AUTH_REST_FLOW table at [4]. However, since it was already saved previously, it results in a unique constraint violation for the FLOW_ID. This needs to be fixed for the SMS OTP component as well.

[1] - https://github.com/wso2-extensions/identity-outbound-auth-email-otp/blob/v4.1.31/component/common/src/main/java/org/wso2/carbon/extension/identity/emailotp/common/EmailOtpServiceImpl.java#L437-L449
[2] - https://github.com/wso2-extensions/identity-outbound-auth-email-otp/blob/v4.1.31/component/common/src/main/java/org/wso2/carbon/extension/identity/emailotp/common/EmailOtpServiceImpl.java#L205
[3] - https://github.com/wso2-extensions/identity-oauth2-grant-rest/blob/main/components/org.wso2.carbon.identity.oauth2.grant.rest.core/src/main/java/org/wso2/carbon/identity/oauth2/grant/rest/core/RestAuthenticationServiceImpl.java#L327
[4] - https://github.com/wso2-extensions/identity-oauth2-grant-rest/blob/23d5e97be7f5c0b8d17ea431b10446a3f3fa7121/components/org.wso2.carbon.identity.oauth2.grant.rest.core/src/main/java/org/wso2/carbon/identity/oauth2/grant/rest/core/dao/FlowIdDAOImpl.java#L220

Steps to Reproduce

• Configure the REST Authentication Connector [1].
• Configure a service provider with Basic Authentication as the first step and SMS OTP & Email OTP as the second step.
• Authenticate using the BasicAuthenticator via the /api/identity/authn/v1/authenticate endpoint and extract the flowId.
• Invoke the /api/identity/authn/v1/init-authenticator endpoint concurrently using the same flowId, as shown below.
• Try the above two steps multiple times (with default resendThrottleInterval value). The first attempt will display a "Slow down" message, and the second attempt will fail.
• The following error appears in the wso2carbon.log file, and the second request fails with an internal server error.

[2025-03-18 13:14:26,527] [aa1861de-2765-4774-a233-c9c66041bfb0] ERROR {org.wso2.carbon.identity.oauth2.grant.rest.endpoint.impl.InitAuthenticatorApiServiceImpl} - Error while adding refreshed Flow Id data to the database. org.wso2.carbon.identity.oauth2.grant.rest.core.exception.AuthenticationServerException: Error while adding refreshed Flow Id data to the database.
	at org.wso2.carbon.identity.oauth2.grant.rest.core.dao.FlowIdDAOImpl.addRefreshedFlowIdData(FlowIdDAOImpl.java:243)
	at org.wso2.carbon.identity.oauth2.grant.rest.core.dao.FlowIdDAOImpl.refreshFlowId(FlowIdDAOImpl.java:148)
	at org.wso2.carbon.identity.oauth2.grant.rest.core.dao.CacheBackedFlowIdDAO.refreshFlowId(CacheBackedFlowIdDAO.java:82)
	at org.wso2.carbon.identity.oauth2.grant.rest.core.RestAuthenticationServiceImpl.executeAuthStep(RestAuthenticationServiceImpl.java:341)
	at org.wso2.carbon.identity.oauth2.grant.rest.endpoint.impl.InitAuthenticatorApiServiceImpl.initAuthenticatorPost(InitAuthenticatorApiServiceImpl.java:53)
	at org.wso2.carbon.identity.oauth2.grant.rest.endpoint.InitAuthenticatorApi.initAuthenticatorPost(InitAuthenticatorApi.java:61)
	at jdk.internal.reflect.GeneratedMethodAccessor187.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.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.doPost(AbstractHTTPServlet.java:217)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:555)
	at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:279)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153)
	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:481)
	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:86)
	at org.wso2.carbon.identity.authz.valve.AuthorizationValve.invoke(AuthorizationValve.java:120)
	at org.wso2.carbon.identity.auth.valve.AuthenticationValve.invoke(AuthenticationValve.java:111)
	at org.wso2.carbon.tomcat.ext.valves.CompositeValve.continueInvocation(CompositeValve.java:101)
	at org.wso2.carbon.tomcat.ext.valves.TomcatValveContainer.invokeValves(TomcatValveContainer.java:49)
	at org.wso2.carbon.tomcat.ext.valves.CompositeValve.invoke(CompositeValve.java:62)
	at org.wso2.carbon.tomcat.ext.valves.CarbonStuckThreadDetectionValve.invoke(CarbonStuckThreadDetectionValve.java:145)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:670)
	at org.wso2.carbon.tomcat.ext.valves.CarbonContextCreatorValve.invoke(CarbonContextCreatorValve.java:59)
	at org.wso2.carbon.tomcat.ext.valves.RequestEncodingValve.invoke(RequestEncodingValve.java:49)
	at org.wso2.carbon.tomcat.ext.valves.RequestCorrelationIdValve.invoke(RequestCorrelationIdValve.java:124)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:928)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1794)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:829)
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '305c6cb0-be89-4f5d-b565-342089a6c5fd' for key 'idn_auth_rest_flow.FLOW_ID_CONSTRAINT'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:115)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:95)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:960)
	at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:388)
	at jdk.internal.reflect.GeneratedMethodAccessor171.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.$Proxy55.execute(Unknown Source)
	at org.wso2.carbon.identity.oauth2.grant.rest.core.dao.FlowIdDAOImpl.addRefreshedFlowIdData(FlowIdDAOImpl.java:229)
	... 57 more

[1] - https://store.wso2.com/connector/identity-oauth2-grant-rest

Version

wso2is-km-5.10.0

Environment Details (with versions)

• OS: Mac OS
• Database: MySQL
• Userstore: JDBC