News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

mergeSMF.php - Script to merge two SMF forums, v2.0.17

Started by shawnb61, October 06, 2020, 02:05:26 AM

Previous topic - Next topic

shawnb61

Here is a link to the latest version of the mergeSMF.php script: mergeSMF.php

History:
Original version for SMF 1.0.x by Oldiesmann at SimpleMachines.org
Modified version for SMF 1.1RC3 by Resourcez at resourcez.biz (way back in 2006)
Modified version for SMF 2.0.6 by bfeist (fall, 2013). Let's start this new version at 2.0.
Modified version for SMF 2.0.17 by shawnb61 (Sept, 2020).  Tested with SMF 2.0.17 & php 7.3.
Modified version for SMF 2.0.17 to better duplicate member handling & logging of any member renames.  Thanks to GL700Wing, Dec, 2020

Description:
- This script will merge two SMF forums. It will take the boards, topics, members, messages, etc., from a SECONDARY forum and merge them into a PRIMARY forum.
- You need to be comfortable with PHP, and wrapping your head around databases, etc., to use this script. It works, but it will take some trial and error to get everything right.
- Given SMF's flexibility, there will be wrinkles... Anticipate issues. Backups, backups, backups...
- We are all indebted to Oldiesmann for the deep institutional SMF knowledge baked into the script & approach.
- This utility is not officially supported by the SMF team. I was asked to do a test run for my forum, so I brought this script current to do so, and am now sharing it here in case others find it useful.

Prerequisites:
1. BACKUP everything, file systems & databases, twice.  In addition, ask your host to do so.  If things go sideways, you must be able to restore everything.
2. Practice all the steps below in a test forum.  If you cannot do so, do not attempt this.
3. Primary & secondary forums must be in perfect working order.  E.g., all routine maintenance should be run, integrity checks run. A broken forum before merging = a horrifically mangled forum after merging...
4. Collations & charsets must be aligned across the forums & tables.
5. Forums must be offline and frozen.  A single update in the middle of execution can break everything...

Instructions:
1. Restore your two forums and get them working side-by-side on your site, both installations within the same database (just use a different database prefix like smf2_ for the SECONDARY one prior to the DB restore).
2. Edit mergeSMF.php in a text editor and change the prefix to match the database prefix of the PRIMARY smf board (define('PRIMARY_DB_PREFIX', 'smf_') - change smf_ to whatever you used...).
3. Also set $secondary_suffix to be the value appended to the end of member names & group names & other records in order to make them unique, in case of conflicts with the primary forum.
4. Save this modified file, put it in the directory for the SECONDARY smf installation and run it (just like you would run install.php or the converter).
5. Follow the instructions, clicking the "Continue" link as needed.
6. Pay careful attention to any errors reported.
7. When completed, copy all files in your SECONDARY attachments/to_move_to_primary directories to the primary attachments directories.
8. Copy all files in your SECONDARY custom_avatar/to_move_to_primary directory to the primary custom_avatar directory.  Be careful to confirm the name of the directory, it is different for different forums.
9. Copy all files in your SECONDARY avatars directory to the primary avatars directory.
10. Once you're done, if everything worked, login to your PRIMARY SMF board, go to Admin -> Forum Maintenance and click on "Recount all totals and statistics" - this will update everything for you.
11. Check for any members in the new (merged) PRIMARY SMF installation that end with your $secondary_suffix. If desired, these members can be merged with the members of the same name using a tool like SMF Admin Toolbox (https://github.com/Spuds/SMF-Admin-Toolbox)
12. Carefully audit all board permissions, membergroups, and subscriptions.
13. Clear your cache.

Approach:
- This script basically makes the contents of the secondary forum unique, and then copies the contents over to the primary forum.  Default & duplicate content are removed from the secondary forum.  Remaining content is made unique by incrementing the keys safely beyond values used in the primary forum (e.g., board IDs), or by appending the $secondary_suffix to the value (e.g., member names).
- Records merged: categories, boards, topics, members, messages, PMs, attachments, polls, poll_choices, log_actions, membergroups, permission_profiles, moderators, notifications, subscriptions, log_subscribed, log_reported, log_reported_comments, ban_groups and ban_items.
- Records not merged: Anything not named above... Packages, themes, logs, calendars, search tables, custom_fields.  Any tables that are new in 2.1, e.g., support for likes.
- Member names, real names & email addresses will be made unique by adding the $secondary_suffix.  Duplicate emails will have the suffix, thus will not work.
- When this script is completed, the secondary forum is unusable.

Enhancements included with this version:
- Many new record types added: Permissions, subscriptions, moderation activity, moderators, bans, & associated logs.  The idea was to bring member history along with each member.
- Category & board hierarchy & order are now preserved. The secondary forum appears to be 'appended' intact to the end of the primary forum in the board index.
- Multiple attachment directories supported.
- Avatars & custom avatars are brought over.
- Mismatches of columns between the primary & secondary forums are handled; no records dropped.
- There is no notion of 'bigger' vs 'smaller' forums. I have merged a massive forum into a smaller one & vice-versa.
- Now uses mysqli.
- Improved error reporting; improved reporting of actions taken.
- 2.1 and 2.0 support.  2.1 tested with 2.1 RC3.

Notes on Permissions:
The basic idea is to only bring over what is unique to the secondary forum:
- Profiles: Only unique profiles will be brought over.  Default & standard profiles should be defined in the primary forum.
- Membergroups: Only non-default, non-guest, membergroups will be brought over.  Default & guest behavior should be defined in the primary forum.
- Post-count-based membergroups will not be brought over - what is in the primary forum should apply.
- Assignments to post-count-based groups are not brought over.

Other Considerations:
- If you had a custom search index, it will need to be rebuilt.
- Links to other posts, including quote links, will be incorrect unless you use separate utilities to fix them.

Other Utilities that may be of help:
- smf_urls_paths.php - I used this to update all the links in the old secondary posts to properly point to the new forum.
- smf_fix_log_actions.php - I used this to clean up log_actions before running the merge. Checks & fixes all string lengths. UTF8 conversions can break string lengths in serialized strings.
- smf_quote_link_fixer.php - Fixes quote links. Note if it can't find the message, it strips the link. Good to run BEFORE doing the merge, so links are solid before you begin. Do not run this immediately after a merge or it will break your links irreparably, as the message numbers would be incorrect.

If you don't understand what these are doing, you probably shouldn't run them...

Revision history:
2.0 Nov 2/2013 - Initial updated script for use with 2.0.6
2.0.1 Nov 2/2013 - Added an initial step to confirm initiation of the script.
2.0.2 June 17/2014 - Fixed an error that skipped step 8.
2.0.17 October 2020 - New version, more record types.  2.1 support.
2.0.17 December 2020 - Enhanced duplicate member logic, courtesy of GL700Wing.

Edited 10/21/20: 2.1 support & multiple attachment folder support.
Edited 11/24/20: Added attachment with before & after screenshots.
Edited 1/5/21: Enhanced member processing.
Edited 10/19/22: Fixed a buncha broken links...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Dzonny


Diego Andrés

Really nice, tried one of the old ones a few years ago and was quite an interesting experiment.

SMF Tricks - Free & Premium Responsive Themes for SMF.

Bigguy



GL700Wing

Quote11. Check for any members in the new (merged) PRIMARY SMF installation that end with your $secondary_suffix. If desired, these members can be merged with the members of the same name using a tool like SMF Admin Toolbox (http://www.simplemachines.org/community/index.php?topic=470463.0)
Looks like the SMF Admin Toolbox can no longer be downloaded from simplemachines.org - found a copy on GitHub: https://github.com/Spuds/SMF-Admin-Toolbox
Life doesn't have to be perfect to be wonderful ...

shawnb61

Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

Also note a new version was uploaded.  Works for 2.1 forums as well now.  Also handles multiple attachment folders now.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

I added a document with some before & after screenshots in the original post, to provide a feel for what to expect...
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

GL700Wing

#9
I've used this script to do several test runs of merging two SMF 2.0.17 forums (the secondary forum was converted from BBPress 2.6.6) and it's great.

The only issue I encountered is in relation to the renaming of member accounts.  Basically there were some member accounts in each database that had the same value for either 'member_name' (ie, the username they login with) or 'real_name' (ie, the name that is displayed for a member throughout the forum) where the accounts were for two completely different members (ie, different people in real life, different corresponding 'real_name' or 'member_name', and different 'email_address').  Ironically, and in one example I found, a member in the secondary database had the same 'real_name' as me!

By default the mergeSMF.php script renames all of the 'member_name', 'real_name' and 'email_address' values that are imported from the secondary database if any one of these values has a match in the primary database.  As a result I ended up with several imported member accounts where the member wouldn't have been able to login using their username or to recover their password via email (and they also wouldn't have had a secret question/answer set as they were a member of the forum that had been converted from BBPress).

To resolve this issue I have modified the mergeSMF.php script I'm using so that only the matching 'member_name', 'real_name' and/or 'email_address' values are renamed for imported member accounts (this can still be all three values if they all match).  I've also modified the mergeSMF.php file to copy the details of all the member renaming actions to a log file for later reference.




The changes I've made to mergeSMF.php may be found in a post below.

Finally, and to make it possible for Admins to search the member list for 'member_name' and 'real_name' values separately after forums have been merged (by default both database columns are searched for name matches), I've also updated the ManageMembers code.

In ./Sources/ManageMembers.php

Find:
'membername' => array(
'db_fields' => array('member_name', 'real_name'),
'type' => 'string'
),


Replace with:
'membername' => array(
'db_fields' => array('member_name'),
'type' => 'string'
),
'realname' => array(
'db_fields' => array('real_name'),
'type' => 'string'
),



In ./Themes/default/ManageMembers.template.php

Find:
<dt class="righttext">
<strong>', $txt['username'], ':</strong>
</dt>
<dd>
<input type="text" name="membername" value="" class="input_text" />
</dd>


Add after:
<dt class="righttext">
<strong>', $txt['display_name'], ':</strong>
</dt>
<dd>
<input type="text" name="realname" value="" class="input_text" />
</dd>


Life doesn't have to be perfect to be wonderful ...

shawnb61

Glad it worked out for you!

Your enhancements sound like they make a lot of sense. 

If you don't mind, I'd like to fold them into the script up on GitHub.  Or, better yet, if you could submit a PR up on GH, that would even be cleaner!
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

GL700Wing

Quote from: shawnb61 on December 27, 2020, 12:37:35 AM
Glad it worked out for you!

Your enhancements sound like they make a lot of sense.
Actually - I just realised that I forgot to include some very necessary code in my previous message - oops!

The correct information for the second code block I posted above for mergeSMF.php is as as follows - if you can edit my post and fix it up that'd be great.

In mergeSMF.php
Find:
if(($member_name_hits > 0) || ($real_name_hits > 0) || ($email_hits > 0))
{
echo "Member $mem[1] exists in primary. Renaming...<BR>";
$sql = "UPDATE {$db_prefix}members SET member_name = CONCAT(member_name, '{$secondary_suffix}'), real_name = CONCAT(real_name, '{$secondary_suffix}'), email_address = CONCAT(email_address, '{$secondary_suffix}') WHERE ID_MEMBER = '$mem[0]'";
$query0 = call_mysqli_query($sql, false);
}


Replace with:
if(($member_name_hits > 0) || ($real_name_hits > 0) || ($email_hits > 0))
{
$mn_suffix = ($member_name_hits > 0 ? $secondary_suffix : '');
$rn_suffix = ($real_name_hits > 0 ? $secondary_suffix : '');
$ea_suffix = ($email_hits > 0 ? $secondary_suffix : '');

$matches = ($member_name_hits > 0 ? '&nbsp;\'member_name\'&nbsp;' : '');
$matches .= ($real_name_hits > 0 ? '&nbsp;\'real_name\'&nbsp;' : '');
$matches .= ($email_hits > 0 ? '&nbsp;\'email_address\'&nbsp;' : '');

$exists = "The same <strong>$matches</strong> for the username <strong>&nbsp;'$mem[1]'&nbsp;</strong> (member ID '$mem[0]') already exists in primary.";
$renaming = "Renaming <strong>$matches</strong> in secondary ...";
echo "$exists $renaming <br />";

$now = '[' . date("d-M-Y H:i:s e") . '] ';
$exists = str_replace(array('&nbsp;', '<strong>', '</strong>'), array(' ', '', ''), $exists);
$renaming = str_replace(array('&nbsp;', '<strong>', '</strong>'), array(' ', '', ''), $renaming);
error_log(print_r($now . $exists . "  " . $renaming . "\n\n", true), 3, $renameMembersLog);

$sql = "UPDATE {$db_prefix}members SET member_name = CONCAT(member_name, '{$mn_suffix}'), real_name = CONCAT(real_name, '{$rn_suffix}'), email_address = CONCAT(email_address, '{$ea_suffix}') WHERE ID_MEMBER = '$mem[0]'";
$query0 = call_mysqli_query($sql, false);
}




QuoteIf you don't mind, I'd like to fold them into the script up on GitHub.  Or, better yet, if you could submit a PR up on GH, that would even be cleaner!
I haven't used GitHub before but I'll give it a go ...
Life doesn't have to be perfect to be wonderful ...

shawnb61

GL700Wing - I have tested your updates & folded them info the version up on GitHub.

THANK YOU.  I like 'em. 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

shawnb61

#13
Quote from: GL700Wing on December 27, 2020, 01:11:36 AM
I haven't used GitHub before but I'll give it a go ...

Some instructions on how to contribute via GitHub are attached.
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Shambles

#14
Quote from: shawnb61 on January 06, 2021, 01:02:21 AM

Just as an FYI, there is a note on how to contribute via GitHub over here:
https://www.simplemachines.org/community/index.php?topic=576283.0

Is that in a "team only" board?

QuoteThe topic or board you are looking for appears to be either missing or off limits to you.


EDIT: Fixed the link above...

shawnb61

Sorry about that...  It is now an attachment above!
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Aleksi "Lex" Kilpinen

It's in a restricted board yes, but I see the original post was fixed already :)

EDIT: Ninja'd. :P
Slava
Ukraini!
"Before you allow people access to your forum, especially in an administrative position, you must be aware that that person can seriously damage your forum. Therefore, you should only allow people that you trust, implicitly, to have such access." -Douglas

How you can help SMF

GL700Wing

Quote from: shawnb61 on January 06, 2021, 12:53:11 AM
GL700Wing - I have tested your updates & folded them info the version up on GitHub.

THANK YOU.  I like 'em. 

You're welcome!

Quote from: shawnb61 on January 06, 2021, 01:02:21 AM
Quote from: GL700Wing on December 27, 2020, 01:11:36 AM
I haven't used GitHub before but I'll give it a go ...

Some instructions on how to contribute via GitHub are attached.

Thanks - I've downloaded the instructions and will use them next time.

I already had a GitHub account with the same name as my SMF user name and I did create a pull request about 10 days ago but I don't know if you were able to access/use it.
Life doesn't have to be perfect to be wonderful ...

shawnb61

Quote from: GL700Wing on January 06, 2021, 06:42:57 AM
I already had a GitHub account with the same name as my SMF user name and I did create a pull request about 10 days ago but I don't know if you were able to access/use it.

It looks like all the work was done & uploaded to your repo, but the final step, submitting the PR to update my repo, was not done. 

Lots of steps using GitHub! 
Address the process rather than the outcome.  Then, the outcome becomes more likely.   - Fripp

Speed King

#19
I have two SMF forums at the same domain.
The old forum is larger, it's created in 2006. The new forum is smaller, it's created in 2018.
If I decide to merge two forums, I would set the new forum as primary, so the old forum to be attached to it.
But messages in old forum are older than messages in new forum, how they will be joined? Before, or after messages in the new forum?

Advertisement: