Today I migrated Trac 0.10.3 to 0.10.4 and finally managed to get Trac set up with MySQL. Upgrading Trac went fine. Migrating from one database to another was a bit difficult, though. The script for migrating from SQLite to MySQL on Trac's site didn't quite do the job. It failed to convert the quotes from SQLite output into backticks for MySQL. I wrote a new script and will outline how to work through the whole process below.

Here's a short Python script for altering the output of SQLite that converts to input that should work with MySQL in most cases:

#!/usr/bin/env python
import sys
import re
file = sys.stdin.read()
file = re.sub(r'(CREATE (TABLE|INDEX)[^;]*|COMMIT|BEGIN TRANSACTION);', '', file)
file = re.sub(r'INSERT INTO "([^"]+)"', lambda m: 'INSERT INTO `%s`' % m.groups(1), file)
sys.stdout.write(file)

To create a MySQL database that works with Trac, you'll need to issue the following commands:

create database dbname DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
grant all privileges on dbname.* to username identified by 'password';

Here's what you'll have to do now to set thins up:

1. Save the script above anywhere you want (referred to later as /path/to/alter.py)
2. Create a temporary database called trac_tmp using the SQL code above.
3. Create your real database (referred to later as new_database)
4. Use trac-admin to create a temporary Trac project set up to use the database trac_tmp. The database will be populated with the proper structure for Trac.

You're all set up now. All that's left to do is issue the following commands:

echo ".dump" | sqlite3 /path/to/trac.db | /path/to/alter.py > trac.sqlite.data
mysqldump --no-data trac_tmp > trac.mysql.structure
cat trac.mysql.structure trac.sqlite.data > trac.sql
mysql new_database < trac.sql

Tags: MySQL, SQLite, Trac, Webserver