Using 1.3.9h, changing from latin1_swedish to utf8_general
In addition to defining charset as indicated here:
http://www.zen-cart.com/forum/showthread.php?t=153582
This is the most functional script I have found for converting tables and table data:
<?php
function MysqlError()
{
if (mysql_errno())
{
echo "<b>Mysql Error: " . mysql_error() . "</b>\n";
}
}
$username = "root";
$password = "";
$db = "database";
$host = "localhost";
$target_charset = "utf8";
$target_collate = "utf8_general_ci";
echo "<pre>";
$conn = mysql_connect($host, $username, $password);
mysql_select_db($db, $conn);
$tabs = array();
$res = mysql_query("SHOW TABLES");
MysqlError();
while (($row = mysql_fetch_row($res)) != null)
{
$tabs[] = $row[0];
}
// now, fix tables
foreach ($tabs as $tab)
{
$res = mysql_query("show index from {$tab}");
MysqlError();
$indicies = array();
while (($row = mysql_fetch_array($res)) != null)
{
if ($row[2] != "PRIMARY")
{
$indicies[] = array("name" => $row[2], "unique" => !($row[1] == "1"), "col" => $row[4]);
mysql_query("ALTER TABLE {$tab} DROP INDEX {$row[2]}");
MysqlError();
echo "Dropped index {$row[2]}. Unique: {$row[1]}\n";
}
}
$res = mysql_query("DESCRIBE {$tab}");
MysqlError();
while (($row = mysql_fetch_array($res)) != null)
{
$name = $row[0];
$type = $row[1];
$set = false;
if (preg_match("/^varchar\((\d+)\)$/i", $type, $mat))
{
$size = $mat[1];
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARBINARY({$size})");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR({$size}) CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "CHAR"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} BINARY(1)");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} VARCHAR(1) CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "TINYTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TINYTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "MEDIUMTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} MEDIUMTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "LONGTEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGBLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} LONGTEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
else if (!strcasecmp($type, "TEXT"))
{
mysql_query("ALTER TABLE {$tab} MODIFY {$name} BLOB");
MysqlError();
mysql_query("ALTER TABLE {$tab} MODIFY {$name} TEXT CHARACTER SET {$target_charset}");
MysqlError();
$set = true;
echo "Altered field {$name} on {$tab} from type {$type}\n";
}
if ($set)
mysql_query("ALTER TABLE {$tab} MODIFY {$name} COLLATE {$target_collate}");
}
// re-build indicies..
foreach ($indicies as $index)
{
if ($index["unique"])
{
mysql_query("CREATE UNIQUE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
MysqlError();
}
else
{
mysql_query("CREATE INDEX {$index["name"]} ON {$tab} ({$index["col"]})");
MysqlError();
}
echo "Created index {$index["name"]} on {$tab}. Unique: {$index["unique"]}\n";
}
// set default collate
mysql_query("ALTER TABLE {$tab} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
}
// set database charset
mysql_query("ALTER DATABASE {$db} DEFAULT CHARACTER SET {$target_charset} COLLATE {$target_collate}");
mysql_close($conn);
echo "</pre>";
On my test database, it works almost flawlessly. However, I get a few errors. For example:
Dropped index idx_status_group_zen. Unique: 1
Mysql Error: Can't DROP 'idx_status_group_zen'; check that column/key exists
Dropped index idx_status_group_zen. Unique: 1
Dropped index idx_expires_date_zen. Unique: 1
Dropped index idx_date_scheduled_zen. Unique: 1
Altered field banners_title on banners from type varchar(64)
Altered field banners_url on banners from type varchar(255)
Altered field banners_image on banners from type varchar(64)
Altered field banners_group on banners from type varchar(15)
Altered field banners_html_text on banners from type text
Created index idx_status_group_zen on banners. Unique:
Mysql Error: Duplicate key name 'idx_status_group_zen'
Created index idx_status_group_zen on banners. Unique:
Dropped index idx_key_value_zen. Unique: 1
Mysql Error: Can't DROP 'idx_key_value_zen'; check that column/key exists
Dropped index idx_key_value_zen. Unique: 1
Dropped index idx_cfg_grp_id_zen. Unique: 1
Altered field configuration_title on configuration from type text
Altered field configuration_key on configuration from type varchar(255)
Altered field configuration_value on configuration from type mediumtext
Altered field configuration_description on configuration from type text
Altered field use_function on configuration from type text
Altered field set_function on configuration from type text
Created index unq_config_key_zen on configuration. Unique: 1
Created index idx_key_value_zen on configuration. Unique:
Mysql Error: BLOB/TEXT column 'configuration_value' used in key specification without a key length
Created index idx_key_value_zen on configuration. Unique:
Are these pre-existing errors or is there a problem with the script?
http://www.divinelighting.com -- Super Orders 3.0 -- Ultimate SEO -- Quantity Discounts -- SitemapXML -- Image Handler -- Shipworks
