Why can't I store 4 byte emojis in this mysql field?

Question

I have a database in which I require that only two fields in one table allow for 4 byte emojis to be stored. I did this (obviously with the correct table and column names):

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I know it worked, because when I do show create table chatbots_proposalarea; it shows me this:

CREATE TABLE `chatbots_proposalarea` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_bin NOT NULL,
  `proposal` varchar(1500) COLLATE utf8mb4_bin DEFAULT NULL,
  `candidate_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `chatbots_proposalare_candidate_id_6465160e_fk_chatbots_` (`candidate_id`),
  CONSTRAINT `chatbots_proposalare_candidate_id_6465160e_fk_chatbots_` FOREIGN KEY (`candidate_id`) REFERENCES `chatbots_candidate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

The fields "name" and "proposal" do seem to have the uft8mb4_bin collation, and it showed no error when running the commands. However, when I try and save a value there such as "Seguridad ", it gives me the error

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9F\x8D\x9E' for column 'name' at row 1

Any help in discovering what am I missing would be very appreciated.

NOTES

This is on a Django project, and mounted on an Ubuntu server, and the SQL version is mysql Ver 14.14 Distrib 5.7.28, for Linux (x86_64) I don't see why that would make a difference, but the same happens when I update directly to the database by doing

UPDATE chatbots_proposalarea SET name='Seguridad ' where id=1;

A solution was recommended, but that one depended on the usage of triggers, and that was the cause of the issue, which isn't my case.

UPDATE

If it shows any important information, when I run show variables where Variable_name like 'character\_set\_%' or Variable_name like 'collation%'; I get the following:

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_general_ci |
| collation_database       | utf8mb4_general_ci |
| collation_server         | utf8mb4_general_ci |
+--------------------------+--------------------+

I changed these values to the same that a coworker has in one of his projects, in which he also requires 4 byte emojis to be stored.

Does this answer your question? [Trouble inserting 4-Byte UTF-8 characters / emoji into MySQL Database when insert trigger is active](https://stackoverflow.com/questions/45549840/trouble-inserting-4-byte-utf-8-characters-emoji-into-mysql-database-when-inser) — Tim, Feb 24 '21 at 21:25
I see a lot of "utf8" in those system variables. It should all be "utf8mb4". — miken32, Feb 24 '21 at 22:33
Yeah, mine are all 'utf8mb4' except for `character_set_system` which cannot be changed. — Bill Karwin, Feb 24 '21 at 22:36
I changed the variables and it remains the same, I updated the question — Iván Flores Vázquez, Feb 24 '21 at 23:34
You need to re-create the database after you changed the variables.You can create a new one to test. — ElapsedSoul, Feb 25 '21 at 06:17

score 0 · Answer 1 · answered Feb 28 '21 at 07:04

0

That's not the table's fault; it is the connection. Something like:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        ...
        'OPTIONS': {
                    'charset': 'utf8mb4',
                    'use_unicode': True, },
    },
}

answered Feb 28 '21 at 07:04

Rick James

135,179
13
127
222

Although the same happens when I go directly and make an UPDATE statement directly on the mysql console, and that wouldn't be the case, or would it? Not just when I update through Django. – Iván Flores Vázquez Mar 01 '21 at 18:10
Each connection controls its own charset (etc) options. What I showed is for Django. The commandline "mysql" tool can use `SET NAMES utf8mb4;` before running queries. – Rick James Mar 01 '21 at 21:04

Why can't I store 4 byte emojis in this mysql field?

1 Answers1