Merging two Flarum databases into one?

jacko

I have asked about this as a part of another thread but I feel this will get better attention as a separate discussion.

The task:

I'd like to merge two separate Flarum instances into one. I think this isn't going to be easy, because user IDs, post IDs discussion IDs and tag IDs will overlap. So there's a question if there is an easy way to make a script that would look carefully at those factors and that would automatically create changes to one of the databases to make required space in the IDs of the other database.

Possible solution?

Something that would probably work is adding some large number to all IDs. For example each ID would be incremented by 100000 (100k). Current user ID = 5 would become ID = 100005 and so on.

Any thoughts if that would be the easiest approach? Would you have some other suggestions?

jordanjay29

jacko Possible solution?

Something that would probably work is adding some large number to all IDs. For example each ID would be incremented by 100000 (100k). Current user ID = 5 would become ID = 100005 and so on.

A better way would be to check the last row of the two databases user tables, determine which one is larger, and the add the row number of the largest user table to every user id of the smaller database.

jacko

jordanjay29 add the row number of the largest user table to every user id of the smaller database

I would have to proceed with the same approach to discussion, post and tag IDs. Easier for me to make a mistake. Adding a big number like 100k would make it easier for me to identify posts from the imported database for further manipulation such as merging two user accounts, wouldn't it?

luceos

If you do this you would have to change all related entries as well. Discussions have the OP user registered etc. Best thing is to mutate one of the databases to increment with the value of the other. However doing so would require several loops over the same tables (data) for several (if not all) tables:

users: increment user Id with A
posts: increment post id with B and user Id's with A
discussions: increment discussion Id with C and user Id's with A and any posts with B
posts: increment discussion Ids with C

There will be more tables that need this.

jacko

luceos Best thing is to mutate one of the databases to increment with the value of the other.

Why is it the best?

luceos doing so would require several loops over the same tables (data) for several (if not all) tables:

users: increment user Id with A
posts: increment post id with B and user Id's with A
discussions: increment discussion Id with C and user Id's with A and any posts with B
posts: increment discussion Ids with C
There will be more tables that need this.

That's why I thought about replacing A,B,C, ... with a large number. That would perhaps allow me to skip checking what A, B, C, D, ... values actually are, wouldn't it? Could this gap in IDs create issues in future, when for example post IDs finally reach 100k?

Franz

jacko Could this gap in IDs create issues in future, when for example post IDs finally reach 100k?

It would probably be wise to change the AUTO_INCREMENT counters to start after the highest of the newly inserted users. (In fact, MySQL might be smart enough to do this automatically - i.e. increase the counter whenever you explicitly create a record with a higher ID than the current value of the counter.)

Kulga

Franz In fact, MySQL might be smart enough to do this automatically

I created a discussion, in database changed Discussion ID from 2 -> 4 (Also had to change relevant parts of discussion_tags, posts, s, user_discussions (maybe others))
It then loaded at /d/4 instead of /d/2
Then I created another discussion, no error at /d/3, then I created another (which would normally go to /d/4) and this error showed

So it doesn't work as currently setup (at least in my setup) ?

Franz

Kulga Well, that's changing the ID, I was talking about inserting new ones explicitly.