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 has worn many hats. He now spends most of his time managing the product teams and growing the business. Before starting this company, Brad was a freelance web developer, specializing in front-end development.