Discussion :

[Christophe P.]

Bonjour,

j'essaie d'importer un fichier csv de 126 952 ko (1229204 lignes) et j'obtiens l'erreur suivante :

ERROR 1205 (HY000) at line 2 in file: '/.../load_aff.sql': Lock wait timeout exceeded; try restarting transaction

Quand le fichier ne faisait "que" 112 470 ko cela passait. La ligne 2 correspond au delete (la table est actuellement vide). Toad me donne la même erreur sur le LOAD DATA INFILE en plus.
Code de load_aff.sql :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
USE vichus; 
delete from aff; 
LOAD DATA INFILE '.../aff.csv' INTO TABLE aff FIELDS TERMINATED BY ';' lines terminated by '\r\n' IGNORE 1 LINES 
(EMPLOYE_ID, EMPLOYE_CODEEMPLOYE, @AFFSTRUCT_NOMCOURT, @AFFSTRUCT_NOM, AFFSTRUCT_BRISDEGLACE, @AFFSTRUCT_DEBUT, @AFFSTRUCT_FIN, AFFSTRUCT_TYPE, AFFSTRUCT_ID) 
SET 
AFFSTRUCT_NOMCOURT = if(@AFFSTRUCT_NOMCOURT = '', null, trim(@AFFSTRUCT_NOMCOURT)), 
AFFSTRUCT_NOM = if(@AFFSTRUCT_NOM = '', null, trim(@AFFSTRUCT_NOM)), 
AFFSTRUCT_DEBUT = if(@AFFSTRUCT_FIN = '', null, str_to_date(@AFFSTRUCT_FIN, '%d/%m/%Y %H:%i')), 
AFFSTRUCT_FIN = if(@AFFSTRUCT_FIN = '', null, str_to_date(@AFFSTRUCT_FIN, '%d/%m/%Y %H:%i'));

La table :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
create table aff(
	AFFSTRUCT_ID int,
	AFFSTRUCT_NOMCOURT varchar(100),
	AFFSTRUCT_NOM varchar(100),
	AFFSTRUCT_BRISDEGLACE varchar(100),
	AFFSTRUCT_DEBUT datetime default 0,
	AFFSTRUCT_FIN datetime default 0,
	AFFSTRUCT_TYPE varchar(100),
	EMPLOYE_ID varchar(20),
	EMPLOYE_CODEEMPLOYE varchar(100),
	ORA_ID int not null auto_increment,
	primary key(ORA_ID),
	KEY IDX_ORA_AFFSTRUCT_ID (AFFSTRUCT_ID),
	KEY IDX_ORA_AFFSTRUCT_NOMCOURT (AFFSTRUCT_NOMCOURT),
	KEY IDX_ORA_AFFSTRUCT_FIN (AFFSTRUCT_FIN),
	KEY IDX_ORA_AFFSTRUCT_TYPE (AFFSTRUCT_TYPE),
	KEY IDX_ORA_EMPLOYE_ID (EMPLOYE_ID),
	KEY IDX_ORA_EMPLOYE_CODEEMPLOYE (EMPLOYE_CODEEMPLOYE),
	KEY IDX_ORA_NOMCOURT_TYPE_CODEEMPLOYE (AFFSTRUCT_NOMCOURT, AFFSTRUCT_TYPE, EMPLOYE_CODEEMPLOYE)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

J'ai changé les "tx_isolation" comme indiqué ici mais l'import donne toujours la même erreur.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SET GLOBAL tx_isolation = 'READ-COMMITTED';

Avez-vous une idée pour importer ce gros fichier ?

[ddoumeche]

Bonsoir,

ce fichier n'est pas spécialement gros, sachant que le LOAD DATA INFILE est justement prévu pour ce genre de cas. Le problème est qu'un thread doit bloquer la table en écriture et que le delete ne peut se faire tant que ce thread n'est pas terminé.

Un

SHOW PROCESSLIST;

te donner la liste des requêtes en cours et un

KILL <id>

te permettra de terminer la requête bloquant la table (c'est peut-être un reliquat d'une de tes précédente tentative d'import).

Si la table se verrouille parce qu'elle est trop usitée, essaye d'augmenter la durée de tentative d'acquisition du verrou :

SET GLOBAL innodb_lock_wait_timeout = 120;

[Artemus24]

Salut à tous.

La réponse est dans l'augmentation de la variable 'InnoDB_Lock_Wait_Timeout'.
Voir le lien ci-après : https://dev.mysql.com/doc/innodb/1.1...t_timeout.html
http://dev.mysql.com/doc/refman/5.7/...k_wait_timeout

Le temps d'exécution du chargement de votre table est trop long, ce qui fait que vous perdez la transaction.

Allez sous phpmyadmin et tapez :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

show variables like 'innodb_lock_wait_timeout';

Augmentez la valeur en la passant par exemple à 600 --> 10 minutes.

Quand vous n'êtes pas seul à travailler, la première des choses est de verrouiller cette table.
A la fin du chargement, vous déverrouiller la table afin de la rendre accessible à tout le monde.
https://dev.mysql.com/doc/refman/5.0...ck-tables.html

@+

[Christophe P.]

Merci pour ces infos.

La taille du fichier risque d'augmenter encore. Je préfère ne pas avoir à paramétrer un timeout très long ni le modifier à chaque fois que nécessaire.
Dans mon script bash, j'ai utilisé split sur mon fichier avant de lancer l'import et ça passe :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
cd repertoire_csv
split -l 1000000 aff.csv csvaff
mysql -uuser -ppwd -Dbase -hlocalhost -e "delete from aff;"
for fichier in csvaff*
do
	script_impot ${fichier}
done

[ddoumeche]

Normalement un LOAD DATA INFILE traite 3-5 millions de ligne à la minute,

Il y a beaucoup d'index sur ta table, ce qui va ralentir les performances en écriture (Nota : Mysql ne peut utiliser qu'un seul index par table)

La séquence suivante peut également améliorer les performances de l'import :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
-- truncate est plus rapide que delete qui s'exécute ligne à ligne 
truncate <table>;
-- pas de vérification des clées primaires
set unique_checks = 0;
-- pas de vérification des clées étrangères
set foreign_key_checks = 0;
-- suppression des logs pour la session de l'import
set sql_log_bin=0;
-- desactive la mise à jour des index
alter table <table> DISABLE KEYS;
 
set autocommit = 0;
 
load data infile <path> into table <table>...
 
commit;
 
-- mise à jour des index en une seule fois
ALTER TABLE <table> ENABLE KEYS;
set unique_checks = 0;
set foreign_key_checks = 0;
set sql_log_bin=1;