How can I run rebuild posts on 4.4?

[Maxxius]

Hello, I have learned today that all the member mentions with @ symbol that were done in pre-4.4 days are suffering from one problem - those mentions feature the info of my test domain. For example when I click edit and turn on HTML code I see

<p>@<a contenteditable="false" data-ipshover="" href="<___base_url___>/profile/237-scalman/" data-ipshover-target="http://wrongDomain.com/profile/237-membername/?do=hovercard" data-mentionid="237" rel="">@membername</a> 

How do I run the post rebuild thing again to make it change to the real domain?

[Maxxius]

more details:

I mean I did the upgrade to 4.4 while I was in my test-install and after all the upgrading done to 4.4 I moved it to out of the test installation onto main domain and it now doesn't load the mentions because test install url got written into the database.

[Morrigan]

There isn't a way to rebuild this as these are in the posts. I think someone can assist with a replace SQL though. @bfarber???

[Maxxius]

But back in the old days there were ways to rebuild posts among other things. As BBcode starts working after the rebuild wouldn't mentions be corrected too?

[Nathan Explosion]

Maybe/maybe not.

If you want to trigger a rebuild, turn off(or on, depending on current state) lazy load and a rebuild will take place.

Will it work for your issue, no idea.

 

[Ryan Ashbrook]

11 hours ago, Maxxius said:

But back in the old days there were ways to rebuild posts among other things. As BBcode starts working after the rebuild wouldn't mentions be corrected too?

This was actually removed late in 3.x's life because it caused more problems then it actually solved. It was only ever meant to be run once after upgrading to 3.x from 2.x. In fact, this was the catalyst behind the entire background queue system, so it would just happen automatically.

This is the same for 4.x - the post rebuild is only ever meant to run once after you upgrade, so it cannot be run again, unfortunately.

10 hours ago, Nathan Explosion said:

Maybe/maybe not.

If you want to trigger a rebuild, turn off(or on, depending on current state) lazy load and a rebuild will take place.

Will it work for your issue, no idea.

 

This will only rebuild Lazy Load data in posts - not the full content.

[CoffeeCake]

You could do it with an UPDATE statement and the REPLACE() function in SQL, but back everything up first and test in your test environment.

REPLACE() documentation:

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_replace

If you need more granularity, look at REGEXP_REPLACE():

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#function_regexp-replace

[bfarber]

I would second running a one-time REPLACE SQL statement for this specific purpose.

Back up the database, then run something like

UPDATE forums_posts SET post=REPLACE(post,'http://wrongDomain.com/','http://correctDomain.com/');

Note that you may wish to check other similar tables (blog entries, status updates, personal conversations, etc.) but the method is the same, just the table name and column will vary.

[CoffeeCake]

Slight warning that @bfarber's suggestion will replace every occurrence of http://wrongDomain.com in every post in every thread and every forum. Any time it's mentioned in the body of a post, or in any part of a link. If you or anyone else have made posts intentionally referencing wrongDomain.com, then this would change those too. Maybe that's not what you want. Only you know.

You can add conditions with a WHERE clause to exclude certain posts where the domain should not be changed, etc. If you can, test this on a copy of your community first and make sure things are as you expected afterwards.

[Maxxius]

That would be good actually thanks @bfarber Will have to run some tests to see if it replaces every instance because this domain should be never mentioned anywhere in posts. Thanks for all the help. @Paul E.

One final question. If phpmyadmin method fails can I simply export forum_posts table, open it in notepad++ run a mass search & replace and import it back in ? logically this sorts practice sounds good but might there be any hidden dangers of this?

[CoffeeCake]

6 minutes ago, Maxxius said:

If phpmyadmin method fails can I simply export forum_posts table, open it in notepad++ run a mass search & replace and import it back in ? logically this sorts practice sounds good but might there be any hidden dangers of this?

Don't do this via phpMyAdmin. Connect to your MySQL server via SSH (the mysql command) and perform the operation there. Do not export and reimport the data to Notepad++. Back up everything twice before you start. That way, when you forget or ignore the suggestion to back things up the first time, you have your second warning to fall back on. 😁

You can see all impacted posts and preview the change by running a SELECT statement with a conditional that matches your replace. For example:

SELECT `post`, REPLACE(`post`,'http://wrongDomain.com/','http://correctDomain.com/') AS `expectedresult` FROM `forums_posts` WHERE `post` LIKE '%http://wrongDomain.com/%';

 

[Maxxius]

I finally got around to doing this task and I noticed that replacing the URL via

UPDATE forums_posts SET post = REPLACE(post, 'olddomain.com', 'newdomain.com');

will not entirely solve my problem since I also have updated the URL structure of members:

The original structure

https://www.website.com/profile/22355-username/

My modified structure

https://www.website.com/p22355/username/

Therefore I should run a more complex command which would also shorten the word profile to letter p, when remove slash and replace - symbol into / after the number. I can't even begin to think how to google that. Would there be an expert who could provide a command which would replace:

https://www.website.com/profile/22355-username/ into https://www.website.com/p22355/username/

@CoffeeCake @bfarber

Edited April 17, 2021 by Maxxius

[CoffeeCake]

15 hours ago, Maxxius said:

I finally got around to doing this task and I noticed that replacing the URL via

UPDATE forums_posts SET post = REPLACE(post, 'olddomain.com', 'newdomain.com');

will not entirely solve my problem since I also have updated the URL structure of members:

The original structure

https://www.website.com/profile/22355-username/

My modified structure

https://www.website.com/p22355/username/

Therefore I should run a more complex command which would also shorten the word profile to letter p, when remove slash and replace - symbol into / after the number. I can't even begin to think how to google that. Would there be an expert who could provide a command which would replace:

https://www.website.com/profile/22355-username/ into https://www.website.com/p22355/username/

@CoffeeCake @bfarber

You could use REGEX_REPLACE():

SELECT REGEXP_REPLACE('This is some text with a url in it like https://www.example.com/profile/34251-username/ and https://www.exAMPLE.com/profile/95823-another-guy and such.','www\.example\.com\/profile\/([0-9]+)-','www.example.com/p$1/',1,0,'i');

 

[Maxxius]

Thanks for the efforts @CoffeeCake but all I'm getting is ERROR 1582 (42000): Incorrect parameter count in the call to native function 'REGEXP_REPLACE'

Hmm What if I replace my domain first via UPDATE forums_posts function and only then use a simpler regex query to sort out the profile links?

Perhaps a less complicated query can be made easier which turns

profile/22355-username/

into p22355/username/

?

[CoffeeCake]

6 hours ago, Maxxius said:

Thanks for the efforts @CoffeeCake but all I'm getting is ERROR 1582 (42000): Incorrect parameter count in the call to native function 'REGEXP_REPLACE'

What version of MySQL are you running?

[Maxxius]

Its  5.5.62 @CoffeeCake

[CoffeeCake]

29 minutes ago, Maxxius said:

Its  5.5.62 @CoffeeCake

Ahhh... The sample I provided is for 8.x+. It may be worth upgrading your MySQL server version. You'd otherwise need to create a user-defined function to create a similar tool to do it.

[CoffeeCake]

Here's a link to dbfiddle that shows the output:

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0238af66d4e90f4553c5769a13e0f2b9