Encoding Problem After Reconnect

[watson]

Hello! I've found a bug:

After a long break SQLyog is trying to reconnect to the database and the connection encoding is incorrect (perhaps latin1 is on).

Once the the “SET NAMES utf8” is made then – everything comes back to normal.

Thank you

i'm using cyrillic symbols

[peterlaursen]

What SQLyog version are you using? I think recent versions should not have that problem.

[watson]

'peterlaursen' wrote:

What SQLyog version are you using? I think recent versions should not have that problem.

I'm using v9.30

[peterlaursen]

And also tell MySQL server version please? Also tell you conenction method (direct connection, SSH- or HTTP-tunnel. If HTTP-tunnel also tell PHP version)

Please try to reproduce like this:

1) Connect with SQLyog

2) Execute “SHOW VARIABLES LIKE '%char%';

3) Force a reconnect somehow. One way is to have the sever running running with a very low 'wait_timeout' setting and keep SQLyog idle for longer time than this setting

4) Again execute “SHOW VARIABLES LIKE '%char%';”

Does 2 and 4 return different results?

[peterlaursen]

BTW: If you are root/have SUPER privilege you will not need to change the MySQL configuration file and restart server to do 3.

Simply execute “SET GLOBAL wait_timeout = 5;” will cause the server to time-out the connections to clients after 5 seconds and if SQLyog is idle for longer it will reconnect.

[peterlaursen]

And if you do not want other clients to be affected you may

SET SESSION wait_timeout = 5;

.. after connection

[peterlaursen]

.. and finally please check the MySQL error log. There will be a record for every (re-)connect. Anything abnormal here?

[watson]

MySQL ver5.1.52-rel11.6 connected via SSH tunnel

before:

Variable_name Value

---

---

character_set_client utf8

character_set_connection utf8

character_set_database cp1251

character_set_filesystem binary

character_set_results utf8

character_set_server cp1251

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

after reconnect:

Variable_name Value

---

---

character_set_client cp1251

character_set_connection cp1251

character_set_database cp1251

character_set_filesystem binary

character_set_results cp1251

character_set_server cp1251

character_set_system utf8

character_sets_dir /usr/share/mysql/charsets/

[peterlaursen]

OK .. then this is reproduced as you originally suggested!

I very much believe we DO initialize the charset to uft8 in mysql_options() (http://dev.mysql.com/doc/refman/5.5/en/mysql-options.html) when reconnecting. Why it does not happen on your environment I cannot tell now.

We will have to research this!

[peterlaursen]

Brief test on MySQL 5.1.59

— server has latin1 default charset

SHOW VARIABLES LIKE '%char%';

/*

Variable_name Value

---

---

character_set_client utf8

character_set_connection utf8

character_set_database latin1

character_set_filesystem binary

character_set_results utf8

character_set_server latin1

character_set_system utf8

character_sets_dir C:Program FilesMySQLMySQL Server 5.1sharecharsets

*/

SET SESSION wait_timeout = 5;

— wait 1 minute

SHOW VARIABLES LIKE '%char%';

/*

Variable_name Value

---

---

character_set_client utf8

character_set_connection utf8

character_set_database latin1

character_set_filesystem binary

character_set_results utf8

character_set_server latin1

character_set_system utf8

character_sets_dir C:Program FilesMySQLMySQL Server 5.1sharecharsets

*/

— and also my Danish letters (æøåÆØÅ) display correctly – what they would not if not character_set_client was utf8.

.. so not reproducible here. We will have to discuss how to debug this. I will try with cp1251 as well.

[peterlaursen]

No luck with cp1251 either:

— server has latin1 default charset

SHOW VARIABLES LIKE '%char%';

/*

Variable_name Value

---

---

character_set_client utf8

character_set_connection utf8

character_set_database cp1251

character_set_filesystem binary

character_set_results utf8

character_set_server cp1251

character_set_system utf8

character_sets_dir C:Program FilesMySQLMySQL Server 5.1sharecharsets

*/

SET SESSION wait_timeout = 5;

— wait 1 minute

SHOW VARIABLES LIKE '%char%';

/*

Variable_name Value

---

---

character_set_client utf8

character_set_connection utf8

character_set_database cp1251

character_set_filesystem binary

character_set_results utf8

character_set_server cp1251

character_set_system utf8

character_sets_dir C:Program FilesMySQLMySQL Server 5.1sharecharsets

.. but I did not try SSH tunnel. Should not matter.

What is this server “MySQL ver5.1.52-rel11.6”? It looks li´ke it shipped with a Linux distro? Officail MySQL binaries are not versioned like this (“rel11.6), Which distro? From where did you get the server?

[Lexx918]

I have the same problem. Just look at the logs:

/ * [11:41:30] [4 ms] * / SET NAMES 'utf8';

/ * [11:41:30] [4 ms] * / USE `my_database_name`;

/ * [11:41:54] [9 ms] * / SELECT … <- The correct result

/ * [11:41:54] [1523 ms] SQLyog reconnected * /

/ * [11:41:59] [9 ms] * / SELECT … <- Wrong result

Connection puts “set names”, but reconnect – no!

[peterlaursen]

@Lexx918

Do you also have cp1251 as server default charset.? And what is the exact server version?

[Lexx918]

'peterlaursen' wrote:

Do you also have cp1251 as server default charset?

And what is the exact server version?

* Yes

* 5.1.52-rel11.6

(upd.: watson sits two meters from me!)

[peterlaursen]

The version name “5.1.52-rel11.6” does not refer to an official MySQL/Oracle server build. Likely it was included with some Linux distro. Which one?

If this can be solved by upgrading the server to most recent release and/or using an official MySQL/Oracle build I doubt we will try to solve it. I also doubt that we can.

But first of all we need to find a server where this is reproducible. So please reply to the question about from where you got the server.

[Lexx918]

'peterlaursen' wrote:

The version name “5.1.52-rel11.6” does not refer to an official MySQL/Oracle server build. …

Error is also a version 5.0.90-log on hosting http://masterhost.ru

I do not understand how to build mySQL linked to the fact that SQLyog does “set names” after the connection and does not perform after reconnet.

[peterlaursen]

OK .. I am afraid that we will check this again.

[peterlaursen]

Please refer this MySQL bug report: http://bugs.mysql.com/bug.php?id=11972 . This is a server bug. We can reproduce with 5.5.9 actually (the patch was applied to 5.5.11).

I am 99.9 sure that this happens for you because the patch was not merged to the 'unofficial' MySQL build you are using.

As not reproducible with recent and official MySQL servers we will not take any action (and also could not if we wanted to).

[Lexx918]

'peterlaursen' wrote:

I am 99.9 sure that ..

We use MySQL Percona Server 5.1.52.

The code base is no different from the main mysql.

Bug has been fixed (even in 5.1.12).

In other programs, no problem: I tried to repeat the bug in MySQL Workbench 5.2.35 CE – no problems after a reconnect. The console also no anomalies. Only in SQLyog.

[peterlaursen]

Then we probably have to try with this one too (if we can get it).

But if you select a large 'session timeout' value in SQLyog connection settings is it then a big problem?

[peterlaursen]

We have tried with PerconaServer versions “5.1.59-rel13.0” and “5.5.17-55”. Still not reproducible for us.

[Lexx918]

Video http://lexx918.ru/files/sqlyog.zip 🙂

[peterlaursen]

Please understand that we don't misbelieve you. We just cannot reproduce it.

[Lexx918]

Add detailed logging operations, trace execution, creation of dumps, etc.

I'm sure you can track the time of receipt of an invalid response from the server. Or incorrect data output to the user.

[peterlaursen]

Since this issue is not reproducible on our environments it will make no sense for us to do these things.

Please read:: http://webyog.com/faq/content/1/178/en/sqlyog-is-a-client-for-the-mysql-server-_-but-what-server-versions-are-supported.html

“for all stable/GA releases it also may happen that we will not resolve an issue if an upgrade to latest stable versions resolves the issue. It depends on the nature and seriousness of the issue and the effort required to fix or 'work around' the issue with the older version.” This applies not only to MySQL releases from Oracle but also to 'forks' like PerconaServer, MariaDB etc.

You are not using the lastest 5.1 PerconaServer (and also were not at the time of posting). Since we have already spent quite a lot of time with no success on this I will have to ask you to upgrade the server (to PerconaServer version: 5.1.60-rel13.1 at least) or set it up on another similar system. Next check if the problem is still reproducible. If it is we will give it another try – provided that we can have what access to an environment where it is reproducible is needed to debug the issue. We have not been able to reproduce it and as long as it is not the case we cannot debug it.

[Lexx918]

'peterlaursen' wrote:

Since we have already spent quite a lot of time with no success on this I will have to ask you to upgrade the server (to PerconaServer version: 5.1.60-rel13.1 at least) or set it up on another similar system. Next check if the problem is still reproducible.

We have updated the server. Now his version 5.5.19-55. It did not help.

[peterlaursen]

Is it still a Percona Server?

[Lexx918]

'peterlaursen' wrote:

Is it still a Percona Server?

Yes, of course.

—

settings [mysqld]:

skip-character-set-client-handshake

character-set-server=cp1251

collation_server=cp1251_general_ci

[peterlaursen]

hmmm .. I did not know the option “skip-character-set-client-handshake”. Let us check this!

[peterlaursen]

http://dev.mysql.com/doc/refman/5.1/en/server-options.html says:

“To ignore client information and use the default server character set, use –skip-character-set-client-handshake; this makes MySQL behave like MySQL 4.0.”

So I am almost sure that that is the reason (we will verify). You have told the server (in its configuration) to ignore character set specification sent by clients (we DO SEND it but the server IGNORES it because it has been told to do so) Why do you use this option? Was it a deliberate decision for some reason or does PerconaServer ship with a default configuration using this option?

[peterlaursen]

Verified. This happens because of the option. The option disables clients' ability to specify a charset for the connection. We will not do further. You can remove that option from configuration.

(and we also checked that this is not default for PerconaServer)

[peterlaursen]

I have more more detail/consideration (for completeness).

You may have noticed in HISTORY TAB that with a fresh connection SQLyog will execute “SET NAMES 'utf8';” as a regular query (and not as a parameter to mysql_options() as is the case with reconnncts). I would not be surprised if you'd then suggest that we after reconenction should then exectute a regular query as well.

But this is not possible. SQLyog does not know when it reconnects (true for direct connection – not for SSH-tunnel. For HTTP tunnel the discussion does not apply as a new conenction will be established for every query). Reconneccts are handled by by the MySQL client API internally (by setting the reconnect flag in mysql_real_connect() ) and it does not tell its 'parent code' (= our code) when it happens. Our only option is to reconnect with charset specification in mysql_options().

(and frankly – I also don't see any reason to ask a recent server to 'behave as MySQL 4.0').

[Lexx918]

1.

If I remove that option, then I have to do SET NAMES in the code after each connection. Otherwise, the DBMS gives “??? ?? ???”.

We think that the decision in the configuration file more concise.

2.

You did not answer, why does SQLYog SET NAMES after the first connection and does not do after the other: http://www.webyog.com/forums/index.php?showtopic=6301&st=0&p=26082&#entry26082

[peterlaursen]

1) I do not understand 1). Could you share the complete my.cnf? A screenshot? A test case (including a dump of a table)?

2) I did reply to 2). SQLyog does not know when reconnects takes place as reconnects are handled in the API internally. So the only thing we can do is to define connection charset in charset mysql_options()

(another solution would be BTW to make utf8 server default charset)

[Lexx918]

'peterlaursen' wrote:

1) I do not understand.

Now all settings are in the configuration file database.

All clients (SQLYog, php, phpMyAdmin, MySQL Workbench, etc.) work with the option “skip-character-set-client-handshake”. And do not SET NAMES. The first diagram in Fig.

If I remove that option, all clients will have to put SET NAMES after connecting. The second scheme in Fig.

Why do I need to make two requests during the connection in php, if I make one?

[peterlaursen]

Quote:

If I remove that option, all clients will have to put SET NAMES after connecting

Short answer is YES, you should. That is good practice. And even we wanted to execute (as a regular statement) we cannot because we don't know and cannot know when to do it.

(FYI – I have blog about same: http://www.webyog.com/blog/2012/02/01/cleanup-old-options/. It will soon appear on planet.mysql.com. You are welcome to comment there of course. But I did not link to this discussion myself as I do not know how much you want to be exposed).

[Lexx918]

Thank you. We think!

A link can be published. I do not mind.

Maybe someone even notice the word “Dota” in my profile, and to dare to play with me!

[Lexx918]

There is also an option: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_init_connect

[mysqld]

init_connect = 'SET NAMES cp1251'

And it worked for php! But not in SQLYog.

[peterlaursen]

Actually I was about to propose you exactly that. Are you perfectly sure that it does not work with SQLyog after reconnection?

This contradicts what I reported here: http://bugs.mysql.com/bug.php?id=59826

(but we should check this. If you are right this is a bug in mySQL IMHO)

[peterlaursen]

BTW: did you notice that 'init-connect' is not executed for users with SUPER privilege? (refer http://dev.mysql.com…ar_init_connect). Most often SQLyog will connect as a user with SUPER privilege.

So you should remove/comment the “–skip…” option and replace with “init-connect = “SET NAMES …”. Was that what you did?

[Lexx918]

'peterlaursen' wrote:

Are you perfectly sure that it does not work with SQLyog after reconnection?

Yes. The administrator has removed the option '-skip..' and added the option '-init..'. Restart the server.

Then we checked the text on the site – everything is fine.

Then I repeated the SELECT + KILL + SELECT in SQLYog – all bad! Reconnect is, but the answer came broken.

That is, seen as an option '-init..' (without the option '-skip..') affect the text on the site. In SQLYog no difference.

[Lexx918]

'peterlaursen' wrote:

Most often SQLyog will connect as a user with SUPER privilege.

SHOW GRANTS FOR CURRENT_USER();

GRANTS FOR [email protected].5.0/255.255.255.0

---

GRANT USAGE ON *.* TO 'myname'@'192.168.5.0/255.255.255.0' IDENTIFIED BY PASSWORD '*…'

[peterlaursen]

Can you explain me how such a user (having only USAGE privilege and not SELECT privilege to any table at all) is able to SELECT anything at all? The original problem was about cyrillic letters garbling, right?

[Lexx918]

'peterlaursen' wrote:

Can you explain me how such a user (having only USAGE privilege and not SELECT privilege to any table at all) is able to SELECT anything at all? The original problem was about cyrillic letters garbling, right?

I do not know the details USAGE/SELECT/GRANTS, etc., but: http://dev.mysql.com/doc/refman/5.1/en/show-grants.html

Quote:

SHOW GRANTS requires the SELECT privilege for the mysql database.

So to this I have the right!

Yes, the problem is in cyrillic.

[peterlaursen]

— I have started a server (MySQL 5.1.60) wiht the options:

default-character-set=cp1251

init_connect = 'set names cp1251'

— I have a table in `test` database like this

CREATE TABLE `русский` (

`id` int(11) DEFAULT NULL,

`txt` varchar(10) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=cp1251

— Data in the table are:

id txt

---

---

1 русский

2 язык

— I connect as a user with GRANTS like this (no SUPER privilege):

Grants for pl@%

---

GRANT USAGE ON *.* TO 'pl'@'%'

GRANT SELECT ON `test`.* TO 'pl'@'%'

— Next I execute

SET wait_timeout = 5;

— and I now wait +5 seconds to ensure reconnect takes place and I execute

SELECT * FROM `русский`;

/* the result is displayed as expected

id txt

---

---

1 русский

2 язык

*/

[peterlaursen]

BTW: I the purpose of this discussion is to decide who is right, then please find an academic Forums instead of this one. We are trying to help people and not to decide 'who is right'. But actually the documentation is wrong and you read it like the devil reads the Bible.  Reported here: http://bugs.mysql.com/bug.php?id=64215 

I want to see your complete configuration file and I want acces to this server if we shall spend more time with it. Writing forth and back here leads nowhere.

[peterlaursen]

Please refer: http://www.webyog.com/blog/2012/03/22/sqlyog-mysql-gui-9-63-released/

With a — skip-character-set-client-handshake setting in configuration you can now enter SET NAMES UTF8 as an INIT-COMMAND for the connection in SQLyog

[Lexx918]

'peterlaursen' wrote:

..you can now enter SET NAMES UTF8

COOL! It works! 🙂

Tnx!

[Author]

Posts