Comme d'habitude, je me fais à chaque fois avoir, alors cette fois-ci, j'en fait un petit listing pour que je ne plus perdre de temps avec ça...

Avec drupal, tout est beau et sympa lorsqu'on développe en local, mais dès qu'on doit passer en distant, ça devient la grosse prise de tête au niveau de l'installation du script sql (base de données).

Le problème se site au niveau de l'encodage des caractères lors de l'exportation via mysql < 5. On ne peut pas focément spécifier l'encodage à l'export, donc on se retrouve avec un fichier txt ansi. Lorsqu'on le réinjecte sur la db distante, bien entendu, il faut que l'encodage soit en utf8 et là, tout foire dans notre base de données.

En général, il arrive qu'on obtienne le message d'erreur suivant (que je n'ai pas rencontré cette fois-ci, mais sur des installations précédentes) : user error: Illegal mix of collations
(latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)
for operation '='

Bref. Pour résoudre ce problème, il y a différentes solutions, j'en ai une qui fonctionne donc je m'en contente.

Tout d'abord, préparer la base de données distante et l'encoder en utf8_general_ci :

//------------------------------------
ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
------------------------------------//

Injecter ensuite le script sql de la base de données (structure et données). C'est ici que le problème d'affichage se présente.

Pour le résoudre, encoder toutes les tables en latin1 :

//------------------------------------
ALTER TABLE access CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE authmap CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE blocks CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE blocks_roles CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE boxes CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE cache CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE cache_filter CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE cache_menu CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE cache_page CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE cache_views CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE captcha_points CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE comments CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE contact CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE contemplate CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE content_access CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE files CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE file_revisions CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE filters CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE filter_formats CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE flood CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE history CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE locales_meta CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE locales_source CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE locales_target CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE menu CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE nodewords CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_access CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_blog CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_comment_statistics CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_counter CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_date CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_fichier_mp3 CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_liens_vers_les_films CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_url CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_video_high CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_data_field_video_low CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_field CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_field_instance CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_films CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_galeries CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_group CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_group_fields CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_image CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_images CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_liens CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_medias CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_musiques CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_page CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_projets CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_revisions CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_story CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_toshprodpage CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE node_type CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE page_title CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE path_access CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE permission CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE profile_fields CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE profile_values CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE role CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE role_weights CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE sequences CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE sessions CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE system CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE term_data CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE term_hierarchy CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE term_node CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE term_relation CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE term_synonym CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE url_alias CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE users CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE users_roles CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE variable CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE view_argument CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE view_exposed_filter CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE view_filter CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE view_sort CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE view_tablefield CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE vocabulary CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE vocabulary_node_types CHARACTER SET latin1 COLLATE latin1_general_ci;
ALTER TABLE watchdog CHARACTER SET latin1 COLLATE latin1_general_ci;
------------------------------------//

Il y a ici une liste non exhaustive des tables pouvant être présentes dans la base de données. Elle correspond au site que je suis en train de mettre en place en ce moment. A vous de contrôler si tout correspond avec votre base de données.