julp/README.fr.md

## README.fr.md

      
    Raw
  

              README.fr.md
            
          
    Warnings d'usage

Je décline toute responsabilité quant à l'usage fait de ces "codes".
Lisez attentivement cette description avant de faire quoi que ce soit.
Vous penserez bien évidemment, comme avant toute intervention sur une base de données à effectuer au préalable une sauvegarde (données, tables, procédures/triggers) de celle-ci (mysqldump ou éventuellement via phpMyAdmin - déconseillé avec des bases importantes, surtout sur un serveur distant).
S'il est question d'une base de données en production, mettez absolument les applications qui en dépendent hors ligne avant toute chose parce que s'il y a des insertions ou mises à jour dans le même laps de temps, ça risque de poser différents problèmes.
Important : vous ne devez chercher à corriger vos données qu'après avoir définitivement réglé le problème de jeu(x) de caractères qui a lieu essentiellement lors des insertion et mise à jour (il vous manque certainement l'équivalent client d'un SET NAMES et héritez alors du jeu de caractères par défaut de MySQL qui ne correspond pas à celui utilisé par votre application). Si vous le faites avant, vous allez avoir un mélange de données correctes (celles antérieures à la correction) et des nouvelles qui seront mal encodées, dès lors il sera difficile voire impossible de les reprendre à nouveau. Le but est vraiment de les fixer une fois pour toute, absolument pas de les réparer périodiquement.
Quoi utiliser ?

Ce gist est composé de 3 "codes" :

export_import_sql.txt tente de fixer globalement les données d'une base de données en l'exportant (première ligne/commande) puis la réimporant (seconde ligne/commande). Les parties entre crochets (et crochets compris, ils ne sont pas à conserver) sont à remplacer par vos différents identifiants, noms de base de données, etc. A moins d'exporter et réimporter dans des bases de données différentes, il faudra d'abord détruire (DROP) toutes les tables. Si vous rencontrez (ou avez peur de rencontrer) un conflit avec les clés étrangères, désactiver-les avant par SET FOREIGN_KEY_CHECKS=0; pour les réactiver après SET FOREIGN_KEY_CHECKS=1;.
reencode_bad_utf8_in_mysql.php est un script PHP utilisant mysqli qui va effectuer globalement (= sur toute une base) différentes opérations pour tenter de fixer l'encodage des données. Les seuls éléments à modifier sont les identifiants de connexion de la fonction mysqli_connect. Il ne doit être exécuté qu'en CLI (ie à partir d'une console/invite de commande), pas depuis un navigateur/via un serveur web, le script étant très lourd, il peut avoir besoin de s'exécuter pendant un long moment or en web, il risque d'être tué en plein milieu avec le risque de rendre vos données incohérentes !
specific_column.sql est une requête SQL destinée à fixer des données précises = vous devez indiquer le nom de la table, de la colonne et ajouter une clause WHERE si nécessaire et répéter manuellement l'opération pour toute autre colonne concernée. Cette approche, permet d'indiquer une clause WHERE si seulement une partie de la table nécessite une correction (suivant une date d'insertion et/ou mise à jour, un id ou autre).

Seule une de ces trois méthodes doit être employée, choisissez-la avec soins : s'il n'y a qu'une partie des tables qui doit être corrigée, utilisez la dernière (specific_column.sql), pas les deux autres. Avec une correction globale, les données qui sont correctement encodées, ne le seraient plus, donc retour à la case de départ (dans une configuration différente = ces codes ne s'appliquent plus).
Note/rappel : ALTER TABLE implique dans tous les cas un auto-commit

  
## export_import_sql.txt
mysqldump -u [user] -p --skip-set-charset --default-character-set=latin1 [database] [list of tables unless you want all of them] > output.sql
mysql --default-character-set=utf8 -u [user] -p -D [database] < output.sql

## reencode_bad_utf8_in_mysql.php
<?php
# encoding: UTF-8

/**
 * NOTE: ce script est très lourd, exécutez-le en CLI - en web (Apache/FPM/etc), il va certainement être stoppé (timeout) avant d'avoir fini !
 *
 * Historique:
 * - 21/08/2017 :
 *     + correction de l'échappement des noms des index et des colonnes qui les composent
 *     + extension du DROP INDEX à tout index comprenant une colonne *CHAR ou *TEXT au lieu de le limiter aux FULLTEXT car MySQL renvoie une erreur lors des ALTER TABLE comme quoi l'index n'a pas de longueur (BLOB/TEXT column ... used in key specification without a key length)
 */

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$cnx = new MySQLi('localhost', 'user', 'password', 'database');

// Fill database with some "corrupted" data for testing
if (0) {
    $cnx->set_charset('latin1');

    $cnx->multi_query(<<<'EoS'
        DROP TABLE IF EXISTS test;

        CREATE TABLE test (
            id int unsigned NOT NULL AUTO_INCREMENT,
            string varchar(120) NOT NULL,
            PRIMARY KEY (id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

        INSERT INTO test(string) VALUES("éloïse");

        DROP TABLE IF EXISTS pleintexte;

        CREATE TABLE pleintexte (
            id int unsigned NOT NULL AUTO_INCREMENT,
            `tit``le` VARCHAR(120) NOT NULL,
            description TEXT NOT NULL,
            PRIMARY KEY (id)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

        CREATE FULLTEXT INDEX ft_title ON pleintexte (`tit``le`);
        CREATE FULLTEXT INDEX ft_description ON pleintexte (description);
        CREATE FULLTEXT INDEX ft_title_description ON pleintexte (`tit``le`, description);

        INSERT INTO pleintexte(`tit``le`, description) VALUES("éloïse est partie à la pêche", "Cette journée fut mémorable. Nous avons ramené deux brochets et fait un bon pique-nique.");
EoS
    );
    // skip all result sets
    while ($cnx->more_results()) {
        if ($result = $cnx->use_result()) {
            $result->close();
        }
        $cnx->next_result();
    }

    var_dump(
        $cnx->character_set_name(),
        $cnx->query('SELECT *, LENGTH(string), HEX(string) FROM test')->fetch_all() // NOTE: fetch_all implies mysqli to use mysqlnd
    );

    exit;
}

// Converter code really starts here

$columns = $cnx->query(<<<'EoS'
    SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME, C.IS_NULLABLE, C.COLUMN_DEFAULT/*, CHARACTER_MAXIMUM_LENGTH*/
        FROM information_schema.TABLES AS T
            JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
            JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
            ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
        WHERE TABLE_SCHEMA = SCHEMA()
            AND C.DATA_TYPE IN("enum", "varchar", "char", "text", "mediumtext", "longtext")
        ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
EoS
);

$map = array(
    'char' => 'tinyblob', // max 255 bytes
    'tinytext' => 'tinyblob',
    'varchar' => 'blob', // max 255 code points, may be > to 255 bytes (up to 765 for utf8, 1020 for utf8mb4)
    'text' => 'blob',
    'mediumtext' => 'mediumblob',
    'longtext' => 'longblob',
);

// properly escape identifiers
function e(/*string*/ $identifier)/*: string*/ {
    return '`' . str_replace('`', '``', $identifier) . '`';
}

// properly escape strings/values
function q(mysqli $conn, /*string*/ $value)/*: string*/ {
    return "'" . $conn->real_escape_string($value) . "'";
}

define('KEY_FULLTEXT', 'FULLTEXT');
define('KEY_UNIQUE', 'UNIQUE');
define('KEY_INDEX', '');

// Drop fulltext and problematic indexes
$indexes = array(
    KEY_INDEX => array(),    // regular index
    KEY_FULLTEXT => array(), // fulltext index
    KEY_UNIQUE => array(),   // unique index/key
);
$indq = $cnx->query(<<<'EoS'
    SELECT DISTINCT TABLE_NAME, INDEX_NAME, S.COLUMN_NAME, INDEX_TYPE, NON_UNIQUE
        FROM information_schema.STATISTICS S
            JOIN (
                SELECT DISTINCT S.INDEX_NAME, S.COLUMN_NAME, S.TABLE_NAME, S.TABLE_SCHEMA
                    FROM information_schema.COLUMNS C
                        JOIN information_schema.STATISTICS S USING(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
                    WHERE C.TABLE_SCHEMA = SCHEMA()
                        AND C.DATA_TYPE IN("enum", "varchar", "char", "text", "mediumtext", "longtext")
            ) t USING(INDEX_NAME, TABLE_NAME, TABLE_SCHEMA)
        ORDER BY TABLE_NAME, INDEX_NAME
EoS
);
while ($row = $indq->fetch_object()) {
    $type = 'FULLTEXT' == $row->INDEX_TYPE ? KEY_FULLTEXT : (!$row->NON_UNIQUE ? KEY_UNIQUE : KEY_INDEX);
    if (!array_key_exists($row->TABLE_NAME, $indexes[$type])) {
        $indexes[$type][$row->TABLE_NAME] = array();
    }
    if (!array_key_exists($row->INDEX_NAME, $indexes[$type][$row->TABLE_NAME])) {
        $indexes[$type][$row->TABLE_NAME][$row->INDEX_NAME] = array();
        $cnx->query('DROP INDEX ' . e($row->INDEX_NAME) . ' ON ' . e($row->TABLE_NAME)); // for multicolumn indexes, drop it just once
    }
    $indexes[$type][$row->TABLE_NAME][$row->INDEX_NAME][] = $row->COLUMN_NAME;
}

while ($row = $columns->fetch_object()) {
    $new_type = FALSE;

    foreach ($map as $k => $v) {
        if (0 === strpos(strtolower($row->COLUMN_TYPE), $k)) {
            $new_type = $v;
            break;
        }
    }

    if (!$new_type) {
        die('Bug ? (mapping failed)');
    }

    $default = $length = '';
    $null = 'NO' == $row->IS_NULLABLE ? ' NOT NULL ' : '';
    // *BLOB and *TEXT columns cannot have DEFAULT values
    if (FALSE === stripos($row->COLUMN_TYPE, 'text')) {
        $default = ' DEFAULT ' . ('NO' != $row->IS_NULLABLE && is_null($row->COLUMN_DEFAULT) ? 'NULL' : q($cnx, $row->COLUMN_DEFAULT)) . ' ';
    }
//     if (!is_null($row->CHARACTER_MAXIMUM_LENGTH)) {
//         $length = '(' . $row->CHARACTER_MAXIMUM_LENGTH . ') ';
//     }
    // TODO: DRY?
    $cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $new_type . $length . $null);
    $cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET utf8 ' . $null . $default);
    $cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET latin1 ' . $null . $default);
    $cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $new_type . $length . $null);
    $cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET utf8 ' . $null . $default);
}

// Restore fulltext indexes
foreach ($indexes as $type => $subindexes) {
    foreach ($subindexes as $table_name => $table_indexes) {
        foreach ($table_indexes as $index_name => $indexed_columns) {
            $cnx->query('CREATE ' . $type . ' INDEX ' . e($index_name) . ' ON ' . e($table_name) . ' (' . implode(', ', array_map('e', $indexed_columns)) . ')');
        }
    }
}

## specific_column.sql
UPDATE table SET column = CONVERT(
    CONVERT(
        CONVERT(
            CONVERT(
                CONVERT(column USING binary)
                USING utf8
            )
            USING latin1
        )
        USING BINARY
    )
    USING utf8
)
WHERE ...
	mysqldump -u [user] -p --skip-set-charset --default-character-set=latin1 [database] [list of tables unless you want all of them] > output.sql
	mysql --default-character-set=utf8 -u [user] -p -D [database] < output.sql
	<?php
	# encoding: UTF-8

	/**
	* NOTE: ce script est très lourd, exécutez-le en CLI - en web (Apache/FPM/etc), il va certainement être stoppé (timeout) avant d'avoir fini !
	*
	* Historique:
	* - 21/08/2017 :
	* + correction de l'échappement des noms des index et des colonnes qui les composent
	* + extension du DROP INDEX à tout index comprenant une colonne CHAR ou TEXT au lieu de le limiter aux FULLTEXT car MySQL renvoie une erreur lors des ALTER TABLE comme quoi l'index n'a pas de longueur (BLOB/TEXT column ... used in key specification without a key length)
	*/

	mysqli_report(MYSQLI_REPORT_ERROR \| MYSQLI_REPORT_STRICT);

	$cnx = new MySQLi('localhost', 'user', 'password', 'database');

	// Fill database with some "corrupted" data for testing
	if (0) {
	$cnx->set_charset('latin1');

	$cnx->multi_query(<<<'EoS'
	DROP TABLE IF EXISTS test;

	CREATE TABLE test (
	id int unsigned NOT NULL AUTO_INCREMENT,
	string varchar(120) NOT NULL,
	PRIMARY KEY (id)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

	INSERT INTO test(string) VALUES("éloïse");

	DROP TABLE IF EXISTS pleintexte;

	CREATE TABLE pleintexte (
	id int unsigned NOT NULL AUTO_INCREMENT,
	`tit``le` VARCHAR(120) NOT NULL,
	description TEXT NOT NULL,
	PRIMARY KEY (id)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8;

	CREATE FULLTEXT INDEX ft_title ON pleintexte (`tit``le`);
	CREATE FULLTEXT INDEX ft_description ON pleintexte (description);
	CREATE FULLTEXT INDEX ft_title_description ON pleintexte (`tit``le`, description);

	INSERT INTO pleintexte(`tit``le`, description) VALUES("éloïse est partie à la pêche", "Cette journée fut mémorable. Nous avons ramené deux brochets et fait un bon pique-nique.");
	EoS
	);
	// skip all result sets
	while ($cnx->more_results()) {
	if ($result = $cnx->use_result()) {
	$result->close();
	}
	$cnx->next_result();
	}

	var_dump(
	$cnx->character_set_name(),
	$cnx->query('SELECT *, LENGTH(string), HEX(string) FROM test')->fetch_all() // NOTE: fetch_all implies mysqli to use mysqlnd
	);

	exit;
	}

	// Converter code really starts here

	$columns = $cnx->query(<<<'EoS'
	SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, C.CHARACTER_SET_NAME, C.IS_NULLABLE, C.COLUMN_DEFAULT/, CHARACTER_MAXIMUM_LENGTH/
	FROM information_schema.TABLES AS T
	JOIN information_schema.COLUMNS AS C USING (TABLE_SCHEMA, TABLE_NAME)
	JOIN information_schema.COLLATION_CHARACTER_SET_APPLICABILITY AS CCSA
	ON (T.TABLE_COLLATION = CCSA.COLLATION_NAME)
	WHERE TABLE_SCHEMA = SCHEMA()
	AND C.DATA_TYPE IN("enum", "varchar", "char", "text", "mediumtext", "longtext")
	ORDER BY TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
	EoS
	);

	$map = array(
	'char' => 'tinyblob', // max 255 bytes
	'tinytext' => 'tinyblob',
	'varchar' => 'blob', // max 255 code points, may be > to 255 bytes (up to 765 for utf8, 1020 for utf8mb4)
	'text' => 'blob',
	'mediumtext' => 'mediumblob',
	'longtext' => 'longblob',
	);

	// properly escape identifiers
	function e(/string/ $identifier)/: string/ {
	return '`' . str_replace('`', '``', $identifier) . '`';
	}

	// properly escape strings/values
	function q(mysqli $conn, /string/ $value)/: string/ {
	return "'" . $conn->real_escape_string($value) . "'";
	}

	define('KEY_FULLTEXT', 'FULLTEXT');
	define('KEY_UNIQUE', 'UNIQUE');
	define('KEY_INDEX', '');

	// Drop fulltext and problematic indexes
	$indexes = array(
	KEY_INDEX => array(), // regular index
	KEY_FULLTEXT => array(), // fulltext index
	KEY_UNIQUE => array(), // unique index/key
	);
	$indq = $cnx->query(<<<'EoS'
	SELECT DISTINCT TABLE_NAME, INDEX_NAME, S.COLUMN_NAME, INDEX_TYPE, NON_UNIQUE
	FROM information_schema.STATISTICS S
	JOIN (
	SELECT DISTINCT S.INDEX_NAME, S.COLUMN_NAME, S.TABLE_NAME, S.TABLE_SCHEMA
	FROM information_schema.COLUMNS C
	JOIN information_schema.STATISTICS S USING(TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
	WHERE C.TABLE_SCHEMA = SCHEMA()
	AND C.DATA_TYPE IN("enum", "varchar", "char", "text", "mediumtext", "longtext")
	) t USING(INDEX_NAME, TABLE_NAME, TABLE_SCHEMA)
	ORDER BY TABLE_NAME, INDEX_NAME
	EoS
	);
	while ($row = $indq->fetch_object()) {
	$type = 'FULLTEXT' == $row->INDEX_TYPE ? KEY_FULLTEXT : (!$row->NON_UNIQUE ? KEY_UNIQUE : KEY_INDEX);
	if (!array_key_exists($row->TABLE_NAME, $indexes[$type])) {
	$indexes[$type][$row->TABLE_NAME] = array();
	}
	if (!array_key_exists($row->INDEX_NAME, $indexes[$type][$row->TABLE_NAME])) {
	$indexes[$type][$row->TABLE_NAME][$row->INDEX_NAME] = array();
	$cnx->query('DROP INDEX ' . e($row->INDEX_NAME) . ' ON ' . e($row->TABLE_NAME)); // for multicolumn indexes, drop it just once
	}
	$indexes[$type][$row->TABLE_NAME][$row->INDEX_NAME][] = $row->COLUMN_NAME;
	}

	while ($row = $columns->fetch_object()) {
	$new_type = FALSE;

	foreach ($map as $k => $v) {
	if (0 === strpos(strtolower($row->COLUMN_TYPE), $k)) {
	$new_type = $v;
	break;
	}
	}

	if (!$new_type) {
	die('Bug ? (mapping failed)');
	}

	$default = $length = '';
	$null = 'NO' == $row->IS_NULLABLE ? ' NOT NULL ' : '';
	// BLOB and TEXT columns cannot have DEFAULT values
	if (FALSE === stripos($row->COLUMN_TYPE, 'text')) {
	$default = ' DEFAULT ' . ('NO' != $row->IS_NULLABLE && is_null($row->COLUMN_DEFAULT) ? 'NULL' : q($cnx, $row->COLUMN_DEFAULT)) . ' ';
	}
	// if (!is_null($row->CHARACTER_MAXIMUM_LENGTH)) {
	// $length = '(' . $row->CHARACTER_MAXIMUM_LENGTH . ') ';
	// }
	// TODO: DRY?
	$cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $new_type . $length . $null);
	$cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET utf8 ' . $null . $default);
	$cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET latin1 ' . $null . $default);
	$cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $new_type . $length . $null);
	$cnx->query('ALTER TABLE ' . e($row->TABLE_SCHEMA) . '.' . e($row->TABLE_NAME) . ' MODIFY ' . e($row->COLUMN_NAME) . ' ' . $row->COLUMN_TYPE . $length . ' CHARACTER SET utf8 ' . $null . $default);
	}

	// Restore fulltext indexes
	foreach ($indexes as $type => $subindexes) {
	foreach ($subindexes as $table_name => $table_indexes) {
	foreach ($table_indexes as $index_name => $indexed_columns) {
	$cnx->query('CREATE ' . $type . ' INDEX ' . e($index_name) . ' ON ' . e($table_name) . ' (' . implode(', ', array_map('e', $indexed_columns)) . ')');
	}
	}
	}
	UPDATE table SET column = CONVERT(
	CONVERT(
	CONVERT(
	CONVERT(
	CONVERT(column USING binary)
	USING utf8
	)
	USING latin1
	)
	USING BINARY
	)
	USING utf8
	)
	WHERE ...