Once you dump the sql file you need to make some modification to a urls due to domain name change. Now you have few options to choice. If you do it with text editor on windows machine you might get encoding and sterilize data issues. There fore you really should to import the file and make changes through SQL query instead but only where dentata is not stored as sterilized object. There fore it is better to use tools to handle this situation in a proper way.

Now that you know about the risk and possible solutions you can read more in depth about the issue when making changes manually or go to the bottom of the post and see two available solutions that will save you time and headache.

First lets show an example how not to change your database (example bellow will cause issue with serialized objects).

Alerting site url in wordpress database with SQL query (do not use it in production)

SET @OLDURL = "site.com";
SET @NEWURL = "site.dv";

SELECT * FROM wp_options WHERE option_value LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_posts WHERE guid LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_posts WHERE post_content LIKE CONCAT('%',@OLDURL,'%');
SELECT * FROM wp_postmeta WHERE meta_value LIKE CONCAT('%',@OLDURL,'%');


UPDATE wp_options SET option_value = replace(option_value, @OLDURL, @NEWURL);
UPDATE wp_posts SET guid = replace(guid, @OLDURL, @NEWURL);
UPDATE wp_posts SET post_content = replace(post_content, @OLDURL, @NEWURL);
UPDATE wp_postmeta SET meta_value = replace(meta_value, @OLDURL, @NEWURL);

Once the swap is completed you might want to navigate to theme customiser and select logo image as it might not display as expected due to modification of serialized data in database.
Reselect images for footer, header / widgets will overwrite database serialized data with valid values.

You could also do this using sed command on unix system, but this could also make some changes to encoding of your file. Just so you know it can be done very easy:

cd toSQLdir
sed -i 's/site.com/site.dv/g' *.sql

Notice that you might run in to problems when importing sql which comes from mysqldump command.

It all happens because during the mysqldump some chars are added to serialized data {s:\" vs {s:" do not reflect the original string length. Values that comes from php_serialize() are binary there fore it should not be stored in a MySQL database as plain text in VARCHAR, CHAR or TEXT field. It should be stored in binary format such as BLOB or VARBINARY.

The above might not be an issue for you if after import the additional chars will not be present in database. However what might cause the issue is the change of the original url length. So if you move your site to another domain with different length or you change http to https in serizized data make sure to also update the string length. Example. If the site url length was equal to (s:15) and you and a letter “s” to http which become https then also have to change length of the string so it becomes (s:16) (and so on).

Corrupted serialized php objects

Storing php sterilized object in database is not forbidden but it comes with several issues. When dumping a mysql database mysqldump escape serialized data which prevent it to load correctly when importing database.

Note the escape \” char before double quotes. This push out of the sinc the serialisation data. It should be s:3:"raw"; instead of s:3:\"raw\"; Note how the number indicates a length of a string.

You could try to replace \” with ” but it might be a little bit more in depth issue since you only want to run this on serialised data stored in mysql tables.

Don’t run this on your production database! This is not full solution to the issue. This section only presents the problem so you can understand what is going on behind the scene.

If you need instant solution go to the bottom of this post and read sections “Solution One” and “Solution Two“.

If you would like to try make changes on your own on develop your own fix solution you can try to write extended version of the fallowing command which would not only remove unnecessary escape chars but also do it only on sterilized data and not whole database. Depending on your use case you could also need to replace old string with a new one and also update the string length value “s:” for that data.

Removing escape char

This works on entire database! You would want to run this only on serialized data.

# during dump
mysqldump [options] DB | sed -e 's/\\"/"/g'
# on a .sql file
cd toSQLdir
sed -e 's/\\"/"/g' *.sql

Or edit in text editor

This will not work with large file. To edit large file use the CLI as in example above

You might also need to take care of escaping forward slash:

https://dba.stackexchange.com/questions/29575/mysqldump-not-escaping-single-quotes-properly/29576

There is much work and testing when trying to solve this problem on your own. So unless you have a very important reason you should use third party tools to handle this issue.

Alternative solution (if you need to fix .sql file serialization issue)

You can try to fix this by running a from french dev Frédéric GILLES https://wordpress.org/plugins/fg-fix-serialized-strings/

* In WP you can manually recreate all missing widgets and the broken seriziled objects will be overwritten.

https://stackoverflow.com/questions/15138893/fix-serialized-data-broken-due-to-editing-mysql-database-in-a-text-editor
https://stackoverflow.com/questions/22641678/mysqldump-avoid-escape-double-quotes

The Solution (One) – WP-CLI

WP-CLI – command line tool to search and replace all database tables at once with a single command.

If you are familiar with the command line, you can run the following command to update all URLs in the database at once.

  1. First, log into your server via SSH.
  2. Next, navigate to your WordPress directory. Make sure to change username to your Shell user.
    [server]$ cd /pathtosite/example.com
  3. In your site’s directory, you can use wp-cli to update the URLs using the following command.
    [server]$ wp search-replace http://example.com https://example.com/blog --dry-run

The --dry-run call at the end tests the address

If the changes make sense to you, then run the above command again without the dry-run call.

The Solution (Two) – phpMyAdmin + Plugin

When changing site domain name do not manually edit database where the content is serialized. Instead edit only data in wp_option table and if that will not be enough for your site use specially design tools that will recalculate serialized string length and make required adjustments.

Next you need to update remaining urls using “search & replace” wp plugin.

https://help.dreamhost.com/hc/en-us/articles/214580498-How-do-I-change-the-WordPress-Site-URL-

0
Would love your thoughts, please comment.x
()
x