[giobby]

Hi everybody,

I’ve been trying as suggested bbpress 2.6 alpha but the PhpBB import fails right at the beginning.
I’ve been already importing successfully by using the latest 2 versions and I’ve never faced this error before.

Also take into account I am running the import in the same environment and against the same data set.
Here the error:

SELECT convert(users.user_id USING “utf8mb4”) AS user_id,convert(users.user_password USING “utf8mb4”) AS user_password,convert(users.user_form_salt USING “utf8mb4”) AS user_form_salt,convert(users.username USING “utf8mb4”) AS username,convert(users.user_email USING “utf8mb4”) AS user_email,convert(profile_fields_data.pf_phpbb_website USING “utf8mb4”) AS pf_phpbb_website,convert(users.user_regdate USING “utf8mb4”) AS user_regdate,convert(profile_fields_data.pf_phpbb_aol USING “utf8mb4”) AS pf_phpbb_aol,convert(profile_fields_data.pf_phpbb_yahoo USING “utf8mb4”) AS pf_phpbb_yahoo,convert(profile_fields_data.pf_phpbb_icq USING “utf8mb4”) AS pf_phpbb_icq,convert(profile_fields_data.pf_phpbb_wlm USING “utf8mb4”) AS pf_phpbb_wlm,convert(profile_fields_data.pf_phpbb_facebook USING “utf8mb4”) AS pf_phpbb_facebook,convert(profile_fields_data.pf_phpbb_googleplus USING “utf8mb4”) AS pf_phpbb_googleplus,convert(profile_fields_data.pf_phpbb_skype USING “utf8mb4”) AS pf_phpbb_skype,convert(profile_fields_data.pf_phpbb_twitter USING “utf8mb4”) AS pf_phpbb_twitter,convert(profile_fields_data.pf_phpbb_youtube USING “utf8mb4”) AS pf_phpbb_youtube,convert(users.user_jabber USING “utf8mb4”) AS user_jabber,convert(profile_fields_data.pf_phpbb_occupation USING “utf8mb4”) AS pf_phpbb_occupation,convert(profile_fields_data.pf_phpbb_interests USING “utf8mb4”) AS pf_phpbb_interests,convert(users.user_sig USING “utf8mb4”) AS user_sig,convert(profile_fields_data.pf_phpbb_location USING “utf8mb4”) AS pf_phpbb_location,convert(users.user_avatar USING “utf8mb4”) AS user_avatar FROM forum_users AS users LEFT JOIN forum_profile_fields_data AS profile_fields_data USING (user_id) WHERE users.user_type !=2 LIMIT 0, 100

Not sure yet what the problem is.
I’ll do my little investigation and I’ll let you know.

Gio

[giobby]

After further investigation, It looks to be something related to the character – see error below.

Error Code: 1115. Unknown character set: ‘“utf8mb4”’

Not sure why it’s trying to make this conversion :-\

[giobby]

Adding additional details:

I am using MySql Server 5.7.12
I’ve tried to change my database charset to utf8mb4 but I still get the same error.

[giobby]

Just realised I didn’t post the right error.
Here it is…

Error Code: 1054. Unknown column ‘profile_fields_data.pf_phpbb_website’ in ‘field list’.

That column doesn’t exist in my profile_fields_data table.
Also, I was not getting that error with the previous version of the converter so i am wondering if the converter expects a specific/recent phpBB version to convert from

[giobby]

Ok, I had to create manually all these columns.
Here what I did (I use ‘forum_’ as table prefix):

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_facebook varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_twitter varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_occupation varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_interests varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_location varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_youtube varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_skype varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_googleplus varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_website varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_aol varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_yahoo varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_icq varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_wlm varchar(255);

[giobby]

Ok, I’ve finally completes the conversion (took only 8 hours against the 2 days of the previous versions).

Nevertheless, big problem here: I can see no forum after the conversion.
I’ve been checking the database schema in which wordpress is installed and nothing….
I can only find an empty wp_bbp_converter_translator table and for the rest: no users converted, no posts, no forum and topics tables.

Does someone has any idea why this happened?
It actually looked like it was converting something and the number of interactions I’ve seen in the log during the conversion matches the actual numbers in PhpBB.

I would appreciate if someone can help.

Thanks,

Gio

[senatorman]

Same problem here with exact same error

Unknown column ‘profile_fields_data.pf_phpbb_website’ in ‘field list’

[Stephen Edgar]

bbPress 2.6 has dropped support for importing from phpBB 3.0.x, you need to upgrade your phpBB database to 3.1.x before importing into bbPress 2.6

[senatorman]

I understand that there is no support for older versions phpbb 3.0.x and that this feature only have to work with the latest versions. But iám not happy with that.

I have a huge (+1000K posts) phpbb 3.0.x forum with a lot of extra custom contributions and will stop with phpbb becausse phpbb is not populair anymore. support is bad and there is no evolution.

To update a huge forum to phpbb3.1 it is a lot of strugling and not easy to do.
Now i’ve to do it, and hope there are not data lost.

Is there a old plugin available to import pbp3.0.x with is working fine with huge database?
The last version of the import has problems and lost the relation between post and owner.

[Stephen Edgar]

The last version of the import has problems and lost the relation between post and owner.

That’s because the current version only supports phpBB 3.1.x as I stated above.

As you don’t need to worry about any custom modules as bbPress doesn’t support these anyway I still think you are best of upgrading your phpBB database schema to 3.1.x, but here is a 3.0.x alternative for you to try, though no guarantees and I won’t be making any 3.0.x changes etc.

This here is the 3.1.x version: https://bbpress.trac.wordpress.org/browser/trunk/src/includes/admin/converters/phpBB.php?rev=5795

This here is the 3.0.x version:
https://bbpress.trac.wordpress.org/browser/trunk/src/includes/admin/converters/phpBB.php?rev=5770

You’ll still need to use bbPress 2.6 alpha with that 3.0.x version.

[senatorman]

Thank you for the links i was looking for.

I going to try the 3.0 importversion. When it works i saved me a lot off work.

Is this not the version that lost relation between post and author?

[senatorman]

i’ve do the import completly but also this version lost relation between post and user.

So i think there is no other option and i must upgrade my phpbb to 3.1

[senatorman]

i have do the pbpbb update to the newest version 3.1.9.

And there is still a problem.
When i run the import, the import is not starting.
Only the loadingcircle is visable

What do i wrong or is there a bug in the phpbb convertingfile?

[senatorman]

i’ve update phpbb to 3.1.9 and use BBpress 2.6 alpha

I’ve downloaded te latest version of the phpbb.php importfilefrom:
https://bbpress.trac.wordpress.org/browser/trunk/src/includes/admin/converters/phpBB.php?rev=5795

When i run the converting i get this error:

Repair any missing information: Continue
WordPress databasefout: [Unknown column ‘forums.forum_topics_real’ in ‘field list’]
SELECT convert(forums.forum_id USING “utf8mb4”) AS forum_id,convert(forums.parent_id USING “utf8mb4”) AS parent_id,convert(forums.forum_topics_approved USING “utf8mb4”) AS forum_topics_approved,convert(forums.forum_posts_approved USING “utf8mb4”) AS forum_posts_approved,convert(forums.forum_topics_real USING “utf8mb4”) AS forum_topics_real,convert(forums.forum_posts USING “utf8mb4”) AS forum_posts,convert(forums.forum_name USING “utf8mb4”) AS forum_name,convert(forums.forum_desc USING “utf8mb4”) AS forum_desc,convert(forums.left_id USING “utf8mb4”) AS left_id,convert(forums.forum_type USING “utf8mb4”) AS forum_type,convert(forums.forum_status USING “utf8mb4”) AS forum_status FROM phpbb_forums AS forums LIMIT 0, 100
Geen forums te converteren
Verwijder standaard wachtwoorden van WordPress gebruikers (500 – 599)
Verwijder standaard wachtwoorden van WordPress gebruikers (400 – 499)
Verwijder standaard wachtwoorden van WordPress gebruikers (300 – 399)
Verwijder standaard wachtwoorden van WordPress gebruikers (200 – 299)
Verwijder standaard wachtwoorden van WordPress gebruikers (100 – 199)
Verwijder standaard wachtwoorden van WordPress gebruikers (0 – 99)
Gebruikers aan het converteren (500 – 599)
Gebruikers aan het converteren (400 – 499)
Gebruikers aan het converteren (300 – 399)
Gebruikers aan het converteren (200 – 299)
Gebruikers aan het converteren (100 – 199)
Gebruikers aan het converteren (0 – 99)
Geen gegevens om op te schonen
Conversie starten

[senatorman]

Is there somebody with the same versions (phpbb to 3.1.9 and use BBpress 2.6 alpha) with a working import from a pbpbb database?

If yes, can you send me your the phpbb.php file please

[senatorman]

I have it working now, and there is still a big problem a can’t solved.

When i convert a big phpbb forum to bbpress the relation between post and author get lost.

Pleas help me with this big problem

[senatorman]

please help!

[Stephen Edgar]

If you’re using any phpBB mods this might cause issues.

You also need to make sure you use bbPress 2.6 alpha, which includes all the changes required , you cannot simply copy the changed phpBB.php file to a different bbPress version.

To use the most up to date phpBB importer use bbPress 2.6-alpha as I pointed out, if you have to use the old phpBB version use bbPress 2.5.10.

But as I’ve said, there are huge improvements to the phpBB importer in 2.6-alpha so updating phpBB to the latest version is my recommendation.

[senatorman]

i understand what you say and do that exactly
removed al mods and use a basic pbpbb 3.1.9 database
I use for all the latest verions

-bbpress 2.6 alpha
-phpBB 3.1.9
-phpBB Converter file 5795

As i said earlier in this topic, there is a problem with the latest version of the phpbb converter https://bbpress.trac.wordpress.org/browser/trunk/src/includes/admin/converters/phpBB.php?rev=5795

After converting all the passwords, at the beginning converting posts or topics i there is an error.

[Stephen Edgar]

No, you should not be using phpBB Converter file 5795, you should be using the version that is included with 2.6-alpha.

That will fix the problem in your screenshot.

[senatorman]

ok.

can i separete download this file someware.
can you give me an url please.

[senatorman]

i’ve found the file.

do the import, this takes almost 30 hours

1120363 posts
72212 Topics
50290 members.

And there is still a problem with converting
He lost the relations with many posts and author.
There are hunderds of posts with an anonymous author.
Also recently as very old posts has this problem.

[senatorman]

For us this is a big problem and the only reason we can’t release our new bbpress forum.
Our new site is complete ready, we have worked many months on it. and now we stuck on this hopely little problem.

i know bbpress authors are very busy people,but i hope they can help us.

[giobby]

I hope this helps you: https://bbpress.org/forums/topic/import-phpbb-wrong-authors/
I had to write some custom queries to fix the wrong authors problems.
Can’t promise it’s going to work, but when I’ve run some counts on the tables during the process it looked that all the orphan posts were correctly reassigned to the wrong author.

Good luck!

[senatorman]

Thx @Giobby. A had already read your topic, and do as you told, but i keep this problem.

-The first topicpost has always the correct author
-The second ans all other posts in that topis has the anonymous as author.

This problem has nog every topic, some topics had for al posts the correct author.

Iám sure, there is a problem in the converting file, and it looks that this problem online exist by big phpbb forums

Please. BBpress team, can you look at this problem?

[giobby]

I try to post here the steps I did to import from PHPBB to BuddyPress v2.6 alpha.
The concept is the same of what you’ve already read in my previous post but I think the SQL scripts are different.

So first of all I’ve created manually all the missing columns in my PHPBB tables (I was converting from an old version of PHPBB and the converter was expecting some columns that didn’t exist – this could not be your case):

ALTER TABLE forum_forums
ADD forum_posts_approved numeric;

ALTER TABLE forum_forums
ADD forum_topics_approved numeric;

ALTER TABLE forum_topics
ADD topic_posts_approved numeric;

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_facebook varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_twitter varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_occupation varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_interests varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_location varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_youtube varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_skype varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_googleplus varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_website varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_aol varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_yahoo varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_icq varchar(255);

ALTER TABLE forum_profile_fields_data
ADD pf_phpbb_wlm varchar(255);

Then I’ve performed this KEY STEP: insert a dummy row in the WP_USERS table with a very high userid (e.g.: 99999). This should be higher than the highest userid you have in your phpbb installation.
In this way, when the users get converted, the system will assigned IDs starting from 100000 (again, if you have more than 100K users, then you need a higher value).
This step is necessary, so you will always be able to distinguish the old id (PHPBB, < 100000) from the new one assigned after conversion (>= 100000).

After that I’ve started the conversion (it took long).
After converting, the authors of certain posts were wrong.
There is a table that is generated after the conversion that maps the old IDs to the new ones.
So I’ve execute the following scripts to readjust (cannot guarantee this would fix entirely the problem):

/* Get all posts with wrong author and correspondent right author from translator table */

create table TMP_ORPHANS as
select
<YOUR_SCHEMA>.wp_posts.ID,
<YOUR_SCHEMA>.wp_posts.post_author,
<YOUR_SCHEMA>.wp_bbp_converter_translator.value_id real_author,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_date, NULL) post_date,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_date_gmt, NULL) post_date_gmt,
<YOUR_SCHEMA>.wp_posts.post_content,
CASE
WHEN <YOUR_SCHEMA>.wp_posts.post_type = ‘reply’
THEN (SELECT tmposts.post_title from <YOUR_SCHEMA>.wp_posts as tmposts where tmposts.id = <YOUR_SCHEMA>.wp_posts.post_parent)
ELSE <YOUR_SCHEMA>.wp_posts.post_title
END post_title,
<YOUR_SCHEMA>.wp_posts.post_excerpt,
<YOUR_SCHEMA>.wp_posts.post_status,
<YOUR_SCHEMA>.wp_posts.comment_status,
<YOUR_SCHEMA>.wp_posts.ping_status,
<YOUR_SCHEMA>.wp_posts.post_password,
<YOUR_SCHEMA>.wp_posts.post_name,
<YOUR_SCHEMA>.wp_posts.to_ping,
<YOUR_SCHEMA>.wp_posts.pinged,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_modified, NULL) post_modified,
IFNULL(<YOUR_SCHEMA>.wp_posts.post_modified_gmt, NULL) post_modified_gmt,
<YOUR_SCHEMA>.wp_posts.post_content_filtered,
<YOUR_SCHEMA>.wp_posts.post_parent,
<YOUR_SCHEMA>.wp_posts.guid,
<YOUR_SCHEMA>.wp_posts.menu_order,
<YOUR_SCHEMA>.wp_posts.post_type,
<YOUR_SCHEMA>.wp_posts.post_mime_type,
<YOUR_SCHEMA>.wp_posts.comment_count,
( select <YOUR_SCHEMA>.wp_postmeta.meta_value
from <YOUR_SCHEMA>.wp_postmeta
where
<YOUR_SCHEMA>.wp_postmeta.meta_key = ‘_bbp_author_ip’ and
<YOUR_SCHEMA>.wp_postmeta.post_id = <YOUR_SCHEMA>.wp_posts.id
) user_ip
from <YOUR_SCHEMA>.wp_posts
left join <YOUR_SCHEMA>.wp_users on <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.wp_users.id
left join <YOUR_SCHEMA>.wp_bbp_converter_translator on <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.wp_bbp_converter_translator.meta_value
where
<YOUR_SCHEMA>.wp_posts.post_type in (‘forum’, ‘reply’, ‘topic’) and
(<YOUR_SCHEMA>.wp_users.id is null or
<YOUR_SCHEMA>.wp_users.id = 1) and
<YOUR_SCHEMA>.wp_bbp_converter_translator.value_type = ‘user’ and
<YOUR_SCHEMA>.wp_bbp_converter_translator.meta_key = ‘_bbp_old_user_id’;

/* Do this to be able to create indexes later – some may not work */
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);

/* we create indexes to speed up the update process */
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);

/* Revert bad authors to good authors */
UPDATE
<YOUR_SCHEMA>.wp_postsINNER JOIN <YOUR_SCHEMA>.TMP_ORPHANS
ON <YOUR_SCHEMA>.wp_posts.id = <YOUR_SCHEMA>.TMP_ORPHANS.id
set <YOUR_SCHEMA>.wp_posts.post_author = <YOUR_SCHEMA>.TMP_ORPHANS.real_author
where <YOUR_SCHEMA>.wp_posts.post_type in (‘reply’, ‘topic’) ;

DROP TABLE <YOUR_SCHEMA>.TMP_ORPHANS;

I hope this helps.

Gio