cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
yanivcogan Frequent Visitor
Frequent Visitor

MySQL Runs All Queries From PBI in Non-Global Collation

Hello,

I've recentely moved a MySQL db that I work with in PBI over to another server. For the most part it went fine, but there was a single table where I got this weird error: DataSource.Error: MySQL: Duplicate column name '?????'    ErrorCode=-2147467259.

It's important to note that my data model utilizes Arabic and Hebrew field names, because the end users prefer it that way. What I figured was that I have one field called 'כתובת' and one field called 'טלפון' - different words in Hebrew, but both of which have the same character length.

I figured that somewhere along the line, when PBI sends the query to the MySQL server, the name of the field is boiled down to the aforementioned '?????', and when mysql tries to run "SELECT '?????', '?????' ...." that produces the error. I tried changing the fields names to be of different sizes, which indeed fixed the error.

 

Since I'm using a copy of the same PBI model that worked without errors when configured against the previous server, I was inclined to believe that the cause of this problem isn't PBI itself, but some server configuration. I checked, and found that some MySQL variables contained different values in the two servers:

propertyOld serverNew server
character set client utf8 latin1
character set connection utf8 latin1
character set database utf8 latin1
character set results utf8 latin1
character set server utf8 latin1
collation connection utf8_general_ci latin1_swedish_ci
collation database utf8_general_ci latin1_swedish_ci
collation server utf8_general_ci latin1_swedish_ci


I was pretty convinced that this was the problems, so I changed all of the properties above in the new server to match those in the old one, but it doesn't look like that helped, and I wasn't able to find a similar issue mentioned anywhere on the web.

 

Any help would be very much appreciated,

Thanks, and have a great day,

       - Yaniv

1 REPLY 1
Moderator Eric_Zhang
Moderator

Re: MySQL Runs All Queries From PBI in Non-Global Collation

@yanivcogan

What's going on when wrap the filed name with backticks( ` )? I doubt it is some system encoding issue.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 46 members 996 guests
Please welcome our newest community members: