Heidi changes the collation on connecting to the server

[expired user #8717]'s profile image [expired user #8717] posted 9 years ago in General Permalink
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.
ansgar's profile image ansgar posted 9 years ago Permalink
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.
[expired user #8717]'s profile image [expired user #8717] posted 9 years ago Permalink
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 smile
[expired user #9112]'s profile image [expired user #9112] posted 9 years ago Permalink
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.

cplummer41's profile image cplummer41 posted 2 years ago Permalink

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

Please login to leave a reply, or register at first.