Duplicate entry for key 'assets_anr_code_unq' during migration

[Pyr0technicien]

Hi,

I'm trying to upgrade an old version of onarc that hasn't been updated for a long time, I struggled with all the dependencies etc to be able to launch the update script. All this seems to be solve, but now during the migration I have this error :

I really have no idea what to do with that

[K8Sudo]

Hi,

We had the same problem with our custom databases. With the update to 2.11 I think there was an addition of a Uniq identifier to the database in three tables (assets, threads, vulnerabilities). This is formed by "code" and "ANR_ID". If in the knowledge base in the table "assets" of a risk analysis the code (A-006) occurs several times, the new identifier is no longer unique and therefore runs on an error during the creation. To do this, simply adjust the codes via DB update.

The incorrect entries can be identified with the following command:
SELECT * from `assets` WHERE `anr_id` = 41 AND `code` LIKE 'A-006%';

Afterwards the entry can be adjusted with this command.
UPDATE `assets` SET `code` = '[NEW CODE NAME LIKE A-006-1]' WHERE `uuid` = '[UUID of the entry to be changed]';

I hope this helps

[Pyr0technicien]

Cheers man, I was getting to the problem as well here after finally accessing our database.
How can that happen, I'm not really an user of Monarc so how can explain that in simple user words ?

I end up having these recurring problems on other tables too.
I'm using this query to identify all the code - anr_id combination that are problematic, and I have a lot :

select code, anr_id, count(*) from threats group by code, anr_id having count(*) > 1

That means i'll have to update manually 71 entries ( for my threat table).
hopefully the is a easier way to solve this

[K8Sudo]

Within MONARC it is possible to customize the knowledge base and add entries. Up to now, it has not been necessary to ensure that the "code" is unique. This was up to now only a simple shortcut to be able to identify entries in the web interface faster. This has now changed and the migration script wants to create the unique identifier described above to ensure that there are no problems in the links.

To simplify the update process, it would of course be possible to write a script. However, since the "code" is very often a reference to standards, this should be adapted specific to the descriptive content.

Another possibility would be the roleback to the old version and to give the users the order to make these adjustments independently via web interface. Another possibility would not occur to me at present.

[ruslanbaidan]

Hello,

Thanks to @KonzeptAcht , the solution we did on our side was the same, to determine and update the code duplications on assets, threats and vulnerabilities tables.

The migration script to define the constraint to the code + anr_id was added in the v2.9.3.
Why it was added? because basically it suppose to be unique based on the code and application consistency considerations.
For example, in previous versions, import of those entities (asset, threat, vulnerability), relied on the code uniqueness, we used to search and apply the existed one or created a new one if not exists, and it could be wrong one applied if the code is not unique.
There was a validator before v.2.10.3, which prevented saving assets, thereats or vulnerabilities, if the code was already presented within the anr, but based on a param ($partial), sometimes it was not applied. That's why we decided to add the constraintsto have the code always unique within an analysis.

There is another solution, that can help you to finish the update without manually changing or deleting the duplicated codes.
You can emulate the migration is executed by inserting in your phinxlog the following data:
INSERT INTO phinxlog (version, migration_name, start_time, end_time) VALUES (20210517000000, 'AddUniqueCodeConstraint', NOW(), NOW());
But in this case your application may have some issues, however if you face them, you can update the codes at that time.
After performing the insert, you can proceed with the normal update procedure.

[Pyr0technicien]

Thanks for the precious and rapid help provided !

We will finish the current analyses by taking this change into consideration and we will migrate to a new, updated instance of Monarc that we will keep updating too. That way we won't have to change every single dupplicates.

It was our mistake not to update it before today.