We have a query that CONCATs a bunch of values together, and then searches against it, and we're getting this error:
Illegal mix of collations (utf8mb4_bin,NONE) and (utf8mb4_general_ci,COERCIBLE) for operation 'like'
The strange thing is that on one RDS instance the error does not occur and on another it does. Both instances are running MariaDB 10.3, and i've checked and both databases are default charset utf8mb4 and default collation utf8mb4_unicode_ci.
To troubleshoot, i've run a modified query that just calls CHARSET() and COLLATION() on the CONCAT'ed value. And low and behold, on the instance that's throwing an error that results in CHARSET=utf8mb4, COLLATION=utf8mb4_bin and on the instance that the query runs ok on that results in CHARSET=utf8mb4, COLLATION=utf8mb4_unicode_ci.
The CONCAT looks like this:
CONCAT_WS(', '
,CASE WHEN SUM((`table1`.`View`)) <> 0 THEN CONCAT('View(', CAST(SUM((`table1`.`View`)) AS CHAR) ,')') ELSE NULL END
,CASE WHEN SUM((`table1`.`Edit`)) <> 0 THEN CONCAT('Edit(', CAST(SUM((`table1`.`Edit`)) AS CHAR) ,')') ELSE NULL END
,CASE WHEN SUM((`table1`.`Add`)) <> 0 THEN CONCAT('Add(', CAST(SUM((`table1`.`Add`)) AS CHAR) ,')') ELSE NULL END
, CONCAT('Static string: ',(`table2`.`Name`)))
The tables on both are identical (from my inspection :-/), table1.View/Add/Edit are bit(1) types and table2.Name is a varchar(100) with charset=utf8mb4 and collation=utf8mb4_unicode_ci.
I can't figure out how the problematic instance ends up with utf8mb4_bin as the collation for the string result of the concat!
