MySQL Error: Data Too Long For Column CHAR(5) - MariaDB 10.3.10

When inserting data from CSV to MariaDB 10.3, the following error is generated while trying to insert value

'00010'

into

(5)

column

ERROR 1406 (22001): Data too long for column 'nicSiegeUniteLegale' at row 38663

Here is MySQL command line :

LOAD DATA INFILE "/filePath/StockUniteLegale_utf8.csv" INTO TABLE erp_dev.sirene_UniteLegale
 FIELDS TERMINATED BY ','
 
IGNORE 1 ROWS
 
 (@vsiren,@vstatutDiffusionUniteLegale,@vunitePurgeeUniteLegale,@vdateCreationUniteLegale,@vsigleUniteLegale,@caractereEmployeurUniteLegalesexeUniteLegale,@vprenom1UniteLegale,@vprenom2UniteLegale,@vprenom3UniteLegale,@vprenom4UniteLegale,@vprenomUsuelUniteLegale,@vpseudonymeUniteLegale,@videntifiantAssociationUniteLegale,@vtrancheEffectifsUniteLegale,@vanneeEffectifsUniteLegale,@vdateDernierTraitementUniteLegale,@vnombrePeriodesUniteLegale,@vcategorieEntreprise,@vanneeCategorieEntreprise,@vdateDebut,@vetatAdministratifUniteLegale,@vnomUniteLegale,@vnomUsageUniteLegale,@vdenominationUniteLegale,@vdenominationUsuelle1UniteLegale,@vdenominationUsuelle2UniteLegale,@vdenominationUsuelle3UniteLegale,@vcategorieJuridiqueUniteLegale,@vactivitePrincipaleUniteLegale,@vnomenclatureActivitePrincipaleUniteLegale,@vnicSiegeUniteLegale,@veconomieSocialeSolidaireUniteLegale,@vcaractereEmployeurUniteLegale)

SET

 siren = nullif (@vsiren,''),
 statutDiffusionUniteLegale = nullif (@vstatutDiffusionUniteLegale,''),
 unitePurgeeUniteLegale = if (@vunitePurgeeUniteLegale ='true', 1, 0),
 dateCreationUniteLegale = nullif (@vdateCreationUniteLegale,''),
 -- Repeat above line for each column --
 nicSiegeUniteLegale = nullif (@vnicSiegeUniteLegale,''),
 economieSocialeSolidaireUniteLegale = nullif (@veconomieSocialeSolidaireUniteLegale,''),
 caractereEmployeurUniteLegale = nullif (@vcaractereEmployeurUniteLegale,'');

Here is table DDL (definition) :

CREATE TABLE sirene_UniteLegale (
  siren                                     NChar(9) COLLATE utf8_general_ci,
  statutDiffusionUniteLegale                Enum('O') CHARACTER SET utf8 COLLATE utf8_general_ci,
  unitePurgeeUniteLegale                    TinyInt(1),
  dateCreationUniteLegale                   Date,
  sigleUniteLegale                          NVarChar(20) COLLATE utf8_general_ci,
  sexeUniteLegale                           Enum('F', 'M') CHARACTER SET utf8 COLLATE utf8_general_ci,
  prenom1UniteLegale                        NVarChar(20) COLLATE utf8_general_ci,
  prenom2UniteLegale                        NVarChar(20) COLLATE utf8_general_ci,
  prenom3UniteLegale                        NVarChar(20) COLLATE utf8_general_ci,
  prenom4UniteLegale                        NVarChar(20) COLLATE utf8_general_ci,
  prenomUsuelUniteLegale                    NVarChar(20) COLLATE utf8_general_ci,
  pseudonymeUniteLegale                     NVarChar(100) COLLATE utf8_general_ci,
  identifiantAssociationUniteLegale         NChar(10) COLLATE utf8_general_ci,
  trancheEffectifsUniteLegale               Enum('NN', '01', '02', '03', '11', '12', '21', '22', '31', '32', '41', '42', '51', '52', '53') CHARACTER SET utf8 COLLATE utf8_general_ci,
  anneeEffectifsUniteLegale                 SmallInt(6),
  dateDernierTraitementUniteLegale          DateTime,
  nombrePeriodesUniteLegale                 TinyInt(4),
  categorieEntreprise                       Enum('PME', 'ETI', 'GE') CHARACTER SET utf8 COLLATE utf8_general_ci,
  anneeCategorieEntreprise                  SmallInt(6),
  dateDebut                                 Date,
  etatAdministratifUniteLegale              Enum('A', 'C') CHARACTER SET utf8 COLLATE utf8_general_ci,
  nomUniteLegale                            NVarChar(100) COLLATE utf8_general_ci,
  nomUsageUniteLegale                       NVarChar(100) COLLATE utf8_general_ci,
  denominationUniteLegale                   NVarChar(120) COLLATE utf8_general_ci,
  denominationUsuelle1UniteLegale           NVarChar(70) COLLATE utf8_general_ci,
  denominationUsuelle2UniteLegale           NVarChar(70) COLLATE utf8_general_ci,
  denominationUsuelle3UniteLegale           NVarChar(70) COLLATE utf8_general_ci,
  categorieJuridiqueUniteLegale             NChar(4) COLLATE utf8_general_ci,
  activitePrincipaleUniteLegale             NChar(6) COLLATE utf8_general_ci,
  nomenclatureActivitePrincipaleUniteLegale NChar(8) COLLATE utf8_general_ci,
  nicSiegeUniteLegale                       NChar(5) COLLATE utf8_general_ci,
  economieSocialeSolidaireUniteLegale       Enum('O', 'N') CHARACTER SET utf8 COLLATE utf8_general_ci,
  caractereEmployeurUniteLegale             Enum('O', 'N') CHARACTER SET utf8 COLLATE utf8_general_ci
) ENGINE=InnoDB AUTO_INCREMENT=1 ROW_FORMAT=DYNAMIC DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE sirene_UniteLegale COMMENT = '';

Here is the error generating line :

038684312,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,3,PME,2016,2017-02-28,A,,,SYND.COPR. 6 RUE HEROLD 75001   PARIS RE,,,,9110,81.10Z,NAFRev2,00010,,N

**Debug :** - Inserting the line alone works (bash)

$ head -n 1 StockUniteLegale_utf8.csv > test.csv
$ head -n 38663 StockUniteLegale_utf8.csv | tail -n 1 >> test.csv

- Exact same field in previous lines where inserted without error (bash)

$ head -n 38663 StockUniteLegale_utf8.csv | cut -d ',' -f 31 | grep --line-number '00010' | tail -n 5
38635:00010   # Format: lineNumber:value
38649:00010
38652:00010
38660:00010
38663:00010   # Error here

Exemple of those entries (CSV format) :

$ grep -n ',00010,' StockUniteLegale_utf8.csv | egrep '^38663:' -C5
38628:038683751,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,2,PME,2016,2008-01-01,A,,,SYND.COPR. DU 30 RUE DE         RICHELIE,,,,9110,81.10Z,NAFRev2,00010,,O
38635:038683850,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,2,PME,2016,2008-01-01,A,,,SYND.COPR. 9 RUE THERESE 75001  PARIS RE,,,,9110,81.10Z,NAFRev2,00010,,O
38649:038684072,O,,1995-12-25,,,,,,,,,,NN,,2011-11-29T00:14:24,2,,,2008-01-01,A,,,SYND.COPR. 56 RUE DE RICHELIEU  75001 PA,,,,9110,81.10Z,NAFRev2,00010,,N
38652:038684114,O,,1995-12-25,,,,,,,,,,NN,,2016-04-26T01:04:52,2,PME,2016,2008-01-01,A,,,SYND.COPR. 270 RUE ST HONORE    75001 PA,,,,9110,81.10Z,NAFRev2,00010,,O
38660:038684270,O,,1997-12-25,,,,,,,,,,,,2011-11-29T00:14:24,2,,,2008-01-01,A,,,S C I DE LA PORTE ST HONORE,,,,9110,81.10Z,NAFRev2,00010,,N
38663:038684312,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,3,PME,2016,2017-02-28,A,,,SYND.COPR. 6 RUE HEROLD 75001   PARIS RE,,,,9110,81.10Z,NAFRev2,00010,,N
38672:038684411,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,2,PME,2016,2008-01-01,A,,,SYND.COPR. 6 R DE LA COSSONNERIE 75001 P,,,,9110,81.10Z,NAFRev2,00010,,O
38702:038685038,O,,1995-12-25,,,,,,,,,,01,2016,2019-04-16T04:03:37,3,PME,2016,2017-05-16,A,,,SYND.COPR. DU 55 RUE REAUMUR    75002 PA,,,,9110,81.10Z,NAFRev2,00010,,N
38705:038685095,O,,1995-12-25,,,,,,,,,,NN,,2011-11-29T00:14:24,2,,,2008-01-01,A,,,SYND.COPR. DU 26 RUE DE GRAMONT 75002 PA,,,,9110,81.10Z,NAFRev2,00010,,N
38708:038685137,O,,1995-12-25,,,,,,,,,,01,2016,2018-09-29T12:00:37,2,PME,2016,2008-01-01,A,,,SYND.COPR. DU 8 RUE SAINTE FOY  75002 PA,,,,9110,81.10Z,NAFRev2,00010,,O
38721:038685335,O,,1995-12-25,,,,,,,,,,NN,,2011-11-29T00:14:25,2,PME,2016,2008-01-01,A,,,SYND.COPR. 14 RUE SAINT SAUVEUR 75002 PA,,,,9110,81.10Z,NAFRev2,00010,,O

- As the file is really long (~20M lines), I tried segmenting into smaller files. Error still at the same entry. **Additionnal debug :** The error seems not to be engine or syntax related. If i run the command above in MySQL CLI, i got the error. If I copy-paste the command in a file and print it to mysql's stdin, i had no error and all entries are now in DB :

$ cat SqlLoadCommand.sql | mysql -uX -pX erp_dev

MySQL and System version :

mysql  Ver 15.1 Distrib 10.3.10-MariaDB, for Linux (x86_64) using readline 5.1
CentOS Linux release 7.5.1804
Linux kernel 3.10.0-862.14.4.el7.x86_64

*Mandatory License Information : All data displayed are public data, courtesy of French Government and administration*