Convert Trac DB, from SQLite to MySQL

This is a copy from the mailing list …

% echo ".dump" | sqlite3 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 truncate to get rid of your default data - it will be replaced by the import:

   TRUNCATE `attachment` ;
   TRUNCATE `auth_cookie` ;
   TRUNCATE `component` ;
   TRUNCATE `enum` ;
   TRUNCATE `milestone` ;
   TRUNCATE `node_change` ;
   TRUNCATE `permission` ;
   TRUNCATE `report` ;
   TRUNCATE `revision` ;
   TRUNCATE `session` ;
   TRUNCATE `session_attribute` ;
   TRUNCATE `system` ;
   TRUNCATE `ticket` ;
   TRUNCATE `ticket_change` ;
   TRUNCATE `ticket_custom` ;
   TRUNCATE `version` ;
   TRUNCATE `wiki` ;
   

4. Use sqlite .dump to dump the structure + data from sqlite into trac.sqlite.sql (unfortunately there's no option to only dump the data).
5. Remove all the structure ('create table' and 'create index') from trac.sqlite.sql You could use this simple perl script (or the more complete python script mentioned below):

   $ cat cleansql
   #!/usr/bin/env perl
   
   $_ = join("", <>);
   
   $_ =~ s/^CREATE.*?\);$//smgi;
   
   print $_;
   
   # NEOF
   
   $ ./cleansql trac.sqlite.sql >  trac.sqlite.sql.dataonly
   

6. Concatenate trac.mysql.sql and trac.sqlite.sql.dataonly into trac.sql

   cat trac.mysql.sql trac.sqlite.sql.dataonly > trac.sql  
   

---

You should open the trac.sql file in a text editor of choice and replace all \ with \\ as otherwise, these will be interpreted as escape symbols by mySQL.

---

One thing to note is that SQLite uses utf8 as default. If you have your server configure with encoding other than utf8 and you can't change that, you are going to get your data bad encoded.

Despite the fact that you may probably already set your MySQL database to use utf8 as a default character set, you could have problems when you import data. It's very important to keep in mind that your connection encoding counts. So be sure that whatever software you use as client you set your charset as utf8. As an example for the console client:

mysql trac_db -h host -u user -p --default-character-set=utf8 < 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.

---

One alternative to dropping the reports is to log into trac with a privileged user that can edit the reports, call up one report after the other, click on EDIT and look out for CAST(p.value AS int). Change this to CAST(p.value AS unsigned) and mySQL should be happy. This might be easier than recreating all your custom reports.

---

I used: ​http://trac-hacks.org/wiki/SqliteToMySqlScript

This worked really well… painless. The only issues you might run into are with character collation on your database. With the default latin1 character set and latin1 collation, /path/to/file and /path/to/FILE are identical, potentially causing index collisions. It is highly advised that you create your database with UTF8 binary collation and set the server's default table type to InnoDB:

• in my.cnf:

  [mysqld]
  default-table-type = InnoDB
  

• in the mysql client:

  CREATE DATABASE trac DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
  

Also, look at ticket:6986 to significantly improve Trac performance with MySQL.