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

[@senatorman Participant 8 years, 2 months ago]

i’ve php 3.1.9.
The convert file is made voor this version, so i do not have to make the missing tables.

The script you post above to maps the old IDs to the new ones is maybe a good solution an i wil give that a tray.

I don’t understand the field <YOUR_SCHEMA>
Do i have to change this field. If so, what must u fill in?

[@giobby Participant 8 years, 2 months ago]

Yes you should replace <YOUR_SCHEMA> with the name of the database schema in which your tables are.
Anyway it’s probably not necessary, so you may actually remove it from the script (including the following ‘.’).

[@senatorman Participant 8 years, 2 months ago]

i removed (<YOUR_SCHEMA>.) and run this as an sql in my MySql database
i get the follow error

#1054 – Unknown column ‘‘reply’’ in ‘field list’

When i fill in the name of the database in <YOUR_SCHEMA>
i get the follow error

#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘<****FOR_MONZA>.wp_posts.ID,
<****FOR_MONZA>.wp_posts.post_author,
<****FOR_MO’ at line 4

(**** i make it hidden)

[@giobby Participant 8 years, 2 months ago]

Hi @senatorman , I think you’ve replied in the wrong topic (https://bbpress.org/forums/topic/bbpress-2-6-alpha-problem-importing-phpbb/page/2/#post-178123).

Just to follow up on your error: I’ve copied and pasted my queries from a Ms Word document and something got broken.
In fact you look at the single quotes in the queries they are all wrong (’ instead of ‘).

I have fixed the query for you and you can find the new version below:

/* 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 TMP table */
DROP TABLE <YOUR_SCHEMA>.TMP_ORPHANS;

Please remember to execute each query individually and not all of them together.
You can easily distinguish the queries as I have put a comment before each of them.

[@senatorman Participant 8 years, 2 months ago]

No, the moderator combine the too topics to this one topic. i think.

I’ve run all query individually and has one errormesage on the last query
#1146 – Table ‘****_monza.wp_postsINNER’ doesn’t exist

I take a look of my forum, and see no changes. Many posts has still anonymous as author.

[@senatorman Participant 8 years, 2 months ago]

should the error i get with the last query be the reason that i don’t see any difference?

#1146 – Table ‘****_monza.wp_postsINNER’ doesn’t exist

[@giobby Participant 8 years, 2 months ago]

There’s a missing space…

wp_postsINNER should be wp_posts INNER

[@senatorman Participant 8 years, 2 months ago]

Great! it works. All authors are correct now, and there is no post with anonymous as author.

Thx for your help and patience.

Hopely the BBpress creators implement this in the convertor.

[@giobby Participant 8 years, 2 months ago]

Happy it worked for you. Unfortunately it’s more a workaraound than a solution so I don’t think it will be implemented. I’ve shared my feedbacks already and i truly believe there’s a bug in the converter. The temp table in which bbpress stores the metadata for the conversion works fine, so it’s probably about how the converter uses that table.
Anyway, enjoy your new converted forum! 🙂

[@steveim Participant 8 years, 2 months ago]

Hi. I have just converted a PHPBB v3.1.10 to BBPress using the 2.6 alpha version. I am unsure whether the user passwords are converted or not.

In the codex https://codex.bbpress.org/getting-started/importing-data/import-forums/phpbb/ it says “Existing passwords are converted during the import so users can login to WordPress/bbPress as soon as the import and recounts have finished.”
However, in the importer it says “Non-bbPress passwords cannot be automatically converted. They will be converted as each user logs in.”

I don’t know if they have been converted or what is meant by “They will be converted as each user logs in.”

Can you tell me what the status of the WP User passwords is and what the users can or need to do?

Cheers

[@netweb Keymaster 8 years, 2 months ago]

@steveim, I’ll update those docs, but what happens is the passwords are copied during import, and then when an imported user first logs in then the old password is converted from phpBB format to a WordPress formatted password and stored in WP’s database user table

[@steveim Participant 8 years, 2 months ago]

Hi Stephen. Thanks for the quick reply. That’s what I had thought should happen from other reading but it isn’t the case. The user simply gets a message saying the password is incorrect and offered the lost password option. Is there a way to check that the conversion did what it should?

[@netweb Keymaster 8 years, 2 months ago]

The best way to test this is on a local site, perform a phpBB import and try logging in with a user that you are confident you know the password.

I’m pretty sure this should be working correctly last time I tested it…

[@steveim Participant 8 years, 2 months ago]

Hi Stephen. I then set up a new WordPress install with only the bbPress 2.6 alpha plugin installed and did my import. Everything seems fine and the forums work great except for the login. I’ve tested this with a user I can log in to the PHPBB site with but it won’t let me in to the WP site at all. I can see the user in the WP Users list and it has the same details (username/email) as in the PHPBB forum.