MariaDB errors using utf8mb4

Question

I am trying to create a database using the utf8mb4 character set and utf8mb4_unicode_ci collation. However, I don't seem to be able to insert unicode characters into my tables.

What I have done:

SET NAMES utf8mb4;
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE mydb;
CREATE TABLE test (val VARCHAR(16));
INSERT INTO test (val) VALUES ("á");

ERROR 1366 (22007): Incorrect string value: '\xA0' for column `mydb`.`test`.`val` at row 1

If I don't use SET NAMES utf8mb4;, then I can insert the "á" character without issue.

These are my default character set variables:

show variables like 'char%'; show variables like 'collation%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | cp850                                         |
| character_set_connection | cp850                                         |
| character_set_database   | utf8mb4                                       |
| character_set_filesystem | binary                                        |
| character_set_results    | cp850                                         |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.5\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.000 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | cp850_general_ci   |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8_general_ci    |
+----------------------+--------------------+
3 rows in set (0.000 sec)

And after using SET NAMES:

show variables like 'char%'; show variables like 'collation%';
+--------------------------+-----------------------------------------------+
| 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     | utf8                                          |
| character_set_system     | utf8                                          |
| character_sets_dir       | C:\Program Files\MariaDB 10.5\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.000 sec)

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
| collation_database   | utf8mb4_unicode_ci |
| collation_server     | utf8_general_ci    |
+----------------------+--------------------+
3 rows in set (0.000 sec)

How can I fix this issue so I can insert characters in the utf8mb4 character set?

Answer 1

If you are using Windows cmd, the command "chcp" controls the "code page". chcp 65001 provides utf8, but it needs a special charset installed, too. To set the font in the console window: Right-click on the title of the window → Properties → Font → pick Lucida Console

Answer 2

Your text (or .sql) file itself is encoded in cp850 and not in utf-8.

You can see that encoded value is a single byte - UTF-8 encoding should be at least 2 bytes.

In order to use SET NAMES utf8mb4; command, your file needs to be converted to utf-8. Some advanced editors allow that, and even windows notepad can save a text file as utf-8 in modern versions.