Importer un gros fichier dans une base SQL

© Christian PAULUS. Document créé le 16 octobre 2010 , mis à jour le 16 octobre 2010.

Ce n'est pas le tout d'être une femme du monde. Encore faut-il savoir de quel monde il s'agit. Pierre Dac

Accueil du site > Astuces > SPIP > Importer un gros fichier dans une base SQL

Import / MySQL 5/PHP 5/SPIP 2.n

Pour la petite histoire, ce billet illustre l’incident rencontré par Sylvain lors d’une tentative d’import de 40 000 adresses dans SPIP-Listes. L’import échouait à chaque tentative. L’opération était tout simplement impossible !

Les symptômes d’un import MySQL défectueux

Les premiers tests ont permis de valider l’import de 1000 adresses en moins de 1 seconde, et de l’échec lors de 2000 adresses, en prenant en compte le délai imposé par PHP (temps d’exécution, max_execution_time, par défaut réglé à 30 secondes). D’autres tests réalisés en modifiant cette directive n’ont pas eu d’effet positif.

Logiquement, l’import de 2000 adresses prend au maximum deux fois plus de temps que 1000 adresses. Donc moins de 2 secondes. Mais cet import échouait avec comme seule erreur un temps d’exécution PHP dépassé.

Le problème ne semblait pas venir de PHP, mais plutôt des entrées/sorties. Taille du fichier ou engorgement réseau/disque. Bon ! Là, c’est de l’intuition. Des chiffres complémentaires sont indispensables. Les journaux seront nos amis.

Par défaut, MySQL est installé en mode production. Disons plutôt que le mode debug n’est pas activé. Pas de journaux, pas de suivi des erreurs.

Sur une machine de développement, il est préférable d’activer ce type d’informations. Le serveur s’en trouve peut-être moins rapide, mais sans indicateur, difficile de régler ce genre d’erreur.

Dans le fichier php.ini, donner la valeur On à la directive mysql.trace_mode :

; Trace mode. When trace_mode is active (=On), warnings for table/index scans and
; SQL-Erros will be displayed.
;;mysql.trace_mode = Off
mysql.trace_mode = On

Redémarrer le serveur pour activer cette option.

Message d’erreur salvateur

Dès la tentative d’import, le message tant attendu apparaît dans le fichier php_error.log :

[16-Oct-2010 12:57:58] PHP Warning:  mysql_query() [<a href='http://www.mysql.com/doc'>http://www.mysql.com/doc</a>]: Got a packet bigger than 'max_allowed_packet' bytes in /usr/local/www/www.test.SPIP_2/ecrire/req/mysql.php on line 567

Par ailleurs, le log de SPIP-Listes indique un fichier de plus de 5 Mo. Et la commande SQL :

SHOW SESSION VARIABLES LIKE 'max_allowed_packet'

(que vous pouvez entrer dans phpMyAdmin ou via la console mysql) répond 1048576 (1 Mo).

Il est donc tout à fait normal de constater l’échec pour ce type d’opération. max_allowed_packet est la taille maxi acceptée par MySQL pour un fichier transmis par le réseau (paquet). Et même si vous transmettez en local (serveur PHP et MySQL sur la même machine), la transmission est effectuée par les protocoles réseau (ici, localhost).

Augmenter la taille mémoire nécessaire à l’import de gros fichiers

La commande magique, pour 32 Mo, est :

SET GLOBAL max_allowed_packet=33554432

Vous pouvez entrer cette commande via la console mysql ou phpMyAdmin. L’effet est immédiat, il est inutile de redémarrer MySQL.

Plussoyez !

Les forums sont fermés.

  • Importer un gros fichier dans une base SQL 17 octobre 2010 13:53, par Sylvain
    Merci Christian d’avoir investigué de ce côté, ce qui règle considérablement les problèmes d’import avec SPIP-Listes.

    Les forums sont fermés.