Failed to import with environment variables from time to time

(3) Share

Report

Posted on by live1206 Microsoft Employee

We created environment variables in our solution, and export and import the solution.

It used to work fine with ALM tasks, and apply different values for env vars using `DeploymentSettingsFile`.

But from time to time, we got error as below:

Cannot insert duplicate key exception when executing non-query: System.Data.SqlClient.SqlCommand Exception: System.Data.SqlClient.SqlException (0x80131904): Violation of UNIQUE KEY constraint 'ndx_envvarval_schemaname'.
Cannot insert duplicate key in object 'dbo.EnvironmentVariableValueBase'.

The duplicate key value is (xxxx, 0, Jan 1 1900 12:00AM).

The fact is, we do not have any duplicated env vars in our solution and they were created a couple of months ago. All of a sudden, we got the above error during importing.

I figured out a workaround as delete the complaining env vars in the development environment, then re-create them with the same name and values, everything works again.

It happened once in July and came up again recently.

Really appreciate if anyone could help out with the root cause. I can provide the ALM pipeline task error details if needed.

Thanks,

Wei

Categories:

Building Power Apps

I have the same question (0)

All responses (23)

Answers (0)

Rafael Khalilyullov 23 on at

Like (1)

Report

Hi @live1206 ,
Have you overcome this issue somehow?
We have the same issue.
We have a pipeline that rebuilds our solution each time we run the pipeline and bump the version (regardless of whether we have changed anything in this solution or not).
And it is deployed correctly only the first time. And then it fails each attempt until we delete all environment variable values. Then it deployed successfully once, and for the next deployment, we have to delete all values again.
The versions Power Platform CLI tool we use tried are 1.17.5 and the latest one (1.21.4).

1 people found this reply helpful.

Was this reply helpful? Yes No
lukas.schauffele 186 on at

Like (1)

Report

I've raised this with Microsoft Support (Ticket 2211160030000704) in December 2022. They are aware that this is an issue and have indicated a fix in April or October 2023. They made the following comment:

“Environment variables have problem (that will be addressed by the previous bug mentioned) when there is a mix of managed/unmanaged (active).
If the customer keeps their environment variables managed only, they won't have problems.
They can use remove active customizations to the ones that are already in mixed mode or delete the ones that are only unmanaged (active).”
Not sure if that is actually correct. We do not have an unmanaged layer in our downstream environments and we still get the error.

My workaround is to delete the current value of the environment variable in the downstream environment prior to import.

Was this reply helpful? Yes No
Rafael Khalilyullov 23 on at

Like (3)

Report

Hi guys,

If I'm not mistaken I've solved the issue we had.
And, if I'm not mistaken, the root cause was the following,
We had values for environment variables specified in the deploymentSettings.json file and also, we realized that by mistake the environment variable values haven't been removed from the solution.
So, after removing environment variable values from the solution and running pipelines once again, everything works well.
Most probably it is a different case than you have but who knows 🙂

Was this reply helpful? Yes No
lukas.schauffele 186 on at

Like (2)

Report

If anyone is using the ALM Accelerator, please see my GitHub comment on how to automatically delete environment variables before deployment.

Was this reply helpful? Yes No
rcarrigan 67 on at

Like (1)

Report

I'm running into this issue for the first time right now. @RKhalilyullov , thank you for pointing out that it's caused by the environment variables being pre-populated within the exported solution, which is true in my case as well. However, does anyone know why we have to delete them before exporting? If it's so important, why doesn't it happen automatically when exporting a solution, or at least provide us with a toggle in the UI and a flag in the CLI to delete them?

On a separate note, @LKS , does your PowerShell solution delete the environment variables from the target environment or the source environment? Apologies, but PowerShell isn't my strength so I couldn't tell from reading the code.

Was this reply helpful? Yes No
TomislavTasic 8 on at

Like (6)

Report
Steps:
Open environment Default solution
Open Environment variables
Find and single click on environment variable you have an issue with
Current value - Delete from this environment
Import solution

Was this reply helpful? Yes No
sgtsnacks64 322 Super User 2024 Season 1 on at

Like (1)

Report

Thank you! this was really helpful!

Was this reply helpful? Yes No
rvroman 29 on at

Like (1)

Report

Ugh - I'm having this issue now too. I am not using Pipelines or any "accelerator" - but did edit some of the Environmental Variables in the Default solution in the downstream environment previously. My concern with the proposed solution of deleting the current Environmental Variable from Default in the downstream environment is that, in our case, they are used in more than one solution (perhaps that was a bad idea... was trying to save work.) - so doing so could disrupt use of the other solution while I'm importing the other.

Was this reply helpful? Yes No
GerryG 2 on at

Like (1)

Report

Just had the same experience. Anyone had any joy finding a root cause to this. Deleting the Environment Variables and recreating them seems a bit drastic

Was this reply helpful? Yes No
helal 36 on at

Like (1)

Report

Getting the same error deploying a solution through pipeline.

One of the fix proposed is to delete the environment variable in the destination solution, then redeploy. We cannot do that because the destination solution is a managed solution.

Anyone has another solution?

Was this reply helpful? Yes No