Data Encoding

[webtask]

SQLYog 6 RC2

Does SQLYog convert user queries (user input from sql editor) to appropriate code page when sending queries to MySQL server or it always use utf8???

I work with tables like

ENGINE=MyISAM DEFAULT CHARSET=cp1251

Default SQLYog query after establishing connection is:

set names utf8;

It works fine and I can see national characters under 'table data' tab.

But if then I send query:

set names cp1251;

and select any data (using select query) all my text fileds (with cyrillic text) becomes unreadable. I think SQLyog shows results in utf8 again and could not detect that code page was changed.

Also, when I don't change default utf8 and then use select like:

select A, B, C

from T

where D like '%cyrillic string%'

MySQL return an error:

Error Code : 1267

Illegal mix of collations (cp1251_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) …

If I send after this set names cp1251;

Query was successfully done but return no results!!! It is wrong, as I know that the row where D == 'cyrillic string' is in the database! And I can obtain data for example on php, but not in SQLYog 🙁

[peterlaursen]

You simply shall not 'set names cp1251;' with SQLyog 6.0

It is designed to always use utf8 on the client side.  It sends 'set names utf8;' (with MySQL servers that support 'set names') when it connects.

You shall not be worried that one character set is used on the server side and another on the client side!  That is basically what the 'set names' command is for!  The server transforms the encoding between the charsets used on the server and the client when sending and recieving data !

[webtask]

When I do not use set names cp1251 it is possible to view table data, but not possible to use queries which selects rows with 'like' statement and international characters. And for those queries I MUST use cp1251. And in this case results are unreadable but they exists!

I know that server transforms encodings, but I suppose that SQLYog not convert data from sql-editor to utf8 and send it as is.

When there is only international characters in the query – everything ok, but when I want to select 'cyrillc' data and use or or calculate 'cyrillic' values in the field list <(case when D='cyrillic' then 1 else 0 end)> – query fails.

[peterlaursen]

“but not possible to use queries which selects rows with 'like' statement and international characters. And for those queries I MUST use cp1251.”

NOOOOOOOOO!  It will never work with 'set names' otherwise than 'set names = utf8' in SQLyog 6.  Instead of guessing like that you should give a test case for reproducing your problem!

“but I suppose that SQLYog not convert data from sql-editor to utf8 and send it as is”  

You should not 'suppose' either! You can ask! 

SQLyog does not do any such thing!  The server does – or should do – it all and that is the whole idea!  

-all sorts OF COMMUNICATION between SQLyog and MySQL is – or should be – in utf8 with SQLyog6.

“When there is only international characters in the query – everything ok, but when I want to select 'cyrillc' data and use or or calculate 'cyrillic' values in the field list <(case when D='cyrillic' then 1 else 0 end)> – query fails”  

Could you explain the difference between 'international and 'cyrillic characters?

Please create a small sample table with cyrillic characters, export it, zip and attach here.  Plus give a few example queries failing.  

That will give us a chance to reproduce and understand.

Also answer: What is your MySQL version?

Finally I will repeat.  YOU SHALL NEVER 'SET NAMES' YOURSELF WITH SQLYOG 6 !!!

[webtask]

MySQL Server 4.1.7

[peterlaursen]

thanks – still it would be very nice if you could create a very small table, dump, zip and attach.  Plus give a few examples of queries failing!

But 4.1.7 is a very early version.  There could very well be some issues with unicode in the parser.

[vygi]

I am susing one of the latest MySQL 5 versions (5.0.40) but am very confused with all these UTF8 features of SQLyog 6, and these issues forces me to uninstall it and replace it again by SQLyog 5.

EXAMPLE:

I have a small table with country names; one of them is Österreich (Autria) in name field with latin1_german_ci collation.

SELECT statement via mysql CLI returns correctly “Österreich”.

SELECT via SQLyog 6 returns “sterreich” (!) without “Ö”.

OK… I have put “Ö” in front of the name manually .

Now SELECT via SQLyog 6 returns “Österreich” but mysql CLI and all PHP scripts read it “Ãsterreich” for some reason!!

Similar with “Åland Islands”: either “Åland” via CLI but “land” via SQLyog or “Åland” via SQLyog but “Ã

land” (yes: with carriage return after 1st character) via CLI.

Can SQLyog 6 correctly handle non-UTF databases at all?!…

[peterlaursen]

yes .. SQLyog 6 can handle non-unicode data – or at least it should!

I still think your server is a 4.0 ?

[vygi]

peterlaursen wrote on Jun 6 2007, 09:42 AM:

yes .. SQLyog 6 can handle non-unicode data – or at least it should!

I still think your server is a 4.0 ?

In my case it's 5.0.40 Enterprise.

Let me know how can I/we debug and fix this vehaviuor, otherwise I have to uninstall yog 6.0 ASAP.

[peterlaursen]

we tried to build a small table here with the examples.  It works here!

can you attach a small table (zipped .sql) and some example queries failing like this?

also execute ” SHOW VARIABLES like '%character%' ” and paste the result.

When SQLyog does “SET NAMES utf8”  the server should handle everything – i.e. tranform between the charsets used for stored the data and the charset for the client and the connection!  And we have not seen any situation where it does not!

How were data inserted? What happens if you insert data with SQLyog?

[vygi]

surprise: I have opened another, new SQLyog instance, and it works correctly!

UTF8 vs. non-UTF handling seems to be “broken” in my first SQLyog, which is running now for 3 days or so.

[peterlaursen]

it is more likely that something has broken in Windows then, I think!

Internally SQLyog 6 stores strings in variables of type 'multibyte'.  whenever communicating with the Windows API strings are transformed to a 'widechar' type.  'multibyte' is for storing utf8 characters, 'widechar' for ucs2/utf16.  utf16 is the native Windows Unicode implementation.

The datatypes 'multibyte' and 'widechar' are basic C/C++ types and the library with all sorts of functions doing the transformation is a standard library too.

[webtask]

I tried to do simple test under MySQL 5.0.37 – all queries works without errors. Under 4.1.7 – I got an error. Probably, it is realy server-side problem.

Example data:

Code:

* Insert example data. File encoding is Windows-1251
*
*/

/*
CREATE TABLE `cyr` (
`ROWID` int(10) unsigned NOT NULL auto_increment,
`TITLE` text,
PRIMARY KEY (`ROWID`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251
*/

if (($db = mysql_connect('HOST', 'USER', 'PASSWORD')) !== false) {
mysql_select_db('DATABASE');
mysql_query('set names cp1251'); // all data is sending to server is in Windows-1251 !!!
$query = “insert into `cyr`(`ROWID`,`TITLE`) values (1,'Мама'),(2,'мыла'),(3,'раму'),(4,'азбука'),(5,'One'),(6,'Two'),(7,'Three')”;
mysql_query($query); // that's all
echo 'OK';
} else {
echo 'Was not connected!';
}

?>

id = 1,2,3,4 – are cyrillic titles

All the same, I think all problems may solve custom SET NAMES command, but this way is deprecated in SQLYog 6.

[peterlaursen]

Yes .. I would not rely on 4.1.7!!

and an add-on to my latest reply to vygi: it also could be the MySQL Server who has 'forgotten' the SET NAMES statement for the connection.

[Author]

Posts