Failed to import with environment variables from time to time

[live1206]

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

[RKhalilyullov]

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).

[LKS]

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. 

[RKhalilyullov]

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 🙂

[LKS]

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

[rcarrigan]

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.

[TomislavTasic]

Steps:

1. Open environment Default solution
2. Open Environment variables
3. Find and single click on environment variable you have an issue with
4. Current value - Delete from this environment
5. Import solution

[sgtsnacks64]

Thank you! this was really helpful!

[rvroman]

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.