Error -2147467259 Failed to enable constraints via gateway, fine in desktop

[WGordon]

I have a report which I've published to the cloud. Got the gateway set up, everything seemed to be working and managed to refresh a couple of times.

Unfortunately, I can no longer refresh the report in the Cloud due to the error:
'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'

I am perfectly able to refresh the report in the desktop from the same data sources and I have double checked the original data source and the only field on the one side of a one to many relationship is perfectly unique.

The data source is an AWS MySQL server for which a gateway is set up and working (I can refresh other models from the same source).

I do not understand why this issue occurs and why it occurs only in the cloud and not in my desktop, any help would be greatly appreciated.

WG

[v-yetao1-msft]

Hi @WGordon 

Is AWS MySQL server local ? You mentioned you published the report to cloud , how can you get the report that stored in cloud ? 

Best Regards,
Community Support Team _ Ailsa Tao

[WGordon]

Hello @v-yetao1-msft,

Thank you for getting back to me. The MySQL server is hosted on AWS, there is a gateway connection to the AWS Virtual Private Network which is able to access to the Databases on MySQL.

I'm not sure I understand your second question, maybe I was misleading when I said the cloud. I published the Power BI Report containing the data model to Power BI.com (what I meant by the cloud), from here I am now trying to refresh the data model.

It is clearly able to find the MySQL server and the tables needed to refresh the data as the error is on constraints. It seems to think one of the tables has a non unique field which should be unique. My issue is that, the field in question is very much unique and I'm able to refresh the exact same data model in Power BI desktop without any such issues. Very confusing!

Thank you very much for getting back to me
WG

[v-yetao1-msft]

Hi @WGordon 

Please correct me if I wrongly your issue .

Your data source is MySQL server and it is hosted on AWS . You created a report in Desktop and you can refresh data successfully in Desktop . Then you published the report to Service and refreshed the report in Service , but it failed , right ?

In this case , can you make sure that the gateway can connect to your data source successfully ?  As we all know , that the Desktop can refresh data without gateway , but Service is not . If it is possible , can you explain what did you do to refresh on the Service?

Best Regards,
Community Support Team _ Ailsa Tao

[WGordon]

Hi @v-yetao1-msft,

Thank you very much for your reply. Your assessment is correct, I can refresh on desktop but I'm failing to refresh in Power BI Service. I've tried scheduling a refresh as well as manually refreshing the dataset.

The error I receive is 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'

I do not believe it is an issue with the data gateway because
1. I was able to refresh the data in the service briefly before encountering this issue
2. I am using the same gateway and data sources to refresh other datasets.

But I'm open to ideas.

Kind regards,
WG

[WGordon]

@v-yetao1-msft Update on this, I have recently tried loading a single table data model which connects to MySQL data source hosted on AWS. I still recieve the following error:
An error happened while reading data from the provider: 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'

Which makes no sense to me considering there are no constraints on a single table with no defined relationships.

Kind regards,
Will