2011
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&#91;0&#93;." <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;

6 comments so far

Add Your Comment
  1. Mischa ter Smitten

    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.

  2. Mischa ter Smitten

    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.

  3. Mischa ter Smitten

    Thanks for the clarification…

  4. Mischa ter Smitten

    No problem

  5. Mischa ter Smitten

    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

  6. Mischa ter Smitten

    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!