= Convert Trac DB, from SQLite to MySQL =

This is a copy from the mailing list ...

{{{
% echo ".dump" | sqlite trac.db > trac.sql
% mysql tracdb < trac.sql
}}}
There could be syntax errors that would have
to be manually fixed in the file trac.sql.

 1. Use trac-admin initenv to create the database structure
 2. Use mysqldump --no-data to dump the database structure into trac.mysql.sql
 3. Use sqlite .dump to dump the structure + data from sqlite into trac.sqlite.sql (unfortunately there's no option to only dump the data).
 4. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql
    You could use this simple perl script:
    {{{
$ cat cleansql
#!/usr/bin/env perl

$_ = join("", <>);

$_ =~ s/^CREATE.*?\);$//smgi;

print $_;

# NEOF

$ ./cleansql trac.sqlite.sql >  trac.sqlite.sql.dataonly
    }}}
 5. Concatenate trac.mysql.sql and trac.sqlite.sql.dataonly into trac.sql
    {{{
cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql  
    }}}

----

An alternative to the cleansql above (that works a bit better):
{{{

$ cat cleansql.py
#!/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)

$ ./cleansql.py < trac.sqlite.sql > trac.sqlite.sql.dataonly
}}}
----
One gotcha for all this is that the reports stored in the report table might not work as expected. I solved this by:
 1. Dropping the report table from my newly migrated trac mysql db
 2. Dumping the report table from the temporary trac mysql db I created to get the schema(in steps 1 and 2 at the top of the page)
 3. Loading the sql dump back in to my newly migrated trac mysql db

One catch is that this will only restore the default trac queries. If you had custom queries you will still need to re-write them if they don't work right on MySQL.

{{{
mysql -D tracmysql -e "drop table report"
mysqldump -D temptracdbforschema report > report.sql
mysql -D tracmysql < report.sql
}}}
You need to get the full report table from the mysql db you created for its schema in to your new mysql trac db. This also assumes that you did not have any custom queries and were just using the defaults.