Viewing Unicode characters

28 posts

Danya posted 10 years ago in General

Hi all!

Does anyone know how to set the character encoding for viewing the database in HeidiSQL?
The chars work fine on the website and are stored in a utf8mb4 database via the admin, which works great.
But the moments I do use the editor, I would like to see what's in there, instead of a square or, in this case a 4 byte character, a question mark.

Thanks!

959 posts

kalvaro posted 10 years ago

I'd say you just need to set an appropriate fond at Tools-> Preferences-> Data appearance. I use "Arial Unicode MS" in my PC.

28 posts

Danya posted 10 years ago

Thanks for your help, Kavaro, but that would not change anything to special characters being displayed or not. That's *how* they are displayed, not if.
Anyone else, maybe? :)

560 posts

jfalch posted 10 years ago

care to explain how a program will display an extended utf-8 character if the font selected for display does not contain a glyph for that character ? what do you think display of square / '?' means ?

28 posts

Danya posted 10 years ago

Well, I'm using Tahoma (a standard true type font) for displaying them on my website and also in HeidiSQL, and the website. That does the job perfect.
The problem is that HeidiSQL probably can't handle 4 byte characters? 2 byte chars, as you see in the example image are displayed well.
Oh well, not really a problem though, since I use a CMS I build myself which displays them perfectly.
Was just wondering if there is just some way to display them correctly in HeidiSQL.

10361 posts

ansgar posted 10 years ago

HeidiSQL does all client-server communication in UTF-8, after calling mysql_set_character_set(FHandle, 'utf8').

Is there a slight chance that utf8mb4 contains characters which are not present in utf8?

28 posts

Danya posted 10 years ago

That's correct, ansgar, you know what I'm talking about! :D

UTF8mb4 contains 4 byte characters, which UTF8 doesn't support. That's why the other UTF8 characters show as they should, but these ones don't.
I use mysql_query('SET NAMES utf8mb4') to get them properly from the database. And as you said, if HeidiSQL calls it with mysql_set_character_set(FHandle, 'utf8'), they will not appear.
Is that something I can change or is that deeply embedded in HeidiSQL software code?

10361 posts

ansgar posted 10 years ago

That utf8 is hardcoded in HeidiSQL. No way to change that from the users point. A workaround could be to create a startup script with "SET NAMES utf8mb4" in it, and apply it to the sessions advanced options. (See tab "Advanced" in the session manager). But I'm afraid that's only one part of the story. Values written to and read from the server are still expected to be encoded in utf8, and so various hardcoded places in HeidiSQL will apply the Delphi method "Utf8ToString" to strings. That's something I cannot give you some workaround for. HeidiSQL should use utf16 or even utf32 in the future, not utf8, I guess.

28 posts

Danya posted 10 years ago

Thanks, ansgar! Your answer is very appreciated! happy

Well, I hope with a next version or so it's going to be working.
Like said, I'm using a CMS, so it's not really a problem. :)

10361 posts

ansgar posted 10 years ago

I'm curious. Could you provide me an .sql dump of your utf8mb4 table with some values in it? So I could experiment with utf16-based communication in HeidiSQL.

28 posts

Danya posted 10 years ago

I could, but the output file is utf-8, so I guess it's not helpful.

But, try this:

CREATE TABLE IF NOT EXISTS `testing_table` (
`index_id` int(11) NOT NULL DEFAULT '0',
`charlist_id` int(11) NOT NULL,
`cOriginal` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
`cReplace` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC;

And insert some of the characters in the cReplace column from the following URL: http://www.fileformat.info/info/unicode/block/mathematical_alphanumeric_symbols/list.htm

And in this case pick some of the chars from MATHEMATICAL FRAKTUR or MATHEMATICAL SCRIPT.

10361 posts

ansgar posted 10 years ago

I just inserted the "A" character via HeidiSQL's query tab:

insert into utable (cReplace) values (_ucs2'\xD835\xDC00')

But I get chinese chars in the data grid, no matter if I ran "SET NAMES utf8" or "SET NAMES utf8mb4" before.

28 posts

Danya posted 10 years ago

Yeah, don't know about that, when it comes to inserting them directly via the Query tab.
I'm using it via my CMS with a normal query. That's how I got them into the database. The output is fine too.
It's just only the viewing from within HeidiSQL, the character itself is inserted properly in the database.

10361 posts

ansgar posted 10 years ago

You could post the result of "SELECT HEX(cReplace) from "yourtable" LIMIT 10" here. That should preserve the character integrity.

28 posts

Danya posted 10 years ago

HEX(cReplace)
E28284
E28283
E28282
E28281
E28280
E292B5
E292B4
E292B3
E292B2
E292B1

28 posts

Danya posted 10 years ago

Sorry, these are the MATHEMATICAL FRAKTUR characters:
HEX(cReplace)
F09D9484
F09D9485
F09D9487
F09D9488
F09D9489
F09D948A
F09D948D
F09D948E
F09D948F
F09D9490

10361 posts

ansgar posted 10 years ago

Got it working here. Now that I read about utf8 issues here, I would like to switch from utf8 to utf8mb4 in HeidiSQL. However, that requires an update of the libmysql.dll to a newer one which knows the utf8mb4 charset. Seems that utf8mb4 was introduced not so long ago as a fix to the normal utf8 charset?

See my variables and values:

2 attachment(s):

28 posts

Danya posted 10 years ago

Good to see you got the characters working!
Hopefully the HeidiSQL team will introduce their system to a utf8mb4 character encoding soon! :D

10361 posts

ansgar posted 10 years ago

I'm on the way. I did not know about that bug in MySQL's utf8 charset up to yesterday. So, give me some hours for a downwards-compatible fix.

10361 posts

ansgar posted 10 years ago

The bad thing is that the libmysql.dll shipped with the HeidiSQL installer does not yet contain that utf8mb4 charset. In order not to break older fixes again I will need a newer MariaDB client library and test other things with that as well. See r4532

10361 posts

ansgar posted 10 years ago

Eh, I meant http://code.google.com/p/heidisql/source/detail?r=4532

28 posts

Danya posted 10 years ago

Sounds like you're in the develop team, ansgar?
Anyway, great job of trying to fix it!

10361 posts

ansgar posted 10 years ago

I am the develop team smile

Yes, HeidiSQL coding is a one-man-show. There were other developers for short times in the past, but I'd say 99.5% of the code is from me. And of course there are other contributors, for example the myriad of translators, then the Transifex manager, the Facebook writer etc.

28 posts

Danya posted 10 years ago

Ok, great. Well, you're doing an amazing job, mate!
I used MySQL Front before HeidiSQL, and wasn't satisfied with the utf8 support, it had almost none. Copying tables meant loss of all utf8 characters, even though everything was set to utf8_unicode_ci. That was why I switched to HeidiSQL (and because MySQL Front wasn't updated anymore).
Using HeidiSQL now for 6 months or so and very satisfied, which I expressed with a little donation, since you're doing a great job!
I run about 30 MySQL driven websites, so I'm using HeidiSQL a lot. It's stable, the design is nice, the functions work superb and the utf8 support is great. :D

10361 posts

ansgar posted 10 years ago

Done in r4822. HeidiSQL now tries to use utf8mb4 by default, and if that charset does not exist in libmysql, it uses utf8 as fallback. At the same time I committed a newer libmysql from https://downloads.mariadb.org/client-native/2.0.0/ so the installer contains the utf8mb4 charset. If you just update your HeidiSQL build it will spit out a warning in the sql log about the non existent utf8mb4 charset.

28 posts

Danya posted 10 years ago

Nice! I updated HeidiSQL to r8422, but still question marks. Do I need to install the mariadb-native-client-2.0.0-win32.msi from MariaDB.org too to get it working?

146 posts

lemon_juice posted 10 years ago

When connecting I see the following in the log:

/* Attempt to create plink.exe process, waiting 4s for response ... */
/* Connected. Thread-ID: 310502 */
/* Can't initialize character set utf8mb4 (path: C:\Program Files (x86)\MySql\share\charsets\) */
/* Characterset: utf8 */

This doesn't look right because there's no folder "C:\Program Files (x86)" on my system, is it hard coded?

560 posts

jfalch posted 10 years ago

probably should use the value of server var @character_sets_dir

10361 posts

ansgar posted 10 years ago

The message with that faulty path comes from libmysql.dll and you can ignore the fact that it does not use a valid directory.

You should use the nightly build installer in order to get a newer libmysql which has that utf8mb4 charset. When you have the libmysql from the last official 8.3 installer you will always get that error message. Well, HeidiSQL still uses utf8 in that case, so it's not a real problem.

10361 posts

ansgar posted 10 years ago

Danya: No, you don't need to install anything from MariaDB. Just use the nightly installer: http://www.heidisql.com/installers/HeidiSQL_8.3.0.4822_Setup.exe

146 posts

lemon_juice posted 10 years ago

Yes, the nightly installer did it - thanks! Nice to have this feature.

28 posts

Danya posted 10 years ago

Hm, the question marks are now two squares, which is a tad better, but still not like in your sqreenshot.
Might now be one of my own settings.
They display correctly in the webbrowser, so my system supports it. The database standard encoding is utf8 though, the table too. Just the column has utf8mb4, since I just started experimenting with it. But I'm not eager to change the complete database/table encoding (yet) on this domain.
It might work on a clean database, with everything set to mb4.
I'll update you on that tomorrow!

10361 posts

ansgar posted 10 years ago

And you installed the nightly build installer r4822? If yes, what do you get in the very first lines of your SQL log at the bottom. I have an old .dll here and I get:

/* Can't initialize character set utf8mb4 (path: compiled_in) */
/* Characterset: utf8 */

10361 posts

ansgar posted 10 years ago

A problem might be that not only HeidiSQL + libmysql.dll needs to support that utf8mb4 charset, but also the server needs to have support for it. On an MySQL 5.1.66 I get that above error message, but on a MySQL 5.6.19 not so.

28 posts

Danya posted 10 years ago

The first lines are:
/* Delimiter changed to ; */
/* Connecting to *******.com via MySQL (TCP/IP), username *******, using password: Yes ... */
/* Connected. Thread-ID: 8794864 */
/* Characterset: utf8mb4 */

I'm using MySQL 5.5.37, and it supports the charset since everything is working fine on the frontend.

I did not had the time to test on a fresh domain though and will try ASAP.

By the way, since I updated to r4822 connecting to the server is taking about 10-15 seconds instead of 1 ish second.

28 posts

Danya posted 10 years ago

Ok, so, I created a fresh database on a clean domain, with default collation set to utf8mb4 and created a table with one column, varchar utf8mb4. Still 2 squares.

ALTER DATABASE `in4_db` COLLATE 'utf8mb4_unicode_ci';
CREATE TABLE `test` (
`test` VARCHAR(50) NULL COLLATE 'utf8mb4_unicode_ci'
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=MyISAM;
INSERT INTO `in4_db`.`test` (`test`) VALUES ('

28 posts

Danya posted 10 years ago

Whoops, forum breaks on that character...

10361 posts

ansgar posted 10 years ago

Shit. What's wrong here. Worked for me in my tests out of the box.

28 posts

Danya posted 10 years ago

Don't know. Like said, might just be my system. So many variables.
But as said too when I started this topic, it's not a big problem! I don't use HeidiSQL to edit those characters, that's all done by the CMS.

10361 posts

ansgar posted 10 years ago

Well I thought I could bump HeidiSQL's feature set by introducing that fixed utf8 charset, so I find that thing quite interesting and important. And I too have this latency on *some* of my connection attempts, mostly not, I'd say in 1 out of 5 sessions I get a 3 second waiter. Weird.

28 posts

Danya posted 10 years ago

Hm, well, I never experienced the wait time but with the r4822 I do, for every domain and database. Once connected it's all good, but the first connection takes 15 seconds instead of 1.
Hopefully that'll get fixed soon.

If I can do or test anything or supply you with data, let me know.

10361 posts

ansgar posted 10 years ago

The delay is directly in the mysql_real_connect method, as I see that when writing to session log files:

/* 2014-10-03 14:06:47 [localhost] */ /* Connecting to 127.0.0.1 via MariaDB (TCP/IP), username root, using password: No ... */
/* 2014-10-03 14:06:59 [localhost] */ /* Connected. Thread-ID: 103 */

Will try a newer libmysql from the current MariaDB GA release.

28 posts

Danya posted 10 years ago

Ok, and by the way, holy crap, have you changed the font family on HeidiSQL.com? o.O Gee wiz.

10361 posts

ansgar posted 10 years ago

r4827 updates libmysql.dll again to those from the MariaDB 10.0.14 package (current GA release)

And please try again with my exported characters:

CREATE TABLE `utable` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`val` VARCHAR(20) NOT NULL COLLATE 'utf8mb4_unicode_ci',
PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_unicode_ci';
INSERT INTO utable (id, val) VALUES (1, UNHEX('F09D9484'));
INSERT INTO utable (id, val) VALUES (2, UNHEX('F09D9485'));
INSERT INTO utable (id, val) VALUES (3, UNHEX('F09D9487'));
INSERT INTO utable (id, val) VALUES (4, UNHEX('F09D9488'));
INSERT INTO utable (id, val) VALUES (5, UNHEX('F09D9489'));
INSERT INTO utable (id, val) VALUES (6, UNHEX('F09D948A'));
INSERT INTO utable (id, val) VALUES (7, UNHEX('F09D948D'));
INSERT INTO utable (id, val) VALUES (8, UNHEX('F09D948E'));
INSERT INTO utable (id, val) VALUES (9, UNHEX('F09D948F'));
INSERT INTO utable (id, val) VALUES (10, UNHEX('F09D9490'));

28 posts

Danya posted 10 years ago

I'm sorry.. bad news.
1) The connecting latency of about 15 seconds is still there on r4827
2) Still seeing squares after running the query:

10361 posts

ansgar posted 10 years ago

Be sure to use the installer from r4827. Another similar delay issue was fixed by the newer libmysql: http://www.heidisql.com/forum.php?t=16547 . And also the connection delay is gone for me here.

And try another font in Preferences > Data appearance, like kalvaro said. Try "Calibri" or the one kalvaro meant.

These hex values should work here, they should work for you too.

28 posts

Danya posted 10 years ago

Thanks, the installer did the job. It's connecting within a second. Great!! happy

Did not do anything for the squares though.
Like I said before, changing the font is not gooing to be helping. As long as you use true type fonts, they just work. I'm using Tahoma, which is a truetype font.
But, to prove that, here's a screenshot with Calibri:

9 posts

[expired user #8438] posted 10 years ago

After upgrading to 8.3.0.4839, my Chinese characters become corrupted.

/* Delimiter changed to ; */
/* Connecting to 10.55.99.29 via MySQL (TCP/IP), username root, using password: Yes ... */
/* Connected. Thread-ID: 102 */
/* Can't initialize character set utf8mb4 (path: C:\Program Files (x86)\MySql\share\charsets\) */
/* Characterset: latin1 */

10361 posts

ansgar posted 10 years ago

You should download a nighly installer, as you're probably missing some dll.

9 posts

[expired user #8438] posted 10 years ago

I have tried to install the nightly installer but the problem still exists.

Now I have rolled back to use the stable version 8.3.0.4694.
/* Delimiter changed to ; */
/* Connecting to 10.55.99.29 via MySQL (TCP/IP), username root, using password: Yes ... */
/* Connected. Thread-ID: 165 */
SHOW STATUS;

Thanks.

10361 posts

ansgar posted 10 years ago

Hm, well it's clear that latin1 breaks your characters, but why does the installer not use utf8mb4 and not even utf8 as the fallback? I have no clue.

9 posts

[expired user #8438] posted 10 years ago

It should use big5 as the character_set and latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis as the character_sets.

My OS is 32-bit.
The path C:\Program Files (x86)\MySql\share\charsets\ doesn't exist at all.

10361 posts

ansgar posted 10 years ago

I just used the 32 bit installer to try to reproduce your situation, but I get utf8mb4:

/* Connecting to 127.0.0.1 via MariaDB (TCP/IP), username root, using password: No ... */
/* Connected. Thread-ID: 284 */
/* Characterset: utf8mb4 */
SHOW STATUS;

What OS is it on your side?

10361 posts

ansgar posted 10 years ago

Even on my Windows XP box I get utf8mb4.

9 posts

[expired user #8438] posted 10 years ago

My PC is Windows 7 32-bit.
The database is MySQL 4.0.23 which is not installed in my PC.

Do I need to install any MySQL stuff in my PC to support that?
If so, why would the HeidiSQL stable version do not need to install this?

10361 posts

ansgar posted 10 years ago

MySQL 4.0.23? That's really, *really* old. And utf8mb4 is supported from MySQL 5.5.3 upwards: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

9 posts

[expired user #8438] posted 10 years ago

Yea... That's a legacy system in my workplace.
Could you please make it backward compatible? smile

9 posts

[expired user #8438] posted 10 years ago

MySQL 4.0.23? That's really, *really* old. And utf8mb4 is supported from MySQL 5.5.3 upwards: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html

Would you mind adding an option to let the user choose the default character_set to fallback?

10361 posts

ansgar posted 10 years ago

You can set the charset at session startup. Just create a .sql script with "SET NAMES latin1" or whatever and apply that file to your session's option "Startup script" (in the "Advanced" tab)