Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Default Collation_Connection Problem

forums forums SQLyog SQLyog: Bugs / Feature Requests Default Collation_Connection Problem

  • This topic is empty.
Viewing 7 reply threads
  • Author
    Posts
    • #12927
      meneves
      Member

      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;

    • #34178
      peterlaursen
      Participant

      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)

    • #34179
      peterlaursen
      Participant

      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.

    • #34180
      peterlaursen
      Participant

      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!

    • #34181
      meneves
      Member

      I also tried to use

      [mysqld]

      skip-character-set-client-handshake

      Effects?

      bash:

      [08:21] [email protected]:/ # 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” 🙁

    • #34182
      meneves
      Member
    • #34183
      peterlaursen
      Participant

      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.

    • #34184
      peterlaursen
      Participant

      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

Viewing 7 reply threads
  • You must be logged in to reply to this topic.