[jd wuz here]

peterlaursen wrote on Jul 13 2007, 01:44 PM:

It is not that easy! I also have explained why 5.32 works. But it will not with multilingual data or languages for which no non-unicode charsset is availbale. It will not either if data uses one ANSI codepage and the computer uses a LOCALE setting not matching this.

The 'mysql' client you are using is running on Windows or Linux?

Also I think you never told about the server platform?

I have one (maybe silly) idea. Maybe the 'SET NAMES UTF8' statement simply comes too fast! Could you try connecting and then 'SET NAMES UTF8;' yourself from SQL editor before entering any data?

Also: do you have an option to try on another server?

mysql client is on linux, SQLyog is on windows and server is also linux.. the other server i have handy is also on linux, but it is 4.0.27 and it seems to have no problem with SQLyog, atleast when reading CP1251 data from ANY field (that is varchar AND blob).

SET NAMES UTF8 didn't help on 5.0.37..

[jd wuz here]

peterlaursen wrote on Jul 12 2007, 12:01 PM:

http://bugs.mysql.com/bug.php?id=29665 was updated.

Not reproducable for the MySQL supporter that SET NAMES is ignored. Still I am 99.9% sure tthat that is what happens on your server. I don't have any ideas right now.

the easiest way is to make user decide if he want a UTF of 'his own' language.

if it was on my server then how come that /mysq/bin/mysql works correctly and a pre6 version also does?

will have to stay on 5.32 then..

[jd wuz here]

it seems that your guess about init_connect is correct. but, unfortunately commenting out this option will be a major trouble to a running production web server..

what seem to be a major problem is that different types of colums (varchar & blob) are displayed different way. my suggestion is to make an option in SQLyog to behave in a 5.xx way or a new, utf way, or in other words be a totally uft or an “old-school” manner. this will atleast give me (and lots of russian people i guess 🙂 a CHANCE to use your otherwise great tool. sometimes (for me that is MOST time) i just don't NEED to work with unicode.

and yes, i have root axx to the server.

peterlaursen wrote on Jul 9 2007, 07:24 PM:

OK .. we reached a little bit further today.

SQLyog does this:

1) SET NAMES UTF8

2) sends utf8-encoded strings

That is consistent. But your server ignores SET NAMES UTF8 as sent by SQLyog and maps every byte to cp1251.

You can verify that by importing the dump and next doing an ALTER table on the garbled column (change the column to a utf8-collation)

Maybe the 'init_connect' 'locks'/prevents SQLyog SET NAMES command. We will study tomorrow!

I also found this:

http://golgote.freeflux.net/blog/archive/2…-1-and-utf.html

Are you root user or not?

Also could you try temporarily removing/commenting the 'init_connect' parameter in config?

With MySQL 4.0x SQLyog does not SET NAMES and SQLyog 5.x operates a ANSI locale, so this explains that the issue does not occur. But you cannot work with mulitlingual and other unicode data then!

[jd wuz here]

please find attached archived copy. and yes, the first line was inserted by SQLyog.

[jd wuz here]

— MySQL dump 10.11

—

— Host: localhost Database: imgsrc

—

---

— Server version 5.0.37-standard-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

—

— Table structure for table `temp`

—

DROP TABLE IF EXISTS `temp`;

CREATE TABLE `temp` (

`id` mediumint(3) unsigned NOT NULL auto_increment,

`title` varchar(255) NOT NULL,

`body` blob NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=cp1251;

—

— Dumping data for table `temp`

—

LOCK TABLES `temp` WRITE;

/*!40000 ALTER TABLE `temp` DISABLE KEYS */;

INSERT INTO `temp` VALUES (1,'Первый','Первый!'),(2,'второй','второй');

/*!40000 ALTER TABLE `temp` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

— Dump completed on 2007-07-09 13:19:18

[jd wuz here]

just as i expected – pre6 version of SQLyog shows my data (in cp1251) fine.

i now created a temp table and inserted one row with SQLyog and one directly with local/bin/mysql in console. both rows contain russian characters. the first line shows ok in SQLyog, while second is garbled (see attachment). in console the situation is reversed:

[codebox]+—-+

---

+

---

+

| id | title | body |

+—-+

---

+

---

+

| 1 | Первый | Первый! |

| 2 | второй | второй |

+—-+

---

+

---

+

2 rows in set (0.01 sec)[/codebox]

so here's what happened: regardless of anything SQLyog inserted all data in utf8, but that is NOT what i want. is there a way to disable such behavier?

moreover, when i disconnected from the db and logged in again what i see in SQLyog is a total mess (image2.png) while console is still showing same as before..

peterlaursen wrote on Jul 6 2007, 03:31 PM:

This (in my.cnf)

set-variable=default-character-set=cp1251

init_connect='SET NAMES cp1251'

.. will not have effect with SQLyog because SQLyog does 'SET NAMES utf8'.

You can try yourself to execute (from SQLyog)

'SHOW VARIABLES LIKE '%character%'.

SQLyog 6 is designed to ALWAYS use utf8 on the client side!

I also think the FAQs explained in detail!

But SET NAMES (neither yours or ours) has no effect on binary types like BLOBs! If you alter table and change BLOBs to TEXT I think it will display. It surprises me that selecting ASCII/ANSI in the dropdown does not make things display correct. I will hav to study cyrillic charsets more in detail, I think!

Please dump, zip and attach a small table that illustrates the problem plus a few screendumps of how it displays on your system!

SQLyog 5.32 COMMUNITY can be downloaded from

http://www.webyog.com/downloads/SQLyog532.exe

[jd wuz here]

the above only occures when i use SQLyog against a 5.0.37 version of MySQL. with version 4.0.27 both (strings AND blobs) are displaying correctly in the same row. all data is cp1251. selecting the encoding in the “upper-right” droplist of blob viewer helps me nothing 🙁 showing UTF-8 by default. locale of my WXP is set to russian..

in my.cnf i have

set-variable=default-character-set=cp1251

init_connect='SET NAMES cp1251'

and all the collation_% vars are set to cp1251_general_ci

which more variables should i look for in my installation or what do i do for fixing this?

is there a way to obtain a latest pre-6 SQLyog version for me to test?

THANK YOU!

[Author]

Posts