Skip to content

Instantly share code, notes, and snippets.

@slackero

slackero/db_utf8_fix.php

Forked from jaguerra/db_utf8_fix.php
Last active August 29, 2015 14:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save slackero/1fbab442cecd3a124ff1 to your computer and use it in GitHub Desktop.
Save slackero/1fbab442cecd3a124ff1 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
db_utf8_fix.php
<?php
/******************************************/
// db_utf8_fix.php //
// Original author: J. van Hemert //
// Original Date: 26-10-2011 //
// //
// Fixes encoding when utf-8-encoded data //
// is stored in tables with other (e.g. //
// latin_swedish_ci) encoding. //
// Will convert all columns in all tables //
// to utf8_general_ci. //
// Run from fileadmin folder in TYPO3 //
// installation. //
/******************************************/
//Set to TRUE to generate an enormous amount of debug output with
//analysis of table structure.
define('DEBUG', FALSE);
//Set to FALSE to really convert the database
define('SIMULATE', TRUE);
require_once ('../typo3conf/localconf.php');
echo str_repeat(' ', 256);
?>
<html>
<head>
<style type="text/css">
.normal {
color: black;
}
.okay {
color: green;
}
.label {
color: blue;
}
.error {
color: red;
}
</style>
</head>
<body>
<?php
$tables = array();
$typeconv = array(
'char' => 'binary',
'text' => 'blob',
);
$serializedFields = array(
'sys_history' => array('history_data'),
'tx_templavoila_tmplobj' => array('templatemapping'),
'be_users' => array('uc'),
'sys_log' => array('log_data')
);
$db = mysql_connect($typo_db_host, $typo_db_username, $typo_db_password, TRUE);
if (!is_resource($db)) {
die('Could not connect to db!: ' . mysql_error());
}
if (mysql_select_db($typo_db, $db) === FALSE) {
die('Could not select database!: ' . mysql_error());
}
// Collect table names
$sql = 'SHOW TABLES;';
$db_res = mysql_query($sql, $db);
if (!is_resource($db_res)) {
die ('Could not get query result!: ' . mysql_error() . "\n" . $sql);
}
while ($row = mysql_fetch_array($db_res, MYSQL_NUM)) {
if (DEBUG) {
var_dump($row);
}
$tables[] = $row[0];
}
// process each table
foreach ($tables as $table) {
echo '<div><span class="label">' . $table . ': </span><span class="normal">';
// Collect column information
$sql = 'SHOW FULL COLUMNS FROM `' . $table . '`;';
$db_res = mysql_query($sql, $db);
if (!is_resource($db_res)) {
die ('Could not get table data!: ' . mysql_error() . "\n" . $sql);
}
$columns = array();
while ($row = mysql_fetch_assoc($db_res)) {
if (DEBUG) {
echo 'column: ';
var_dump($row);
}
$columns[] = $row;
}
// process each column
foreach ($columns as $column) {
set_time_limit(60);
$oldtype = $column['Type'];
if (DEBUG) {
echo 'Original: ' . $column['Type'] . "\n";
}
// modify type into a binary equivalent
$column['Type'] = str_replace(array_keys($typeconv), array_values($typeconv), $column['Type']);
if (DEBUG) {
echo 'modified: ' . $column['Type'] . "\n";
}
// only do the magic if the type was modified
if ($column['Type'] != $oldtype) {
$column['Null'] = (strtolower($column['Null']) == 'yes') ? 'NULL' : 'NOT NULL';
$column['Default'] = (is_numeric($column['Default']))
? $column['Default']
: ($column['Default'] === 'NULL') ? $column['Default'] : '\'' . $column['Default'] . '\'';
/*
* Convert fields storing serialized PHP arrays to blob(s)...
*/
if(is_array($serializedFields[$table]) && in_array($column['Field'], $serializedFields[$table])){
// keep the binary field...
$sql = 'ALTER TABLE `' . $table . '` MODIFY COLUMN `' . $column['Field'] . '` ' . $column['Type'] .
' ' . $column['Null'];
} else {
// modify type back to the non-binary equivalent, but add utf8 character set / collation setting
$column['Type'] = str_replace(array_values($typeconv), array_keys($typeconv), $column['Type']);
$sql = 'ALTER TABLE `' . $table . '` MODIFY COLUMN `' . $column['Field'] . '` ' . $column['Type'] .
' CHARACTER SET utf8 COLLATE utf8_general_ci ' . $column['Null'];
}
if (strpos($column['Type'], 'text') === FALSE) {
$sql .= ' DEFAULT ' . $column['Default'];
}
$sql .= ' ' . $column['Extra'] . ';';
if (DEBUG) {
echo $sql . "\n";
} else {
if (!SIMULATE) {
$db_res = mysql_query($sql, $db);
if (!is_resource($db_res) && mysql_errno($db) != 0) {
echo 'Could not execute query!: ' . mysql_error($db) . "\n" . $sql;
}
}
echo '.';
ob_flush();
flush();
}
}
}
// set defaults for table to utf8
$sql = 'ALTER TABLE `' . $table . '` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;';
if (DEBUG) {
echo $sql . "\n";
} else {
if (!SIMULATE) {
$db_res = mysql_query($sql, $db);
if (!is_resource($db_res) && mysql_errno($db) != 0) {
echo 'Could not execute query!: ' . mysql_error($db) . "\n" . $sql;
}
}
echo '</span><span class="okay"> OK</span></div>';
ob_flush();
flush();
}
}
// set defaults for database to utf8
echo '<div><span class="label">DATABASE: </span><span class="normal">';
$sql = 'ALTER DATABASE `' . $typo_db . '` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;';
if (DEBUG) {
echo $sql . "\n";
} else {
if (!SIMULATE) {
$db_res = mysql_query($sql, $db);
if (!is_resource($db_res) && mysql_errno($db) != 0) {
echo 'Could not execute query!: ' . mysql_error($db) . "\n" . $sql;
}
}
echo '</span><span class="okay"> OK</span></div>';
ob_flush();
flush();
}
echo '<div>finished converting tables</div>';
mysql_close($db);
?>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment