Why does changing Database Table Prefixes result in loss of content?

Question

As to add an additional layer of security, to an eCommerce website I am working on, I have changed the default Database prefix from wp_ to wp_additional_prefix.

To do this, I opened the wp-config.php file and modified the $table_prefix = 'wp_'; entry to $table_prefix = 'wp_additional_prefix';

To modify existing Database Tables, I ran the following SQL Query for each Database Table:

RENAME table `wp_table_name` TO `wp_additional_prefix_table_name`;

I then searched the Fields, within the wp_options and wp_usermeta Tables, and replaced all references of wp_ with wp_additional_prefix.

Whilst I was able to successfully perform the above actions, it did result in some of the website's content going missing. Content such as the website's Logo and Sliders. Additionally, each page's layout changes.

As such, I am wondering where I could be going wrong. Could it be that I am missing a Field or simply deviating from the default Database Prefix breaks the website?

Answer 1

You should have not searched the field and replaced all the references to wp_ with anything. For example : the value of the entry with meta_key wp_page_for_privacy_policy will be change to wp_addtionnal_prefix page_for_privacy_policy. Hence there could be a lot of misfitted values in your table.

A better way would have been to :

• export the DB
• import the DB as a new DB
• rename all tables with the new prefixes
• export it again
• import it in you old DB
• rename prefix in wp_config.php
• check if everything is allright