{"id":476,"date":"2017-03-12T19:08:31","date_gmt":"2017-03-12T18:08:31","guid":{"rendered":"http:\/\/paguilar.org\/?p=476"},"modified":"2017-03-12T19:08:31","modified_gmt":"2017-03-12T18:08:31","slug":"migrating-from-trac-to-redmine","status":"publish","type":"post","link":"https:\/\/paguilar.org\/?p=476","title":{"rendered":"Migrating from Trac to Redmine"},"content":{"rendered":"<p>In the past few days, I needed to migrate from a 6 years old server with CentOS 5 (it&#8217;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 <a href=\"https:\/\/trac.edgewall.org\/\" target=\"_blank\">Trac<\/a> 0.11 and I had several issues to move it to version 1.2. I like Trac and I&#8217;ve used it without any issues until now. After a while I decided to try <a href=\"http:\/\/www.redmine.org\/projects\/redmine\" target=\"_blank\">Redmine<\/a> 3.3.2, but I was not sure about the migration process.<\/p>\n<p>After installing a large amount of requirements (a lot of Ruby packages), I followed <a href=\"http:\/\/www.redmine.org\/projects\/redmine\/wiki\/RedmineMigrate\" target=\"_blank\">this migration procedure<\/a> 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.<\/p>\n<p>I wrote this simple Perl script that changes the timestamps in all the Trac database tables:<\/p>\n<pre lang=perl>\r\n#!\/usr\/bin\/perl\r\n\r\nuse strict;\r\nuse DBI;\r\nuse POSIX;\r\n\r\nmy $dbh = DBI->connect(\r\n    \"dbi:SQLite:dbname=trac.db\",\r\n    \"\",\r\n    \"\",\r\n    { RaiseError => 1 },\r\n) or die $DBI::errstr;\r\n\r\n# attachment\r\nprint \"Processing 'attachment' table \";\r\nmy $sth = $dbh->prepare(\"SELECT COUNT(*) FROM attachment\");\r\n$sth->execute() or die $DBI::errstr;\r\nmy $rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT type, id, filename, time FROM attachment\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($type, $id, $filename, $time) = $sth->fetchrow();\r\n\r\n    $time = ceil($time \/ 1000000);\r\n    my $sth2 = $dbh->prepare(\"UPDATE attachment SET time=$time WHERE type='$type' AND id='$id' AND filename='$filename'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    print \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprint \"\\nDone\\n\\n\";\r\n\r\n# revision\r\nprint \"Processing 'revision' table \";\r\n$sth = $dbh->prepare(\"SELECT COUNT(*) FROM revision\");\r\n$sth->execute() or die $DBI::errstr;\r\n$rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT repos, rev, time FROM revision\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($repos, $rev, $time) = $sth->fetchrow();\r\n\r\n    $time = ceil($time \/ 1000000);\r\n    my $sth2 = $dbh->prepare(\"UPDATE revision SET time=$time WHERE repos='$repos' AND rev='$rev'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    print \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprint \"\\nDone\\n\\n\";\r\n\r\n\r\n# ticket\r\nprintf \"Processing 'ticket' table\";\r\n$sth = $dbh->prepare(\"SELECT COUNT(*) FROM ticket\");\r\n$sth->execute() or die $DBI::errstr;\r\n$rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT id, time, changetime FROM ticket\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($id, $time, $changetime) = $sth->fetchrow();\r\n\r\n    $time = ceil($time \/ 1000000);\r\n    $changetime = ceil($changetime \/ 1000000);\r\n    my $sth2 = $dbh->prepare(\"UPDATE ticket SET time=$time, changetime=$changetime WHERE id='$id'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    printf \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprintf \"\\nDone\\n\\n\";\r\n\r\n# ticket_change\r\nprint \"Processing 'ticket_change' table \";\r\n$sth = $dbh->prepare(\"SELECT COUNT(*) FROM ticket_change\");\r\n$sth->execute() or die $DBI::errstr;\r\n$rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT ticket, time, author, field, oldvalue, newvalue FROM ticket_change\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($ticket, $time, $author, $field, $oldvalue, $newvalue) = $sth->fetchrow();\r\n\r\n    #my $sth2 = $dbh->prepare(\"DELETE FROM ticket_change WHERE ticket='$ticket' AND time='$time' AND field='$field'\");\r\n    #$sth2->execute();\r\n\r\n    my $prev_time = $time;\r\n    $time = ceil($time \/ 1000000);\r\n    #my $query = \"INSERT INTO ticket_change (ticket, time, author, field, oldvalue, newvalue) VALUES ('$ticket', '$time', '$author', '$field', '\".quotemeta($oldvalue).\"', '\".quotemeta($newvalue).\"'\";\r\n    #printf(\"query: $query\\n\");\r\n    #my $sth2 = $dbh->prepare($query);\r\n    my $sth2 = $dbh->prepare(\"UPDATE ticket_change SET time='$time' WHERE ticket='$ticket' AND time='$prev_time' AND field='$field'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    print \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprint \"\\nDone\\n\\n\";\r\n\r\n# version\r\nprint \"Processing 'version' table \";\r\n$sth = $dbh->prepare(\"SELECT COUNT(*) FROM version\");\r\n$sth->execute() or die $DBI::errstr;\r\n$rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT name, time FROM version\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($name, $time) = $sth->fetchrow();\r\n\r\n    $time = ceil($time \/ 1000000);\r\n    my $sth2 = $dbh->prepare(\"UPDATE version SET time=$time WHERE name='$name'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    print \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprint \"\\nDone\\n\\n\";\r\n\r\n# wiki\r\nprint \"Processing 'wiki' table \";\r\n$sth = $dbh->prepare(\"SELECT COUNT(*) FROM wiki\");\r\n$sth->execute() or die $DBI::errstr;\r\n$rows = $sth->fetchrow();\r\n$sth->finish();\r\n\r\n$sth = $dbh->prepare(\"SELECT name, version, time FROM wiki\");\r\n$sth->execute() or die $DBI::errstr;\r\n\r\nwhile ($rows) {\r\n    my ($name, $version, $time) = $sth->fetchrow();\r\n\r\n    $time = ceil($time \/ 1000000);\r\n    my $sth2 = $dbh->prepare(\"UPDATE wiki SET time=$time WHERE name='$name' AND version='$version'\");\r\n    $sth2->execute();\r\n    $sth2->finish();\r\n\r\n    print \".\";\r\n    $rows--;\r\n}\r\n\r\n$sth->finish();\r\nprint \"\\nDone\\n\\n\";\r\n\r\n$dbh->disconnect();\r\n\r\n<\/pre>\n<p>Assuming that you have installed all the requirements (including the Perl ones with CPAN) and that the SQLite3 Trac database is called <em>trac.db<\/em>, execute the above script followed by the Redmine migration script:<\/p>\n<pre lang='bash'>\r\n# Make a copy of the trac database just in case...\r\ncp trac.db trac.db.back\r\n\r\n# Execute the Perl script that fixes the timestamps\r\nperl trac2redmine.pl \r\n\r\n# Execute the Ruby migration script. Adjust the target environment, normally it is 'production'\r\nrake redmine:migrate_from_trac RAILS_ENV=\"production\"\r\n<\/pre>\n<p>The Ruby (rake) script asks for your Trac settings:<\/p>\n<pre lang='bash'>\r\nTrac directory []: \/var\/trac\/myproject\r\nTrac database adapter (sqlite, sqlite3, mysql, postgresql) [sqlite]:\r\nDatabase encoding [UTF-8]:\r\nTarget project identifier []: myproject\r\n<\/pre>\n<p>and outputs all the items that were migrated without any errors.<\/p>\n<p>I checked immediately in Redmine if everything was migrated correctly and I noticed a couple of issues:<\/p>\n<ul>\n<li>The attachments in the wiki and tickets are not migrated<\/li>\n<li>The wiki links are corrupted. The whole wiki was migrated (all the pages and their content is correct), but the links got corrupted.<\/li>\n<\/ul>\n<p>The attachments were not a big issue since I had a few of them.<br \/>\nThe wiki links was a bit annoying, I went to the <em>Index by title<\/em>, check the new name and edit the pages that link the correspondent page. My wiki is large, but it&#8217;s not heavily linked, so it didn&#8217;t take too much time to fix it.<\/p>\n<p>Now that I&#8217;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&#8217;s.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In the past few days, I needed to migrate from a 6 years old server with CentOS 5 (it&#8217;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\u2026 <span class=\"read-more\"><a href=\"https:\/\/paguilar.org\/?p=476\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[44,62,45,14],"tags":[],"class_list":["post-476","post","type-post","status-publish","format-standard","hentry","category-databases","category-git","category-linux","category-networking"],"_links":{"self":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=476"}],"version-history":[{"count":5,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/476\/revisions"}],"predecessor-version":[{"id":483,"href":"https:\/\/paguilar.org\/index.php?rest_route=\/wp\/v2\/posts\/476\/revisions\/483"}],"wp:attachment":[{"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/paguilar.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}