Beta 6 Remarks

[DonQuichote]

Thank you for creating a database frontend that supports unicode.

When I first started the beta 6 version, the database values were finally legible. However, when I started it again, they were not. It turned out that I connected to different servers that caused the difference. I run SQLYog v6.0 beta 1 under Windows XP.

I think you are interested in the server settings that cause this:

Correct encoding:

Linux (Gentoo)

4.1.20-log

“character_set_client”=”utf8”

“character_set_connection”=”utf8”

“character_set_database”=”utf8”

“character_set_results”=”utf8”

“character_set_server”=”latin1”

“character_set_system”=”utf8”

utf-8 shown as latin-1 (A~ etc.):

Windows XP (localhost development server)

5.0.22-community-max-nt-log

“character_set_client”=”utf8”

“character_set_connection”=”utf8”

“character_set_database”=”utf8”

“character_set_filesystem”=”binary”

“character_set_results”=”utf8”

“character_set_server”=”utf8”

“character_set_system”=”utf8”

One extra remark: If there is an apostrophe in a column remark, the syntax colouring of the object tab is affected.

Best regards

[peterlaursen]

I do not fully understand.

Do you have an issue with SQLyog 6.0 not displaying data form your local server correct?

[DonQuichote]

peterlaursen wrote on Mar 27 2007, 04:50 PM:

I do not fully understand.

Do you have an issue with SQLyog 6.0 not displaying data form your local server correct?

I did not file a bug report, if that is what you mean. I also do not have much trouble with it, as this beta version is the first MySQL database frontend I know of that can handle encodings. Even phpMyAdmin does not do it right, even though it is web-based. So I solved the problem some time ago by creating a PHP page that allows me to make a change to any table value through the website.

I probably should have posted this in the bugs forum. Sorry.

[Rohit]

@DonQuichote:

Did you create the PHP page as a workaround because the Unicode features of SQLyog were not working as you had expected?

We really want to know if you are STILL not able to see data properly from your local server.

[peterlaursen]

First ensure that the data are STORED correctly on the server.

Export a table or database (as utf8 – use SQLyog or mysqldump as you like) and open in Notepad.

Check every non-ASCII/non-English character.

We have seen before that people has implemented a 'workaround' (encoding/decoding in the client) for data not really being stored as unicode.  That we cannot replicate.  But once data are correctly stored we should also display/save/edit them correctly.

[DonQuichote]

Rohit wrote on Mar 28 2007, 11:52 AM:

@DonQuichote:

Did you create the PHP page as a workaround because the Unicode features of SQLyog were not working as you had expected?

We really want to know if you are STILL not able to see data properly from your local server.

Well, yes. But that was when I was working with the previous versions (5.x). I could not find any other database frontend as well that gave me utf-8 compliance. The data gets sent as unicode and all the character set variables (I wish they had called them encoding variables) are pointing at utf-8. I have baretail running to see what is sent exactly and it is really utf-8. Off course, it could be that my MySQL version translates the encoding, but it should not do that (see variables posted earlier).

Extra test:

This really gets funny.

I type in the SQLyog window:

INSERT INTO ErrorLog(strMessage, tstOccurredAt) VALUES('€ 5',NOW());

and check the contents of the table ErrorLog. I see a euro sign. I check the MySQL query log. It also shows a euro sign. I view the page online and it shows a question mark. The page is viewed in utf-8 and I do no conversions in my code.

The querylog shows:

070330 9:57:48 6 Connect root@localhost on

6 Query use `HamerReport`

6 Query show variables like '%%character%%'

6 Query Set character_set_connection=utf8

6 Query Set character_set_results=utf8

6 Query Set character_set_client=utf8

…

070330 9:59:54 6 Query INSERT INTO ErrorLog(strMessage, tstOccurredAt) VALUES('€ 5',NOW())

With my workaround-page, I correct the question mark to a euro sign. I check the query log. It shows a euro sign. I check the contents of the table in SQLyog. It shows “€ 5”. I check the page online and see a euro sign.

The SQL log shows:

070330 10:02:38 12 Connect admin@localhost on

12 Init DB hamerreport

12 Query SHOW TABLES

12 Query SHOW FIELDS FROM ErrorLog

12 Query SELECT intErrorLogId, strMessage FROM ErrorLog ORDER BY strMessage

12 Query UPDATE ErrorLog SET strMessage='€ 5' WHERE intErrorLogId=1

Fearing this is a settings problem, I check my.ini. It shows:

default-character-set=utf8

in both the [mysql] and the [mysqld] sections, and all sorts of other quite specific settings that work (so I have the right file).

I hope this is of any help.

[DonQuichote]

peterlaursen wrote on Mar 28 2007, 12:09 PM:

First ensure that the data are STORED correctly on the server.

Export a table or database (as utf8 – use SQLyog or mysqldump as you like) and open in Notepad.

Check every non-ASCII/non-English character.

We have seen before that people has implemented a 'workaround' (encoding/decoding in the client) for data not really being stored as unicode. That we cannot replicate. But once data are correctly stored we should also display/save/edit them correctly.

Ah. There's the little bug. I used mysqldump to dump the table and it shows “€ 5” when viewed as unicode. All settings (during dump and for the server) point to utf-8, so there is really a storage problem in MySQL.

Thanks for helping me out.

For other people having the same problem:

I configure both the server (mysqld section) and the client (mysql section) in the my.ini file to use utf-8. However, this affects the server only and does NOT set the default encoding for a client connection. So the connections I opened from PHP defaulted to latin-1. SQLyog did not show this problem, as it sends a SET NAMES statement upon opening a connection. What is even more troubling is that this seems to affect modern (5.0) versions of MySQL only. And pre-4.1 versions do not even understand the SET NAMES command. Trying to stay compatible with different versions really gets hard this way…

So the solution is checking the MySQL version and sending the appropriate SET NAMES command if the version is >4.1.

[Mikel]

DonQuichote wrote on Mar 30 2007, 08:41 AM:

Ah. There's the little bug. I used mysqldump to dump the table and it shows “€ 5” when viewed as unicode. All settings (during dump and for the server) point to utf-8, so there is really a storage problem in MySQL.

Thanks for helping me out.

For other people having the same problem:

I configure both the server (mysqld section) and the client (mysql section) in the my.ini file to use utf-8. However, this affects the server only and does NOT set the default encoding for a client connection. So the connections I opened from PHP defaulted to latin-1. SQLyog did not show this problem, as it sends a SET NAMES statement upon opening a connection. What is even more troubling is that this seems to affect modern (5.0) versions of MySQL only. And pre-4.1 versions do not even understand the SET NAMES command. Trying to stay compatible with different versions really gets hard this way…

So the solution is checking the MySQL version and sending the appropriate SET NAMES command if the version is >4.1.

Thanks a lot for this solution, it works great and stopped me from ripping my hair out trying to work out why I kept getting question marks instead of my utf-8 data! Now if only PHP supported UTF8 natively too…. :huh: Well hopefully not too long till PHP6 now anyway 😉

[Author]

Posts