WSO2 API Manager authentication Violation of UNIQUE KEY constraint error

Question

We have implemented WSO2 API Manager (v1.10.0) in a distributed architecture as outlined in the online documentation here.

This consists of the following (on 5 separate servers):

Gateway (x2)
Publisher & Store (on a single server)
Key Manager (x2)

These are wired-up to the 3 normal API Manager databases (Registry, User Manager & API Manager), which are on a SQL Server 2014 instance.

We are using the Key Managers for the authentication (login, forgotten password, etc.) of the website users, as well as for authenticating API calls.

However, when trying to log in to the site I'm seeing the following (Violation of UNIQUE KEY constraint) error on the Key Manager:

TID: [-1] [] [2016-10-06 00:36:47,842] ERROR {org.wso2.carbon.identity.oauth2.dao.TokenPersistenceTask} - Error occurred while persisting access token :c5a0a11e63388dCHANGEDea34b0533445 {org.wso2.carbon.identity.oauth2.dao.TokenPersistenceTask} org.wso2.carbon.identity.oauth2.IdentityOAuth2Exception: Error when storing the access token for consumer key : fpA6AhOfbVCHANGEDgH0WzBDOga at org.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.storeAccessToken(TokenMgtDAO.java:246) at org.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.persistAccessToken(TokenMgtDAO.java:284) at org.wso2.carbon.identity.oauth2.dao.TokenPersistenceTask.run(TokenPersistenceTask.java:52) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of UNIQUE KEY constraint 'CON_APP_KEY'. Cannot insert duplicate key in object 'dbo.IDN_OAUTH2_ACCESS_TOKEN'. The duplicate key value is (15, [email protected], -1234, , APPLICATION_USER, 369db21a386ae4CHANGED0ff34d35708d, ACTIVE, NONE). at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216) at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180) at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155) at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:332) at org.wso2.carbon.identity.oauth2.dao.TokenMgtDAO.storeAccessToken(TokenMgtDAO.java:224) ... 5 more

This is resulting in the following .NET error on the website:

I've tried Googling this, but cannot find an up-to-date answer.

I have not configured the Key Managers to have master and worked nodes (as outlined here) as the documentation seems to suggest that this isn't needed.

Any help would be much appreciated please!

andunslg andunslg · Accepted Answer · 2016-10-09T17:40:53

After some debugging found the issue! Before we put this config,

<JDBCPersistenceManager>
    <SessionDataPersist>
        <PoolSize>0</PoolSize>
    </SessionDataPersist>
</JDBCPersistenceManager>

APIM can save more than one ACTIVE OAUTH token to the IDN_OAUTH2_ACCESS_TOKEN table for single token obtaining call.

When the token validation endpoint queries the tokens, only the last one is returned (Time based sorting and Limits are used). When that one is expired token validation mark it as inactive. But the previous one is kept as it is.

When the refresh token happens, it check whether the latest token is inactive. Since it is inactive, it issues a new token. But when the token endpoints tries to persist the token, there is another ACTIVE token. That caused this exception.

To sort this out we can run a update query on IDN_OAUTH2_ACCESS_TOKEN table to mark all the ACTIVE tokens to INACTIVE.

update IDN_OAUTH2_ACCESS_TOKEN set TOKEN_STATE="INACTIVE" where TOKEN_STATE="ACTIVE"

Then the old faulty tokens will be removed and server will start working fine!

WSO2 API Manager authentication Violation of UNIQUE KEY constraint error

3 Answers