Hi, This issue occurred since I was using the same TOKEN_STATE_ID and TOKEN_STATE in the batch update operation of access tokens which creates duplicate entries violating the unique constraint on CON_APP_KEY index.
This was resolved with the following approach; UPDATE IDN_OAUTH2_ACCESS_TOKEN SET TOKEN_STATE= "REVOKED", TOKEN_STATE_ID= "78c4e5cc-382a-4af0-8bb1-bef58a7c824a" WHERE TOKEN_STATE="ACTIVE" AND CONSUMER_KEY_ID = (SELECT ID FROM IDN_OAUTH_CONSUMER_APPS WHERE CONSUMER_KEY = "OazCSjIjOw2wHp9uhf7x2wJbfxga" ) AND TENANT_ID != -1234 Through this way I'm only revoking the tokens in active state(only a single entry is updated to 'revoked' state) which avoids setting duplicate entries of access tokens in revoked state with the same state ID. Best regards, Sathya On Tue, May 23, 2017 at 1:24 PM, Danushka Fernando <[email protected]> wrote: > Hi Sathya > > Please find my comments inline. > > On Tue, May 23, 2017 at 12:29 PM, Sathya Bandara <[email protected]> wrote: > >> Hi all, >> >> It is required to alter the state of access tokens from 'active' to >> 'revoked' of multiple entries in the IDN_OAUTH2_ACCESS_TOKEN table for the >> scenario where access tokens issued to other tenants by a saas application, >> need to be revoked when saas is disabled. I used the following query to >> achieve this; >> >> "UPDATE IDN_OAUTH2_ACCESS_TOKEN SET TOKEN_STATE=?, TOKEN_STATE_ID=? WHERE >> CONSUMER_KEY_ID = (SELECT ID FROM IDN_OAUTH_CONSUMER_APPS WHERE >> CONSUMER_KEY = ? ) AND TENANT_ID != ? " >> >> Have you tested this query directly in some sql console? So is it giving > the same error when you do that? > >> >> - Parameter 1(Access token state): REVOKED >> - Parameter 2(Token state id): if access token is in active state the >> state id should be 'NONE' if in revoked state it should be updated with a >> unique string >> - Parameter 3(consumer key): client ID of oauth application >> - Parameter 4(tenant id): application tenant ID >> >> >> This gives >> com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: >> Duplicate entry '1-admin-1-PRIMARY-APPLICATION >> _USER-369db21a386ae433e65c0ff34d357' for key 'CON_APP_KEY' exception >> which occurs because of the unique constraint violation on CON_APP_KEY >> index; >> > Here it says duplicate entry. So did you check whether your database > contains any values similar to what you are trying to update? > >> >> >> >> >> >> >> >> >> >> >> >> >> >> *Index: CON_APP_KEYDefinition:Type BTREEUnique YesColumns >> CONSUMER_KEY_ID AUTHZ_USER TENANT_ID USER_DOMAIN USER_TYPE >> TOKEN_SCOPE_HASH TOKEN_STATE TOKEN_STATE_ID* >> >> Is it possible to perform multiple entry update operations without having >> to update a single entry at a time in Access token table? Appreciate your >> help on this. >> >> Best regards, >> Sathya >> >> -- >> Sathya Bandara >> Software Engineer >> WSO2 Inc. http://wso2.com >> Mobile: (+94) 715 360 421 <+94%2071%20411%205032> >> >> <+94%2071%20411%205032> >> >> _______________________________________________ >> Dev mailing list >> [email protected] >> http://wso2.org/cgi-bin/mailman/listinfo/dev >> >> Thanks & Regards > Danushka Fernando > Associate Tech Lead > WSO2 inc. http://wso2.com/ > Mobile : +94716332729 <+94%2071%20633%202729> > > -- Sathya Bandara Software Engineer WSO2 Inc. http://wso2.com Mobile: (+94) 715 360 421 <+94%2071%20411%205032> <+94%2071%20411%205032>
_______________________________________________ Dev mailing list [email protected] http://wso2.org/cgi-bin/mailman/listinfo/dev
