Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Problem with mysql connection and varchar containing latin-1 supplement characters

Hello,

I'm having trouble with Qlik Sense Desktop (non-paid) & MySQL.

My db character set is utf-8 and the server's version is 5.1.73.

I connected to my db successfully using MySQL Enterprise Edition connector, but in some tables I have varchar columns containing accents (éàùè etc.) that are related to the Latin-1 supplement block from the Unicode standard. In this case, the value as well as the others fields of the current line are deleted (I mean replaced with null I guess). See the image below for comparison between SQL query result & data shown in Qlik Sense. When an accent is at the end of the string, it's only deleted without erasing the next fields.

I tried to replace those characters with various characters from other Unicode blocks, and the result was Qlik just replacing the unknow character with '?' (a normal behavior I'd say).

Haven't tried with others db (that are not mysql) but no problem with flat files like excel, csv exported from the same db.

Also never got any problems of this kind with other connections (like SAP or Talend tools), that's why i'd like to know if some of you have already managed this problem or could have an explanation ?

Don't hesitate to ask me for more informations, it may be unclear (first post btw).

Clément

test_mysql_qlik_accents.png

4 Replies
petter
Partner - Champion III
Partner - Champion III

A problem like this is almost always a matter of getting the parameters of the driver set up correctly. For MySQL it would be the ODBC-driver setup.

I suppose the characters come out correctly with a regular SQL query tool?

Anonymous
Not applicable
Author

Thank you for the answer Petter

Absolutely, the first part of the image on my post is from MySQL Workbench. Also works perfectly with datagrip and phpmyadmin.

petter
Partner - Champion III
Partner - Champion III

You can actually send SQL statements that are not SELECT statements from a Qlik Sense load script.

So what I would suggest that you do is to try to run this and see what your database and connection use for character sets:

LIB CONNECT TO 'MySQL_Enterprise_Edition_localhost';

SQL

   SHOW VARIABLES LIKE 'character_set%';

You will get two fields and as many rows as you the MySQL has variables that starts with character_set. Have a look at them and see if you find some inconsistencies.

2018-10-23 21_42_39-Window.png

Anonymous
Not applicable
Author

I've got the exact same result as you with this query.

However I installed MySQL 5.7.24 and it handles everything correctly. Maybe the problem is more about Qlik Sense not fully supporting MySQL 5.1 connection (I don't know more to be honest).

Thank's for your help Petter !