XenForo import script imported banned users and soft-deleted topics and posts

I used this script today and it worked well. The only issue I had was that it imported all of the banned users and soft deleted (hidden) posts/threads from Xenforo. That made hundreds of spam posts/threads visible in Discourse after the import.

If you have a lot of spam post/threads that you don’t want to import, then here are a few SQL changes you should consider making to the “xenforo.rb” script:

  1. Add the WHERE clause below to ensure only active, unbanned users are imported.
       def import_users
        puts '', "creating users"

        total_count = mysql_query("SELECT count(*) count FROM #{TABLE_PREFIX}user;").first['count']

        batches(BATCH_SIZE) do |offset|
          results = mysql_query(
            "SELECT user_id id, username, email, custom_title title, register_date created_at,
                    last_activity last_visit_time, user_group_id, is_moderator, is_admin, is_staff
             FROM #{TABLE_PREFIX}user
             WHERE user_state = 'valid' AND is_banned = 0
             LIMIT #{BATCH_SIZE}
             OFFSET #{offset};")
  1. Modify the WHERE clause below to ensure only visible threads/posts are imported.
       def import_posts
        puts "", "creating topics and posts"

        total_count = mysql_query("SELECT count(*) count from #{TABLE_PREFIX}post").first["count"]

        posts_sql = "
            SELECT p.post_id id,
                   t.thread_id topic_id,
                   #{@prefix_as_category ? 't.prefix_id' : 't.node_id'} category_id,
                   t.title title,
                   t.first_post_id first_post_id,
                   p.user_id user_id,
                   p.message raw,
                   p.post_date created_at
            FROM #{TABLE_PREFIX}post p,
                 #{TABLE_PREFIX}thread t
            WHERE p.thread_id = t.thread_id AND p.message_state = 'visible' AND t.discussion_state = 'visible'
            ORDER BY p.post_date
            LIMIT #{BATCH_SIZE}" # needs OFFSET
5 Likes

Sure thanks for that, can we fold those changes into our script @techAPJ?

4 Likes

Done via:

Thanks for the tips @msinger. :+1:

6 Likes

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.