2011
06.16

How to convert a MySQL database and all its tables to UTF-8/InnoDB

Category: Uncategorized / Tag: , , , ,  / Add Comment

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. TJM said: 2011.07.26 22:49

    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. tersmitten said: 2011.07.26 23:42

    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. TJM said: 2011.07.27 01:41

    Thanks for the clarification…

  4. tersmitten said: 2011.07.27 15:40

    No problem

  5. mahmoud said: 2012.02.14 23:41

    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. wookie said: 2014.09.24 14:49

    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!

Your Comment