Changing URL via Database edit

@christine: Thanks for the link. Can this be used on ANY database, or do you have to KNOW which tables contain data in a Serial Array?

@marventus: Thanks for the response. I am a little confused. You mentioned:

. . . there should be no need for plugins and themes to store that information again inside a serialized array, since the info can be accessed as a regular (non-serialized) string via default WP options.

Then what’s the purpose of running this script that Christine has offered ( https://interconnectit.com/products/search-and-replace-for-wordpress-databases/ ) as an option to deal with that data and a change in site URL?

Maybe I missed the point. Could you help me here?

This still doesn’t really leave me with a clear answer on this:

#1
If 1.4 Changing the URL directly doesn’t address the ‘Serialized array problem’ then isn’t it a BAD thing to suggest “1.4 Changing the URL directly” in the database as an option? Or DOES it address Serialization?

#2
Does the 1.2 Edit functions.php method update all instances of the ‘myOLDurl.com’ and replace them with the ”myNEWurl.com’ URL references (serialized or not) within the database?

Thanks

@marventus (et al):

I am reposting the link that esmi submitted earlier regarding the serialized arrays:
https://wpgarage.com/tips/data-portability-and-data-serialization-in-wordpress/

Hello, again. I’m sorry my first reply was not clear or helpful. Let me answer the questions you just mentioned, and clarify what I meant in the process:

#1 Changing the URL directly:
As mentioned earlier, you should try to avoid direct DB manipulation if you don’t know exactly what you are doing. The recommended steps in the link you shared would create an SQL query similar to (but not as broad as) the one I shared.
Regarding serialized values, no, such a method will not work, but then again (and as I was saying earlier), if the plugins and themes you are using follow WP coding practices and standards, there shouldn’t be any instances of the home or site urls as serialized array values in the Db in the first place, since that info can be accessed through several WP functions, such as get_bloginfo(), bloginfo(), etc., so it would be pointless to store it inside a serialized array.
Bottomline, serialized strings should not be an issue here.

#2. Edit functions.php:
That method would render the same results as if you changed the home and site url values in your WP General settings section, or if you hardcoded those values inside the wp-config.php file located in your WP root folder. The latter method would render the corresponding settings in the WP admin panel uneditable (since they are linked anyway), but it should do the trick. Neither method will, however, automatically replace all values of the oldURL in your database, but your new site should work normally after following any of them.

Does that make more sense now?

Yes it does. Thank You. I think the key here is:

if the plugins and themes you are using follow WP coding practices and standards

I am not sure how one would know that.
Could you help me with that?
How would I know?

Obviously some people have run into this:
https://wpgarage.com/tips/data-portability-and-data-serialization-in-wordpress/

. . . enough so that Christine posted a script to solve the problem HERE

I am not sure how one would know that. Could you help me with that? How would I know?

Basically, if the theme or plugin is listed in WP’s plugin and theme directories it’s because they should, in principle, abide by standards and guidelines. WP staff puts a lot of effort into reviewing every single plugin and theme that gets uploaded into their repository. If a theme or plugin you are using is not listed, it could be a hint that it is not entirely up to standards or that there’s a disagreement between devs and WP staff regarding their interpretation.
Of course, I am not saying that every plugin or theme out there not listed in the directories is not up to standards, since, for instance, premium software could be perfectly valid and not listed due to their premium nature, or devs may prefer to host them directly on their servers for tracking or performance purposes.

As to serialized or json-encoded data, as I said before, you should not have to modify those manually since they shouldn’t contain any vital information that would otherwise be available in the WP environment, specially site and home url values. If a certain plugin does store such information, you have the choice as an user not to use it, or to use Christine’s script to take care of necessary S&R replacements. That’s totally your choice.

Thanks Marventus. That certainly helps clear a few things up.

This does bring me full circle back to my original post.

What I wanted to know is if following this procedure is incorrect for some reason:

1. Export the database using phpMyAdmin
2. Open the *.sql file in notepad++
3. Find and replace ‘myOLDurl.com’ with ‘myNEWurl.com’ (without single quotes)
4. Save and close file.
5. Import changed file back to myNEWurl.com’s database

Question 1:
What am I risking by doing it this way?

I know you said that:

Since URLs have special characters that will get escaped upon saving settings to the database, . . . .

I very well could be wrong, but I didn’t think that there were any characters that get escaped in a url ( assuming you don’t add the ==> https:// )
I understood that some areas the https:// gets recorded, and others not. Some areas have the trailing forward slash, and others not.

If I just use the URL ONLY this solves all those problems.

I realize that you have given me the SQL commands in this post here but my way seemed so much simpler – which is why I started this thread. It seems to cover everything.

Site and home URLs do get saved with the https:// bit. However, I just did a Db dump and searched for the site’s url in a code editor and , contrary to what I thought, I didn’t find any escaped characters, which means searching for the oldURL and replacing it with the new one using Notepad++ should do the trick as well.
Since forward slashes are special characters, I assumed they would get escaped before being saved into the Db.
The only advantage I see in using an SQL query over brute S&R is that it gives you more control over what tables you are searching in, so if something breaks, you can detect where you went wrong more easily.
Sorry for the confusion!

. . . well this is interesting: Just for the fun of it I used the SQL commands suggested in this post here to make the changes. All total it affected 35 Rows ===> Rows don’t seem to be the issue here and I don’t think are very relevant.

I then exported the database and opened it in Notepad++ and did a search for the ‘newURL.com’ (without the single quotes).

That method found 52 of them.

I then searched for the oldURL.com and it found 6 more that the SQL commands didn’t change.

Probably because they were not in a table that I told it to go to – I am not sure which table it was but it looks like a couple of them have something to do with the “Dashboard”. Nothing to serious.

My original method of just opening the original exported database file changed a total of 58 occurrences.

I don’t know if this little test is very scientific or not, but my method sure is super simple, hard to screw up, and, though I realize we have already discussed that it is not really that important, at first blush seems to catch every instance there is of the ‘oldURL.com’.

Hopefully I’m not offending any SQL purists out there, but isn’t easier better if it is doing the same job?

Maybe someone could try it with a more complex DB and tell me how it all works.

@marventus: Sorry – you posted while I was typing and testing.

Sounds like we’ve come to the same conclusion basically.

Thanks everyone for weighing in here.

What I have got out of this is that as long as the plugin comes from the “WP plugin and theme directories”, we shouldn’t have to worry much about Data being in Serialized Arrays.

Well, if you used the SQL query I shared above, it makes sense it didn’t replace all instances of the oldURL since it is purposefully targeting certain tables and rows in the Db, whereas with your approach, you are replacing all of them.
As I said before, you can use your method just fine, but be careful with it since raw replacements can have disastrous effects if not done correctly.
I hope that helps.

Edit: Just saw your update. Yes, our findings seem to very similar and yeah, as long as themes and plugins are listed in the directory, you should be fine.