MySQL Forums
Forum List  »  Views

strange utf8mb4 issue with views
Posted by: Jeff Stockett
Date: October 16, 2017 10:10AM

I have a strange problem whereby when creating views, MySQL 5.7 is "automagically" inserting convert() around calls to group_concat and lpad that are parameters to concat - for example:

CREATE VIEW `AccountDTOHelper05EmailByType` AS select `u`.`AccountID` AS `AccountID`,concat(`u`.`Email`,' (',group_concat(`u`.`Type` separator ','),')') AS `Email` from `AccountDTOHelper06EmailUnion` `u` group by `u`.`AccountID`,`u`.`Email`;

If I look at it in HeidiSQL after the statement is executed - I can see that it has been changed to:

CREATE VIEW `AccountDTOHelper05EmailByType` AS select `u`.`AccountID` AS `AccountID`,concat(`u`.`Email`,' (',convert(group_concat(`u`.`Type` separator ',') using utf8mb4),')') AS `Email` from `AccountDTOHelper06EmailUnion` `u` group by `u`.`AccountID`,`u`.`Email`;

Note the convert( using utf8mb4) that got added around the group_concat.

The only problem with this change is that it causes problems when doing certain types of queries against that column - namely the dreaded:

Illegal mix of collations (utf8mb4_bin,NONE) and (utf8_general_ci,COERCIBLE) for operation 'like'

My server and db are setup to handle utf8mb4 correctly I believe. Further, if I alter the view in HeidiSQL back to the original (i.e. remove the convert() part) - the change sticks, the view works, and the illegal mix of collations error goes away.

Anyone seen this before and know the fix. Given the fact that HeidiSQL can successfully alter the view to be like I originally had it - I'm thinking it must be setting something I'm not setting when I programmatically execute the create on all my DTO views.

Options: ReplyQuote

Written By
strange utf8mb4 issue with views
October 16, 2017 10:10AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.