Unable to translate Unicode character \\uDCC9 at index 5 to specified code page

Question

I'm using MariaDB (and Adminer)

MySQL version: 5.5.5-10.10.2-MariaDB-1:10.10.2+maria~ubu2204 through PHP extension MySQLi

And this is the show create table Pages:

CREATE TABLE `Pages` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Guid` uuid NOT NULL DEFAULT uuid(),
  `Name` varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `AcceptsComment` bit(1) NOT NULL DEFAULT b'1',
  `Key` varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT uuid(),
  `IsVital` bit(1) DEFAULT NULL,
  `Slug` varchar(400) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT uuid(),
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Pages_Unique_Guid` (`Guid`),
  UNIQUE KEY `IX_Pages_Unique_Key` (`Key`),
  UNIQUE KEY `IX_Pages_Unique_Slug` (`Slug`),
  CONSTRAINT `Ck_Pages_NonEmptyGuid` CHECK (`Guid` <> '00000000-0000-0000-0000-000000000000')
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

I'm using Bogus to insert some fake data using MySqlBulkCopy. But when I call bulkCopy.WriteToServer(table), I get this error:

Unable to translate Unicode character \uDCC9 at index 5 to specified code page.

I realized that \uDCC9 is a � character.

However, I use UTF for all of my columns.

I can't understand why this happens. What have I done wrong?

Answer 1

"Surrogates" are not handled in utf8mb3; you need utf8mb4.

ALTER TABLE pages CONVERT TO CHARACTER SET utf8mb4
                  COLLATE utf8mb4_unicode_520_ci;`