Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<'

[edimar]

Hello guys,

I create one view in MySQL and after that try use in Metabase with CUSTOM QUESTION and it is introducing the message below, but if I use the NATIVE QUERY is not happening the issue:

Illegal mix of collations (utf8mb4_unicode_ci,EXPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<'

This one happening when use the instructions SQL to create one column:

select 
CAST(IF(((TIME_FORMAT(`m`.`mov_ini_atividade`, '%H:%i') < '12:00')
                    AND (TIME_FORMAT(`m`.`mov_ter_atividade`, '%H:%i') > '12:00')),
                ((TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(`m`.`mov_ter_atividade`,
                                        `m`.`mov_ini_atividade`),
                                '%H:%i')) / (60 * 60)) - 1),
                (TIME_TO_SEC(TIME_FORMAT(TIMEDIFF(`m`.`mov_ter_atividade`,
                                        `m`.`mov_ini_atividade`),
                                '%H:%i')) / (60 * 60)))
            AS DECIMAL (10 , 2 )) AS `hs_produtiva`,
            placa
from 
`movem` `m`

Anybody already solve this one issue? I already adjust all the variables in Server MySQL but is not working.

Thank you all.

[flamber]

Hi @edimar
Well, probably one of the most annoying things about MySQL - collation.
You have a mix of utf8mb4_unicode_ci and utf8_general_ci
You should use utf8mb4_unicode_ci everywhere - never use utf8_general_ci (or any other utf8_...)

[edimar]

Hi @flamber, thank you for adjust my code in post:sweat_smile:
So I ajdust all the variables to "utf8mb4_unicode_ci" but is not working.

Thank you, I will continue check this one.

[flamber]

@edimar I couldn't read it, so thought I might as well adjust it

Remember that collation is not just variables, it's also the database and table and columns.

I really hate collation and it's probably one of the things that most people get wrong in their setup.
A good rule on a new setup is to only use utf8mb4, but it's a lot more difficult on an existing database, which might have a mix of collations and it's not always easy to convert them.

We even had to address this in the Metabase application database, since it was a common problem:
https://github.com/metabase/metabase/issues/10691

[edimar]

It is clear @flamber. But I change all the variable, tables and database to use the utf8mb4 but continue with same problems.

In the picture below it is showing just any tables, but I apply to all tables, variables and database, but not working yet.

Thank you for your time.

[edimar]

@flamber, In this case, what the your orientation? Create a new database? If create a New Database with collate correct, do you thing that it is possible to restore a backup from legacy database?

Thank you.

[flamber]

@edimar Try exporting your database (including Views) - that should show any charset or collation that might be bad in the SQL dump.

As for complete restore of MySQL - I would recommend that you have a look on stackoverflow.com for information about how to best handle that. It's way outside Metabase.

[edimar]

Hi @flamber I do export and import to another server and all right. Not showing nothing with error.
Import using mysql in terminal and was all ok.

My schema MySQL "metabase" not have VIEWs...

See the images Server Production and Server using to import the dump form database.

[flamber]

@edimar mysqldump is silly - you need to manually include views.
https://stackoverflow.com/questions/1658204/backing-up-views-with-mysql-dump

Try running this:

-- Set the database name to the Metabase metadata database - change it to match your db:
USE metabase;
-- Show system variables
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
-- Show metadata database/tables/columns
SELECT TABLE_SCHEMA,
       TABLE_NAME,
       CCSA.CHARACTER_SET_NAME AS DEFAULT_CHAR_SET,
       COLUMN_NAME,
       COLUMN_TYPE,
       C.CHARACTER_SET_NAME,
       C.COLLATION_NAME
  FROM information_schema.TABLES AS T
  JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
  JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
       ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
 WHERE TABLE_SCHEMA=SCHEMA()
   AND C.DATA_TYPE IN ('enum', 'varchar', 'char', 'text', 'mediumtext', 'longtext')
 ORDER BY TABLE_SCHEMA,
          TABLE_NAME,
          COLUMN_NAME;

[edimar]

I perform the query above @flamber.
See the result part, it is all right.

[flamber]

@edimar Okay, then run it on the database that you're trying to make a query to, not the Metabase application database.

[edimar]

Ok, but the query it is working well in the database not Metabase and the same query run very good using the Native Query in Metabase enviroment, but if I choose the Custom Question the view not appear.

Thank you

[flamber]

@edimar
So is the rest of the database (or View) using utf8 or utf8mb4?
Post "Diagnostic Info" from Admin > Troubleshooting, and which version of MySQL you're using.
Check your MySQL log for more details.