Heidi changes the collation on connecting to the server
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.
Btw, in newer HeidiSQL versions, the utf8mb4 character set is used.
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
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.
SET @@session.collation_connection = 'utf8mb4_unicode_ci';
Set a startup script under Session Manager, Advanced:
Please login to leave a reply, or register at first.