Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MySql variable in query - collation error 1267 Illegal mix of collations

gismofx's profile image gismofx posted 2 weeks ago in General Permalink

I'm not sure if this is a MySql/MariaDB error or a HeidiSQL error.

my varchar columns and tables are all collated as utf8mb4_unicode_520_ci

I have a query like this with a variable:

SET @Id = '6B1306DB-AA53-DE45-8000-68C37A9EE35F';
SELECT * FROM myTable WHERE ForeignId = @Id;

I get a collation error:

SQL Error (1267) Illegal mix of collations
(utf8mb4_unicode_520_ci,IMPLICIT) and
(utf8mb4_general_ci,IMPLICIT) for operation '='

When I replace the variable with direct string, the query works fine:
SELECT * FROM myTable WHERE ForeignId = '6B1306DB-AA53-DE45-8000-68C37A9EE35F'

Any ideas?

1 attachment(s):
  • collation
ansgar's profile image ansgar posted 2 weeks ago Permalink

The connection itself runs with utf8mb4_general* collation, and your column has a different one (utf8mb4_unicode_520_ci).

You can either define the @Id's collation so it matches the connection:

SET @Id = '6B1306DB-AA53-DE45-8000-68C37A9EE35F' COLLATE utf8mb4_unicode_520_ci;
SELECT * FROM myTable WHERE ForeignId = @Id;

... or, if your tables on the server all have that collation, set the server's collation option its my.ini (or my.cnf):

[mysqld]
collation-server = utf8mb4_unicode_520_ci

That should affect each HeidiSQL session, and so should your initial query work without further changes.

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




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.