MySQL error when trying to name a Report column label as a string including an emoji

SuiteCRM Forum - English Language SuiteCRM General Discussion
#1

I wanted to change the label of a column in a report to an emoji to save space when using the report in a Dashboard Dashlet. After saving the report with an emoji in the column label I get no line item results. After checking suitecrm.log I see the following fatal error:

 Query Failed: SELECT `cases`.id AS 'ID_😀0', `cases`.id AS 'cases_id' FROM `cases`  WHERE cases.deleted = 0  LIMIT 0,60: MySQL error 3854: Cannot convert string 'ID_\xF0\x9F\x98...' from utf8mb4 to utf8mb3

I have MySQL 8.0.31 running in AWS RDS which is what SuiteCRM 7 is using. I recently set up the database to accept utf8mb4 and tested entering emojis into table data. For instance, I can successfully copy-paste an emoji into an Account name and save it to the database table, recall that data in a detail view and list view without issue.

For some reason, MySQL won’t accept the query:

SELECT count(*) c FROM (SELECT `cases`.id AS 'ID_😀0', `cases`.id AS 'cases_id' FROM `cases`  WHERE cases.deleted = 0 ) AS n;

but the following query is just fine:

SELECT count(*) c FROM (SELECT `cases`.id AS 'ID_test0', `cases`.id AS 'cases_id' FROM `cases`  WHERE cases.deleted = 0 ) AS n;

I know this isn’t specifically a SuiteCRM issue; I get the error when connected directly to the MySQL database with HeidiSQL. Since using emojis as column labels seems like a valid and useful thing to do in SuiteCRM I’m hoping someone here can shed some light on how this can be resolved.

Can MySQL be configured to accept the emoji in the query?

Why is my MySQL instance trying to convert the query (or results) to utf8mb3? Can that be changed?

Will this never work and SuiteCRM needs to re-encode the emojis so they’re not sent to MySQL in a query?

Thanks!

#2

Search the Github repo, I think there is an issue there about this…

#3

I found this one: Error importing mail into project, containing UTF8mb4 characters · Issue #2915 · salesagility/SuiteCRM · GitHub but that issue appears to be different in that it’s a different error and it involves adding data to the database.

I don’t see any issues in the Github repo that capture my issue: using a utf8mb4-specific character in a column alias in a MySQL query causes an error. I don’t believe this is really even a SuiteCRM issue as I can replicate the issue with just MySQL server and a client (HeidiSQL).

I posted a Stackoverflow question for it here: utf 8 - How to use emojis (or other utf8mb4) characters in a column alias of a MySQL query? - Stack Overflow

I don’t use Asian character sets that require utf8mb4 but I believe this issue would also affect them, effectively breaking SuiteCRM reports if a field name contains one of those characters. Perhaps this is just an issue with my MySQL server configuration though? I triple-checked the settings and I didn’t see anything that seemed like it would cause this issue.

#4

Ah. I see the difference now, thanks for the explanation. I don’t know the answer. I will follow your SO question.

#5

According to the feedback I got on Stackoverflow it looks like MySQL does not support using utf8mb4-specific characters in queries by design.

I think this means that SuiteCRM needs to create a temporary utf8mb3-based string to substitute when performing the query against the MySQL database and then swap the utf8mb4 string back in for display.

1 Like