The user pfaffman is experiencing trouble with the discourse remap command, specifically when remapping topic_links url. The error occurs due to the url column in the topic_links table exceeding the maximum allowed length of 500 characters.
pfaffman and Falco investigate the issue, and Falco suggests increasing the column length to character varying(2000) or text, but notes that this would require a migration.
Falco also suggests that the remap command should skip the problematic rows instead of quitting entirely. pfaffman confirms that the command quits instead of skipping.
pfaffman proposes truncating the URL in line 120 of topic_link.rb to fix the issue, but encounters another error related to duplicate key values. Falco recommends updating the affected rows with a SQL query to truncate the URLs.
pfaffman attempts to implement a solution by adding a next statement to self.extract_from(post) in topic_link.rb, but encounters the same duplicate key error.
I’m trying to
discourse remap community.x.com staging.community.x.com
as described here (edit: Hmm. Maybe I should have made this a post over there?) and am getting this error:
Remapping topic_links url
Error: ERROR: value too long for type character varying(500)
The remap has only been partially applied due to the error above. Please re-run the script again.
Can you share the value for the column url in the table topic_links where the length is that big? Is even a valid url?
psql
\x
SELECT * FROM topic_links WHERE length(url) > 450;
(Thanks for the SELECT!)
Looks like it’s a long http://site/new-topic?body=a%20bunch%20of%20stuff that’s to blame. Looks like it go truncated to 500 chars and so when we try to replace HOSTNAME with staging.HOSTNAME we’re out of luck.
P.S. If someone else wants to do that select, first do this:
./launcher enter app
su discourse
We could move that column to character varying(2000) (or even text and keep the validation on Ruby) but I’m not sure if it’s worth the hassle of a migration.
Since the remap is smart enough to just skip this line, I guess all is ok in the end?
Unless topic_links is the last table it’s handling, it looks like it’s quitting rather than just skipping.
Hey @Falco, would truncating the URL in line 120 fix this?
change it to
uri = UrlHelper.relaxed_parse(u.url[0..self.max_url_length])
EDIT: well, on my first attempt, this yielded
Remapping topic_links url
Error: ERROR: duplicate key value violates unique constraint "unique_post_links"
DETAIL: Key (topic_id, post_id, url)=(19484, 61049, https://staging.community.rstudio.com/t/birds-of-a-feather-bof-at-rstudio-conf-2019l/19328) already exists.
The remap has only been partially applied due to the error above. Please re-run the script again.
So short of a database migration to make the URL longer. . . maybe add another rescue on duplicate URLs and let them pass? So also add a
rescue PG::UniqueViolation
that does nothing?
Since this is the very first time someone hits this, I would go with a simple approach of fixing the few affected rows.
Something like:
UPDATE topic_links
SET url = substring(url FOR 450)
WHERE length(url) > 450;
I’ll give that a shot. My solution was to add a next if parsed.length > 500 to self.extract_from(post) in topic_link.rb`. It seems like that would fix it for everyone. I think that it worked on the site where I am doing the remapping, but then I’m getting
Error: ERROR: duplicate key value violates unique constraint "unique_post_links"
DETAIL: Key (topic_id, post_id, url)=(19484, 61049, https://staging.community.rstudio.com/t/birds-of-a-feather-bof-at-rstudio-conf-2019l/19328) already exists.
The remap has only been partially applied due to the error above. Please re-run the script again.
More on this over at Migrate_to_s3 for Digital Ocean Spaces woes - #12 by pfaffman. I hit the same topic_link problem doing the S3 migrate on another site.