3

I'm using MySQL 5.6.34 with the following settings: 

"character_set_client","utf8mb4"
"character_set_connection","utf8mb4"
"character_set_database","utf8mb4"
"character_set_filesystem","utf8mb4"
"character_set_results","utf8mb4"
"character_set_server","utf8mb4"
"character_set_system","utf8"
"collation_connection","utf8mb4_general_ci"
"collation_database","utf8mb4_unicode_ci"
"collation_server","utf8mb4_unicode_ci"

3 byte emojis seem to work correctly within a trigger but 4 byte do not. They just become a '?'. Is there a different setting that should be used or is this just not supported within a trigger?

JEMA
  • 31
  • 1

2 Answers2

0

As in the github issue was recommended to change the settings on the following:

connection_charset: utf8mb4
connection_collation: utf8mb4_unicode_ci
charset: utf8mb4
collation: utf8mb4_unicode_ci

I suppose you could change 

"collation_connection","utf8mb4_general_ci"

on

"collation_connection","utf8mb4_unicode_ci"
Nikita Zavyalov
  • 34
  • 4
0

Collations are irrelevant when it comes to 3-byte encoding versus 4-byte.

Check SHOW CREATE TABLE to see if the column can accept utf8mb4.

Check SHOW CREATE TRIGGER to see what the charset was when you created the trigger. (This is the likely cause of your trouble.) Example:

mysql> SHOW CREATE TRIGGER t3trig\G
*************************** 1. row ***************************
               Trigger: t3trig
              sql_mode: 
SQL Original Statement: CREATE DEFINER=`root`@`127.0.0.1` trigger t3trig before insert on t3 for each row ...
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

This trigger might get the same problem you have.

The solution is probably to set the desired charset and recreate the trigger:

SET NAMES utf8mb4;
DROP TRIGGER ...;
CREATE TRIGGER ...;

And, before you ask, there is not an 'alter trigger': https://dba.stackexchange.com/a/78442/1876

Rick James
  • 80,479
  • 5
  • 52
  • 119