Fix permalinks to point to topic-url and not to first post of topic

Product Support Migration

AI-generated summary

A Discourse forum issue has been identified where permalinks point to the first post of a topic instead of the topic URL itself, e.g., https://forum.example.com/t/example-topic/123/1 instead of https://forum.example.com/t/example-topic/123.

This issue can be checked using the provided SQL query. A quick fix is available using an SQL update statement that sets topic_id and clears post_id for permalinks pointing to the first post in a topic.

rrit discovered that this issue occurred after a customized migration. cocococosti inquired about the method used to create permalinks, pointing out that the official import script checks the post number and omits it if it’s 1. rrit revealed that the issue was caused by missing the AND post_number <> 1 condition in their SQL-based approach, similar to the Lithium import script.

1

After a customized migration many permalinks were pointing to the first post in a topic instead of the topic-url itself.

e.g. permalinks pointing to
https://forum.example.com/t/example-topic/123/1

instead of
https://forum.example.com/t/example-topic/123

Check

A Discourse forum is affected if there are records for this SQL query:

SELECT permalinks.*,
       posts.id,
       posts.topic_id,
       posts.post_number
FROM permalinks
         LEFT JOIN posts
                   ON posts.id = permalinks.post_id
WHERE permalinks.post_id IS NOT NULL
  AND posts.post_number = 1
LIMIT 20;

Quick Fix

This changes permalinks to directly point to the topic, if they were originally pointing to the first post in topic.

UPDATE permalinks
SET topic_id = posts.topic_id,
    post_id  = NULL
FROM posts
WHERE permalinks.post_id = posts.id
  AND permalinks.post_id IS NOT NULL
  AND posts.post_number = 1;

This might be a good cleanup routine to run at the end of migration scripts.

2

How did you create the permalinks?

For example, here discourse/script/import_scripts/vanilla_mysql.rb at main · discourse/discourse · GitHub they specifically check the post number, if it’s 1, then the post_is not passed (left as NULL) and thus the permalink shouldn’t point to the first post.

3

I did a SQL based approach on permalinks based on some code similar to

… but with AND post_number <> 1 missing – so that was the culprit.