I couldn't find any functional tools or manuals, so I tried to find a way myself and hopefully found one which is quite independent of the phpbb3 version.
What I did till now is successfully merging 1 phpbb2, 1 phpbb2 orion and 2 phpbb3.06 forums, so I think it works
btw: my english is not the best (my german is much better ). I only tried this with XAMPP on Windows, don't ask me questions about linux ...
What do you need:
1) a phpbb3 environment at home
- latest phpbb3 board software
- phpmyadmin
- mysqldumper
- two databases (1 with the target forum and 1 with the content you want to merge into the target (I will call it 'source'))
- provider with forum and mysqldumper and ftp access and so on (should be normal stuff)
The whole merge process takes place at home. The resulting db is restored at your provider afterwards.
What to do:
1) backup target database (always a good idea) with mysqldumper, the 'source' database will not be changed
2) upgrade - if neccessary - the 'source' database to the same version as the target db (3.06). I won't describe how this works, because you will find the information elsewhere.
3) backup from 'source' db only the following tables uncompressed (either all 4 at once or separately):
phpbb_forums, phpbb_posts, phpbb_users, phpbb_topics, phpbb_privmsgs and phpbb_privmsgs_to (if the prefix is different adapt it to yours)
4) open the backup file and replace:
phpbb_users with phpbb_users_2
phpbb_topics with phpbb_topics_2
phpbb_forums with phpbb_forums_2
phpbb_posts with phpbb_posts_2
phpbb_privmsgs with phpbb_privmsgs_2
phpbb_privmsgs_to with phpbb_privmsgs_to_2
Why this ? I want to have in the target db the original tables and the tables with the additional content with the suffix _2. There may be many other ways to do this, I wrote down mine.
5) restore these tables (..._2) into the target db
Now the preparation is finished and the merging process can start.
Users:
Open phpmyadmin select the target db and run the following sql statement
alter table phpbb_users_2 add user_id_old mediumint(8)
This statement adds a column to the phpbb_users_2 table which serves as a marker for duplicate users.
If you want to see all users which are in both forums with the same name, type this:
SELECT phpbb_users.user_id, phpbb_users_2.user_id, phpbb_users_2 . * FROM phpbb_users_2 INNER JOIN phpbb_users ON phpbb_users_2.username_clean LIKE phpbb_users.username_clean;
My only criteria is username_clean, if you want things like password, email etc, you must change it yourself...
To avoid a duplicate key constraint error, you need to find out the max user_id an write it down !
SELECT max( user_id )
FROM phpbb_users;
in my example it will be 133.
Now you need to remove the auto increment attribute and the primary key index (2 statements):
ALTER TABLE phpbb_users_2 CHANGE `user_id` `user_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;
alter table phpbb_users_2 drop primary key;
Now increment the users in users_2 with the value written down:
update phpbb_users_2 set user_id = user_id + 133;
update phpbb_posts_2 set poster_id = poster_id + 133;
update phpbb_topics_2 set topic_poster = topic_poster + 133, topic_last_poster_id = topic_last_poster_id +133;
update phpbb_forums_2 set forum_last_poster_id = forum_last_poster_id + 133;
update phpbb_privmsgs_2 set author_id = author_id + 133;
update phpbb_privmsgs_to_2 set user_id = user_id + 126, author_id = author_id + 133;
Compare both user tables, find duplicate users and prepare them:
UPDATE phpbb_users_2, phpbb_users
SET phpbb_users_2.user_id_old = phpbb_users_2.user_id, phpbb_users_2.user_id = phpbb_users.user_id WHERE phpbb_users_2.username_clean LIKE phpbb_users.username_clean;
The LIKE operator doesn't work with some special charcters, so it might be possible that you will get an error later in the merge process. Simply find the matching user manually and fill the user_id_old cell for this user (s). From 600 users I had exactly one...
Replace the user information in the tables:
update phpbb_posts_2, phpbb_users_2
set phpbb_posts_2.poster_id = phpbb_users_2.user_id
where phpbb_posts_2.poster_id = phpbb_users_2.user_id_old;
update phpbb_forums_2, phpbb_users_2
set phpbb_forums_2.forum_last_poster_id = phpbb_users_2.user_id where phpbb_forums_2.forum_last_poster_id = phpbb_users_2.user_id_old;
update phpbb_topics_2, phpbb_users_2
set phpbb_topics_2.topic_poster = phpbb_users_2.user_id where phpbb_topics_2.topic_poster = phpbb_users_2.user_id_old;
update phpbb_topics_2, phpbb_users_2
set phpbb_topics_2.topic_last_poster_id = phpbb_users_2.user_id where phpbb_topics_2.topic_last_poster_id = phpbb_users_2.user_id_old;
update phpbb_privmsgs_2, phpbb_users_2
set phpbb_privmsgs_2.author_id = phpbb_users_2.user_id
where phpbb_privmsgs_2.author_id = phpbb_users_2.user_id_old;
update phpbb_privmsgs_to_2, phpbb_users_2
set phpbb_privmsgs_to_2.author_id = phpbb_users_2.user_id
where phpbb_privmsgs_to_2.author_id = phpbb_users_2.user_id_old;
update phpbb_privmsgs_to_2, phpbb_users_2
set phpbb_privmsgs_to_2.user_id = phpbb_users_2.user_id
where phpbb_privmsgs_to_2.user_id = phpbb_users_2.user_id_old;
Now merge the users:
1. (optional): deactivate the added users:
update phpbb_users_2 set user_type = 1 where user_id_old is null;
2. merge:
INSERT INTO phpbb_users (user_id, user_type, group_id, user_permissions, user_perm_from, user_ip, user_regdate, username, username_clean, user_password, user_passchg, user_pass_convert, user_email, user_email_hash, user_birthday, user_lastvisit, user_lastmark, user_lastpost_time, user_lastpage,
user_last_confirm_key, user_last_search, user_warnings, user_last_warning, user_login_attempts, user_inactive_reason, user_inactive_time, user_posts, user_lang, user_timezone, user_dst, user_dateformat, user_style, user_rank, user_colour, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_message_rules, user_full_folder, user_emailtime, user_topic_show_days, user_topic_sortby_type, user_topic_sortby_dir, user_post_show_days, user_post_sortby_type, user_post_sortby_dir, user_notify, user_notify_pm, user_notify_type, user_allow_pm, user_allow_viewonline, user_allow_viewemail, user_allow_massemail, user_options, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height, user_sig, user_sig_bbcode_uid,
user_sig_bbcode_bitfield, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_form_salt)
select user_id, user_type, group_id, user_permissions, user_perm_from, user_ip, user_regdate, username, username_clean, user_password, user_passchg, user_pass_convert, user_email, user_email_hash, user_birthday, user_lastvisit, user_lastmark, user_lastpost_time, user_lastpage, user_last_confirm_key, user_last_search, user_warnings, user_last_warning, user_login_attempts, user_inactive_reason, user_inactive_time, user_posts, user_lang, user_timezone, user_dst, user_dateformat, user_style, user_rank, user_colour, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_message_rules, user_full_folder, user_emailtime, user_topic_show_days, user_topic_sortby_type, user_topic_sortby_dir, user_post_show_days, user_post_sortby_type, user_post_sortby_dir, user_notify, user_notify_pm, user_notify_type, user_allow_pm, user_allow_viewonline, user_allow_viewemail, user_allow_massemail, user_options, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height, user_sig, user_sig_bbcode_uid, user_sig_bbcode_bitfield, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_form_salt from phpbb_users_2 where user_id_old is null;
This statement looks a bit ... big, but it's simply copied from the backup file(s) generated before.
If you get an error saying that something is wrong with any column then it is because you have an upgraded forum. From version to version columns changed, so
simply ensure that the target columns are filled. This happened to me I guess with the forums tables later --> no problem.
Now users are finished, let's go on with PMs.
Private Messages:
This part is much m ore complicated than the rest because in two columns data ist not saved as it should be: to_address and bcc_address. There are not only IDs but text values starting bith 'u_' and (what is even worse), several entries in one field.
My 'script' ensures (I hope so) that all messages after the merge are assigned to the correct user (pms are a bit more sensitive than posts I guess). All user created pm subfolders will go to Inbox (Posteingang). All received pms are in the inobox of the correct user.
Not perfectly solved is sent items:
If a pm was sent to 1 person, all will be fine. If a pm was sent to several users or has a bcc, this pm certainly will be in the inbox of the receipient but in the senders sent items there will be the message with a wrong info where the message was sent to. In my opinion this is absolutely uninteresting, but you are absolutely invited to improve the code.
First: ensure that there is enough space for the added messages in your postbox (ACP setting) . Otherwise it begaves like this: Inbox: doesn't matter, sent items: first open: all there, second open: cut.
Make all user folders to inbox:
update phpbb_privmsgs_to_2 set folder_id = 0 where folder_id > 0;
Find max. msg_id:
SELECT MAX( msg_id ) FROM phpbb_privmsgs; --> remember 406
Delete auto increment and primary key:
ALTER TABLE phpbb_privmsgs_2 CHANGE `msg_id` `msg_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;
alter table phpbb_privmsgs_2 drop primary key;
update phpbb_privmsgs_2 set msg_id = msg_id + 406;
Delete auto increment:
ALTER TABLE phpbb_privmsgs_to_2 CHANGE `msg_id` `msg_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL
Increase IDs
update phpbb_privmsgs_to_2 set msg_id = msg_id + 406;
Create translation table:
CREATE TABLE phpbb_privmsgs_3 (
msg_id MEDIUMINT( 8 ),
to_address TEXT,
bcc_address TEXT)
Now we have to work with a cursor (which doesn't make me happy..)
Enter the following code in phpmyadmin (but edit the line: set touser=touser + 133; . The 133 is the remembered value from before.
Code: Select all
delimiter ;;
CREATE PROCEDURE msgto()
BEGIN
DECLARE userid MEDIUMINT(8);
DECLARE useridold MEDIUMINT(8);
DECLARE done INT DEFAULT 0;
DECLARE myid MEDIUMINT(8);
DECLARE touser int;
DECLARE toadr text;
DECLARE bccadr text;
declare cur1 cursor for select msg_id, to_address, bcc_address from phpbb_privmsgs_2;
open cur1;
repeat
fetch cur1 into myid, toadr, bccadr;
if instr(toadr,':') = 0 and instr(toadr,'u_') = 1 THEN
set touser=convert(right(toadr,length(toadr)-2), UNSIGNED);
set touser=touser + 133;
set useridold=0;
select user_id, user_id_old into userid, useridold from phpbb_users_2
where user_id_old=touser;
if useridold >=1 then
set touser=userid;
end if;
end if;
insert into phpbb_privmsgs_3 values (myid, concat('u_', convert(touser, char)), bccadr);
UNTIL done END REPEAT;
close cur1;
end;;
After the procedure is created, execute it:
call msgto
update phpbb_privmsgs_2, phpbb_privmsgs_3
set phpbb_privmsgs_2.to_address = phpbb_privmsgs_3.to_address
where phpbb_privmsgs_2.msg_id = phpbb_privmsgs_3.msg_id;
Merge:
INSERT INTO phpbb_privmsgs (msg_id, root_level, author_id, icon_id, author_ip, message_time, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, message_subject, message_text, message_edit_reason, message_edit_user, message_attachment, bbcode_bitfield, bbcode_uid, message_edit_time, message_edit_count, to_address, bcc_address)
select msg_id, root_level, author_id, icon_id, author_ip, message_time, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, message_subject, message_text, message_edit_reason, message_edit_user, message_attachment, bbcode_bitfield, bbcode_uid, message_edit_time, message_edit_count, to_address, bcc_address
from phpbb_privmsgs_2
INSERT INTO phpbb_privmsgs_to (msg_id, user_id, author_id, pm_deleted, pm_new, pm_unread, pm_replied, pm_marked, pm_forwarded, folder_id)
select msg_id, user_id, author_id, pm_deleted, pm_new, pm_unread, pm_replied, pm_marked, pm_forwarded, folder_id
from phpbb_privmsgs_to_2[/color]
Let's go on with forums:
Forums:
SELECT MAX( forum_id ) FROM phpbb_forums; --> remember (example: 34)
SELECT MAX( right_id ) FROM phpbb_forums; --> remember (example: 68)
The values left_id and right_id are normally used for a tree structure, but in phpbb3 till now it is ok if these values only are not identical.
These two statements are described in the users part:
ALTER TABLE phpbb_forums_2 CHANGE `forum_id` `forum_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;
alter table phpbb_forums_2 drop primary key;
Now increase the id's
update phpbb_forums_2 set forum_id = forum_id + 34, right_id = right_id + 68, left_id = left_id + 68;
Only increase parent id's if they are not 0 (which means root):
update phpbb_forums_2 set parent_id = parent_id + 34 where parent_id <> 0;
Alter the relating tables:
update phpbb_topics_2 set forum_id = forum_id + 34;
update phpbb_posts_2 set forum_id = forum_id + 34;
This was all for forums, the final merge must wait a bit.
Topics:
SELECT MAX( topic_id ) FROM phpbb_topics; --> remember 424 (example)
ALTER TABLE phpbb_topics_2 CHANGE `topic_id` `topic_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;
alter table phpbb_topics_2 drop primary key;
update phpbb_topics_2 set topic_id = topic_id + 424;
update phpbb_posts_2 set topic_id = topic_id + 424;
Posts:
SELECT MAX( post_id ) FROM phpbb_posts; --> remember 9959
ALTER TABLE phpbb_posts_2 CHANGE `post_id` `post_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;
alter table phpbb_posts_2 drop primary key;
update phpbb_posts_2 set post_id = post_id + 9959;
update phpbb_forums_2 set forum_last_post_id = forum_last_post_id + 9959;
update phpbb_topics_2 set topic_first_post_id = topic_first_post_id +9959, topic_last_post_id = topic_last_post_id + 9959;
Now the final merge:
INSERT INTO phpbb_forums (forum_id, parent_id, left_id, right_id, forum_parents, forum_name, forum_desc, forum_desc_bitfield, forum_desc_options, forum_desc_uid, forum_link, forum_password, forum_style, forum_image, forum_rules, forum_rules_link, forum_rules_bitfield, forum_rules_options,
forum_rules_uid, forum_topics_per_page, forum_type, forum_status, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_last_poster_colour, forum_flags, forum_options, display_subforum_list, display_on_index, enable_indexing, enable_icons, enable_prune, prune_next, prune_days, prune_viewed, prune_freq)
select forum_id, parent_id, left_id, right_id, forum_parents, forum_name, forum_desc, forum_desc_bitfield, forum_desc_options, forum_desc_uid, forum_link, forum_password, forum_style, forum_image, forum_rules, forum_rules_link, forum_rules_bitfield, forum_rules_options, forum_rules_uid, forum_topics_per_page, forum_type, forum_status, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_last_poster_colour, forum_flags, forum_options, display_subforum_list, display_on_index, enable_indexing, enable_icons, enable_prune, prune_next, prune_days, prune_viewed, prune_freq from phpbb_forums_2;
Here I had the problem with the column .. can't remember
INSERT INTO phpbb_topics (topic_id, forum_id, icon_id, topic_attachment, topic_approved, topic_reported, topic_title, topic_poster, topic_time, topic_time_limit, topic_views, topic_replies, topic_replies_real, topic_status, topic_type, topic_first_post_id, topic_first_poster_name, topic_first_poster_colour, topic_last_post_id, topic_last_poster_id, topic_last_poster_name, topic_last_poster_colour, topic_last_post_subject, topic_last_post_time, topic_last_view_time, topic_moved_id, topic_bumped, topic_bumper, poll_title, poll_start, poll_length, poll_max_options, poll_last_vote, poll_vote_change)
select topic_id, forum_id, icon_id, topic_attachment, topic_approved, topic_reported, topic_title, topic_poster, topic_time, topic_time_limit, topic_views, topic_replies, topic_replies_real, topic_status, topic_type, topic_first_post_id, topic_first_poster_name, topic_first_poster_colour, topic_last_post_id,
topic_last_poster_id, topic_last_poster_name, topic_last_poster_colour, topic_last_post_subject, topic_last_post_time, topic_last_view_time, topic_moved_id, topic_bumped, topic_bumper, poll_title, poll_start, poll_length, poll_max_options, poll_last_vote, poll_vote_change from phpbb_topics_2;
INSERT INTO phpbb_posts (post_id, topic_id, forum_id, poster_id, icon_id, poster_ip, post_time, post_approved, post_reported, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, post_username, post_subject, post_text, post_checksum, post_attachment, bbcode_bitfield, bbcode_uid, post_postcount, post_edit_time, post_edit_reason, post_edit_user, post_edit_count, post_edit_locked)
select post_id, topic_id, forum_id, poster_id, icon_id, poster_ip, post_time, post_approved, post_reported, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, post_username, post_subject, post_text, post_checksum, post_attachment, bbcode_bitfield, bbcode_uid, post_postcount, post_edit_time,
post_edit_reason, post_edit_user, post_edit_count, post_edit_locked from phpbb_posts_2;
If you have an Umlaut (german) Problem : http://www.phpbb.de/community/viewtopic ... 3&t=168750
Now you can enter your forum and:
a) resync statistics
b) resync user counter
c) drop and recreate full text index
d) reset the permissions of the migrated forums (source) in acp (otherwise they will not be visible even for admins in the forum view)
e) test it
Delete the tables with the _2 suffix then upload it to your provider via mysqldumper.
The SQL statements are very fast. Rebuilding the fulltext index can be very slow depending on the size of your forum. With 180000 posts the merge ithself took 20 minutes (this is very fast I guess), the rebuilding took 45 minutes.