3

I have a Ruby on Rails application that has two active environments, Stage and Production. Our development team has been using Stage, but we would like to move our data onto the production server for various reasons. However, there are conflicting ID's in the new database, so it's not as simple as pulling the data from one location and inserting it into another. For example, say we have a table called Widgets:

Widget:
  id: 9836
  name: "Staging widget"
  parent_id: 9635
  container_id: 533

If the above data is one of our widgets, we can't do the import because there is already a widget with ID 9836 and/or there is already a container with the ID 533, meaning we would need to crawl the association chain to construct new containers before putting widgets in them.

We are using MySQL databases for both environments. I thought about doing the import, and just adding 10,000 to all the appropriate columns that end in _id, as it would push us beyond the conflicting boundaries, but that seems like a bad solution.

Are there any tools, projects, or ideas that may help me solve this problem?

Mike Trpcic
  • 25,305
  • 8
  • 78
  • 114

3 Answers3

1

In the past I have solved this problem in a kludgy way by making all of the IDs negative for the data I am importing. This only works if your ID column is signed, and there are only two data sources at most, of course.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
1

If you can't use negative ids simply add a number to all imported ids. The number has to be greater than maximum id from the destination database.

aeon
  • 89
  • 1
  • Will this cause an issue with the MySQL sequence generator when it catches up to those ID's? – Mike Trpcic Sep 29 '10 at 13:53
  • It will not cause any problems. The next value generated by the autoincrement sequence will be max id +1, regardless if that id was explicitly inserted or generated by autoincrement – aeon Sep 29 '10 at 14:06
  • Here is a fragment from mysql manual detailing autoincrement behavior http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html – aeon Sep 29 '10 at 14:17
0

Well, what you should do (I am assuming this is MySQL based on the tag) is to run the following SQL (I am not a RoRails guy, but a PHP w/ MySQL, so I apolgize if this doesnt apply):

This post id one basic idea

The other option is to use a program like the phpMyAdmin (yes, it is php) which is essentially a database GUI tool. With phpMyAdmin you can export select fields of the table. Just export all the data EXCEPT the ID field and then you can just run an import of that data.

This part I am a little fuzzy on. I think you have an actual file with the Widget stuff in it. If so, create a script that goes through each row of data and makes the Widget file.

Community
  • 1
  • 1
Addo Solutions
  • 1,619
  • 3
  • 20
  • 37
  • This won't work, as importing all Widgets won't automatically update their association ID's (foreign keys) to parents and containers. That's the real issue. Also, I don't have an actual file with widget data in it, that was a YAML based construction of a database row. – Mike Trpcic Sep 29 '10 at 13:53
  • Can you create a YAML file? Because if you were to get the data in the DB you could just re-create the YAML file. Perhaps something like: http://snippets.dzone.com/posts/show/2525 – Addo Solutions Sep 29 '10 at 14:02