Duplicate Key Error at Login

[SBarnes]

We have a client with a Jiwa 7.2.0 database that is producing a duplicate key error at login after two plugins were updated.

I have tried with SQL to disable all the plugins in the database but it still won't let us log back in and none of the service will start either.

Other databases from the same server will log in.

Any ideas?

[Mike.Sheen]

We have a client with a Jiwa 7.2.0 database that is producing a duplicate key error at login after two plugins were updated.

I have tried with SQL to disable all the plugins in the database but it still won't let us log back in and none of the service will start either.

Other databases from the same server will log in.

Any ideas?

The full error message might help to isolate the problem - if it offers any more information.

You could also profile trace to see if you can identify the last SQL read which is performed before the error - that might point to where the duplicate is. My guess would be system setting or something like that.

[SBarnes]

Unfortunately the message box says Add failed. Duplicate key value supplied and unfortunately we are talking Azure SQL so I can't profile trace the SQL without exporting the database to a regular server.

[Mike.Sheen]

Unfortunately the message box says Add failed. Duplicate key value supplied and unfortunately we are talking Azure SQL so I can't profile trace the SQL without exporting the database to a regular server.

Yes you can. Just install Azure Data Studio and add the Profiler plugin extension - you can then trace sql queries on Azure SQL databases.

[SBarnes]

Just discovered that https://www.sqlshack.com/sql-server-pro ... ta-studio/

[SBarnes]

The following SQL is where it stops, so I am assuming it's when it tries to put the values in a Jiwa collection is where the problem is?

Is there a quick way to find the duplicate?

Code: Select all

exec sp_executesql N'SELECT Section, IDKey, Contents, CellType FROM SY_SysValues ORDER BY Section, IDKey',N'@UserID char(20)',@UserID='ZZZZZZZZZZ0000000000'


[Mike.Sheen]

Is there a quick way to find the duplicate?

Code: Select all

SELECT IDKey, Section
FROM SY_SysValues
GROUP BY IDKey,Section
HAVING COUNT(IDKey) > 1

Not sure why you're seeing a UserID as a parameter there. Does not look like something we would do...

[Mike.Sheen]

Not sure why you're seeing a UserID as a parameter there. Does not look like something we would do...

I checked and we do provide the UserID param even though the query doesn't use it.

Not anymore!

[SBarnes]

We are back in, we just deleted anything in the REST API section, we will now delete the REST API and re import it.

I would have through there would be indexes on the database table for those combinations (IDKey and Section)?

Again thanks for the assist.

[Mike.Sheen]

I would have through there would be indexes on the database table for those combinations (IDKey and Section)?

Again thanks for the assist.

Logged as DEV-9175. We already had a unique index on IDKey, Section AND DisplayOrder - but evidently that wasn't enough to stop you

This is the new index:

Code: Select all

IF NOT EXISTS(SELECT TOP 1 * FROM sys.sysindexes WHERE Name = 'IX_SY_SysValues_IDKey_Section')
   CREATE UNIQUE INDEX IX_SY_SysValues_IDKey_Section ON SY_SysValues(IDKey, Section)