Redmine

Hello,

I have a machine where I am using WEbrick to serve a very old instance of Redmine.

I have rolled out a version of redmine to be served with Passenger over Apache (see: http://mbrownnyc.wordpress.com/rhodecode-and-redmine/part-3-install-redmine/).

I am curious, is it possible to export the DB from the 0.8.4 instance straight into the 1.3.0 instance?

If not, what should be the proper procedure to migrate the DB?

Thanks,

Matt

[edit]

Okay, this was nightmarish... to help anyone out here's the process:

Create a backup of your old schema redmine.
Restore this to the a new schema by modifying the backup file and changing the DB to create as old_redmine.

INSERT `redmine`.`attachments` (`id`,`container_id`,`container_type`,`filename`,`disk_filename`,`filesize`,`content_type`,`digest`,`downloads`,`author_id`,`created_on`,`description`) SELECT `id`,`container_id`,`container_type`,`filename`,`disk_filename`,`filesize`,`content_type`,`digest`,`downloads`,`author_id`,`created_on`,`description` FROM `old_redmine`.`attachments`;
INSERT `redmine`.`auth_sources` (`id`,`type`,`name`,`host`,`port`,`account`,`account_password`,`base_dn`,`attr_login`,`attr_firstname`,`attr_lastname`,`attr_mail`,`onthefly_register`,`tls`) SELECT `id`,`type`,`name`,`host`,`port`,`account`,`account_password`,`base_dn`,`attr_login`,`attr_firstname`,`attr_lastname`,`attr_mail`,`onthefly_register`,`tls` FROM `old_redmine`.`auth_sources`;
INSERT `redmine`.`comments` (`id`,`commented_type`,`commented_id`,`author_id`,`comments`,`created_on`,`updated_on`) SELECT `id`,`commented_type`,`commented_id`,`author_id`,`comments`,`created_on`,`updated_on` FROM `old_redmine`.`comments`;
INSERT `redmine`.`custom_fields` (`id`,`type`,`name`,`field_format`,`possible_values`,`regexp`,`min_length`,`max_length`,`is_required`,`is_for_all`,`is_filter`,`position`,`searchable`,`default_value`) SELECT `id`,`type`,`name`,`field_format`,`possible_values`,`regexp`,`min_length`,`max_length`,`is_required`,`is_for_all`,`is_filter`,`position`,`searchable`,`default_value` FROM `old_redmine`.`custom_fields`;
INSERT `redmine`.`documents` (`id`,`project_id`,`category_id`,`title`,`description`,`created_on`) SELECT `id`,`project_id`,`category_id`,`title`,`description`,`created_on` FROM `old_redmine`.`documents`;
INSERT `redmine`.`enabled_modules` (`id`,`project_id`,`name`) SELECT `id`,`project_id`,`name` FROM `old_redmine`.`enabled_modules`;
INSERT `redmine`.`issue_categories` (`id`,`project_id`,`name`,`assigned_to_id`) SELECT `id`,`project_id`,`name`,`assigned_to_id` FROM `old_redmine`.`issue_categories`;
INSERT `redmine`.`issue_relations` (`id`,`issue_from_id`,`issue_to_id`,`relation_type`,`delay`) SELECT `id`,`issue_from_id`,`issue_to_id`,`relation_type`,`delay` FROM `old_redmine`.`issue_relations`;

#resolve discrepency with issue_statuses table:
UPDATE `redmine`.`issue_statuses` SET `name`='Assigned' WHERE `id`=2 ;
INSERT INTO `redmine`.`issue_statuses`(`id`,`name`,`is_closed`,`is_default`,`position`) VALUES (7,'In Progress',0,0,1);
INSERT INTO `redmine`.`issue_statuses`(`id`,`name`,`is_closed`,`is_default`,`position`) VALUES (8,'Suspended',0,0,8);

#resolve discrepency with issues table
INSERT `redmine`.`issues` (`id`,`tracker_id`,`project_id`,`subject`,`description`,`due_date`,`category_id`,`status_id`,`assigned_to_id`,`priority_id`,`fixed_version_id`,`author_id`,`lock_version`,`created_on`,`updated_on`,`start_date`,`done_ratio`,`estimated_hours`) SELECT `id`,`tracker_id`,`project_id`,`subject`,`description`,`due_date`,`category_id`,`status_id`,`assigned_to_id`,`priority_id`,`fixed_version_id`,`author_id`,`lock_version`,`created_on`,`updated_on`,`start_date`,`done_ratio`,`estimated_hours` FROM `old_redmine`.`issues`;
UPDATE `redmine`.`issues` SET `root_id`=`id`,`lft`=1,`rgt`=2; # seems okay

INSERT `redmine`.`journal_details` (`id`,`journal_id`,`property`,`prop_key`,`old_value`,`value`) SELECT `id`,`journal_id`,`property`,`prop_key`,`old_value`,`value` FROM `old_redmine`.`journal_details`;
INSERT `redmine`.`journals` (`id`,`journalized_id`,`journalized_type`,`user_id`,`notes`,`created_on`) SELECT `id`,`journalized_id`,`journalized_type`,`user_id`,`notes`,`created_on` FROM `old_redmine`.`journals`;
INSERT `redmine`.`members` (`id`,`user_id`,`project_id`,`created_on`,`mail_notification`) SELECT `id`,`user_id`,`project_id`,`created_on`,`mail_notification` FROM `old_redmine`.`members`;

#resolve discrepency with member_roles table
INSERT INTO `redmine`.`member_roles` (`member_id`,`role_id`) SELECT `id`, `role_id` FROM `old_redmine`.`members`;

INSERT `redmine`.`news` (`id`,`project_id`,`title`,`summary`,`description`,`author_id`,`created_on`,`comments_count`) SELECT `id`,`project_id`,`title`,`summary`,`description`,`author_id`,`created_on`,`comments_count` FROM `old_redmine`.`news`;

#resolve discrepency with projects table:
INSERT `redmine`.`projects` (`id`,`name`,`description`,`homepage`,`is_public`,`parent_id`,`created_on`,`updated_on`,`identifier`,`status`) SELECT `id`,`name`,`description`,`homepage`,`is_public`,`parent_id`,`created_on`,`updated_on`,`identifier`,`status` FROM `old_redmine`.`projects`;
UPDATE `redmine`.`projects` SET lft = 2 * id - 1, rgt = 2 * id;
UPDATE `redmine`.`projects` SET `is_public`=0;

INSERT `redmine`.`projects_trackers` (`project_id`,`tracker_id`) SELECT `project_id`,`tracker_id` FROM `old_redmine`.`projects_trackers`;
INSERT `redmine`.`queries` (`id`,`project_id`,`name`,`filters`,`user_id`,`is_public`,`column_names`) SELECT `id`,`project_id`,`name`,`filters`,`user_id`,`is_public`,`column_names` FROM `old_redmine`.`queries`;
INSERT `redmine`.`repositories` (`id`,`project_id`,`url`,`login`,`password`,`root_url`,`type`) SELECT `id`,`project_id`,`url`,`login`,`password`,`root_url`,`type` FROM `old_redmine`.`repositories`;
INSERT `redmine`.`time_entries` (`id`,`project_id`,`user_id`,`issue_id`,`hours`,`comments`,`activity_id`,`spent_on`,`tyear`,`tmonth`,`tweek`,`created_on`,`updated_on`) SELECT `id`,`project_id`,`user_id`,`issue_id`,`hours`,`comments`,`activity_id`,`spent_on`,`tyear`,`tmonth`,`tweek`,`created_on`,`updated_on` FROM `old_redmine`.`time_entries`;

#resolve discrepency with trackers table
INSERT INTO `redmine`.`trackers` (`id`,`name`,`is_in_chlog`,`position`,`is_in_roadmap`) VALUES (4,'Contracts',1,4,1);

INSERT `redmine`.`versions` (`id`,`project_id`,`name`,`description`,`effective_date`,`created_on`,`updated_on`,`wiki_page_title`) SELECT `id`,`project_id`,`name`,`description`,`effective_date`,`created_on`,`updated_on`,`wiki_page_title` FROM `old_redmine`.`versions`;
INSERT `redmine`.`watchers` (`id`,`watchable_type`,`watchable_id`,`user_id`) SELECT `id`,`watchable_type`,`watchable_id`,`user_id` FROM `old_redmine`.`watchers`;
INSERT `redmine`.`wiki_content_versions` (`id`,`wiki_content_id`,`page_id`,`author_id`,`data`,`compression`,`comments`,`updated_on`,`version`) SELECT `id`,`wiki_content_id`,`page_id`,`author_id`,`data`,`compression`,`comments`,`updated_on`,`version` FROM `old_redmine`.`wiki_content_versions`;
INSERT `redmine`.`wiki_contents` (`id`,`page_id`,`author_id`,`text`,`comments`,`updated_on`,`version`) SELECT `id`,`page_id`,`author_id`,`text`,`comments`,`updated_on`,`version` FROM `old_redmine`.`wiki_contents`;
INSERT `redmine`.`wiki_pages` (`id`,`wiki_id`,`title`,`created_on`,`protected`,`parent_id`) SELECT `id`,`wiki_id`,`title`,`created_on`,`protected`,`parent_id` FROM `old_redmine`.`wiki_pages`;
INSERT `redmine`.`wiki_redirects` (`id`,`wiki_id`,`title`,`redirects_to`,`created_on`) SELECT `id`,`wiki_id`,`title`,`redirects_to`,`created_on` FROM `old_redmine`.`wiki_redirects`;
INSERT `redmine`.`wikis` (`id`,`project_id`,`start_page`,`status`) SELECT `id`,`project_id`,`start_page`,`status` FROM `old_redmine`.`wikis`;

#resolve discrepency with workflows
DELETE FROM `redmine`.`workflows` WHERE `id` >= 91;
INSERT `redmine`.`workflows` (`id`,`tracker_id`,`old_status_id`,`new_status_id`,`role_id`) SELECT `id`,`tracker_id`,`old_status_id`,`new_status_id`,`role_id` FROM `old_redmine`.`workflows`;

The users table is quite messy, as you need to have the `id` and `userid` correlate between your old DB and your new.

Perform the post migration stuff:

rake db:migrate RAILS_ENV=production
rake tmp:cache:clear
rake tmp:sessions:clear
service httpd restart

Caveats:
1) projects_controller.rb on 1.3.1+ orders projects on the projects page in order of lft. Therefore projects will be ordered strangely, as well as subprojects will not be listed.

The fix:

• I noted each of the subproject relationships for all projects (both archived and not archived) on a spreadsheet.
• I then performed these two queries on the DB to clear and reset the relationships:
  

  update `redmine`.`projects` set `parent_id`=NULL;
  UPDATE `redmine`.`projects` SET lft = 2 * id - 1, rgt = 2 * id;
  

• I then reloaded the rails apps (my instance is hosted via passenger through apache, so I restarted apache)
• I then, by hand, reset all of the subproject relationships. This adjusts the parent_id, lft, and rgt columns in the redmine.projects table.

2) more to come...