The Error
"No mapping for the Unicode character exists in the target multi-byte code page"
The Scenario
I setup a cron job that my live server runs every X iteration of time using roughly the following terminal command:
mysqldump -u [user] '-p[password]' --host='[host]' --port='[port]' --default-character-set=utf8mb4 [db] > /[db]/[path]/[here]/db_file.sql 2>&1 --databases [database name]
I couldn't import the data from those however automated exports (via HeidiSQL → Run SQL File...) work just great. I kept getting the following error message:
I eventually determined that while I have the character set defined the file output from mysqldump was ANSI! So I loaded the SQL file from mysqldump (the one from the cron job) in Notepad++ and converted it to UTF-8. Suddenly this error in HeidiSQL stopped and I could run the file just fine.
The Question
How do I force mysqldump to output the file itself as UTF-8?
I've gone through the documentation here:
https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
And searched for all instances of UTF-8, UTF8, ANSI and more miscellaneous terms.
Now after some investigating I managed to get mysqldump to at least output the file to be encoded as UTF-8 (as determined by Notepad++ using the following syntax:
/usr/bin/mysqldump -u [user] \'-p[password]\' --default-character-set=utf8mb4 [database name] --result-file=\'/[db path]/[db file].sql\' 2>&1 --databases [database name]
Now with the file output as UTF-8 I'm still getting this error. There are literally less than a dozen total results for "mysqldump" and this error (in quotes) on Google and half of them are on Asian websites that won't load.
Here is the top of the file output for the SQL files:
====================================
-- MySQL dump 10.19 Distrib 10.3.39-MariaDB, for Linux (x86_64)
--
-- Host: localhost Database: [database name]
-- ------------------------------------------------------
-- Server version 10.3.39-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Current Database: `[database name]`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `[database name]` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci */;
====================================
Suggestions please?