Import PHPBB: wrong authors
that’s my first post so I take the opportunity to thank the community for its amazing job.
I am in the process of migrating an existing community (PHPBB based) to BBPress on a dedicated server.
In terms of volumes, we are talking about 6K users, 350K replies and 15K topics.
The import seems to run quite well but I’ve notice that the author of certain posts is wrong.
I’ve found in the forum someone with the same problem but he was running a specific plugin to sync his existing PHPBB installation whilst in my case I have a pure PHPBB installation running on version 3.0.12
Do you guys no some magic options to remap the posts to the right authors?
Thank you in advance,
I leave this reply for those who faced the same issue and also for the developers who may find where the problem is.
From one side we have a wrong author getting associated to the wordpress posts (forum, topic and replies).
One thing that can be done to fix this after having run the import is to match the date and time of the post, the ip address of the author and potentially the last 8 characters of the content (will not work if it’s a smiley or bb code).
This would fix the issue even if I can’t guarantee it will work for all the dirty posts.
The second thing, even more worrying, is the fact that certain authors are associated to the topics/replies with their old PHPBB user_id.
It’s easy to spot them because those dirty posts won’t join with the wp_user table and therefore they are reported as anonymous users in the website.
One way to fix that after the import is to update all those old PHPBB ids by replacing them with the corresponding wordpress users ids (just join on email address).
You should try bbPress 2.6-alpha, there are heaps of phpBB improvements in bbPress 2.6 🙂
That should fix your author mapping, it also adds support for mapping anonymous/deleted users to their topics which is pretty cool.
The mapping of the users old phpBB user ID is also updated to work quite differently to how it did in the 2.5.x release so again this should fix that for you also.
p.s. bbPress.org here runs 2.6-alpha, and we’re hoping to ship 2.6 in the next couple of weeks.
Edit: Get 2.6-alpha via our downloads page https://bbpress.org/download
That’s great news.
I will wait for the stable version and then I’ll run again the conversion.
Any update on when the new version will be release?
I am using the latest 2.5.9 but still no success (I was using 2.5.8 when I wrote this post)
After further investigations I’ve found a workaround that should work.
Just to recap on the error: it seems like several posts are left with the old phpBB poster id rather than using the new wordpress user_id (which really makes me think that somewhere in the converter code a wrong reference has been used).
The best way to work around the issue is the following (assuming a fresh installation):
1) before migration
– check what is the highest user_id in your phpBB installation (e.g.: 6235)
– insert a dummy row in wp_users with id grater than that (e.g.: 6236, or even better, round it up to 10000). In this way all the converted users will have their IDs starting from a higher value (e.g.: 10001, 10002, …) and it will be easy to spot the posts with wrong author (e.g. post_author < 10000).
2) launch migration as usual
3) after migration, create a temporary table that lists all the bbpress posts with wrong author
/* Get all posts with wrong author and correspondent right author from bbpress translator table */ create table TMP_ORPHANS as select wp_posts.ID, wp_posts.post_author, wp_bbp_converter_translator.value_id real_author, IFNULL(wp_posts.post_date, NULL) post_date, IFNULL(wp_posts.post_date_gmt, NULL) post_date_gmt, wp_posts.post_content, CASE WHEN wp_posts.post_type = 'reply' THEN (SELECT tmposts.post_title from wp_posts as tmposts where tmposts.id = wp_posts.post_parent) ELSE wp_posts.post_title END post_title, wp_posts.post_excerpt, pescasubac, wp_posts.quea.wp_posts.post_status, wp_posts.comment_status, wp_posts.ping_status, wp_posts.post_password, wp_posts.post_name, wp_posts.to_ping, wp_posts.pinged, IFNULL(wp_posts.post_modified, NULL) post_modified, IFNULL(wp_posts.post_modified_gmt, NULL) post_modified_gmt, wp_posts.post_content_filtered, wp_posts.post_parent, wp_posts.guid, wp_posts.menu_order, wp_posts.post_type, wp_posts.post_mime_type, wp_posts.comment_count, ( select wp_postmeta.meta_value from wp_postmeta where wp_postmeta.meta_key = '_bbp_author_ip' and wp_postmeta.post_id = wp_posts.id ) user_ip from wp_posts left join wp_users on wp_posts.post_author = wp_users.id left join wp_bbp_converter_translator on wp_posts.post_author = wp_bbp_converter_translator.meta_value where wp_posts.post_type in ('forum', 'reply', 'topic') and (wp_users.id is null or wp_users.id = 1) and /* all posts of type forum looks to be assigned to the site admin (id=1 in my case)*/ wp_bbp_converter_translator.value_type = 'user' and wp_bbp_converter_translator.meta_key = '_bbp_old_user_id'; /* this is to be able to create indexes later */ ALTER TABLE TMP_ORPHANS modify column post_date datetime default NULL; ALTER TABLE TMP_ORPHANS modify column post_date_gmt datetime default NULL; ALTER TABLE TMP_ORPHANS modify column post_modified datetime default NULL; ALTER TABLE TMP_ORPHANS modify column post_modified_gmt datetime default NULL; ALTER TABLE TMP_ORPHANS modify column user_ip varchar(40); ALTER TABLE TMP_ORPHANS modify column post_title varchar(255); /* indexes to speed up access to the table (some are useless) */ ALTER TABLE TMP_ORPHANS ADD INDEX(post_date); ALTER TABLE TMP_ORPHANS ADD INDEX(post_author); ALTER TABLE TMP_ORPHANS ADD INDEX(real_author); ALTER TABLE TMP_ORPHANS ADD INDEX(user_ip); ALTER TABLE TMP_ORPHANS ADD INDEX(post_title);
4) Now that you’ve got the list of posts with wrong authors, you can update your wp_posts table accordingly
/* Revert bad authors to good authors */ UPDATE wp_postsINNER JOIN TMP_ORPHANS ON wp_posts.id = TMP_ORPHANS.id set wp_posts.post_author = TMP_ORPHANS.real_author where wp_posts.post_type in ('reply', 'topic') ;
– this workaround seems to be working for me, but of course I don’t assume any responsibility in case it doesn’t work for you. The update trusts the converter table generated by the bbpress forum converter and assumes that a wrong author id matches the original author id of phpBB forum (I executed some checks and it seems like the problem is always the same = phpBB author id rather than new wordpress author id).
– the update doesn’t fix posts of type ‘forum’ because by default these are assigned to the wordpress site administrator. If you wish to assign them to the original phpBB author, just modify the where condition:
where wp_posts.post_type in ('forum','reply', 'topic') ;
message to the developers: I truly believe somewhere in the converter code a wrong reference is used under certain conditions. The pattern of the error is always the same: when the author doesn’t match it’s because the author id of the phpBB forum rather than the new converted author id.
In version 2.6 alpha seems that the problem is solved, but just if you chose to import the users. When the users remain as guests, all shown as Anonymous and not linked to any topic. There is any soultion for this specific problem?
I’ve used 2.6 alpha for this import on a fresh wordpress installation. I’va also imported the users.
This behaviour was the same in the previous 2 versions.
I had more the 60000 posts affected over more than 300000.
I believe the parser failed to link the replies to the wordpress users under certain conditions but I haven’t been able to prove it yet.
I am using an old version of phpbb so probably this influenced as well.
Try to upgrade your phpBB forum before the import. When you chose to import the users, it should work fine as I checked. The only problem appear if you want just to import the posts without the users or if you delete the users, it show the author as “Anonymous” instead to show the username or nickname.
I know this is an old thread but it is about the most useful on this issue but unfortunately did not solve my problem. (I got zero results returned in the TMP table) I wanted to point out however in case it is useful for anyone else, that in step 3 of @giobby post above there seems to be some extraneous text in there.
these three lines:
wp_posts.post_excerpt, pescasubac, wp_posts.quea.wp_posts.post_status,
I believe should be:
pescasubac doesn’t seem to refer to anything and “wp_posts.quea” also is not a thing. All of the other fields in wp_posts are accounted for without those two items.
- You must be logged in to reply to this topic.