Importing / migrating from phpBB3

It doesn’t seem that’s the issue:
I added line breaks so the content looks like this:

[url=https://www.casimages.com/i/1907271141431956116331631.jpg.html]

[img]https://nsm09.casimages.com/img/2019/07/27//1907271141431956116331631.jpg[/img]

[/url]

And the result:

image

Example posts:

Yes, that’s a good idea! :+1: So basically, replacing the strings (from the topic you linked) using regexes to target Imageshack links only, right?

2 Likes

So, this is not the issue after experimenting with new lines.

But if I copy-paste the post content into a new post on the same forum, the content is correctly displayed :woozy_face: :question: :


(and also the hotlinked images are successfully downloaded locally)

Vs:

I tried rebaking the post with the broken images (that are correctly displayed in the message preview, see my previous post) but it didn’t fix anything.

As far as I know, the raw content from both posts is exactly the same.

But the cooked content from the first post (second line) leads to broken images:

I’m puzzled.

1 Like

Odd thing:
If I try to rebuild the HTML of any post, I got an error 500:

Logs show a fatal error and a warning:

Screenshot of the log window:

Fatal error:

ArgumentError (wrong number of arguments (given 0, expected 1)) lib/guardian/ensure_magic.rb:11:in 'method_missing' app/controllers/posts_controller.rb:570:in 'rebake' app/controllers/application_cont

plugins/discourse-chat/lib/guardian_extensions.rb:71:in `can_rebake?'
lib/guardian/ensure_magic.rb:11:in `method_missing'
app/controllers/posts_controller.rb:570:in `rebake'
actionpack (6.1.4.1) lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack (6.1.4.1) lib/abstract_controller/base.rb:228:in `process_action'
actionpack (6.1.4.1) lib/action_controller/metal/rendering.rb:30:in `process_action'
actionpack (6.1.4.1) lib/abstract_controller/callbacks.rb:42:in `block in process_action'
activesupport (6.1.4.1) lib/active_support/callbacks.rb:117:in `block in run_callbacks'
app/controllers/application_controller.rb:397:in `block in with_resolved_locale'
i18n (1.10.0) lib/i18n.rb:328:in `with_locale'
app/controllers/application_controller.rb:397:in `with_resolved_locale'
activesupport (6.1.4.1) lib/active_support/callbacks.rb:126:in `block in run_callbacks'
activesupport (6.1.4.1) lib/active_support/callbacks.rb:137:in `run_callbacks'
actionpack (6.1.4.1) lib/abstract_controller/callbacks.rb:41:in `process_action'
actionpack (6.1.4.1) lib/action_controller/metal/rescue.rb:22:in `process_action'
actionpack (6.1.4.1) lib/action_controller/metal/instrumentation.rb:34:in `block in process_action'
activesupport (6.1.4.1) lib/active_support/notifications.rb:203:in `block in instrument'
activesupport (6.1.4.1) lib/active_support/notifications/instrumenter.rb:24:in `instrument'
activesupport (6.1.4.1) lib/active_support/notifications.rb:203:in `instrument'
actionpack (6.1.4.1) lib/action_controller/metal/instrumentation.rb:33:in `process_action'
actionpack (6.1.4.1) lib/action_controller/metal/params_wrapper.rb:249:in `process_action'
activerecord (6.1.4.1) lib/active_record/railties/controller_runtime.rb:27:in `process_action'
actionpack (6.1.4.1) lib/abstract_controller/base.rb:165:in `process'
actionview (6.1.4.1) lib/action_view/rendering.rb:39:in `process'
rack-mini-profiler (3.0.0) lib/mini_profiler/profiling_methods.rb:85:in `block in profile_method'
actionpack (6.1.4.1) lib/action_controller/metal.rb:190:in `dispatch'
actionpack (6.1.4.1) lib/action_controller/metal.rb:254:in `dispatch'
actionpack (6.1.4.1) lib/action_dispatch/routing/route_set.rb:50:in `dispatch'
actionpack (6.1.4.1) lib/action_dispatch/routing/route_set.rb:33:in `serve'
actionpack (6.1.4.1) lib/action_dispatch/journey/router.rb:50:in `block in serve'
actionpack (6.1.4.1) lib/action_dispatch/journey/router.rb:32:in `each'
actionpack (6.1.4.1) lib/action_dispatch/journey/router.rb:32:in `serve'
actionpack (6.1.4.1) lib/action_dispatch/routing/route_set.rb:842:in `call'
lib/middleware/omniauth_bypass_middleware.rb:71:in `call'
rack (2.2.3) lib/rack/tempfile_reaper.rb:15:in `call'
rack (2.2.3) lib/rack/conditional_get.rb:40:in `call'
rack (2.2.3) lib/rack/head.rb:12:in `call'
actionpack (6.1.4.1) lib/action_dispatch/http/permissions_policy.rb:22:in `call'
lib/content_security_policy/middleware.rb:12:in `call'
lib/middleware/anonymous_cache.rb:358:in `call'
rack (2.2.3) lib/rack/session/abstract/id.rb:266:in `context'
rack (2.2.3) lib/rack/session/abstract/id.rb:260:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/cookies.rb:689:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/callbacks.rb:27:in `block in call'
activesupport (6.1.4.1) lib/active_support/callbacks.rb:98:in `run_callbacks'
actionpack (6.1.4.1) lib/action_dispatch/middleware/callbacks.rb:26:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/actionable_exceptions.rb:18:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/debug_exceptions.rb:29:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/show_exceptions.rb:33:in `call'
logster (2.10.1) lib/logster/middleware/reporter.rb:43:in `call'
railties (6.1.4.1) lib/rails/rack/logger.rb:37:in `call_app'
railties (6.1.4.1) lib/rails/rack/logger.rb:28:in `call'
config/initializers/100-quiet_logger.rb:23:in `call'
config/initializers/100-silence_logger.rb:31:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/remote_ip.rb:81:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/request_id.rb:26:in `call'
lib/middleware/enforce_hostname.rb:23:in `call'
rack (2.2.3) lib/rack/method_override.rb:24:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/executor.rb:14:in `call'
rack (2.2.3) lib/rack/sendfile.rb:110:in `call'
actionpack (6.1.4.1) lib/action_dispatch/middleware/host_authorization.rb:92:in `call'
rack-mini-profiler (3.0.0) lib/mini_profiler/profiler.rb:249:in `call'
message_bus (4.2.0) lib/message_bus/rack/middleware.rb:60:in `call'
lib/middleware/request_tracker.rb:202:in `call'
railties (6.1.4.1) lib/rails/engine.rb:539:in `call'
railties (6.1.4.1) lib/rails/railtie.rb:207:in `public_send'
railties (6.1.4.1) lib/rails/railtie.rb:207:in `method_missing'
rack (2.2.3) lib/rack/urlmap.rb:74:in `block in call'
rack (2.2.3) lib/rack/urlmap.rb:58:in `each'
rack (2.2.3) lib/rack/urlmap.rb:58:in `call'
unicorn (6.1.0) lib/unicorn/http_server.rb:634:in `process_client'
unicorn (6.1.0) lib/unicorn/http_server.rb:739:in `worker_loop'
unicorn (6.1.0) lib/unicorn/http_server.rb:547:in `spawn_missing_workers'
unicorn (6.1.0) lib/unicorn/http_server.rb:143:in `start'
unicorn (6.1.0) bin/unicorn:128:in `<top (required)>'
vendor/bundle/ruby/2.7.0/bin/unicorn:25:in `load'
vendor/bundle/ruby/2.7.0/bin/unicorn:25:in `<main>'

It was in safe-mode with all theme and plugins disabled, but this code block mentions on the first line:
plugins/discourse-chat/lib/guardian_extensions.rb:71:in 'can_rebake?'

Warning:

Failed to handle exception in exception app middleware : ArgumentError : wrong number of arguments (given 0, expected 1)

/var/www/discourse/plugins/discourse-chat/lib/guardian_extensions.rb:71:in `can_rebake?'
/var/www/discourse/lib/guardian/ensure_magic.rb:11:in `method_missing'
/var/www/discourse/app/controllers/posts_controller.rb:570:in `rebake'
actionpack-6.1.4.1/lib/action_controller/metal/basic_implicit_render.rb:6:in `send_action'
actionpack-6.1.4.1/lib/abstract_controller/base.rb:228:in `process_action'
actionpack-6.1.4.1/lib/action_controller/metal/rendering.rb:30:in `process_action'
actionpack-6.1.4.1/lib/abstract_controller/callbacks.rb:42:in `block in process_action'
activesupport-6.1.4.1/lib/active_support/callbacks.rb:117:in `block in run_callbacks'
/var/www/discourse/app/controllers/application_controller.rb:397:in `block in with_resolved_locale'
i18n-1.10.0/lib/i18n.rb:328:in `with_locale'
/var/www/discourse/app/controllers/application_controller.rb:397:in `with_resolved_locale'
activesupport-6.1.4.1/lib/active_support/callbacks.rb:126:in `block in run_callbacks'
activesupport-6.1.4.1/lib/active_support/callbacks.rb:137:in `run_callbacks'
actionpack-6.1.4.1/lib/abstract_controller/callbacks.rb:41:in `process_action'
actionpack-6.1.4.1/lib/action_controller/metal/rescue.rb:22:in `process_action'
actionpack-6.1.4.1/lib/action_controller/metal/instrumentation.rb:34:in `block in process_action'
activesupport-6.1.4.1/lib/active_support/notifications.rb:203:in `block in instrument'
activesupport-6.1.4.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
activesupport-6.1.4.1/lib/active_support/notifications.rb:203:in `instrument'
actionpack-6.1.4.1/lib/action_controller/metal/instrumentation.rb:33:in `process_action'
actionpack-6.1.4.1/lib/action_controller/metal/params_wrapper.rb:249:in `process_action'
activerecord-6.1.4.1/lib/active_record/railties/controller_runtime.rb:27:in `process_action'
actionpack-6.1.4.1/lib/abstract_controller/base.rb:165:in `process'
actionview-6.1.4.1/lib/action_view/rendering.rb:39:in `process'
rack-mini-profiler-3.0.0/lib/mini_profiler/profiling_methods.rb:85:in `block in profile_method'
actionpack-6.1.4.1/lib/action_controller/metal.rb:190:in `dispatch'
actionpack-6.1.4.1/lib/action_controller/metal.rb:254:in `dispatch'
actionpack-6.1.4.1/lib/action_dispatch/routing/route_set.rb:50:in `dispatch'
actionpack-6.1.4.1/lib/action_dispatch/routing/route_set.rb:33:in `serve'
actionpack-6.1.4.1/lib/action_dispatch/journey/router.rb:50:in `block in serve'
actionpack-6.1.4.1/lib/action_dispatch/journey/router.rb:32:in `each'
actionpack-6.1.4.1/lib/action_dispatch/journey/router.rb:32:in `serve'
actionpack-6.1.4.1/lib/action_dispatch/routing/route_set.rb:842:in `call'
/var/www/discourse/lib/middleware/omniauth_bypass_middleware.rb:71:in `call'
rack-2.2.3/lib/rack/tempfile_reaper.rb:15:in `call'
rack-2.2.3/lib/rack/conditional_get.rb:40:in `call'
rack-2.2.3/lib/rack/head.rb:12:in `call'
actionpack-6.1.4.1/lib/action_dispatch/http/permissions_policy.rb:22:in `call'
/var/www/discourse/lib/content_security_policy/middleware.rb:12:in `call'
/var/www/discourse/lib/middleware/anonymous_cache.rb:358:in `call'
rack-2.2.3/lib/rack/session/abstract/id.rb:266:in `context'
rack-2.2.3/lib/rack/session/abstract/id.rb:260:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/cookies.rb:689:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/callbacks.rb:27:in `block in call'
activesupport-6.1.4.1/lib/active_support/callbacks.rb:98:in `run_callbacks'
actionpack-6.1.4.1/lib/action_dispatch/middleware/callbacks.rb:26:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/actionable_exceptions.rb:18:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/debug_exceptions.rb:29:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/show_exceptions.rb:33:in `call'
logster-2.10.1/lib/logster/middleware/reporter.rb:43:in `call'
railties-6.1.4.1/lib/rails/rack/logger.rb:37:in `call_app'
railties-6.1.4.1/lib/rails/rack/logger.rb:28:in `call'
/var/www/discourse/config/initializers/100-quiet_logger.rb:23:in `call'
/var/www/discourse/config/initializers/100-silence_logger.rb:31:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/remote_ip.rb:81:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/request_id.rb:26:in `call'
/var/www/discourse/lib/middleware/enforce_hostname.rb:23:in `call'
rack-2.2.3/lib/rack/method_override.rb:24:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/executor.rb:14:in `call'
rack-2.2.3/lib/rack/sendfile.rb:110:in `call'
actionpack-6.1.4.1/lib/action_dispatch/middleware/host_authorization.rb:92:in `call'
rack-mini-profiler-3.0.0/lib/mini_profiler/profiler.rb:249:in `call'
message_bus-4.2.0/lib/message_bus/rack/middleware.rb:60:in `call'
/var/www/discourse/lib/middleware/request_tracker.rb:202:in `call'
railties-6.1.4.1/lib/rails/engine.rb:539:in `call'
railties-6.1.4.1/lib/rails/railtie.rb:207:in `public_send'
railties-6.1.4.1/lib/rails/railtie.rb:207:in `method_missing'
rack-2.2.3/lib/rack/urlmap.rb:74:in `block in call'
rack-2.2.3/lib/rack/urlmap.rb:58:in `each'
rack-2.2.3/lib/rack/urlmap.rb:58:in `call'
unicorn-6.1.0/lib/unicorn/http_server.rb:634:in `process_client'
unicorn-6.1.0/lib/unicorn/http_server.rb:739:in `worker_loop'
unicorn-6.1.0/lib/unicorn/http_server.rb:547:in `spawn_missing_workers'
unicorn-6.1.0/lib/unicorn/http_server.rb:143:in `start'
unicorn-6.1.0/bin/unicorn:128:in `<top (required)>'
/var/www/discourse/vendor/bundle/ruby/2.7.0/bin/unicorn:25:in `load'
/var/www/discourse/vendor/bundle/ruby/2.7.0/bin/unicorn:25:in `<main>'

Do this information help?
I’m doing what I can to try to repair these posts, but I’m starting to believe this has something to do with Discourse itself and not something on my side maybe? :person_shrugging:


edit: see the automatically linked post below.


Issue resolved:

Now I just have to rebake all my posts. :wink:


Another edit:

Could have worked for sure, and will for images that still have their old plain URL, but:

  • Since all the old Imageshack broken links were replaced by valid images displaying a Ukraine flag by Imageshack itself, they were automatically uploaded to Discourse, changing their URL to a markdown local image link.

  • Fortunately, all these flags were uploaded to Discourse with identical links (![](upload://sOlmOE8qRgLUJ8cCe6N1JNkDV3Y.jpeg) in my case - excellent Discourse behavior by the way :+1:), which means that they will be very easy to target and remove/replace with something else. :slight_smile:

I’m just writing this to inform people that would encounter the same issue as me after a migration.

4 Likes

It seems like the poll_option_total value is off sometimes. I’m seeing more non-anonymous votes per option than the value of poll_option_total. And all those users exist.

Using GREATEST(..., 0) fixes it. At least, it doesn’t crash.

        SELECT o.poll_option_id, o.poll_option_text, o.poll_option_total AS total_votes,
          GREATEST(CAST(o.poll_option_total AS SIGNED) - (
            SELECT COUNT(DISTINCT v.vote_user_id)
              FROM #{@table_prefix}poll_votes v
                JOIN #{@table_prefix}users u ON (v.vote_user_id = u.user_id)
                JOIN #{@table_prefix}topics t ON (v.topic_id = t.topic_id)
              WHERE v.poll_option_id = o.poll_option_id AND v.topic_id = o.topic_id
          ),0) AS anonymous_votes
        FROM #{@table_prefix}poll_options o
        WHERE o.topic_id = #{topic_id}
        ORDER BY o.poll_option_id
3 Likes

I really want to migrate my company’s phpbb 3.3 forum to Discourse. Does anyone have any idea when that will be supported?

2 Likes

Oh wow, thats great! Thanks!

If I should start a new thread for this, I apologize in advance.

I’ve got a phpBB forum in a subfolder:

thedomain.com/forum/

I’m planning to use the import script to migrate from phpBB to Discourse in a subdomain; i.e.:

forum.thedomain.com

So, it looks like all I’ll need to do–for the domain and permalinks–is set the following in settings.yml using HTTPS on the new site:

site_prefix:
    # this is needed for rewriting internal links in posts
    original: thdomain.com/forum    # without http(s)://
    new: https://forum.thedomain.com       # with http:// or https://

  # Enable this, if you want to redirect old forum links to the the new locations.
  permalinks:
    categories: true  # redirects   /viewforum.php?f=1            to  /c/category-name
    topics: true      # redirects   /viewtopic.php?f=6&t=43       to  /t/topic-name/81
    posts: true      # redirects   /viewtopic.php?p=2455#p2455   to  /t/topic-name/81/4
    # Append a prefix to each type of link, e.g. 'forum' to redirect /forum/viewtopic.php?f=6&t=43 to /t/topic-name/81
    # Leave it empty if your forum wasn't installed in a subfolder.
    prefix: forum

And then, on the original server, which runs Apache, in the .htaccess with RewriteEngine already on, I’ll need:

RewriteCond %{HTTP_HOST} ^thedomain.com
RewriteRule ^forum/(.*)$ https://forum.thedomain.com/$1 [L,R=301]

If it’s really that easy, this script rocks! :grinning:

I do have a couple of additional questions:

  1. Posts are set to “false” in the permalink settings. I know there are links out there to specific posts, so I’d like them permalinked as well. Is there any reason not to set them to true; e.g., performance or something?
  2. Is the prefix okay without single quotes, or should it be quoted as in the comment?
1 Like

It will slightly slow down the import, but you shouldn’t worry about that. It’s mostly disabled by default because it’s rarely needed.

Yes, that’s fine.

1 Like

I ran into a snag as I’m preparing for this import.

The phpBB forum I’m planning to migrate is using a custom BBcode that creates HTML img tags for images stored in folders by year in a directory outside phpBB, but on the same user account on the server. The BBcode takes the following format:

[Gallery]{NUMBER1}|{NUMBER2}|{NUMBER3}|{SIMPLETEXT}[/Gallery]

The {NUMBER1} and {NUMBER2} are integers for the width and height of the image, respectively. The {NUMBER3} is an integer for the year-folder the image is in, and the {SIMPLETEXT} is the image file name. The HTML replacement for the BBcode looks like this:

<img class="postimage gallery-image" src="/gallery/files/{NUMBER3}/{SIMPLETEXT}" width="{NUMBER1}" height="{NUMBER2}" />

What I need to figure out is how to handle this custom BBcode with the migration script, both to convert the custom BBcode to something Discourse can use, and figure out where to put the image files in the file tree for the import.

I took a look at Ruby-BBcode-to-MD, which says it “allows to easily extend this set with custom tags by editing tags.rb.” I took a look at tags.rb, but since this is my first time looking at Ruby code, I’m at a loss as to how to deal with my custom BBcode since its pipe-separated format differs from any of the official BBcodes that the tags.rb file includes which I might have used as a guide.

I don’t have templates/import/phpbb3.template.yml loaded yet, so I’m guessing that will add the Ruby BBcode to MD gem?

So I have questions:

  1. What is the best way to handle this custom BBcode for the migration? Is editing the tags.rb file the way to go? (If so, I’m going to do some research on Ruby coding and maybe regex next, but any hints will be welcome!)
  2. What is the best place to put the image files for this gallery in the import file tree so that they’re saved in Discourse and show up in the imported posts?

That gem isn’t really used anymore. From which version of phpBB are you importing? If it’s v3.1 or below, you will need to apply some regex magic in discourse/text_processor.rb at ea2fd75d10e81687334f8d29e0d3e786dbdb4a53 · discourse/discourse · GitHub.

For v3.2 you can add BBCode handling in discourse/xml_to_markdown.rb at a71b219c9a18ad418e330282fde511bcb79ba7c4 · discourse/discourse · GitHub

1 Like

It’s actually 3.3, so I’m doing prep work pending the PRs coming to make the script compatible with that version of phpBB, or I may give it a shot with the current script using the edit you suggested earlier for the version check…

So I guess I’ll need to work with xml_to_markdown.rb?

1 Like

My attempt to do a test import with a phpBB 3.3.x forum was a big fail, but more on that later.

I learned a lot in the process of trying it out, and thought I’d share what I learned because it might be helpful for other imports and when the import script is updated for phpBB 3.3.

If you need to edit /script/import_scripts/phpbb3/database/database.rb to get past the version check, or /script/import_scripts/phpbb3/support/bbcode/xml_to_markdown.rb to add custom BBcodes, the time to do so is after you enter the import with this command:

/var/discourse/launcher enter import

At that point, you can use nano to edit the files.

Since my attempt to run the import on my phpBB 3.3 forum failed, I can’t be sure my custom BBcode changes would have been effective, but it looks like all you have to do for phpBB 3.2+ is edit the file:

/script/import_scripts/phpbb3/support/bbcode/xml_to_markdown.rb

as noted above, and add a definition in the format of:

def visit_<your case-sensitive custom BBcode here>(xml_node, md_node)
  # code to handle things goes here
end

Again, I didn’t get to test this yet, but one very common custom BBcode in phpBB that doesn’t appear in the version of the xml_to_markdown.rb I had is this one:

[YouTube]{Identifier}[/YouTube]

where the {Identifier} is the “v” value from the YouTube URL’s query string.

So my Ruby-nooby attempt at adding that code to xml_to_markdown.rb is:

def visit_YouTube(xml_node, md_node)
      content = xml_node.content
      url = "https://www.youtube.com/watch?v=" + content
      md_node.text = "#{url}"
      md_node.prefix_linebreaks = md_node.postfix_linebreaks = 2
      md_node.prefix_linebreak_type = LINEBREAK_HTML
end

Finally, when I ran the import script, I only got to the database loading step, and got this error:

ERROR 1071 (42000) at line 1233035: Specified key was too long; max key length is 1000 bytes

I’m not sure if that’s a DB Engine issue–the original uses InnoDB–or what, but I suspect it will be addressed in the phpBB 3.3 version of the script.

1 Like

Okay, so I couldn’t leave this alone.

While I suppose it’s possible that the error I got could be handled by the import script, it really appears to me that it’s an issue with the database structure of phpBB3, and the fact that my database collation is UTF8. I got this error running the import script:

ERROR 1071 (42000) at line 1233035: Specified key was too long; max key length is 1000 bytes

When I looked at line 1233035 of my phpbb_mysql.sql file–I’m using a local copy of the data dump–I saw this:

ALTER TABLE `phpbb_config`
  ADD PRIMARY KEY (`config_name`),
  ADD KEY `is_dynamic` (`is_dynamic`);

Looking at the config_name column in the table phpbb_config, I found that it’s set to VARCHAR(255).

Thanks to this post on Stackoverflow, I discovered that I can use a “prefix index” to shorten the length of the key. I tested it, and it works.

For my phpBB 3.3.8 database, there were actually four tables affected:

  • phpbb_config
  • phpbb_config_text
  • phpbb_migrations
  • phpbb_oauth_accounts

If anyone else wants to try it, here are the steps for the phpbb_config table, and I’ll include the queries for the other tables at the end.

Calculate the shortest value for the prefix index using this query:

SELECT
 ROUND(SUM(LENGTH(`config_name`)<10)*100/COUNT(`config_name`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`config_name`)<20)*100/COUNT(`config_name`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`config_name`)<50)*100/COUNT(`config_name`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`config_name`)<100)*100/COUNT(`config_name`),2) AS pct_length_100
FROM `phpbb_config`;

In my case, it showed that 100% of the rows used less than 50 characters, so I edited my data dump file:

nano /var/discourse/shared/standalone/import/data/phpbb_mysql.sql

and used search Ctrl+W for the following string:

ALTER TABLE `phpbb_config`

then changed this:

ALTER TABLE `phpbb_config`
  ADD PRIMARY KEY (`config_name`),
  ADD KEY `is_dynamic` (`is_dynamic`);

to this:

ALTER TABLE `phpbb_config`
  ADD PRIMARY KEY (`config_name`(50)),
  ADD KEY `is_dynamic` (`is_dynamic`);

The (50) in there is the prefix index length.

In my case, I had to repeat the process with the other three tables using these queries to get the optimal prefix key length:

SELECT
 ROUND(SUM(LENGTH(`config_name`)<10)*100/COUNT(`config_name`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`config_name`)<20)*100/COUNT(`config_name`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`config_name`)<50)*100/COUNT(`config_name`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`config_name`)<100)*100/COUNT(`config_name`),2) AS pct_length_100
FROM `phpbb_config_text`;

SELECT
 ROUND(SUM(LENGTH(`migration_name`)<10)*100/COUNT(`migration_name`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`migration_name`)<20)*100/COUNT(`migration_name`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`migration_name`)<50)*100/COUNT(`migration_name`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`migration_name`)<100)*100/COUNT(`migration_name`),2) AS pct_length_100
FROM `phpbb_migrations`;

SELECT
 ROUND(SUM(LENGTH(`provider`)<10)*100/COUNT(`provider`),2) AS pct_length_10,
 ROUND(SUM(LENGTH(`provider`)<20)*100/COUNT(`provider`),2) AS pct_length_20,
 ROUND(SUM(LENGTH(`provider`)<50)*100/COUNT(`provider`),2) AS pct_length_50,
 ROUND(SUM(LENGTH(`provider`)<100)*100/COUNT(`provider`),2) AS pct_length_100
FROM `phpbb_oauth_accounts`;

Here are the search strings for the other tables to save you some time:

ALTER TABLE `phpbb_config_text`
ALTER TABLE `phpbb_migrations`
ALTER TABLE `phpbb_oauth_accounts`

I won’t say I’m home free, but the above solved my initial database error, and the import is running:

#import_phpbb3.sh
The phpBB3 import is starting...

Loading existing groups...
Loading existing users...
Loading existing categories...
Loading existing posts...
Loading existing topics...

importing from phpBB 3.3.8

creating users
      722 / 5014 ( 14.4%)  [58 items/min]

Update:

The import script finished in 9 hours, 28 minutes. Thank goodness for Screen. This script is awesome!

Post-processing is now in progress.

Is there a log of the output of the import script somewhere, or should I have piped it to a file?

2 Likes

Which procedure for the phpbb3 importer should be used when running discourse_dev in a docker container under WSL?

I have tried doing both the procedure 1. Importing using a Docker container and procedure 2. Importing using development environment. Neither has worked although I did get the script to run to completion under procedure 1 but no data actually made it into the forum database despite the many queries seeming to have succeeded as they went along. Could there be a different database involved that is being populated due to import being run in its own container separate from the discourse_dev container?

In the case of procedure 2 it is simply not possible to execute the build which fails with “An error occurred while installing tiny_tds (2.1.5), and Bundler cannot continue.”

Should I try doing a ‘standard install’ (non-docker, non-dev) in WSL then do the Docker-based import?

That’s what I recommend. I very rarely do a migration on development anymore.

Also, you don’t need tiny_tds, unless you’re using mssql rather than mysql/mariadb.

Thanks very much, Jay. Your tutorials have been spot-on, I really appreciate having them.

1 Like

I successfully installed Discourse on a new VPS using the standard Docker installation process. I then ran the import process according to these instructions and the script finished without problems. I exited the container and then issued the stop import and got the message that:

''You have less than 5GB of free space on the disk where /var/lib/docker is located. You will need more space to continue
Filesystem Size Used Avail Use% Mounted on
/dev/vda1 24G 20G 2.7G 89% /

Would you like to attempt to recover space by cleaning docker images and containers in the system? (y/N)‘’

Recovering space produced no additional room. So how to proceed? Can I safely delete all the image files that I uploaded into /data? I am not sure if they are still needed.

Thanks.

If the import seems to to have finished, and you’re talking about the images that should have been imported, it should be ok, but you’re likely not going to have enough space to make a backup. I would upgrade the droplet to have more space. The price per day shouldn’t be a big deal.

Thank you for this advice, Jay.

In case these details will be helpful to others, I removed the image files (only) which freed up ~ 3.4 GB for a total of 6.1 GB available and Sidekiq then successfully chewed through the post-processing. The disk space has since been increased by an additional 20 GB.

For the record this was an import of a phpBB version 3.3.0 messageboard with a little over 73,000 posts in a little over 8,000 topics with a little over 1300 users.

The only issue I have found, so far, is that some usernames are squirrely but that has been discussed above and isn’t fatal.

There will be an interval between this import and the closure of the source phpBB forum. Can I just leave the import Docker container in place and then use it on an incremental backup to migrate posts made after this import?