Error Upgrade from version 2.37 to 2.38

Support - Assistance technique
1

Hi Community,
I get an error when I wanted to upgrade an instance from 2.37 to 2.38:

ERROR: could not create unique index “in_userinfo_openid”
Detail: Key (openid)=( ) is duplicated.

Note: I was able to upgrade my copy of the demo database with no issues.
If anyone has similar issue let me know how it was solved.

Attached is the full log file
log2.38upgradeError.docx (22.5 KB)

1 Like
2

Hi Jean
I asked for support from the developers as well as server administrators. All ‘caused by’ errors refer to the table “Migration V2_38_36__Drop_users_table.sql” any clue?

Thanks!

3

Hi,

here is a SQL query you could use to find the duplicates:

select * from users ou
where (select count(*) from users inr
where inr.openid = ou.openid) > 1

Once the duplicate values are identified, you would need to change or remove them before you can restart the upgrade.

Kind regards
Morten

2 Likes
4

Thanks after updating the duplicate openid i managed to upgrade to 2.38 successfully.

5

we upgrade the DHIS2 system from 2.32.7 . at first step - to version 2.33.9 I’ve got the error below. I’ve verifies there is o SQL views, or any customization done. Any ideas what can I do?
SQL State : 23505
Error Code : 0
Message : ERROR: could not create unique index “seqnumcount_pkey”
Detail: Key (owneruid, key)=(ZkgX9d3pBhN, ZS_LUAL-20201040-SEQUENTIAL(#####)) is duplicated.
Location : org/hisp/dhis/db/migration/2.33/V2_33_27__Add_sequentialcounter_procedure.sql (/file:/home/dhis/tomcat-dhis/webapps/ROOT/WEB-INF/lib/dhis-support-db-migration-2.33.9.jar!/org/hisp/dhis/db/migration/2.33/V2_33_27__Add_sequentialcounter_procedure.sql)
Line : 3
Statement : – create composite primary key
alter table sequentialnumbercounter add constraint seqnumcount_pkey primary key (owneruid, key)

6

Welcome to the community @TatianaLar! :tada:

Your topic post has been moved to this post because it’s a similar issue. Please try the solution suggested by @netroms.

Thank you!

7

thank you. I tried this method but unfortunately it did not bring back any duplicates. Any other ideas ?

1 Like
8

Hi @TatianaLar ,

I’m uncertain how you have ended up in this state. I’ll have a look at our upgrade scripts to make sure they are correct. However, let me offer you a solution to the issue at hand:

  1. Look up your duplicate records:

SELECT * FROM sequentialnumbercounter WHERE owneruid=‘ZkgX9d3pBhN’ AND key=‘ZS_LUAL-20201040-SEQUENTIAL(#####)’

  1. This should return more than 1 row - Which is the problem. Have a look at the “counter” column. You only want to keep the record with the highest “counter” value. If multiple rows have the same value, pick any one of them to keep.
  2. Delete the rows that you have a lower “count” value (Or that have the same count value as the one you chose)

DELETE FROM sequentialnumbercounter WHERE id=_id_to_delete

  1. Re-run the upgrade.

If the problem persists, that means other records have been duplicated as well. In that case, repeat the steps above, exchanging the owneruid and key criteria to match the new duplicate record.

As to the reason you might have ended up in this situation, it might be related to the fix we made in this upgrade script. Previously there was a small chance to end up with so-called race-conditions when generating new unique values for attributes. If for some reason two people request new values at the same time, and neither finds the record already exists, it would insert a duplicate.

1 Like