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 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 at 10:00
  • @AndriyM If the field in question had NOT been omitted, I would not have said a word. – Akina Jun 6 at 10:03
  • Test grep --line-number ',00010,', please. – Akina Jun 6 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 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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