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
hexeh
Frequent Visitor

MySQL Connection via Desktop

Hello!

I am using 8.0.0-dmr version of MySQL server and latest MySQL Connector (6.10.5) and got an error while connecting without query - 'Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.'

 

Connection to same server with any query successfull.

Connection to same server via Workbench successful.

 

 

Problem is that same error with constraints is shown in Gateway DataSources, so I can't enable auto-refresh.

Can anyone help with solutions?

1 ACCEPTED SOLUTION
hexeh
Frequent Visitor

By switching server versions (checked: 5.7.20 - ga, 8.0.0 - dmr, 8.0.3 - rc) I found, that problem appears only with developer versions (8.0 or higher).

There are no problems connecting without queries via PBI Desktop or PBI Service to latest stable version.

 

The problem of service is seems to be in gateway version (error didn't arise on gateway of old versions and appeared after upgrading on-premises gateway to latest version on December 2017).

View solution in original post

3 REPLIES 3
hexeh
Frequent Visitor

By switching server versions (checked: 5.7.20 - ga, 8.0.0 - dmr, 8.0.3 - rc) I found, that problem appears only with developer versions (8.0 or higher).

There are no problems connecting without queries via PBI Desktop or PBI Service to latest stable version.

 

The problem of service is seems to be in gateway version (error didn't arise on gateway of old versions and appeared after upgrading on-premises gateway to latest version on December 2017).

v-yuezhe-msft
Employee
Employee

@hexeh,

I have no issues when connecting to MySQL database in the latest version of Power BI Desktop(2.53.4954.621),and I use MySQL connector 6.9.8. Do you get errors when you connect to MySQL database from other applications such as Excel?

Could you please post Create statement and Insert Statement of your table here so that I can test?

Regards,
Lydia

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

Hello!

 

We have 3 databases on same server with various settings and same error on connecting via Power BI (or Microsoft Excel, I am using Excel 2016).

There is no errors in MySQL Workbench or using JDBC.

 

Example of table creation:

 

 CREATE TABLE `currencies` (
  `date` date DEFAULT NULL,
  `usd` float(7,4) DEFAULT NULL,
  `eur` float(7,4) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=729 DEFAULT CHARSET=latin1

Example of insert:

insert into currencies (date,usd,eur) values (STR_TO_DATE('2017-12-28'), 72.9299, 79.6395);

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.

Top Solution Authors
Top Kudoed Authors