Pgloader: utf8mb4 emojis from mysql are not migrating correctly

[nibty]

I'm trying to migrate a mysql database which uses utf8mb4 charset on tables. After the migration all my emoji characters are incorrectly encoded. Any ideas?

Example table:

CREATE TABLE `message` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `text` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

pgloader command:

load database
     from      mysql://username:password@localhost:3306/database
     into postgresql://username:password@localhost:5432/database

 WITH include drop, create tables, truncate, create indexes, reset sequences, foreign keys

 CAST type datetime to timestamptz drop default drop not null using zero-dates-to-null,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer
;

[dimitri]

The MySQL driver should be taking utf8mb4 as just utf8 data. Did you have any encoding errors in your logs when doing the data load? You might want to try playing around with the DECODING clause to force a particular way to read in the data, but I'm not sure what encoding to pass it apart from enforcing utf8 in case the metadata got it wrong, which is known to happen with the MySQL protocol.

[nibty]

I'm not seeing any errors related to encoding. I tried the following without any luck.
-- DECODING TABLE NAMES MATCHING 'message' AS utf8
-- DECODING TABLE NAMES MATCHING 'message' AS utf8mb4

[nibty]

Ok I figured it out. The problem was not with pgLoader, but rather with a previous mysql dump I did before running pgLoader.

Apparently, mysql fails to import utf8mb4 dumps correctly. I had to do a
mysqldump --hex-blob --default-character-set=binary instead of mysqldump --default-character-set=utf8mb4

[fcovely]

Great stuff... there are several other solutions out there on import/export that I was unable to make work. Thanks nibty this worked great! In my situation I inherited a mysql db that was set for latin1_swedish but had smiley chars sent over from iPhone and Android. Those appear to be happily stored in a varchar field but if you don't export with --hex-blob and charset binary, they don't come over on an import. Thanks again!