WP Migrate DB Pro: Change the table prefix on migration?

Sebastian Green asks a great question in the comments of my last blog post:

“Improvement: Message to warn the user that the table prefixes differ between installs and instructions to remedy that issue” – this is good. Is there a plan to add in the ability to change the table prefix in the transfer process?

I started to reply but it became quite long, so I’m posting it here instead.

We actually took a couple of stabs at this and found it to be too complex to do automatically. The table prefix is actually used in the data, so changing the table prefix involves not only renaming the tables, but replacing the prefix in the data as well.

We thought about just adding another find & replace (e.g. find “wp_” and replace “dgyrw_”), but found that replacing “wp_” across the whole database was problematic. For example, what if you wrote a blog post talking about the “wp_” prefix for example? It would be replaced but it shouldn’t be. Or what if you installed WordPress into a subfolder named “wp_test”? Then any URLs with “wp_test” would be replaced with “dgyrw_test” and shouldn’t be.

So then, we thought about just targeting the specific table columns where the prefix was used, namely the meta_key column in the usermeta table and the option_name column in the options table. But then, we realized that plugins could also be using the table prefix and inserting data elsewhere, so still not a great solution.

Ultimately we couldn’t think of a scenario where it makes sense for a developer to have different prefixes across environments of the same site. A development environment should mirror a production environment as closely as possible and so developers should really make the table prefix the same.

So, we decided to encourage this by detecting the differing table prefixes and providing instructions on how to make them the same across environments.

Obviously if we learn of a plausible scenario where the table prefixes should actually differ, then we’ll re-evaluate and come up with a solution to automatically change the table prefix on migration.

About the Author

Brad Touesnard

As founder of Delicious Brains Inc., Brad wears many hats; from coding and design, to marketing and partnerships. Before starting Delicious Brains, Brad was a busy freelance web developer, specializing in front-end development.

  • sebastiangreen

    Thanks for the info. It’s great to see you listening to users and responding so quick.

  • Thanks for explaining that, I agree 🙂

  • ThomasvanderWesten

    Hi Brad, Thanks for you time to explain. We work here with different table prefix’s for each environment: for dev: dev_site-abbreviation_, test: tst_site-abbreviation_ ect.. A overview of all table-prefix’s in the database and a (advanced) option to replace then would be great.

    • It is a best practice to build your dev and staging environments to mirror production as closely as possible, so why make the prefix different?

      • mordavia1982

        my issue has been developing with ServerPress – DesktopServer then deploying to wpengine which uses another prefix. For manual wordpress installs there’s no prefix issue but for 1click install/automated installs, there could be.

  • Andy

    I’m not sure what’s so hard about implementing this from a technical point of view. Already there are several standalone plugins in the wordpress plugin repositiory that can change the table prefixes, so when the plugin developer says it’s not possible, it makes me question their coding abilities in general.

    If WP Migrate DB Pro supported renaming the table prefix on import, I would buy this in a heartbeat, but since I have to follow company policy in the naming of my tables, I cannot use the plugin without this feature.

    • Andy, have you actually tried these plugins? My experience so far matches what Brad said above. You end up with a mostly changed install, and then a bunch of hand-cleaning work. (Or worse, it looks like it worked, but down the road, you start running into issues and finding everything didn’t get properly changed, etc.) If you know of one that works well, I’d be interested to know… and Brad could probably look at the code then as well.

      • I couldn’t have said it better Steve, thanks!

      • Andy

        Yes, I have been using the plugin “DB Prefix Change” (http://wordpress.org/plugins/db-prefix-change/) for quite some time now and I have not experienced any of the issues you are talking about.

        • Thanks Andy, I’ve added that to my plugin collection for the next time this comes up. That said, I try very hard now to not change them. I was initially doing it to older sites because it was recommended by security sites and plugins… but now I usually start with it changed from the get-go, or don’t worry about it.

          In theory, I just don’t see how it’s possible a plugin could cover the types of circumstances Brad mentioned. I suppose why it works for one site and not another probably has to do with plugins used and how they store things. I think I’ve done 3 sites and all of them had issues post-change. One was obvious right away, the other two I didn’t notice for some time, and eventually had to do a bunch of DB searches and manual looking to fix things up. That said, I’m sure some methods can be more robust than others…. I just don’t think it could be 100%.

  • WordPress is a highly customizable CMS that have been adopted by millions around the web.
    Unfortunately with the so much popularity, WordPress became a top target of many hackers who created automated scripts to target various vulnerabilities in the WordPress.
    One of the way to harden the security of your WordPress website is to change default database prefix from “wp_” to something else.
    Learn how to do that in this step by step tutorial here: http://www.cloudways.com/blog/change-wordpress-database-table-prefix-manually/

  • The Truth

    godaddy managed wp user whatever prefix. If I’m developing local before creating the actual db in godaddy, then there is no way to know what the prefix the godaddy engine will assign. very difficult… I need a solution.

  • I’m sort of hoping for a “I know what I’m doing, migrate anyway” checkbox here. Scenario: migrate a set of client single site custom prefix tables into staging multisite subsite with another prefix (since you cant choose a prefix per subsite). Live sites are encouraged to have custom prefixes for security reasons anyway, so can’t really follow the “have a similar model” when using a multisite for staging (ton of conveniences which are hard to let go).

    Migrating tables like `wp_posts` etc have no consequences to the table prefix. Why not allow me?

    I’m not sure if there’s a better step available other than manual export/import. Your thoughts?

  • Also need this @bradt66:disqus

    My scenario: I’m hired to improve a client website with custom table prefixes. It’s a regular install that I want to move into the WP Roots workflow but still need to download the existing data into my developer environment.