Moneymanagerex: MMEX database upgrade to version 12 failed

[petubl]

I am trying to open an existing database in 1.4.0alpha 6
I get the message Constraint failed 2067: UNIQUE constraint failed: PAYEE.PAYEENAME

I have been using 1.33 for a long time but i am a complete newbie to programming.

Can anyone advise as to how i can resolve this problem?

[slodki]

You have 2 payees with the same name. After changing one payee name to uniqe one this problem should be fixed

[petubl]

Thank you very much for this slodki! I managed to find more than one duplicate entries...

[cavoinne]

Hi, I am having the same issue. Do you any advice on how to spot the duplicate entries?
Can it be done from MMEX itself ? Or did you use to sqlite database editor?

Thanks!

[vomikan]

It should be some sqlite database editor used. #2329

OK so in case it can help others here is what I did:
1-Downloaded an sqlite database viewer/editer, this one worked: https://sqlitebrowser.org/
2-Opened my MMEX database with the editor
3-In View, selected alldata view, right click and delete
4-Saved the database
It seems it worked since this DB does not fail at version 10 anymore when I try to load it into MMEX 1.4.0 alpha7

[cavoinne]

In case it can help others, here is how I found the duplicate payee using sqlitebrowser tool (link in previous comment form vomikan):
1-Open my MMEX database with the sqlite browser
2-Go to execute SQL tab
3-Enter following SQL code:
SELECT PAYEENAME, COUNT()
FROM PAYEE_V1
GROUP BY PAYEENAME
HAVING COUNT() > 1
4-Click the play button et voila!

[vomikan]

https://github.com/moneymanagerex/database/blob/master/incremental_upgrade/database_version_12.sql#L130

https://github.com/moneymanagerex/database/blob/master/incremental_upgrade/database_version_12.sql#L530

[vomikan]

I suggest the following SQL

with u as (SELECT PAYEENAME||'_'||payeeid  as PAYEENAME, Payeeid, count() c 
FROM PAYEE_V1
GROUP BY PAYEENAME)
SELECT p.payeeid
, case when u.c>1 then u.PAYEENAME else p.PAYEENAME end as PAYEENAME
, p.CATEGID, p.SUBCATEGID
FROM PAYEE_V1 p
 left join u on p.payeeid=u.payeeid

This SQL return repeted names with suffix '_ID'

That is incorrect table with two payees with the same name: