0

I'm using SuiteCRM 7 with a MySQL 8.0.31 database. I have taken steps to migrate the MySQL database to accept utf8mb4 characters which works well when entering in, say, emojis into table data either with the SuiteCRM web frontend or HeidiSQL.

I am now trying to create a report that uses an emoji in a column alias. SuiteCRM constructs queries that look like:

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

and the MySQL server responds with:

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

A similar query (that omits the emoji) works fine:

SELECT count(*) c FROM (SELECT `cases`.id AS 'ID_test0', `cases`.id AS 'cases_id' FROM `cases`  WHERE cases.deleted = 0 ) AS n;
  • Is there a setting I can change on the MySQL server (AWS RDS-based) to make this query work as-is?
  • or is there a way I should reformat the query to make it work?
  • or is this a shortcoming of MySQL and emojis or other utf8mb4-specific characters won't work in column aliases?

Thanks!

I have tried configuring MySQL to generate general logs but I did not see any useful information. I also looked into the error code 3854 and did not find anything useful so far. I double-checked that I could enter in emojis into table data successfully.

user3783243
  • 5,368
  • 5
  • 22
  • 41
2xaronl
  • 3
  • 3

1 Answers1

0

Alas, I think it is not possible. Certain system tables (such as the one including column names) is declared CHARACTER SET utf8 (aka `utf8mb3). Emojis need utf8mb4.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Isn't this problematic for some Chinese characters? I'm surprised to hear that it might not be supported. – 2xaronl Feb 02 '23 at 19:02
  • @2xaronl - Yes, the same problem occurs for any 4-byte Chinese characters. Most, not all, Chinese characters take only 3 bytes in UTF-8, hence are compatible with MySQL's system tables where names of databases, tables, columns, etc are stored. – Rick James Feb 02 '23 at 19:17