2011
06.16
06.16
Today I ran into some Trac problems when trying to view a SVN changeset in the SVN browser. I read that these problems were caused by MySQL? so I tried `the suggested`. Because I had to do this for 3 Trac instances I created a little php (commandline) script to do the job.
< ?php
if ($argc != 2)
{
echo $argv[0]." <DATABASE>\n";
exit(1);
}
$db = trim($argv[1]);
if (!$link = mysql_connect('127.0.0.1', 'root', ''))
{
die(mysql_error());
}
if (!$db_selected = mysql_select_db($db))
{
}
echo "ALTER DATABASE `$db` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;\n";
if (!($result = mysql_query("SHOW TABLES;")))
{
die (mysql_error());
}
while ($row = mysql_fetch_assoc($result))
{
$table = array_shift($row);
echo "ALTER TABLE `$table` ENGINE = InnoDB;\n";
echo "ALTER TABLE `$table` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;\n";
}
?>
Usage:
php convert_trac_db.php trac;
Output:
ALTER DATABASE `trac` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `attachment` ENGINE = InnoDB; ALTER TABLE `attachment` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `auth_cookie` ENGINE = InnoDB; ALTER TABLE `auth_cookie` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `component` ENGINE = InnoDB; ALTER TABLE `component` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `enum` ENGINE = InnoDB; ALTER TABLE `enum` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `fullblog_comments` ENGINE = InnoDB; ALTER TABLE `fullblog_comments` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `fullblog_posts` ENGINE = InnoDB; ALTER TABLE `fullblog_posts` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `mastertickets` ENGINE = InnoDB; ALTER TABLE `mastertickets` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `milestone` ENGINE = InnoDB; ALTER TABLE `milestone` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `node_change` ENGINE = InnoDB; ALTER TABLE `node_change` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `permission` ENGINE = InnoDB; ALTER TABLE `permission` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `report` ENGINE = InnoDB; ALTER TABLE `report` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `revision` ENGINE = InnoDB; ALTER TABLE `revision` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `session` ENGINE = InnoDB; ALTER TABLE `session` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `session_attribute` ENGINE = InnoDB; ALTER TABLE `session_attribute` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `system` ENGINE = InnoDB; ALTER TABLE `system` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `tags` ENGINE = InnoDB; ALTER TABLE `tags` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `team_availability` ENGINE = InnoDB; ALTER TABLE `team_availability` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `ticket` ENGINE = InnoDB; ALTER TABLE `ticket` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `ticket_change` ENGINE = InnoDB; ALTER TABLE `ticket_change` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `ticket_completion` ENGINE = InnoDB; ALTER TABLE `ticket_completion` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `ticket_custom` ENGINE = InnoDB; ALTER TABLE `ticket_custom` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `version` ENGINE = InnoDB; ALTER TABLE `version` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; ALTER TABLE `wiki` ENGINE = InnoDB; ALTER TABLE `wiki` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Run this SQL via phpMyAdmin or pipe it to MySql directly:
php convert_trac_db.php trac | mysql -uroot -p trac;
I tried to use the php file, giving it a name of convert-database-to-utf8-general-innodb.php. I am not a programmer, so, when I ran the program, it failed for me. I assume that the php file should be in the root directory. To run the program, I typed http://www.example.com/convert-database-to-utf8-general-innodb.php
If the mysql database is test and the password is hello, where would the database and password be placed in your script. Or how should the php file be altered for a specific database name, password, etc.
It has to be run from the commandlineline (via php-cli) like the post suggests, but the script can easily be altered to run via a webserver. Still you need permissions to ALTER the database and that’s not GRANTed by most hosting companies.
Thanks for the clarification…
No problem
you can use
show tables;
copy tables and put them in a column in a worksheet in excel file
then using excel concatenation capabilities so you can concatenate “alter table “+ table name+ “convert to character set utf8 collate utf8_general_ci;”
now you have a complete script to apply it to your database
You can do that in 1 query in SQL…
SELECT
CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' ENGINE = InnoDB;') AS alter_engine,
CONCAT('ALTER TABLE ', information_schema.TABLES.TABLE_SCHEMA, '.', information_schema.TABLES.TABLE_NAME, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') AS alter_char
FROM information_schema.TABLES
WHERE
information_schema.TABLES.TABLE_SCHEMA = ''
AND (
information_schema.TABLES.ENGINE != 'InnoDB'
OR information_schema.TABLES.TABLE_COLLATION != 'utf8 collate utf8_general_ci'
)
ORDER BY
information_schema.TABLES.TABLE_SCHEMA
;
You might even be able to modify the information_schema directly though I haven’t tried and it does sound dangerous, so test it on something you don’t care about!