No mapping for the Unicode character exists in the target multi-byte code page

JAB Creations's profile image JAB Creations posted 4 months ago in General Permalink

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?

JAB Creations's profile image JAB Creations posted 4 months ago Permalink

There is no edit option? sigh

So exporting from HeidiSQL makes an SQL file that I can then import via HeidiSQL. It's in the terminal via PuTTY and in PHP on my server running the mysqldump command that outputs bad SQL exports.

Also: the forum software screws with the syntax encoding on here. The commands are correct syntax wise. I just don't know what else I can do to get mysqldump to give me exports that I can then import in using HeidiSQL.

ansgar's profile image ansgar posted 4 months ago Permalink

So the import in HeidiSQL causes crippled characters? What encoding did you select when opening the file?

JAB Creations's profile image JAB Creations posted 4 months ago Permalink

Hi Ansgar and thank you! So I just went to the Run SQL file modal and by default it's set to UTF-8. I tried it again to confirm the same issue and yes, the exact same error.

Then, because I wasn't previously aware of the option, set it to Auto Detect and it accepted the file.

So why would UTF-8 fail with a UTF-8 file?

I'm just trying to make sure that the files my cron job are exporting are importable for obvious reasons. Thank you!

ansgar's profile image ansgar posted 4 months ago Permalink

Is the UTF-8 file importable via "mysql.exe", or does it create crippled characters in your tables?

JAB Creations's profile image JAB Creations posted 4 months ago Permalink

Everything looked fine after importing to a new database (and editing the SQL file to ensure it went in to the temporary database). What information would be useful to send your way?

Please login to leave a reply, or register at first.