Insert gives error code 1366 even though charset is utf8mb4

Question

I am trying to manually copy a table from one server to another. I used mysqldump to export the table. The import fails with the following error:

ERROR 1366 (22007) at line 6: Incorrect string value: '\xF0\x9D\x97\x96\xF0\x9D...' for column `MYDB`.`MYTABLE`.`field` at row 8142

I read everywhere that this is usually an issue with charset and collation. However, I have checked and both servers have the same charset and collation.

Here is the command I used to export my table:

root@master:/var/# mysqldump -uMYUSER -pMYPASSWORD MYDB MYTABLE > MYTABLE_bkp/MYTABLE.sql

Here is the table on the first server (data source):

MariaDB [MYDB]> show create table MYTABLE;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| MYTABLE | CREATE TABLE `MYTABLE` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `sub_id` int(10) unsigned NOT NULL,
  `field` varchar(5000) DEFAULT NULL,
  `field_h` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sub_id`),
  KEY `i_opti_field_id` (`id`),
  KEY `i_opti_field_h` (`field_h`),
  KEY `i_opti_field_did` (`sub_id`)
) ENGINE=ROCKSDB AUTO_INCREMENT=3594408836 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
 PARTITION BY HASH (`id`)
PARTITIONS 50 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.020 sec)

Here is the command I used to import the data to the second server:

root@slave1:/var/db_backup# mariadb -uMYUSER -pMYPASSWORD MYDB < MYTABLE.sql
ERROR 1366 (22007) at line 6: Incorrect string value: '\xF0\x9D\x97\x96\xF0\x9D...' for column `MYDB`.`MYTABLE`.`field` at row 8142

And here is the table on the second server (data destination):

MariaDB [MYDB]> show create table MYTABLE;
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                          |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| MYTABLE | CREATE TABLE `MYTABLE` (
  `id` bigint(20) unsigned NOT NULL,
  `sub_id` int(10) unsigned NOT NULL,
  `field` varchar(5000) DEFAULT NULL,
  `field_h` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`,`sub_id`)
) ENGINE=ROCKSDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
 PARTITION BY HASH (`id`)
PARTITIONS 50 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.019 sec)

I have tried to change the collation of the table (dumping then re-creating the table) on the second server to utf8mb4_unicode_ci but it did not help (same error).
I have also tried to change the charset/collation of the field explicitly to utf8mb4 and utf8mb4_unicode_ci but it did not help either.

ALTER TABLE MYTABLE CHANGE column field VARCHAR(5000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

My MariaDB version is the same on both servers:

mariadb  Ver 15.1 Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

In case you wonder, there are a few differences between source and target tables. I removed 3 keys to speed up import. I also removed the AUTO_INCREMENT option as I don't need it on the target table.
Just so you know everything, the second server is a a slave, and I am trying to rebuild this table after an issue.
My alternative right now is to rebuild the whole slave database with mariabackup as per documentation, but it's massive (and used in production) so I'd like to avoid this.

Answer 1

Actually I needed to set the charset for the connection too. I thought that because I was using the mariadb command, there was no connection, but that was very naïve.

mariadb -uMYUSER -pMYPASSWORD --default-character-set=utf8mb4 MYDB < MYTABLE.sql

I'll leave the question as is because it might help someone some day.