Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Roberto456
Resolver I
Resolver I

MYSQL Cant connect to DB

Hello everyone.

 

im trying to fix this error in power BI when connecting to the database.

 

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

 

Not sure why this is happening.

 

I have the latest drivers and everything as well.

1 ACCEPTED SOLUTION

Thank you all for your help.

 

It turns out using the ODBC connection type works, but the MYSQL Connection does not.

 

I have asked microsoft directly to see why this is the case.

View solution in original post

3 REPLIES 3
v-juanli-msft
Community Support
Community Support

Hi @Roberto456 

When connecting to MySql in Power bi, it tries to apply certain constraints based on what you've defined in the type.

So for instance if your result declares a primary key on the table then the DataTable will attempt to apply that as a constraint.

If in fact there are duplicate entries in the key column then the DataTable would throw the exception you mention.

 

This error indicates that some columns in you MySql database don't follow the constrains set when creating tables.

Please check whether your column follows the constrains like non-null, unique, or foreign-key constraints.

 

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Thank you all for your help.

 

It turns out using the ODBC connection type works, but the MYSQL Connection does not.

 

I have asked microsoft directly to see why this is the case.

3/18/22

Just wanted to mention that I also has zero luck getting to our hosted Azure vm that has our schema for MySQL v. 8.x on it.  We do require a secure shell (tcp/ip over SSH) connection for security, which presents some challenge with the paired keys.  Straight SSH with the private key, all Putty connection session works fine.  I had to set up port forwarding which was not too bad.

 

But the native Power BI 'Get Data' connector for MySQL did not work for my connection.  The only that was successful to query data via Power BI was over ODBC by creating a DSN first.  The PBI Gateway service choked a little as well when I test the connection but it is actually working.  

 

Can Microsoft Power BI developers enhance the abstraction layer (the UI when connecting) for MySQL?  Maybe there is conflict of interest at Oracle around these types of use cases and connections?

 

Thanks,

-Sean

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.