Discourse to Flarum migration support

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.

Likable · Oct 7, 2020

tpokorra thank you so much bro let’s check it

ruscher · Jun 9, 2022

Does your solution work for this 2022 model?

tpokorra · Jun 9, 2022

ruscher I don't know. Can you try it? Should I try it?

tpokorra · Jun 9, 2022

khtmhai5 What kind of error do you get?

I am happy to help in this forum for free.

For support via SSH, I would have to charge you. Please contact me at https://solidevereine.de/contact/