MySQL view - Illegal mix of collations

Question

I'll be very clear: What's the solution for create views in MySQL without have the damned Illegal mix of collations error.

My SQL code is like this (it has some portuguese words), and my database default collation is latin1_swedish_ci:

CREATE VIEW v_veiculos AS
SELECT
    v.id,
    v.marca_id,
    v.modelo,
    v.placa,
    v.cor,
    CASE v.combustivel
        WHEN 'A' THEN 'Álcool'
        WHEN 'O' THEN 'Óleo Diesel'
        WHEN 'G' THEN 'Gasolina'
        ELSE 'Não Informado'
    END AS combustivel,
    marcas.marca,
    /*I think that the CONCAT and COALESCE below causes this error, when the next line the view works fine*/
    CONCAT(marca, ' ', v.modelo, ' - Placa: ', v.placa, ' - Combustível: ', COALESCE(v.combustivel, 'Não informado')) AS info_completa
FROM veiculos v
LEFT JOIN
    marcas on(marcas.id = v.marca_id);

I think that the error cause is because I'm using coalesce and/or concat as the full error's description tells me: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'coalesce'

Answer 1

You may also use CAST() to convert a string to a different character set. The syntax is:

CAST(character_string AS character_data_type CHARACTER SET charset_name)

eg:

      SELECT CAST(_latin1'test' AS CHAR CHARACTER SET utf8);

alternative : use CONVERT(expr USING transcoding_name)

Answer 2

This is kind of old, but well I had this same error, As far as I know the Views does not have a collation, the tables does. So, if you get the "illegal mix..." is because your view is linking (comparing, whatever) 2 tables with different collation The thing is, if you create a table you can specify the collation, for instance

  CREATE TABLE IF NOT EXISTS `vwHotelCode_Terminal` (
`HOTELCODE` varchar(8)
,`TERMINALCODE` varchar(5)
,`DISTKM` varchar(6)
,`DISTMIN` varchar(3)
,`TERMINALNAME` varchar(50)
)ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci  ;

But if you don't, the default collation will be applied. So for me the defaul collation is utf8_unicode_ci so my tables will be created with this collation and I ended having some tables with utf8_spanish_ci and the ones I did not specify with utf8_unicode_ci

If you are exporting from one server to another one and the default collation is different, you are probably going to get the "illegal mix" message.

if you have views, phpmyadmin likes to create the tables of all the views and then the views. The tables are created without the collation so it takes the default one. Then, many times, when the view is created uses different collations.

Answer 3

That is actually a bug in MySQL.

Maybe you can update to the latest version of MySQL?

Answer 4

After searching around for a while and taking information from this answer, I found a hack that could be useful.

Simply check the default character set system default_character_set of your database with the below command:

SHOW VARIABLES LIKE "char%";

You'll see something like this:

mysql> SHOW VARIABLES LIKE "char%"; +--------------------------+----------------------------+ | Variable_name | Value | +--------------------------+----------------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | <-- | character_sets_dir | /usr/share/mysql/charsets/ | +--------------------------+----------------------------+

I just set the character_set_system which is nothing but default system character set. Copied the create code of the view and created a new view and that's all.

What happens here is the new view that you will create will use the new default character set that you defined for the system. Hence resolving the issue.

Just use below command to set the default character set

SET character_set_server = 'latin2';

This worked in my case.

NOTE: Alternatively you can change the character set of that view. That would also do the trick but I wasn't able to find the solution so I used this hack.

REFERENCE: Read more on Illegal Collation Mix on MariaDB.

A CITATION FROM Illegal Collation Mix on MariaDB:

If you encounter this issue, set the character set in the view to force it to the value you want.

Read more about Collation and Character Sets here.