Problem With Utf8

[uyuni]

I have a table with fields of type text with Collation latin1_swedish_ci.

I've done: set names 'utf8';

The result in the result-tab gives me different results for

SELECT text

and for

SELECT substr(text,1,200)

See Screenshot.

SQLYog 6, Beta 4.

[peterlaursen]

please tell the exact server version!

also if you can connect with commandline please check if it does better!

I am not sure, but this looks mostly like a server issue (a bug with the implementation of the SUBSTR() function). I will not make much sense to try reproducing with another server version! I cannot reproduce here with MySQL 5.0.37 and Danish characters

[uyuni]

Serverversion is 4.1.16-nt.

The difference is there even when using SUBSTR twice, it depends on the substring-length, see screenshot.

The result is OK when using the commandline, see screenshot.

Emilio

[peterlaursen]

Thanks for the details! We will study this!

Still it might help if you created a small 'dummy' table with a few rows of data, exported it, zipped and attached here.

What is the locale/language of you windows?.

Please explain 'it depends on the substring length'.

In SQLyog “SELECT 'ö'” and “SELECT substr('ö',1,200)” should return the same. 'ö' simply. I cannot make it do different than that. See attached!

[peterlaursen]

OK .. I overlooked the point that data were stored as TEXT.

Now with this table

CREATE TABLE `test2` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`text` varchar(50) DEFAULT NULL,

`text2` text,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1

I get the attached result.

Do you have different results with VARCHAR and TEXT types?

[peterlaursen]

I wonder if your problem could be an 'incarnation' of this bug with mySQL itself:

http://bugs.mysql.com/bug.php?id=20204

This is fixed only in the very latest releases (such as 4.1.22).  Is it possible for you to upgrade the server?

also please let us see the results of

Code:

SHOW VARIABLES LIKE '%character%';

[uyuni]

It cannot be the MySQL-bug because I use a MYISAM table.

---

I send you an SQL-export.

Do this:

– Run script

– run

select id,substr(text,1,196),substr(text,1,197) from utferror where id=10;

The result is NOT OK

– open “table data”, the result for the column 'text' is NOT OK

– change the value of column 'text', add Umlaut-characters (öäü). They are displayed correctly in the editor window. When you close the editor-window with “OK” then the value is wrong in the “table data”-tab!

---

SHOW VARIABLES LIKE '%character%';

character_set_client;utf8

character_set_connection;utf8

character_set_database;latin1

character_set_results;utf8

character_set_server;latin1

character_set_system;utf8

character_sets_dir;D:\Programme\MySQL\share\charsets/

[peterlaursen]

I am not sure whether this MySQL bug affect only InnoDB. The example they use in the bug report is InnoDB, but the changelogs do not mention any specific table type!

We will look into your dump and other details tomorrow.

And from the charset path I can figure out that you are running a German localised Windows. Mine is Danish and as both are ANSI-western codepage I should be able to reporduce everythong that you experience, æ or ä and ø or ö makes no difference!

[uyuni]

Any news?

[peterlaursen]

I am sorry, but it seems that we forgot this one.

One thing that surprises me is that you create the table like '..DEFAULT CHARSET=latin1'. I do not understand how this is unicode related then? please explain!

Also please see 1st attached image. Ii am not even able to insert that long string without 'ööö' becoming garbled in the display. And that no matter if table charset is latin1 or utf8. Sever is 5.0.41 (i do not have 4.1.x here).

2nd attached image. Depending on the length of the substring it will garble! We will have to find out now if SQlyog garbles or MySQL does!

Thanks for persistency!

[peterlaursen]

here comes the images

[peterlaursen]

OK .. confirmed to be a GRID related bug in SQLyog. If you 'show results in text' it displays correctly!

We will work on that NOW!

[Rohit]

The new RC(RC1) has fixed this issue.

Please confirm!

[uyuni]

It seeems OK now!

Thanx,

Emilio

[jordan]

No different results with VARCHAR and TEXT types.

[peterlaursen]

@jordan

What is the message that you are trying to convey?

Please use this Forum a little more responsible! You are taking other peoples time!

[Author]

Posts