I can't load Spanish/German special characters

Question

Installed mysql-installer-community-8.0.11.0.msi this week, on a Windows 10 machine and am stuck loading Spanish names.

A row with Reynaldo Cantú fails with loader message:

ERROR 1300 (HY000) at line 4: Invalid utf8mb4 character string: 'Reynaldo Cant'

Same with upside-down question marks.

If I remove that row, all the other names with accented vowels such as Chacón, are truncated starting with the accented vowel.

All web searches on the subject say, use utf8mb4. So I went from not specifying a character set, to having added utf8mb4 everywhere I can think of. But the with same result.

My database is utf8mb4. My create table is DROP TABLE IF EXISTS T_JackArtist;

CREATE TABLE T_JackArtist( -- Table of people recorded  
    RID     INT PRIMARY KEY,        -- T_JackArtist unique row ID
    Name    VARCHAR(64)  NOT NULL  ,    -- Name of person recorded
    Note    VARCHAR(256)  NOT NULL, -- Note
    AKA1    VARCHAR(64)  NOT NULL,  -- Name of person recorded
    Track_Changed   DATETIME        NOT NULL DEFAULT Current_Timestamp,
    Track_Userid    VARCHAR(50)     NOT NULL DEFAULT 'Init'
) 
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

My MySql.exe load command is

"C:\Program Files\MySQL\MySQL Server 8.0\bin\Mysql.exe" Jack -h xxx8 -u xxx --password=xxx --batch --raw --local-infile --default-character-set=utf8 < T_JackArtist.cmd

with T_JackArtist.cmd

USE Jack;
SET NAMES 'utf8mb4';
SET foreign_key_checks = 0;
LOAD DATA LOCAL INFILE 'C:\\Dan\\Jack\\MySql\\Schema\\import\\T_JackArtist.txt' INTO TABLE T_JackArtist IGNORE 1 LINES ;

Where am I going wrong?

score 0 · Answer 1 · answered May 25 '18 at 20:53

In the LOAD DATA statement, include CHARACTER SET utf8mb4.

Make sure the bytes in the file are really utf8, not latin1:

ú hex in utf8/utf8mb4: C3FA
ú hex in latin1: FA

See also 'truncated' in Trouble with UTF-8 characters; what I see is not what I stored

I can't load Spanish/German special characters

1 Answers1