The CREATE UNIQUE INDEX statement terminated because a duplicate key was found

[mtalhabalci]

Hello,

I'm trying to upgrade LocalizationProvider package from 5.8.2 to 6.5.2.

I've successfully upgraded and compiled the project but when i start the project, I get dublicate key exception from database.

Couldn't find a solution from old issues or the blog.

Sharing the exception below,

When I check the resourceId from database, I see that there are multiple rows for different languages for a resource. So deleting data will not be a solution.

Looking forward for an answer, thanks in help.

[mtalhabalci]

Hello again,

We've a kind of identified the problem.

I'll be sharing the analyze results and again wait for your answer.

Before we upgrade packages and start the project, we execute the following sql query and get the result below;

This result shows us that there are not dublicate rows when we group the table with Language and ResourceId

After upgrade of packages, same sql query gives us the following result;

And this result shows us that now there are dublicated rows and sql throws exception.

So we guess that in the initialization phase, the package inserts some rows to database and dublicate some data. Are we missing a configuration, how should we proceed?

Thanks in help

[valdisiljuconoks]

Hi,

Strange indeed. Special configuration should not be needed. Can you paste please all rows from translations table for one of the resource with duplicates? I'm interested in which language get duplicate.

Also another diagnostics could be to run sql profiler and trace down what SQL statements init module is executing against SQL server.

[mtalhabalci]

Hello, Firstly I'm sorry for late reply. We some kind of found out the reason behind dublicating rows while debugging the library.

There is an update code which updates the rows with value null to empty string. I will try to share pictures to make it clear.

As you can see here: https://user-images.githubusercontent.com/47703652/189843495-2fe52e7c-d94e-4dce-b4d7-fe5f6a2d92dd.png the row top and the row bottom has empty string values but initially one of it's value was null.

Initializing script null value is being updated to empty string, and we already have empty string initially, so after script execution we have 2 empty strings so we get exception. This is the problem we discovered. Thanks in help.

[valdisiljuconoks]

related https://github.com/valdisiljuconoks/localization-provider-epi/issues/156

[valdisiljuconoks]

Conclusion is: due to an error in older versions (before the index) there was a possibility to get duplicate rows in the database for the same language for the same resource. Unfortunately - library can't make informative decision about which of the resource should be deleted before proceeding with index creation. Therefore - recommendation is to use approach described in issue linked here (https://github.com/valdisiljuconoks/localization-provider-epi/issues/156) to execute script to remove duplicate resource from the database.

Sorry for inconvenience!