MySQL Connection via Desktop

[hexeh]

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?

[hexeh · Accepted solution]

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]

@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

[hexeh]

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