Default Collation_Connection Problem

[meneves]

I want to set default collation_connection to utf8_polish_ci.

I often connect to production server using sqlyog and execute several commands, but i also get a lot of “Illegal mix of collations” errors (for example when doing CONCAT( “ąść”, table.some_field )).

Is there any way to do it?

For now, i have to set it manually every time i connect by doing SET NAMES 'utf8' COLLATE 'utf8_polish_ci';

I tried to set “init command” in connection settings, but it has no effect becouse after connection – sqlyog automatically send those commands to server:

(history)

/*[08:15:12][0 ms]*/ SET NAMES 'utf8';

/*[08:15:12][1 ms]*/ SHOW DATABASES;

[peterlaursen]

If this is true

becouse after connection – sqlyog automatically send those commands to server:

(history)

/*[08:15:12][0 ms]*/ SET NAMES 'utf8';

.. and if it resets the collation to the default UTF8 collation we have a problem. We should probably query what the default collation is, if it is a utf8 collations SET it back after SET NAMES.

I am checking this,

(and you are right that you cannot compare or concatenate or whatever of the kind strings of different collations in MySQL)

[peterlaursen]

Issue confirmed. Very simple to verify actually. I consider it a bug in MySQL even though it may be documented like this!

SET NAMES 'utf8' COLLATE 'utf8_polish_ci';

SET NAMES 'utf8';

SHOW VARIABLES LIKE 'collation_connection'; — returns 'utf8_general_ci'

So what you do in you INIT_COMMAND setting gets overwritten. You can of course send the “SET NAMES 'utf8' COLLATE 'utf8_polish_ci';” statement from the editor for every new connection you create. This is the workaround for now. We will discuss what options we have here.

[peterlaursen]

I posted this bug report to MySQL http://bugs.mysql.com/bug.php?id=68358.

Maybe they will approve it and maybe not. It does not make much difference for neither you nor us really!

[meneves]

I also tried to use

[mysqld]

skip-character-set-client-handshake

Effects?

bash:

[08:21] root@server:/ # mysql

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 3

Server version: 5.5.27-log MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql> show variables like '%colla%';

+

---

+

---

+

| Variable_name | Value |

+

---

+

---

+

| collation_connection | utf8_polish_ci |

| collation_database | utf8_polish_ci |

| collation_server | utf8_polish_ci |

+

---

+

---

+

3 rows in set (0.01 sec)

SQLyog?

Still the same… after SET NAMES 'utf8' i get “utf8_general_ci” 🙁

[meneves]

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

Status: Not a Bug

[peterlaursen]

OK .. we have not discussed this in detail yet in our team as we have been busy with the 11.01 release.

We will discuss as soon as possible and update here.

[peterlaursen]

I am afraid we forgot to update here.

Please refer 11.1 Release notes:

“Changes in the order of execution of statements when opening a new connection. Now the user specified init-command(s) will be executed after SQLyog's init-command(s).”

.. so now you are able to use the INIT_COMMAND

[Author]

Posts