1

When inserting data from CSV to MariaDB 10.3, the following error is generated while trying to insert value '00010' into NCHAR(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

  • Here is MySQL command line I do NOT see where you set the value to the field in question... you load all data into variables, and then set the value to five fields only. – Akina Jun 6 '19 at 9:47
  • @Akina: There's a placeholder comment in the assignment part of the statement probably standing for the missing part of the script: "-- Repeat above line for each column --". – Andriy M Jun 6 '19 at 10:00
  • @AndriyM If the field in question had NOT been omitted, I would not have said a word. – Akina Jun 6 '19 at 10:03
  • Test grep --line-number ',00010,', please. – Akina Jun 6 '19 at 10:04
  • @Akina: Good remark, post have been edited to specify the field plus additionnal debug. I'll produce the grep asked but i have to reduce it (else it's >200MB). – Ender Lisan Al Gaïb Jun 6 '19 at 12:47
0

WARNING: This is not a real solution. It worked for me but i won't recommend you using this on production context without full test of you data.

Adding keyword IGNORE to the import command functionned well :

LOAD DATA INFILE "/filePath/StockUniteLegale_utf8.csv" IGNORE 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,'');

To fully test this "patch", I dumped all table data in a file with same syntax as the source one and did a binary diff check on the two files (the result is exact match).

Next time I update this server version I'll let you know if the problem is still here as it seems the error is a bug and not user-related.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.