# Completed Migration vB3 to Discourse including Legacy vB3 Thank You to Likes
**URL:** https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674
**Category:** Migration
**Tags:** vbulletin
**Created:** [18 maart 2020 om 05:38 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674 "2020-03-18T05:38:06Z")
**Posts on this page:** 12
**Page:** 1
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [18 maart 2020 om 05:38 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/1 "2020-03-18T05:38:06Z")
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 al** l, 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):
```plaintext
ubuntu:/var/www/includes/cron# cat thanks_to_discourse.php
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:
```plaintext
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!!!**
---
### Author:  [@codinghorror](https://meta.discourse.org/u/codinghorror)
#### Post date: [18 maart 2020 om 05:39 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/2 "2020-03-18T05:39:53Z")
Well done and thanks for sharing!!
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [18 maart 2020 om 12:22 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/3 "2020-03-18T12:22:31Z")
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:
```plaintext
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.
---
### Author:  [@michaeld](https://meta.discourse.org/u/michaeld)
#### Post date: [18 maart 2020 om 16:44 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/4 "2020-03-18T16:44:29Z")
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](https://meta.discourse.org/t/migrated-password-hashes-support/19512)). It will probably already support VB3 hashes and if it does not, I’m open to adding that.
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [19 maart 2020 om 00:09 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/5 "2020-03-19T00:09:45Z")
> [@michaeld](#):
>
> 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](https://meta.discourse.org/t/migrated-password-hashes-support/19512)). It will probably already support VB3 hashes and if it does not, I’m open to adding that
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?
---
### Author:  [@michaeld](https://meta.discourse.org/u/michaeld)
#### Post date: [19 maart 2020 om 20:55 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/6 "2020-03-19T20:55:29Z")
What rake task did you use ? And what is wrong? Are all numbers way off, or just for some users, or…?
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [20 maart 2020 om 02:43 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/7 "2020-03-20T02:43:24Z")
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):
```plaintext
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`.
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [20 maart 2020 om 05:31 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/8 "2020-03-20T05:31:27Z")
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.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [20 maart 2020 om 07:07 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/9 "2020-03-20T07:07:37Z")
5.6k likes given… That’s a lot of love ❤
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [20 maart 2020 om 11:44 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/10 "2020-03-20T11:44:06Z")
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… 🙂
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 🙂
---
### Author:  [@neounix](https://meta.discourse.org/u/neounix)
#### Post date: [21 maart 2020 om 07:02 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/11 "2020-03-21T07:02:52Z")
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.
> **[Unix Linux Community](https://community.unix.com)**
>
> Free Unix and Linux technical support for all Unix and Linux operating systems
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](http://meta.discourse.org).
Thank you again, Team Discourse!
Note: I’ll be adding some misc lessons learned in this post on the new site:
> **[Notes on vB3 to Discourse Migration from the Real World](https://community.unix.com/t/notes-on-vb3-to-discourse-migration-from-the-real-world/377631)**
>
> From my recent experience with migrating a legacy vB3 forum to Discourse, here are some tips: Do It In Docker Configuring Discourse in Docker is easy. However, what is really good is that if you have working Discourse site / application, you...
---
### Author:  [@system](https://meta.discourse.org/u/system)
#### Post date: [17 augustus 2020 om 15:19 UTC](https://meta.discourse.org/t/completed-migration-vb3-to-discourse-including-legacy-vb3-thank-you-to-likes/144674/12 "2020-08-17T15:19:22Z")
This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.