import geonames allCountries.txt into MySQL 5.7 using LOAD INFILE - ERROR 1300 (HY000)

Question

I'm trying to import the allCountries.txt file from http://download.geonames.org into MySQL 5.7 using LOAD INFILE but getting the following error:

ERROR 1300 (HY000): Invalid utf8 character string: ''Afikanisitani,'Apekanikana,A Phu Han (Afghanistan),A Phú Hãn '

procedure:
- download & unzip the allCountries.zip from http://download.geonames.org/export/dump/
- create a table in mysql / phpmyadmin / MySQLWorkbench for the import:

CREATE TABLE `geoname` (
 `geonameid` int,
 `name` varchar(200),
 `asciiname` varchar(200),
 `alternatenames` varchar(4000),
 `latitude` float,
 `longitude` float,
 `feature_class` char(1),
 `feature_code` varchar(10),
 `country_code` varchar(2),
 `cc2` varchar(60),
 `admin1_code` varchar(20),
 `admin2_code` varchar(80),
 `admin3_code` varchar(20),
 `admin4_code` varchar(20),
 `population` int,
 `elevation` int,
 `dem` int,
 `timezone` varchar(40),
 `modification_date` date,
 `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY
) CHARACTER SET utf8;

- run the following command to import the .txt file:
LOAD DATA LOCAL INFILE '/location/of/my/allCountries.txt' INTO TABLE geoname;

This procedure worked fine in MySQL 5.6 - but fails with the aforementioned error in MySQL 5.7.13

Answer 1

(Caution: TMI coming...)

There are definitely utf8mb4 characters in that data.

The LOAD DATA needed CHARACTER SET utf8mb4.

The alternatenames column has 46 cases of 4-byte UTF-8 codes. Here's one:

mysql> SELECT * FROM allcountries WHERE geonameid = 6962506\G
*************************** 1. row ***************************
        geonameid: 6962506
             name: Shidanbian
        asciiname: Shidanbian
   alternatenames: Shidanbian,shi dan pian,石旦𡎚
         latitude: 31.08403
        longitude: 107.27187
    feature_class: P
     feature_code: PPL
     country_code: CN
              cc2:
      admin1_code: 32
      admin2_code:
      admin3_code:
      admin4_code:
       population: 0
        elevation: 0
              dem: 378
         timezone: Asia/Shanghai
modification_date: 2014-10-02

I found such rows with WHERE HEX(alternatenames) REGEXP '^(..)*F0'

The last character is HEX F0A18E9A, which needs utf8mb4.

Another example is id 281184, where 𐌹𐌰𐌹𐍂𐌿𐍃𐌰𐌿𐌻𐍅𐌼𐌰 is the "Gothic" writing for Jerusalem.

FYI, here is the table and the LOAD DATA I used:

CREATE TABLE AllCountries (
    geonameid MEDIUMINT UNSIGNED NOT NULL  COMMENT "integer id of record in geonames database",
    name     VARCHAR(200) CHARACTER SET utf8mb4  COMMENT "name of geographical point (utf8) varchar(200)",
    asciiname  VARCHAR(200)    COMMENT "name of geographical point in plain ascii characters, varchar(200)",
    alternatenames TEXT CHARACTER SET utf8mb4   COMMENT "alternatenames, comma separated, ascii names automatically transliterated, convenience attribute from alternatename table, varchar(10000)",
    latitude   DECIMAL(7,5)    COMMENT "latitude in decimal degrees (wgs84)",
    longitude  DECIMAL(8,5)    COMMENT "longitude in decimal degrees (wgs84)",
    feature_class CHAR(1)      COMMENT "see http://www.geonames.org/export/codes.html, char(1)",
    feature_code  VARCHAR(10)  COMMENT "see http://www.geonames.org/export/codes.html, varchar(10)",
    country_code  CHAR(2)      COMMENT "ISO-3166 2-letter country code, 2 characters",
    cc2          VARCHAR(200)  COMMENT "alternate country codes, comma separated, ISO-3166 2-letter country code, 200 characters",
    admin1_code  VARCHAR(20)   COMMENT "fipscode (subject to change to iso code), see exceptions below, see file admin1Codes.txt for display names of this code; varchar(20)",
    admin2_code  VARCHAR(80)   COMMENT "code for the second administrative division, a county in the US, see file admin2Codes.txt; varchar(80) ",
    admin3_code  VARCHAR(20)   COMMENT "code for third level administrative division, varchar(20)",
    admin4_code  VARCHAR(20)   COMMENT "code for fourth level administrative division, varchar(20)",
    population  DECIMAL(11,0)  COMMENT "bigint (8 byte int) ",
    elevation  SMALLINT UNSIGNED  COMMENT "in meters, integer",
    dem        SMALLINT UNSIGNED  COMMENT "digital elevation model, srtm3 or gtopo30, average elevation of 3''x3'' (ca 90mx90m) or 30''x30'' (ca 900mx900m) area in meters, integer. srtm processed by cgiar/ciat.",
    timezone   VARCHAR(40)     COMMENT "the timezone id (see file timeZone.txt) varchar(40)",
    modification_date DATE     COMMENT "date of last modification in yyyy-MM-dd format",
    PRIMARY KEY(geonameid)
) ENGINE=InnoDB DEFAULT CHARACTER SET ascii COMMENT 'http://download.geonames.org/export/dump/';

LOAD DATA INFILE 'C:/htdocs/misc/allcountries.csv'
    INTO TABLE allcountries
    CHARACTER SET utf8mb4
    FIELDS TERMINATED BY "\t"
    LINES TERMINATED BY '\n';

Note the careful picking of ascii vs utf8mb4 for charset.

A SHOW WARNINGS indicated that that is still not 'correct':

Incorrect integer value: '' for column 'elevation' at row ...

That can be solved via use of an @variable, and a SET in the LOAD DATA.

You were stopped at least by Afghanistan in Gothic: 𐌰𐍆𐌲𐌰𐌽𐌹𐍃𐍄𐌰𐌽. That's almost readable!

(That was fun.)

Answer 2

Not sure what exactly changed between MySQL 5.6 and 5.7 but it has something to do with the default character_set and collation in 5.7

Based on this Q&A I updated the default character set to utf8mb4 which then enabled me to load the allCountries.txt file without problems

See this answer to see what your default character set / collation is.

Login to mysql in terminal with mysql -u root -p and run the following query:
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

The default MySQL 5.7.13 response looked like:

+--------------------------+-------------------+
| Variable_name            | Value             |
+--------------------------+-------------------+
| character_set_client     | utf8              |
| character_set_connection | utf8              |
| character_set_database   | latin1            |
| character_set_filesystem | binary            |
| character_set_results    | utf8              |
| character_set_server     | latin1            |
| character_set_system     | utf8              |
| collation_connection     | utf8_general_ci   |
| collation_database       | latin1_swedish_ci |
| collation_server         | latin1_swedish_ci |
+--------------------------+-------------------+

(Working on OS X) I then needed to add a my.cnf file to /etc/my.cnf
- you can copy it :
sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf
- assign my.cnf to _mysql or it won't be able to read it (common problem if a my.cnf file exists but is either owned by root or has too permissive permissions)
sudo chown _mysql /etc/my.cnf
- update the config file to include the following (in the relevant sections - add if not there)

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

- restart mysql (following commands are for OS X):

sudo launchctl unload -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist
sudo launchctl load -F /Library/LaunchDaemons/com.oracle.oss.mysql.mysqld.plist

- run the same SHOW VARIABLE command and the results are

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8               |
| character_set_connection | utf8               |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8               |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8_general_ci    |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

...now the allCountries.txt file load should work properly.