Discourse to Flarum migration support

BBunny · Oct 14, 2018

tpokorra, Hi, I've installed flarum on digitalocean and but I'm newbie to this and need a step by step tutorial on how to migrate through flarum using your script.

tpokorra · Oct 15, 2018

@Bunny, I don't know if the script will be doing what you need. It is not perfect yet.

I don't know how a step by step tutorial would help you. Do you know how to install a Postgresql Server on your container, and how to import the Discourse Database? which Operating System are you using on your container?

BBunny · Oct 16, 2018

tpokorra The conversion was finished but I don't think it was converted properly.
I have received a lot of errors. Here are the last lines from the conversion:

Error: Incorrect datetime value: '' for column 'last_seen_time' at row 1
Wrong SQL: INSERT INTO users ( id, username, email, password, join_time, last_seen_time ) VALUES ('19288', 'abc', 'abc@gmail.com', '20747d06936ce3c7beb472804693425a2e7147b544ae72fc16493b80da28271e', '2018-08-29 06:54:54.109749', '' );

2512 out of 18624 total $new_table_name items converted.

UPDATE users u SET discussions_count=(SELECT count() FROM discussions d WHERE d.is_private=0 AND d.start_user_id=u.id)
UPDATE users u SET comments_count=(SELECT count() FROM posts p WHERE p.is_private=0 AND p.user_id=u.id)
DELETE FROM users WHERE password='NULL'
Exporting: SELECT id, parent_id FROM tags ORDER BY id DESC

tpokorra · Oct 16, 2018

@Bunny, thank you for reporting this issue! I have created a bug report (TBits/discourse_to_flarum5), and I think I have fixed it. Please try again.

BBunny · Oct 17, 2018

tpokorra Thank you for the quick fix.

Update: I'm getting the following erros.

Error: Duplicate entry '889' for key 'PRIMARY'
Wrong SQL: INSERT INTO posts ( id, user_id, discussion_id, content, time, edit_time ) VALUES ('677', '1', '576', '<r><p>Thanks for joining Forum, and welcome!</p>\n\n<p>Here are a few quick tips to get you started:</p>\n\n<H2><s>## </s>Reading</H2>\n\n<p>To read more, <STRONG><s></s>just keep scrolling down!<e></e></STRONG></p>\n\n<p>As new replies or new topics arrive, they will appear automatically – no need to refresh the page.</p>\n\n<H2><s>## </s>Navigation</H2>\n\n<LIST><LI><s>- </s><p>For search, your user page, or the <kbd>☰</kbd> menu, use the <STRONG><s></s>icon buttons at upper right<e></e></STRONG>.</p></LI>\n\n<LI><s>- <

Error: Duplicate entry '19297' for key 'PRIMARY'
Wrong SQL: INSERT INTO users ( id, username, email, password, join_time, last_seen_time ) VALUES ('19298', 'Radeho', '--snip--', '--snip--', '2018-08-31 08:53:23.440785', '2018-08-31 10:25:53.585425' );

Wrong SQL: INSERT INTO tags ( id, name, color, position, parent_id, slug, description ) VALUES ('1', 'Uncategorized', '#AB9364', '14', '', 'uncategorized', '' );

tpokorra · Oct 17, 2018

@Bunny You will have to restore the initial backup of your empty flarum database. The upgrade script always excepts a clean database, and will just insert the data from Discourse.

This is how I run that:

zcat ../flarum.sql.gz | mysql -u flarum flarum -p

BBunny · Oct 17, 2018

tpokorra Hi,

After running this - zcat ../flarum.sql.gz | mysql -u flarum flarum -p command.
php discourse_to_flarum.php
I got the following output.

Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('954', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('121', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('2290', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('2187', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Importing new discussions_tags item... (18550)
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('4996', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('1309', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('2196', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1
Wrong SQL: INSERT INTO discussions_tags ( discussion_id, tag_id ) VALUES ('2088', '' );
Error: Incorrect integer value: '' for column 'tag_id' at row 1

2733 out of 18553 total discussions_tags items converted.

UPDATE discussions d SET is_private=1 WHERE EXISTS (SELECT FROM discussions_tags dt, tags t where dt.discussion_id = d.id and dt.tag_id = t.id and t.name='Staff')
UPDATE posts p SET is_private=1 WHERE EXISTS (SELECT FROM discussions d where d.id = p.discussion_id and d.is_private=1)
UPDATE posts p SET is_private=1 WHERE p.user_id<=0
UPDATE discussions d SET is_private=1 WHERE NOT EXISTS (SELECT from posts p WHERE p.discussion_id = d.id and p.is_private=0)
UPDATE tags t SET t.discussions_count = (SELECT COUNT() FROM discussions_tags dt, discussions d WHERE dt.discussion_id = d.id AND dt.tag_id = t.id AND d.is_private=0)
UPDATE tags t SET is_hidden=1, is_restricted=1 WHERE t.discussions_count = 0
DELETE FROM users WHERE id=1
Exporting: SELECT u.id, username, email, password_hash, u.created_at, last_posted_at FROM users u, user_emails um WHERE user_id=u.id AND "primary"=true AND u.id > 0 ORDER BY u.id

Importing new users item... (3724)
Importing new users item... (7448)
Importing new users item... (11172)
Importing new users item... (14896)
Importing new users item... (18620)

18624 out of 18624 total users items converted.

UPDATE users u SET discussions_count=(SELECT count(*) FROM discussions d WHERE d.is_private=0 AND d.start_user_id=u.id)

tpokorra · Oct 20, 2018

@Bunny I have filed a bug now and fixed it: TBits/discourse_to_flarum6
Please try again!

I am a bit surprised that your category_id in the topics table in Discourse is empty. Please check if the result works ok with tags, or don't you use categories in your Discourse Forum at all?

By the way, which version of Discourse are you upgrading from?

BBunny · Oct 20, 2018

I can see categories imported but not posts.

Error: Out of range value for column 'user_id' at row 1
Wrong SQL: INSERT INTO posts ( id, user_id, discussion_id, content, time, edit_time ) VALUES ('14303', '-1', '11536', '<r><p>The backup was successful.</p>\n\n<p>Visit the <URL url=\"example.com\"><s>[</s>admin > backup section<e>]

Importing new discussions_tags item... (3710)
Importing new discussions_tags item... (7420)
Importing new discussions_tags item... (11130)
Importing new discussions_tags item... (14840)
Importing new discussions_tags item... (18550)

18553 out of 18553 total discussions_tags items converted.

UPDATE discussions d SET is_private=1 WHERE EXISTS (SELECT FROM discussions_tags dt, tags t where dt.discussion_id = d.id and dt.tag_id = t.id and t.name='Staff')
UPDATE posts p SET is_private=1 WHERE EXISTS (SELECT FROM discussions d where d.id = p.discussion_id and d.is_private=1)
UPDATE posts p SET is_private=1 WHERE p.user_id<=0
UPDATE discussions d SET is_private=1 WHERE NOT EXISTS (SELECT from posts p WHERE p.discussion_id = d.id and p.is_private=0)
UPDATE tags t SET t.discussions_count = (SELECT COUNT() FROM discussions_tags dt, discussions d WHERE dt.discussion_id = d.id AND dt.tag_id = t.id AND d.is_private=0)
UPDATE tags t SET is_hidden=1, is_restricted=1 WHERE t.discussions_count = 0
DELETE FROM users WHERE id=1
Exporting: SELECT u.id, username, email, password_hash, u.created_at, last_posted_at FROM users u, user_emails um WHERE user_id=u.id AND "primary"=true AND u.id > 0 ORDER BY u.id

Importing new users item... (3724)
Importing new users item... (7448)
Importing new users item... (11172)
Importing new users item... (14896)
Importing new users item... (18620)

18624 out of 18624 total users items converted.

UPDATE users u SET discussions_count=(SELECT count() FROM discussions d WHERE d.is_private=0 AND d.start_user_id=u.id)
UPDATE users u SET comments_count=(SELECT count() FROM posts p WHERE p.is_private=0 AND p.user_id=u.id)

tpokorra · Oct 26, 2018

@Bunny the user with id -1 is the system account in Discourse. The posts are probably about backup messages, or auto created description posts for categories etc. So for my forum migration, I try to ignore them.
The migration script actually deletes all posts by user -1, but that somehow happens after they have been imported to MySQL. It seems it also fails on my demo database, but differently than your situation: my user_id -1 becomes 0, which also is not intended. In your situation, negative values are not permitted at all.

You need to modify your migrate.yaml like this:

in the section for the posts, add the line with the join:

- action: COPY
  enabled: true
  old-table: posts
  new-table: fl_posts
  # ignore posts by discourse system account, user_id is -1
  join: ' WHERE user_id >= 0'
  columns:
    id: id
    user_id: user_id
    topic_id: discussion_id
    raw*: content
    created_at: time
    updated_at: edit_time

BBunny · Oct 29, 2018

tpokorra

New update:

Wrong SQL: INSERT INTO discussions ( id, title, comments_count, participants_count, start_time, start_user_id, last_time, last_user_id, slug, is_private ) VALUES ('6802', '\"Looking for genuine BPO Non voice projects? Here is the great opportunity for the startups\" was flagged as spam', '2', '1', '2016-09-07 02:24:39.968105', '-1', '2016-09-07 02:24:40.618333', '-1', 'looking-for-genuine-bpo-non-voice-projects-here-is-the-great-opportunity-for-the-startups-was-flagged-as-spam', '1' );

Importing new discussions_tags item... (3710)
Importing new discussions_tags item... (7420)
Importing new discussions_tags item... (11130)
Importing new discussions_tags item... (14840)
Importing new discussions_tags item... (18550)

18553 out of 18553 total discussions_tags items converted.

UPDATE discussions d SET is_private=1 WHERE EXISTS (SELECT * FROM discussions_tags dt, tags t where dt.discussion_id = d.id and dt.tag_id = t.id and t.name='Staff')
UPDATE posts p SET is_private=1 WHERE EXISTS (SELECT * FROM discussions d where d.id = p.discussion_id and d.is_private=1)
UPDATE posts p SET is_private=1 WHERE p.user_id<=0
UPDATE discussions d SET is_private=1 WHERE NOT EXISTS (SELECT * from posts p WHERE p.discussion_id = d.id and p.is_private=0)
UPDATE tags t SET t.discussions_count = (SELECT COUNT(*) FROM discussions_tags dt, discussions d WHERE dt.discussion_id = d.id AND dt.tag_id = t.id AND d.is_private=0)
UPDATE tags t SET is_hidden=1, is_restricted=1 WHERE t.discussions_count = 0
DELETE FROM users WHERE id=1
Exporting: SELECT u.id, username, email, password_hash, u.created_at, last_posted_at FROM users u, user_emails um WHERE user_id=u.id AND "primary"=true AND u.id > 0 ORDER BY u.id

Importing new users item... (3724)
Importing new users item... (7448)
Importing new users item... (11172)
Importing new users item... (14896)
Importing new users item... (18620)

18624 out of 18624 total users items converted.

UPDATE users u SET discussions_count=(SELECT count() FROM discussions d WHERE d.is_private=0 AND d.start_user_id=u.id)
UPDATE users u SET comments_count=(SELECT count() FROM posts p WHERE p.is_private=0 AND p.user_id=u.id)

BBunny · Oct 29, 2018

Also, one more thing I can't see any of the imported items on the new forum. The new flarum forum shows It looks like there are no discussions here.

tpokorra · Oct 31, 2018

@Bunny I guess we first have to solve the import errors, before you can see discussions.

There should be an error description printed after the SQL. Can you post that error message please?
It should print: Wrong SQL: the sql query, newline: Error: that is what I need.

BBunny · Nov 1, 2018

tpokorra Is it possible if I give access to ssh so you can login and check what's the error?
If yes, how do I send you the ssh login creds?

Thanks.

tpokorra · Nov 2, 2018

@Bunny I saw you mentioned in another thread that you are prepared to pay for an upgrade service. I guess, for me, free support ends at the stage where I get access to the server
Please send me an email to timotheus@pokorra.de. We should talk privately how we work out the login details and the payment. Basically, you send me your IP address of the server, and I send you my public SSH key, which you insert into the file /root/.ssh/authorized_keys

BBunny · Nov 2, 2018

tpokorra Fair deal. Sent you an email. Pls check. Thanks

JenniferAbrams · Nov 11, 2019

This will be an important thing to keep updated, this software is basically Discourse but improved, so it would be cool to migrate once it reaches stable.

tpokorra · Nov 12, 2019

I am open for pull requests or bug reports at https://github.com/SolidCharity/discourse_to_flarum
Since my forums have already been moved to Flarum, I am not actively working on the migration tool anymore (scratch your own itch, etc...)
I am happy to do contract work for interested people on the migration tool.

Likable · Oct 7, 2020

I’m using Discourse script but l wanna move my website to flarum is it possible?
link: https://ders.co please check. Before l Was install the Flarum but now l’m using Discourse. Help me please

tpokorra · Oct 7, 2020

Likable

Yes a migration from Discourse to Flarum is possible. Some things might still missing, you need to decide for your own forum if it is enough. see https://github.com/SolidCharity/discourse_to_flarum#features

Please follow the instructions: https://github.com/SolidCharity/discourse_to_flarum#usage-instructions
Let us know if you run into any issues, and post them here.