Transitioning Systems

Added by Tom DeMay over 9 years ago

so if this has already been posted. Seems like it would have been since I would think it's a pretty common issue. I couldn't find anything on it here or through google.

We're transitioning bug tracking systems. I have installed the redmine_importer. My goal is to be able to import the data and preserve issue id's from the old system. I expect to have to do more than one import. An initial one while I'm setting up the system and then a final one once I have verified the system is ready to go live.

When I do the import the Id's are being sequential and not preserving the id from the old system. When I perform import again, I would expect it to update existing records, but instead it creates an additional one. I believe I read somewhere this is because I created an issue so I started the numbering sequence. Is there a way to reinitialize that without losing any data?

And....
Once I do the initial import, can I create issues and then reimport again to update any modified issues? Can I possible set the sequencing at a new number?

Our current system ids are around 12,000. Is it possible to somehow start the sequencing at 15000 so I can reserve the lower ones for imports?

Any tips?

Replies (7)

RE: Transitioning Systems - Added by Felix Schäfer over 9 years ago

I don't think the importers (or at least not all) are built so that you can update already important data, and tinkering with the sequence would mean meddling with the DB and other parts deeper down redmine.

My best advice: either transition or don't, but do it all in one step.

RE: Transitioning Systems - Added by Tom DeMay over 9 years ago

I was able to solve this.

I was able to tweak redmine_importer to make this work. And it seems automatically redmine will adjust and use the latest number that hasn't been used.

All I did was force "update exists issues" to be checked and modified the code so that if it doesn't exist, it will import a new one, or update an existing one. I also had to change to set the id explicitedly. Now it works perfectly for my need. You can keep reimporting over and over again. my existing ticket numbers go up to about 12,500. I'll create a bogus ticket at 15,000 so that any new ones get new number above 15,000 and I can keep importing tickets less than 15,000 over and over and over again. I've tested this and it's working perfectly.

I can publish my diff if someone wants them, but it still needs work before i can go into the plugin. I don't know ruby and I had a difficult time getting simple concepts to work.

My changes REQUIRES the Id being mapped to an incoming field. So there are UI and error handling stuff that still needs to be added.

RE: Transitioning Systems - Added by Mischa The Evil over 9 years ago

A bit off-topic maybe but maybe interesting after-all: I was experimenting with a recent-ish trunk instance of Redmine recently and decided to see what the precise consequences would be if I manually changed the auto_increment attribute-value of the issues table. How would such be handled by the app, the DB itself and, more importantly, does it work?

Situation before:
  • Redmine trunk @ r4533:
    • 6 projects
    • 39 issues spread over the different projects
  • Database (MySQL 4.1.x):
    • auto_increment value of the issues table: 40
    • cardinality of the related indexes on the issues table:
      • PRIMARY: 39
      • index_issues_on_created_on: 39
      • index_issues_on_root_id_and_lft_and_rgt: 39

Alteration:

  • hard-edited the auto_increment value to 15001 using a query like:
    ALTER TABLE `issues` AUTO_INCREMENT =15001
Situation after:
  • Database (MySQL 4.1.x):
    • auto_increment value of the issues table: 15001
    • cardinality of the related indexes on the issues table:
      • PRIMARY: 39
      • index_issues_on_created_on: 39
      • index_issues_on_root_id_and_lft_and_rgt: 39
Does it work?:
  1. created a new issue in a random project
    • newly created issue has, as expected, an id of 15001
      • auto_increment value of the issues table: 15002
      • cardinality of the related indexes on the issues table:
        • PRIMARY: 40
        • index_issues_on_created_on: 40
        • index_issues_on_root_id_and_lft_and_rgt: 40
  2. referenced the new issue from several places (docs, wiki, repo, etc...)
    • references are linked correctly in any place
  3. created another issue in a random project
    • newly created issue has, as expected, an id of 15002
      • auto_increment value of the issues table: 15003
      • cardinality of the related indexes on the issues table:
        • PRIMARY: 41
        • index_issues_on_created_on: 41
        • index_issues_on_root_id_and_lft_and_rgt: 41
  4. referenced several already existing issues (1-39) from several places (docs, wiki, repo, etc...)
    • all references are linked correctly in any place

So, my quick conclusion is that it works without major troubles. Though, it is possible that (small) issues do come up in some more specific parts of Redmine which I haven't tested here thoroughly.
Can we stretch the edges of the experiment even more?

Back to before:
  1. deleted the two newly created issues (15001 and 15002) through the interface provided by Redmine
  2. hard-edited the auto_increment value back to 40 using a query like:
    ALTER TABLE `issues` AUTO_INCREMENT =40
    • auto_increment value of the issues table: 40
    • cardinality of the related indexes on the issues table:
      • PRIMARY: 39
      • index_issues_on_created_on: 39
      • index_issues_on_root_id_and_lft_and_rgt: 39
Does even that work?:
  1. created a new issue in a random project
    • newly created issue has, as expected, an id of 40
      • auto_increment value of the issues table: 41
      • cardinality of the related indexes on the issues table:
        • PRIMARY: 40
        • index_issues_on_created_on: 40
        • index_issues_on_root_id_and_lft_and_rgt: 40
  2. referenced the new issue from several places (docs, wiki, repo, etc...)
    • references are linked correctly in any place
  3. created another issue in a random project
    • newly created issue has, as expected, an id of 41
      • auto_increment value of the issues table: 42
      • cardinality of the related indexes on the issues table:
        • PRIMARY: 41
        • index_issues_on_created_on: 41
        • index_issues_on_root_id_and_lft_and_rgt: 41
  4. referenced several already existing issues (1-39) from several places (docs, wiki, repo, etc...)
    • all references are linked correctly in any place

So, my second quick conclusion is that it works without major troubles even if you need to change it under-while or need to revert it completely after-all. Though, this "test" should be considered highly experimental and always consider that YMMV...

 
Now, back on-topic... ;-)

RE: Transitioning Systems - Added by Felix Schäfer over 9 years ago

Mischa: I haven't read all thoroughly, but I wouldn't expect problems in redmine even with random IDs, or at least I can't think of any feature that relies on IDs being sequential (apart maybe from the issue list where you can sort by ID…). Redmine (or to be precise, the ActiveRecord libs from rails) rely on the DB to get a new and free ID, which the DBs traditionally do with an auto-increment (it's the "cheapest" solution to that problem), if you tweak your DB to return hashes or whatever instead of sequential IDs, it should work the same.

The only problem I can think of meddling with the auto-increment is when setting it lower than the highest existing ID: I'm not sure the DB or rails checks the availability of the "next" ID as per the auto-increment or if they just assume it's free because no one should be meddling with the auto-increment…

RE: Transitioning Systems - Added by Mischa The Evil over 9 years ago

Felix Schäfer wrote:

[...]

The only problem I can think of meddling with the auto-increment is when setting it lower than the highest existing ID: I'm not sure the DB or rails checks the availability of the "next" ID as per the auto-increment or if they just assume it's free because no one should be meddling with the auto-increment…

It seems it is getting fixed automatically when adding a new record, so this use-case is covered by - I guess - Rails itself.

RE: Transitioning Systems - Added by Tom DeMay over 9 years ago

I have had GREAT success with mysql. I can manipulate the contents of these tables FREELY and not have to worry about the auot-increment. mysql seems to monitor the highest number written to the DB and automatically uses the next highest value. I can update/insert to the id fields with no problem. I don't have to set any options to allow it to do so (as with MS SQL Server set identity insert on)

I figured out how to set mysql up as a linked server in MS SQL and I'm using distributed queries to migrate the data from one schema to another. I'm updating/inserting, blowing away and updating/inserting again. Haven't had any problems.

We narrowed our choices down to 6 systems and ended up going with redmine. So far I (and everyone else in our organization) have been REALLY pleased with our selection.

RE: Transitioning Systems - Added by Mischa The Evil over 9 years ago

Tom DeMay wrote:

[...]

We narrowed our choices down to 6 systems and ended up going with redmine. So far I (and everyone else in our organization) have been REALLY pleased with our selection.

Nice to read about such stories... ;)

(1-7/7)