In the past few days, I needed to migrate from a 6 years old server with CentOS 5 (it’s end of life is this month) to a shiny new one with Ubuntu 16.04. One of the services that was running in the old server was Trac 0.11 and I had several issues to move it to version 1.2. I like Trac and I’ve used it without any issues until now. After a while I decided to try Redmine 3.3.2, but I was not sure about the migration process.

After installing a large amount of requirements (a lot of Ruby packages), I followed this migration procedure that seemed straight-forward and should work with Trac 0.11, but it failed almost immediately for a very simple issue: The timestamps in all the database tables in Trac were in microseconds, whilst the script was expecting them in seconds.

I wrote this simple Perl script that changes the timestamps in all the Trac database tables:

#!/usr/bin/perl

use strict;
use DBI;
use POSIX;

my $dbh = DBI->connect(
    "dbi:SQLite:dbname=trac.db",
    "",
    "",
    { RaiseError => 1 },
) or die $DBI::errstr;

# attachment
print "Processing 'attachment' table ";
my $sth = $dbh->prepare("SELECT COUNT(*) FROM attachment");
$sth->execute() or die $DBI::errstr;
my $rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT type, id, filename, time FROM attachment");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($type, $id, $filename, $time) = $sth->fetchrow();

    $time = ceil($time / 1000000);
    my $sth2 = $dbh->prepare("UPDATE attachment SET time=$time WHERE type='$type' AND id='$id' AND filename='$filename'");
    $sth2->execute();
    $sth2->finish();

    print ".";
    $rows--;
}

$sth->finish();
print "\nDone\n\n";

# revision
print "Processing 'revision' table ";
$sth = $dbh->prepare("SELECT COUNT(*) FROM revision");
$sth->execute() or die $DBI::errstr;
$rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT repos, rev, time FROM revision");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($repos, $rev, $time) = $sth->fetchrow();

    $time = ceil($time / 1000000);
    my $sth2 = $dbh->prepare("UPDATE revision SET time=$time WHERE repos='$repos' AND rev='$rev'");
    $sth2->execute();
    $sth2->finish();

    print ".";
    $rows--;
}

$sth->finish();
print "\nDone\n\n";


# ticket
printf "Processing 'ticket' table";
$sth = $dbh->prepare("SELECT COUNT(*) FROM ticket");
$sth->execute() or die $DBI::errstr;
$rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT id, time, changetime FROM ticket");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($id, $time, $changetime) = $sth->fetchrow();

    $time = ceil($time / 1000000);
    $changetime = ceil($changetime / 1000000);
    my $sth2 = $dbh->prepare("UPDATE ticket SET time=$time, changetime=$changetime WHERE id='$id'");
    $sth2->execute();
    $sth2->finish();

    printf ".";
    $rows--;
}

$sth->finish();
printf "\nDone\n\n";

# ticket_change
print "Processing 'ticket_change' table ";
$sth = $dbh->prepare("SELECT COUNT(*) FROM ticket_change");
$sth->execute() or die $DBI::errstr;
$rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT ticket, time, author, field, oldvalue, newvalue FROM ticket_change");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($ticket, $time, $author, $field, $oldvalue, $newvalue) = $sth->fetchrow();

    #my $sth2 = $dbh->prepare("DELETE FROM ticket_change WHERE ticket='$ticket' AND time='$time' AND field='$field'");
    #$sth2->execute();

    my $prev_time = $time;
    $time = ceil($time / 1000000);
    #my $query = "INSERT INTO ticket_change (ticket, time, author, field, oldvalue, newvalue) VALUES ('$ticket', '$time', '$author', '$field', '".quotemeta($oldvalue)."', '".quotemeta($newvalue)."'";
    #printf("query: $query\n");
    #my $sth2 = $dbh->prepare($query);
    my $sth2 = $dbh->prepare("UPDATE ticket_change SET time='$time' WHERE ticket='$ticket' AND time='$prev_time' AND field='$field'");
    $sth2->execute();
    $sth2->finish();

    print ".";
    $rows--;
}

$sth->finish();
print "\nDone\n\n";

# version
print "Processing 'version' table ";
$sth = $dbh->prepare("SELECT COUNT(*) FROM version");
$sth->execute() or die $DBI::errstr;
$rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT name, time FROM version");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($name, $time) = $sth->fetchrow();

    $time = ceil($time / 1000000);
    my $sth2 = $dbh->prepare("UPDATE version SET time=$time WHERE name='$name'");
    $sth2->execute();
    $sth2->finish();

    print ".";
    $rows--;
}

$sth->finish();
print "\nDone\n\n";

# wiki
print "Processing 'wiki' table ";
$sth = $dbh->prepare("SELECT COUNT(*) FROM wiki");
$sth->execute() or die $DBI::errstr;
$rows = $sth->fetchrow();
$sth->finish();

$sth = $dbh->prepare("SELECT name, version, time FROM wiki");
$sth->execute() or die $DBI::errstr;

while ($rows) {
    my ($name, $version, $time) = $sth->fetchrow();

    $time = ceil($time / 1000000);
    my $sth2 = $dbh->prepare("UPDATE wiki SET time=$time WHERE name='$name' AND version='$version'");
    $sth2->execute();
    $sth2->finish();

    print ".";
    $rows--;
}

$sth->finish();
print "\nDone\n\n";

$dbh->disconnect();

Assuming that you have installed all the requirements (including the Perl ones with CPAN) and that the SQLite3 Trac database is called trac.db, execute the above script followed by the Redmine migration script:

# Make a copy of the trac database just in case...
cp trac.db trac.db.back

# Execute the Perl script that fixes the timestamps
perl trac2redmine.pl 

# Execute the Ruby migration script. Adjust the target environment, normally it is 'production'
rake redmine:migrate_from_trac RAILS_ENV="production"

The Ruby (rake) script asks for your Trac settings:

Trac directory []: /var/trac/myproject
Trac database adapter (sqlite, sqlite3, mysql, postgresql) [sqlite]:
Database encoding [UTF-8]:
Target project identifier []: myproject

and outputs all the items that were migrated without any errors.

I checked immediately in Redmine if everything was migrated correctly and I noticed a couple of issues:

• The attachments in the wiki and tickets are not migrated
• The wiki links are corrupted. The whole wiki was migrated (all the pages and their content is correct), but the links got corrupted.

The attachments were not a big issue since I had a few of them.
The wiki links was a bit annoying, I went to the Index by title, check the new name and edit the pages that link the correspondent page. My wiki is large, but it’s not heavily linked, so it didn’t take too much time to fix it.

Now that I’ve been using Redmine for a week I feel quite comfortable with it and I think that its integration with Git is better than Trac’s.