[Gregg]

Hello,
I’m working with a 3.1v of phpBB. I’ve worked my way through a few hurdles but I’m stuck on the current one. The latest error I’m getting is [Unknown column ‘forums.forum_topics’ in ‘field list’]. For the life of me, I have no idea where this is coming from. The error info

SELECT convert(forums.forum_id USING “utf8mb4”) AS forum_id,convert(forums.parent_id USING “utf8mb4”) AS parent_id,convert(forums.forum_topics USING “utf8mb4”) AS forum_topics,convert(forums.forum_posts USING “utf8mb4”) AS forum_posts,convert(forums.forum_topics_real USING “utf8mb4”) AS forum_topics_real,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 /* From…

The very first error I had to overcome was the utf8mb4 issue. I backed up the database and then restored it in a new database. Judging by what I’m seeing it looks like I wasn’t successful?

[Gregg]

Forgot to provide the URLs.

Old phpBB forum: http://www.cpakayaker.com/forums/
New Site: http://cpakayaker.wpengine.com/

Current versions of WordPress and bbPress

[Gregg]

Anyone? Even a hint on how to troubleshoot this would be appreciated.

[Robin W]

@netweb – can you help here ?

[OSCOWP]

Having the same issue, can anybody help?

[OSCOWP]

Help…

[Gregg]

Well shoot. Looks like this is a dead end.

[OSCOWP]

yep… Sad to see no support 🤔🤔

[Gregg]

So your issue is the same as mine, trying to import from an older version of phpBB?

[Gregg]

I’m going to try to upgrade the phpBB install. Backing up files and database now. Will let you know how it goes.

[Robin W]

sorry, I know nothing of imports, so can’t help.

Unfortunately open foundation software relies on either the authors or users (such as myself) to support for free. I try and help as much as I can.

What I would ask though, is that if you fix it then

1. post the solutions(s) here to help others
2. Dip in once a month/quarterly/once on a blue moon to see if you can help someone else – after all it is someone else’s help you are now seeking.

[Gregg]

I will post the solution if/when I figure it out. The phpBB upgrade didn’t go so well – blew up the site. Trying to get it back now.

Happy to.

[Robin W]

ow !

[Gregg]

Well, I can tell you I’m totally unimpressed with the import routine for phpBB. Last count I’ve spent over 10 hours trying to figure out each of the little gotchas. And just when I’m able to get the routine to run – it doesn’t import a bloody thing. At this point, I’m about to just give up and scrap 14 years of forum posts. That or abandon bbPress and focus on fixing phpBB. Rather sad state of affairs.

[Gregg]

phpBB is now fully updated but I still get errors on import like: Unknown column ‘users.user_website’ in ‘field list’

Seriously thinking of just staying with phpBB at this point since there’re no answers to be had here.

[Gregg]

Resolved!

Grab a cup of coffee and a seat and I’ll tell you the story of how you might be able fix this. It may take several hours so be prepared…

Situation I was in:
Older version of phpBB 3.1v and wanting to port all topics, replies, and users to bbPress (latest version at the time). A handful of issues existed that were causing more trouble than they were worth. Host of the old site is GoDaddy. The new site is on WP Engine.

Forum Stats (approx)
Users: 2200
Topics: 7800
Replies: 22500

Outcome:
Successfully ported all topics, replies, and users to bbPress.

Updated phpBB
REMOVE ALL MODS, STYLES, anything that’s not a part of the out-of-the-box install. Then follow the upgrade instructions. Make backups (I kept a copy of the existing site on my local machine and duplicate database on the host). I found these instructions most helpful: https://www.phpbb.com/support/docs/en/3.2/ug/upgradeguide/upgrade31/

The Issues
There were two issues I ran into (in different flavors but it turns out they just stemmed from these two). 1st – utf8 formatting and 2nd missing fields. These issues are with the import and NOT the upgrade for phpBB. I was able to resolve those issues by making sure I followed the instructions on that link to the letter.

The first error (I don’t remember the exact error string but it was either utf8mb4 or just utf8 but it simply means the database holds the data in an older format. The solution is to export the date from the database then create a new database and restore the data into it. By creating the new database the fields will use the proper format and when the restoration is complete this issue should be resolved.

The second error was a little more mysterious – as in I’m not sure why it happened but essentially the import routine is looking for fields that don’t exist in the phpBB install even after I successfully updated it. The solution was to create the fields needed. How?

How to create the fields to get rid of the missing field errors
Note I created a copy of the functioning database on my phpBB install so I could screw it up and not hurt the functioning website. So here’s an example.

The error I got basically said the script couldn’t find “users.user_from” in the database I was trying to import from. That’s not an exact quote but the gist. So I edited the copy of my database and added the missing field (user_from) to the table it was looking for it in (users) using datatype VARCHAR and length of 255 (all else was left to default). I had to do this about a dozen times between the users, topics, and forums tables.

I should note I imported all of my users as well. I had to try this whole process several times. I’ve spent 40+ hours trying to figure this all out (I’m not that familiar with phpBB or bbPress) but the actual process once I figured out the issues – took about 6 hours – mostly waiting for the script to process the data. If your import bombs – read the error and look carefully at what it’s looking for or doesn’t like. Most likely it’s looking for something that’s not there.

I was worried that the connections between topics and replies and users would somehow be messed up. But in the end, it looks like it all came out fine. I’m pretty sure I’ll find something amiss – that’s my cynical side but maybe I’ll be pleasantly surprised.

Good luck!

[Gregg]

FYI – version of phpBB I was trying to upgrade to: 3.2.4

[Robin W]

@lorax

Fantastic, glad that through persistence you got there. I started with bbpress 4 years ago, from a Snitz database, and it took 24 attempts to get the custom importer to work, so I know the frustration you have gone through.

I can’t comment on the missing fields as I don’t know phpBB, but thanks for posting the solution, this should help others trying the import.

[maximemue]

@Gregg: thanks a lot for your problem and solution description. I think I have the same issue (phpBB 3.1, current WordPress/bbpress)

WordPress-Datenbank-Fehler: [Unknown column 'users.user_website' in 'field list']
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(users.user_website USING "utf8mb4") AS user_website,convert(users.user_regdate USING "utf8mb4") AS user_regdate,convert(users.user_aim USING "utf8mb4") AS user_aim,convert(users.user_yim USING "utf8mb4") AS user_yim,convert(users.user_icq USING "utf8mb4") AS user_icq,convert(users.user_msnm USING "utf8mb4") AS user_msnm,convert(users.user_jabber USING "utf8mb4") AS user_jabber,convert(users.user_occ USING "utf8mb4") AS user_occ,convert(users.user_interests USING "utf8mb4") AS user_interests,convert(users.user_sig USING "utf8mb4") AS user_sig,convert(users.user_from USING "utf8mb4") AS user_from,convert(users.user_avatar USING "utf8mb4") AS user_avatar FROM phpbb_users AS users LIMIT 0, 100

If you say you exported the phpBB Database, do you mean simply with phpmyamdin? But then what do I have to do, simply create a new one in phpMyAdmin and import this database? this I would understand, but how do I tell bbpress to use this database?

Thanks for your help!!

Best

Maxime

[Gregg]

@maximemue. The point of the export/import task is to get the data into a database with the latest data format. I found a thread that suggested exporting data out of the old database then creating a new one – which should have the newer format – and importing the data into it. How you accomplish this depends on what tools you have available. In my case, I was using the GoDaddy database interface – not PHPMyAdmin. I didn’t have a choice but PHPMyAdmin should be fine.

Once you’ve done this you’ll need to “wire” up phpBB to use the new database by editing the config file. Look for the documents on their website that deal with installation and setup. That’ll point you in the right direction on which file and what to change.

[maximemue]

ahhh ok, understood, it’s just about preparing the old database so the import routine will work. Ok, thanks für your help, will try it!!

[maximemue]

Hi,

ok, I have copied the phpBB database into a fresh one. It was UTF8_unicode before, the copy is now utf8mb4_unicode. I have linked the import routine to this new database. I still get this message:

WordPress-Datenbank-Fehler: [Unknown column ‘users.user_website’ in ‘field list’]
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(users.user_website USING “utf8mb4”) AS user_website,convert(users.user_regdate USING “utf8mb4”) AS user_regdate,convert(users.user_aim USING “utf8mb4”) AS user_aim,convert(users.user_yim USING “utf8mb4”) AS user_yim,convert(users.user_icq USING “utf8mb4”) AS user_icq,convert(users.user_msnm USING “utf8mb4”) AS user_msnm,convert(users.user_jabber USING “utf8mb4”) AS user_jabber,convert(users.user_occ USING “utf8mb4”) AS user_occ,convert(users.user_interests USING “utf8mb4”) AS user_interests,convert(users.user_sig USING “utf8mb4”) AS user_sig,convert(users.user_from USING “utf8mb4”) AS user_from,convert(users.user_avatar USING “utf8mb4”) AS user_avatar FROM phpbb_users AS users LIMIT 0, 100

Any thoughts where I could start searching for errors?

Thanks a lot!

Best

Maxime

[Gregg]

Your db is missing the field users.user_website You need to create this as I explain in my answer above. See How to create the fields to get rid of the missing field errors

[maximemue]

Thanks Gregg, I did add the missing columns and now the import routine is doing its job. It takes very long for the answers 13400-13499, let’s hope it is not stuck…

[maximemue]

yeah, it’s stuck…

[maximemue]

ok, new and perhaps/hopefully last question: the Import is running smoothly but very slowly. 3,5 hours for 16,000 answers/posts (so 200,000 left…).

Will the import routine stop when I close the admin back-end?