Maxxius Posted January 30, 2021 Posted January 30, 2021 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 Posted January 30, 2021 Author Posted January 30, 2021 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 Posted January 30, 2021 Posted January 30, 2021 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 Posted January 31, 2021 Author Posted January 31, 2021 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 Posted January 31, 2021 Posted January 31, 2021 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 Posted January 31, 2021 Posted January 31, 2021 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. Maxxius 1
CoffeeCake Posted January 31, 2021 Posted January 31, 2021 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 Maxxius 1
bfarber Posted February 1, 2021 Posted February 1, 2021 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. Maxxius and Morrigan 1 1
CoffeeCake Posted February 1, 2021 Posted February 1, 2021 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 Posted February 1, 2021 Author Posted February 1, 2021 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 Posted February 1, 2021 Posted February 1, 2021 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 1
Maxxius Posted April 17, 2021 Author Posted April 17, 2021 (edited) 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 Posted April 18, 2021 Posted April 18, 2021 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 Posted April 18, 2021 Author Posted April 18, 2021 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 Posted April 18, 2021 Posted April 18, 2021 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?
CoffeeCake Posted April 18, 2021 Posted April 18, 2021 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 Posted April 18, 2021 Posted April 18, 2021 Here's a link to dbfiddle that shows the output: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0238af66d4e90f4553c5769a13e0f2b9 Maxxius 1
Recommended Posts