How MySQL 5.7 Handles UTF8MB4 and the Load Data infile

utf8mb4 and the load data infileIn this post, I’ll discuss how MySQL 5.7 handles UTF8MB4 and the load data infile.

Many of my clients have told me that they do not like using the LOAD DATA INFILE statement and prefer to manually parse and load the data. The main reason they do it are issues with the character sets, specifically UTF8MB4 and the load data infile. This was surprising to me as nowadays everyone uses UTF8. MySQL 5.7 (as well as 5.6) has full support for UTF8MB4, which should fix any remaining issues (i.e., you can now load new emoji, like 🐬).

Last week I was investigating an interesting case where we were loading data and got the following error:

The load data statement:

The table uses the correct character set (global character set applied to all varchar fields):

The string looked like “Casa Nº 24”. So this should be N + U+00BA (MASCULINE ORDINAL INDICATOR, hex code: c2ba). When I do “less input.tsv”, it shows N<BA> 24. So why can’t MySQL load it?

After further investigation, we discovered the original encoding is not UTF8. WE found out by running:

So the code <BA> was misleading. Also, when I got the actual character from the file, it was just one byte (UTF8 for this character should be two bytes). When MySQL parsed the UTF8 input file, it found only the first part of the multibyte UTF8 code and stopped with an error.

The original character in hex is “ba”:

(0a is a carriage return, and “ba” is “masculine ordinal indicator”)

The UTF8 equivalent:

This is now two bytes (+ carriage return): c2ba

To solve the problem we can simply change the CHARACTER SET utf8mb4 to CHARACTER SET latin1 when doing a load data infile. This fixed the issue:

Another option will be to detect the character set encoding (iconv can do it) and convert to UTF8.

But it worked before…?

It worked a bit differently in MySQL 5.6:

MySQL 5.7 is more strict and doesn’t allow you to insert data in the wrong format. However, it is not 100% consistent. For some characters, MySQL 5.7 will also throw a warning if disabling strict SQL mode.

Another character that caused the same issue was xC9. When loading to MySQL 5.7 with the default sql_mode (ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION) it throws an error:

When disabling the strict mode it now defaults to warnings: