Changing the URL in a local database

When it comes to assisting the development and maintenance of a website, it may require you to have a copy of the website on your localhost.  Sounds easy enough, right?  Just download the site onto your computer, go offline and poof!  A website clone right one your development device.

This may be true for small websites with a couple of pages and text, but unfortunately when it comes to content rich websites with their own database it isn’t as simple.  Just because you have a local CMS running, and someone gives you a database, doesn’t mean after importing it everything will be hunky dory.  Most of the time you will still have some configuration issues, and a popular one I find is having an incorrect URL in the database.  Actually, the URL in the database is correct for the website running it, but your local site will have a different URL, and so the database needs to be updated accordingly.

The Setup

You should already have a fresh installation of a CMS on your machine. For this tutorial, I’m using WordPress (and in all fairness this tutorial may work only for WordPress).

You will also need a database management application to import and modify the file and database.  I’ll be working with Sequel Pro (a great and free application for the Mac OS).

To get started, use Sequel Pro to connect with the host (you will need to know the local url you’re using, as well as the database username and password).  Once connected, go to File -> Import… and select the .sql file you wish to use.

Technical Bits

Upon a successful import, your database will be full of all the posts and pages from your online website.  Nothing will really work however, until the database starts referring to your local URL.

Fortunately, user chuckreynolds has provided a fantastic snippet of code that does just this:

SET @oldsite='HTTP://OLDURL.COM';
SET @newsite='HTTP://NEWURL.COM';

UPDATE wp_options SET option_value = replace(option_value, @oldsite, @newsite) WHERE option_name = 'home' OR option_name = 'siteurl';
UPDATE wp_posts SET post_content = replace(post_content, @oldsite, @newsite);
UPDATE wp_links SET link_url = replace(link_url, @oldsite, @newsite);
UPDATE wp_postmeta SET meta_value = replace(meta_value, @oldsite, @newsite);

/* only uncomment next line if you want all your current posts to post to RSS again as new */
#UPDATE wp_posts SET guid = replace(guid, @oldsite, @newsite);

Running this, it will take the URL currently listed in the database, and replace it with the new URL.  All it takes is the modification of a few lines and running the query.  Simply copy this code, navigate to the Query tab, and paste it in the text area.

On line 1, replace HTTP://OLDURL.COM with the site URL.  A simple way of knowing if you’re targeting the correct url is to browse to wp_options in the tables column, select content in the top tab, find siteurl under option_name, and copy the option_value.

Then, on line 2, replace HTTP://NEWURL.COM with the url your local machine is using.  Finally, run the query by selecting the ‘Run All Queries’ button located at the top right of the history window.

Incase of Accidents

From my experience of domain migration, there’s usually a 50/50 chance everything works with no issues.  There have been two types of errors that I’ve come across that, when resolved, will successfully update the database.

Illegal mix of collations

[ERROR in query 3] Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT), (utf8mb4_general_ci,IMPLICIT), (utf8mb4_general_ci,IMPLICIT) for operation ‘replace’

From user eggyal, “An “illegal mix of collations” occurs when an expression compares two strings of different collations but of equal coercibility and the coercibility rules cannot help to resolve the conflict.” 

Explain like your 5?  The code doesn’t want to run because it doesn’t like your setting.  It’s getting a mix of different collations, or perhaps the wrong one is set, and fails to execute.

The solution is to select the table the code isn’t executing on, click the table info tab, and set it to the desired collation (in the above example, it would be utf8mb4_general_ci).

If the error continues to occur, then a bit more (tedious) work has to be done.  Select the structure tab at the top, and under the Collation column, change all the offending values to the desired one.

Invalid default value for “”

I had gotten this error once a while ago, and while I can’t recall exact details, it was a major inconvenience.  It had arise from not having a date, or more specifically, having 0000-00-00 as a value, and the setting of NO_ZERO_DATE being enabled.

The solution?  Prior to executing the above script, run this line of code (provided by Joy Zhu) :

SET @@session.sql_mode = “ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION";

You may (as I did) still get collation errors, depending on the setting of the character set.  If that’s the case, run this after setting sql_mode (Thanks mobsted):

alter table wp_options CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
alter table wp_posts CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
alter table wp_links CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
alter table wp_postmeta CONVERT TO CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';

If you get no errors, then the database should have replaced the old URL with the new one.  You can confirm this by clicking on the wp_options table and see if the option_value for siteurl has been updated with the new URL.  If so,  your local site should be good to go.