Completed Migration vB3 to Discourse including Legacy vB3 Thank You to Likes

Just completed, for the most part (still in test bed, but the “hard parts” are basically done and the migration works fine), migrating a 15 year old vb3 forum, which was originally (believe-it-or-not) a vb2 forum from 2000.

This forum has been around for around 20 years (with unimaginable custom PHP code, tables, plugins, etc), and Discourse is the only forum software, in my view, “worthy” of a migration project, which I started around a week ago, going from feasibility to completion (basically).

First of all, I want to thank the entire Discourse team for such a remarkable “work-of-art” modern forum. Discourse is truly remarkable, in my view (and I am sure many others).

Second, I would like to thank the team who coded the original vbulletin.rb migration script. This script was about 95% OK. and I modified that script to get it to work properly for vb3. For example, vb3 does not have a filedata table like vb4 (related to attachments); so I modified vbulletin.rb to get it to work. Also, there were issues importing child vb3 forums as categories, but I modified the code to import all forums as top level categories and then wrote some postgres psql to create new parent-child categories relationships (or manually configured). There were other “gotchas” (a story for another day) but those goblins made it fun and challenging, but not insurmountable.

Third, I would like to thank Sam and his code lithium.rb where I used his import_likes routine as the basis for my vb3 thank you (from a legacy plugin, not part of vB OOTB) to discourse likes. I made minor changes to the import_likes routine and after a few hours of debugging, got it to work.

As you can see from this 2018 post, migrated today:

I thought it was important for users that their vb thanks migrated to discourse likes and so here is the code I used to migrate the likes for other lost vb3 forum souls:

First of all I created a vb3 mysql table called user_actions and wrote a PHP script to fill it from the legacy vb post_thanks table, like this (not polished, but working):

ubuntu:/var/www/includes/cron# cat thanks_to_discourse.php
 <?php

/**************************************************
+-----------+------------+-----------+-----------+
| thanker   | unixtime   | id        | thanked   |
+-----------+------------+-----------+-----------+
|         1 | 1584149592 | 303045211 | 302093876 |
| 302153369 | 1584136706 | 303045214 | 302116191 |
| 302108573 | 1584128526 | 303045211 | 302093876 |
| 302153369 | 1584126659 | 303042175 | 302116191 |
| 302153369 | 1584126400 | 303045174 | 302116191 |
| 302153369 | 1584117711 | 303045184 |         1 |
|     37898 | 1584108187 | 303045175 |         1 |
| 302181242 | 1584106664 | 303045201 | 302122047 |
| 302181242 | 1584104642 | 303045074 | 302052697 |
| 302025710 | 1584103722 | 303045184 |         1 |
+-----------+------------+-----------+-----------+
 **************************************************/

$query = 'SELECT p.threadid AS threadid,t.userid AS thanker,t.date AS unixtime,t.postid AS postid,p.userid AS thanked from post_thanks as t LEFT JOIN post p ON p.postid = t.postid';

$allthanks = $vbulletin->db->query_read($query);

/****************************************
mysql> describe user_actions;
+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| id              | int(11)          | NO   | PRI | NULL    | auto_increment |
| action_type     | int(11) unsigned | NO   |     | NULL    |                |
| user_id         | int(11) unsigned | NO   |     | NULL    |                |
| target_topic_id | int(11) unsigned | YES  |     | NULL    |                |
| target_post_id  | varchar(16)      | YES  |     | NULL    |                |
| target_user_id  | int(11) unsigned | YES  |     | NULL    |                |
| acting_user_id  | int(11) unsigned | YES  |     | NULL    |                |
| created_at      | timestamp        | YES  |     | NULL    |                |
| updated_at      | timestamp        | YES  |     | NULL    |                |
+-----------------+------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
 *************************************/

$action_code = 2; // discourse like action == 2
$target_user_id = "";
while ($action = $vbulletin->db->fetch_array($allthanks)) {

    #code to deal with how discourse identifies the first post in a topic in the post_custom_fields table
    $query = 'SELECT firstpostid FROM thread WHERE threadid =' . $action['threadid'] . ' LIMIT 1';
    $threadinfo = $vbulletin->db->query_first($query);
    if ($threadinfo['firstpostid'] == $action['postid']) {
        $action['postid'] = 'thread-' . $action['threadid'];
    }

    $update = 'INSERT INTO user_actions (action_type, user_id, target_topic_id, target_post_id, target_user_id,acting_user_id, created_at, updated_at)' .
        ' VALUES (' . $action_code . ',' .
        '"' . $action['thanked'] . '",' .
        '"' . $action['threadid'] . '",' .
        '"' . $action['postid'] . '",' .
        '"' . $target_user_id . '",' .
        '"' . $action['thanker'] . '",' .
        'FROM_UNIXTIME(' . $action['unixtime'] . '),' .
        'FROM_UNIXTIME(' . $action['unixtime'] . '))';
    $doit = $vbulletin->db->query_write($update);
}

So, this mysql user_actions table is included with the migration dump.

Here is the modified import_likes routine I used to complete the migration on the discourse side:

def import_likes
    puts "\nimporting likes..."

    # created mysql user_actions table in vb3 using PHP script and included that table with migration dump
    sql = "select acting_user_id as user_id, target_post_id as post_id, created_at from user_actions"
    results = mysql_query(sql)
    puts "length() method form : #{results.count}\n\n"

    puts "skip loading unique id map"
    existing_map = {}
    PostCustomField.where(name: 'import_id').pluck(:post_id, :value).each do |post_id, import_id|
      existing_map[import_id] = post_id
      #puts "postcustomfield existing_map post_id: #{post_id} import_id #{import_id}\n"
    end

    puts "loading data into temp table"

    #manually created the temp like_data table so I could check the table after session ends
    #DB.exec("create temp table like_data(user_id integer, post_id integer, created_at timestamp without time zone)")
    
    puts "like_data temp table created"
    PostAction.transaction do
      results.each do |result|

        result["user_id"] = user_id_from_imported_user_id(result["user_id"].to_s)
        result["post_id"] = existing_map[result["post_id"].to_s]

        next unless result["user_id"] && result["post_id"]

        puts "insert like table user_id: #{result["user_id"]} post_id #{result["post_id"]}\n"

        DB.exec("INSERT INTO like_data VALUES (:user_id,:post_id,:created_at)",
          user_id: result["user_id"],
          post_id: result["post_id"],
          created_at: result["created_at"]
        )

      end
    end


    puts "creating missing post actions"
    DB.exec <<~SQL

    INSERT INTO post_actions (post_id, user_id, post_action_type_id, created_at, updated_at)
             SELECT l.post_id, l.user_id, 2, l.created_at, l.created_at FROM like_data l
             LEFT JOIN post_actions a ON a.post_id = l.post_id AND l.user_id = a.user_id AND a.post_action_type_id = 2
             WHERE a.id IS NULL
    SQL

    puts "creating missing user actions"
    DB.exec <<~SQL
    INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
             SELECT pa.user_id, 1, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
             FROM post_actions pa
             JOIN posts p ON p.id = pa.post_id
             LEFT JOIN user_actions ua ON action_type = 1 AND ua.target_post_id = pa.post_id AND ua.user_id = pa.user_id

             WHERE ua.id IS NULL AND pa.post_action_type_id = 2
    SQL

    # reverse action
    DB.exec <<~SQL
    INSERT INTO user_actions (user_id, action_type, target_topic_id, target_post_id, acting_user_id, created_at, updated_at)
             SELECT p.user_id, 2, p.topic_id, p.id, pa.user_id, pa.created_at, pa.created_at
             FROM post_actions pa
             JOIN posts p ON p.id = pa.post_id
             LEFT JOIN user_actions ua ON action_type = 2 AND ua.target_post_id = pa.post_id AND
                ua.acting_user_id = pa.user_id AND ua.user_id = p.user_id

             WHERE ua.id IS NULL AND pa.post_action_type_id = 2
    SQL
    puts "updating like counts on posts"

    DB.exec <<~SQL
        UPDATE posts SET like_count = coalesce(cnt,0)
                  FROM (
        SELECT post_id, count(*) cnt
        FROM post_actions
        WHERE post_action_type_id = 2 AND deleted_at IS NULL
        GROUP BY post_id
    ) x
    WHERE posts.like_count <> x.cnt AND posts.id = x.post_id

    SQL

    puts "updating like counts on topics"

    DB.exec <<-SQL
      UPDATE topics SET like_count = coalesce(cnt,0)
      FROM (
        SELECT topic_id, sum(like_count) cnt
        FROM posts
        WHERE deleted_at IS NULL
        GROUP BY topic_id
      ) x
      WHERE topics.like_count <> x.cnt AND topics.id = x.topic_id

    SQL
    end
end

I hope this post helps others who might migrate from vB3 to discourse, since many people used the legacy vb3 “thank you” plugin for many years.

Also, I spent a lot of time in the discourse database and inside a lot of discourse ruby scripts, so this migration was tricky (not for the faint of heart to be sure), but certainly doable.

Actually, I enjoyed doing it. This was the first time in ruby for me; so it was fun to start to learn ruby and as a long time mysql person, the postgres part was mostly routine.

We will be going live soon… Thank You Discourse Team!!!

13 Likes

Well done and thanks for sharing!!

7 Likes

Thanks @codinghorror

Just found a small gremlin, which I will work out. The import_likes routine is not transferring likes on the “topic starter” first post (only the replies).

I’ll have to roll up my sleeves and figure this out and post back the updated import_likes routine.

Looks like I was a wee bit premature posting as there is one additional small goblin to slay before I can declare victory.

Update:

Added this code to the PHP script on vB3 to compensate for the difference between how vB3 and discourse deals with the first post in a topic/thread:

    <?php
    #vB3 code to deal with how discourse identifies the first post in a topic in the post_custom_fields table
    $query = 'SELECT firstpostid FROM thread WHERE threadid =' . $action['threadid'] . ' LIMIT 1';
    $threadinfo = $vbulletin->db->query_first($query);
    if ($threadinfo['firstpostid'] == $action['postid']) {
        $action['postid'] = 'thread-' . $action['threadid'];
    }

Now, it works as intended and the “thanks given” in vB3 are migrated to discourse, including the first post in the topic; by adjusting the migration table from vB3 to match the discourse table post_custom_fields.

Doing the final test now, but it looks like it will work based on the preliminary tests.

Also, I updated the code in my original post adding the PHP fragment above.

5 Likes

I read that you’re not migrating passwords because the hashes are different. You might want to take a look at my password migration plugin (here). It will probably already support VB3 hashes and if it does not, I’m open to adding that.

10 Likes

Thanks Michael,

We are not going to migrate passwords and prefer users change their passwords (and change to stronger, longer passwords).

Thanks for the offer to help, anyway! Much appreciated.

We do have one small issue maybe you can help us with.

For some reason, the total number of users likes are not being correctly updated in their profile views, even though all else appears to be fine. I’ve tried to rake the problem away; but my raking is not giving me the results I need.

Do you know a good way to manually get these profile “likes” counters updated?

1 Like

What rake task did you use ? And what is wrong? Are all numbers way off, or just for some users, or…?

Hi.

For starters, I tried the only rake which made since to me (at the time, since the issue is related to user_actions and post_actions); but it had not yielded any fruit (actually, in retrospect, I should have read that rake to see exactly what it did first, LOL):

rake user_actions:rebuild    

So far, frankly, I have not determined what goes wrong in the import_likes migration script actually. If I knew exactly what went wrong and where, I could have fixed it (I often spend hours or days to find a problem which, after finding the problem, the fix takes one minute… the essence of coding LOL). I have restored from a snapshot a number times and re-ran the import_likes routine a few different ways (extra puts statements, etc) and reviewing tables. The results have been inconclusive. This is compounded in my noobieness in ruby and discourse in general; as I am yet to be fluent in the backend. (However, I am getting better at begin... rescue ... end … haha, which has become one of my new debugging tools as a rubynube).

What I have seen, is that the temporary postgres like_data table (on the discourse side) is fine and contains all the data in the correct format and the entries jive with both the mysql migration table and the vB site.

The process appear to break down in creating the user_actions and perhaps even post_actions because my tests have show the data does not get properly transferred to those key tables.

Please note, the core vB3 migration is fine. This issue is related to a legacy vB3 “thank you” plugin which I’m converting to discourse likes. My next step, when I have time later today I hope, is to sit down and try to see exactly where the process is breaking down, when transferring from the like_data table which is fine, to user_actions and post_actions.

My preliminary conclusion, based on yesterday’s testing, is that ruby raking will not help because the breakdown is in the updating of the user_actions or post_actions table process; so I need to determine exactly where the breakdown is and why. It could be as simple as a DB type mismatch, or some other small gremlin in the code.

Our discourse site is not live yet, so it’s not an emergency and like many people here and elsewhere, routing tasks like dealing with immigration or banks has become more than a little troublesome to say the least, and I need to go into the city again today for some non-discourse tasks. would prefer to be exploring discourse and looking for code gremlins.

1 Like

Ran the migration script again (with out any script changes) including the custom vb3 thanks to discourse likes (import_likes) script and it seems to be OK now.

Will trying syncing the big “D” the latest vB DB dump and see what happens!

Very happy with Discourse… Thanks again for this “work-of-art” modern forum. Still testing, but will be going live sooner than later.

5 Likes

5.6k likes given… That’s a lot of love :heart:

3 Likes

After getting all the vb thanks to transfer to discourse likes here is an example of the migrated topic post from 2000, twenty years ago, showing the migrated likes in the topic:

… and I’m starting to look forward to learning to write cool plugins… :slight_smile:

Very happy with Discourse and the team… and especially begin .... rescue ...puts "look at me".... end in ruby… what a life saver!

It’s true… old dogs can learn new tricks if you throw them some bones :slight_smile:

5 Likes

Thank you TEAM Discourse! 20 years ago, we started a forum for unix and linux users on vB2 and around 15 years ago, we ‘upgraded’ to vB3. Today marks a big change for unix and linux users world-wide and for us as the migration to Discourse is now live.

Thank you very much for creating this software and making it available to everyone as an open source project. Your generosity is much appreciated. In my view (and I am sure countless others), Discourse is by far the best forum software on the planet in 2020.

As far as others who are legacy vB3 users who want to migrate to Discourse, this migration is not as “easy” as I have made it look. Unless you are well versed in programming and comfortable with direct integration with your databases on the command-line, I recommend you consider the services of some of the talented and active professionals here at meta.discourse.org.

Thank you again, Team Discourse!

Note: I’ll be adding some misc lessons learned in this post on the new site:

6 Likes