Bug? In A 6.04 And 6.05b1

[jd wuz here]

I am having troubles viewing my tables with SQLyog. Table is filled with windows-1251 data and all the text fields are shown just fine, but i am having troubles with BLOB fields – in view table mode and in BLOB editor i see anything but russian charecters, they show like spaces in table view and like japaneese/chineese/whatever in BLOB editor. This didn't happen in 5.xx brunch.

[peterlaursen]

SQLyog displays what is stored in the database!

that applies to string )char/varchar/TEXT) data. For BLOBs you should try 'froce' ANSI client side encoding for those from the dropdown in the upper right corner of BLOB-viewer.

How does SQLyog BLOB-viewer detect the encoidng of the byte stream? And what is the LOCALE setting of your PC?

Please refer to the FAQs:

http://webyog.com/faq/34_149_en.html

http://webyog.com/faq/34_152_en.html

You are (very) welcome to attach a dumped and zipped (small) table/test case that illustrates your problems

.. plrsn was here too 🙂

[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!

[peterlaursen]

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]

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

[peterlaursen]

Please attach a SQL-dump of that table!

That is the only way for us to do progress understanding the issue! Pictures and words won't do. We need DATA themselves!

[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

[peterlaursen]

This might not work!

please dump, zip and attach the file.

Anything that has been decoded by a browser may have been distorted!

A zipped attachement will be transferrred a a binary copy!

Also please tell: was it first of second row of data that was inserted with SQLyog?

[jd wuz here]

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

[peterlaursen]

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]

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!

[peterlaursen]

Quote:

in other words be a totally uft

SQYog is total utf8 (and even MySQL command line clients on Windows are not (on *nix they are!). Character sets have no meaning with binary types like BLOBS! The MySQL server is (from 4.1) designed to handle encodings on the server side. We follow that concept 100%.

Basically I think (but I need to research a little more) that this config parameter is a depreciated way of setting communication between server and clients. For backward compability it probably still is supported.

I cannot see why you need this option. Every application could “SET NAMES CP1251” or “SET NAMES UTF8” as appropriate for the application.

Also I am not sure yet either if it is a bug with MySQL that this config parameter prevents a client to SET NAMES, or if it is intended to be like that – and not if this config parameter is documented with recent MySQL versions either !!

[peterlaursen]

http://dev.mysql.com/doc/refman/5.0/en/mys…ion-tables.html

For status variables, the scope of the variable is shown (Scope) as either global, session, or both. 'init_connect' is described as a GLOBAL status variable! Still it is not clear if a client may override this

Let us see what MySQL AB say here:

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

[peterlaursen]

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.

[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..

[peterlaursen]

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?

[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..

[peterlaursen]

It was a silly idea then! Thanks anyway!!

At this moment I cannnot tell what we will do. It does not make sense to change anything as long as the issue is not fully understood.

If 5.32 works to your satisfaction then there also is no hurry, I think! But we do intend to find out!

[Author]

Posts