merging xplanner tables

[sgillesp]

Hi- I have two mysql databases, both are 5.0.18. I have two dbs named "xplanner" that I would

like to merge into a single database instance. There are users who have the same name but different

ids and/or userids in the "person" table:

 

first database:

 

mysql> use xplanner

mysql> select name,userid,id from person;

| Gomer Pyle        | gomerp | 245 |

| Natas Kaupas    | natask | 246 |

| Jim Morrison      | jimm    | 247 |

 

second database:

 

mysql> select name,userid,id from person;

| Gomer Pyle        | gomerp | 123 |

| Natas Kaupas    | nkyay  | 124 |

| Jim Morrison      | jimmym| 125 |

 

I am planning on using either mysqldump, bcp or replication to get both databases synchronized.

Ultimately I am going to be moving a ton of projects from various xplanner application databases

into one single xplanner instance. I have tables that look like this:

 

+------------------------+

| attribute              |

| datasample            |

| history                |

| identifier            |

| integration            |

| iteration              |

| note                  |

| notification_receivers |

| patches                |

| permission            |

| person                |

| person_role            |

| project                |

| role                  |

| story                  |

| task                  |

| time_entry            |

| xdir                  |

| xfile                  |

+------------------------+

 

and I'm sure that if there are inconsistencies with the id post-move, there will be problems

getting the application to work for these users.

 

I am not a DBA, I'm a UNIX sysadmin who is stumbling in the dark with this MySQL stuff. Any

help would be most appreciated.

 

Thanks,

 

Scott

[todding01]

Are the two tables identical except for the ID?

[sgillesp]

 

On these two, the two tables are identical.

 

Going forward, there will be additional userids in separate "xplanner" databases that will need to

be merged. Also, there will be duplication, as shown below. Natas Kaupas could have ID=100

on one xplanner DB, 200 on a second, and 300 on a third. Or, Natas Kaupas could not exist

in some other xplanner DB, but I ultimately want Natas to be in the merged, final DB that

everything will move to, and I want him to have access to all the stuff he used to have access

to. Natas is an example, I'd like everyone to move without pain, if possible.

[todding01]

It will take some planning and elbow grease.  Also, some down time for registering new users where you can perform the merge.  Probably just a DISTICT query on username from each table and use those values to create the merged table.  Then, most likely, update the permission and person_role table to reflect the new userId values.

[fenway]

You'll have to decide how to find "duplicates" first.