Heidi changes the collation on connecting to the server

HSQL version: 9.1
RDBMS version: MySQL 5.1

I've set the collation on my server to ut8_unicode_ci. If I connect to the server using mysql console client, I can see that the collation is utf8_unicode_ci:

$ mysql --user='' --password='' database
mysql> show session variables like '%collation_connection%';
+----------------------+-----------------+
| Variable_name | Value |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
+----------------------+-----------------+

But when I connect with HSQL, my collation switches to 'utf8_general_ci'.

It seems to me that HSQL executes 'SET NAMES utf8;' after the connection is established. HSQL doesn't specify a collation, therefore MySQL uses the collation that is default for this character set, which is the dreaded utf8_general_ci. That is the behavior described in mysql's documentation here (look for the section about SET NAMES) http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

It would be great if I could specify a collation in the connection settings.

HeidiSQL does not fire a "SET NAMES somecharset", as this would introduce bugs. Instead, HeidiSQL does that using the API method mysql_set_character_set(), which is called with "utf8". HeidiSQL does that to verify it can handle international characters. It should not matter much to the user, as the server translates every text string into the required target collation when you for example insert something into a table column which does not have the utf8_general collation.

Btw, in newer HeidiSQL versions, the utf8mb4 character set is used.

And it seems that by executing that call you still change the collation from the one that is set as default on the server, to the one that is associated with the charset. http://dev.mysql.com/doc/refman/5.0/en/mysql-set-character-set.html "The connection collation becomes the default collation of the character set"

And here is an example when it becomes a problem:

SET @var = 'some string variable';
SELECT * FROM some_table WHERE some_column = @var;

The variable is in utf8_general_ci and the column is in utf8_unicode_ci. As the result I get the following error:

SQL Error (1267): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

To fix it I need to set collation manually and do it every time I connect to the server and every time the HSQL reconnects. This where the "Startup Script" would've come in handy, but unfortunately it's not executed on the reconnects, as I've explained in another thread. And I admit that working with variables is not the most common use case, and there still a way to overcome the problem, so the ability to set collations is not essential to the app. Yet it would be still nice to have one

I think HeidiSQL had to be able to configure a character set/collation for every connection by the user.
It's necessary for using HeidiSQL with older databases configured in other collations

I cannot work with a standard "utf8mb4" character set, because it restricts using 'utf8_unicode_ci' as collation.

That's the standard collation of the database full of 200GB of data.

If I try to write a procedure with HeidiSQL 9.2 it turns always in 'utf8_general_ci' and it all ends up to a unsolveable 'Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' '.

I have to use other tools than HeidiSQL at the moment to prevent debugging errors with collations caused by editing something with HeidiSQL.

In case anyone finds this via a web search... The workaround I used was to create a startup script and inside it set a session variable.

Example script:

SET @@session.collation_connection = 'utf8mb4_unicode_ci';

Set a startup script under Session Manager, Advanced:

i.imgur[.com]/DrLRHDB.png

Before

i.imgur[.com]/kPtSjk5.png

After

i.imgur[.com]/TXtx2ws.png

Hi I know this is an old tread but I am having issues with the collation sequence that Heidi is defaulting to

Namely Collation Connection seems to be utf8mb4_general_ci if I use other tools ( DB Schema ) for example the connection collation is set to utf8mb4_0900_ai_ci, Which is what I would expect as its the database and server are defaulting to utf8mb4/utf8mb4_0900_ai_ci . I know I can fix this with a script but I would like to understand how it is getting set this way.

The issue for me that this causes is if I use Heidt to generate scripts / create Views or Functions the columns are all utf8mb4_general_ci which then causes a conflict.

Any suggestions/thoughts appreciated.