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 contains 6 replies, has 0 voices, and was last updated by  peterlaursen 6 years, 1 month ago.

  • 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] 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” 🙁

  • #34182

    meneves
    Member

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

    Status: Not a Bug

  • #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

You must be logged in to reply to this topic.