WordPress issue with specific prefix dumped tables

Question

I am creating my first WordPress site. I am developing it on my localhost. I try to migrate the site to a staging environment where to put the site to show it to the Client, but I face a strange issue with the dumped DB.

What have I done: I developed the site using XAMPP. I exported the DB from the php-my-admin. Then I try to import the DB to another server (again using XAMPP only for testing purposes, in the future I will deploy the site in NGINX). But when I try to import the DB the SQL returns me the following error message:

---- Constraints for dumped tables------ Constraints for table 3op_wc_download_log--ALTER TABLE 3op_wc_download_log ADD CONSTRAINT fk_3Op_wc_download_log_permission_id FOREIGN KEY (permission_id) REFERENCES 3op_woocommerce_downloadable_product_permissions (permission_id) ON DELETE CASCADE, ADD CONSTRAINT fk_wp_wc_download_log_permission_id FOREIGN KEY (permission_id) REFERENCES wp_woocommerce_downloadable_product_permissions (permission_id) ON DELETE CASCADE;

I don't have much experience with databases. I think the error is occur because I changed the default database prefix from "wp" to "3op" for security purposes.

I researched the error on the internet and here in StackOverflow. I try some things, but nothing helped (I am not sure if I make everything correct).

Answer 1

I am a huge fan for - https://wordpress.org/plugins/all-in-one-wp-migration/

This makes local > staging/prod migrations fairly painless. (does most of the lifting for you).

However, if you are looking to deal with this manually.

Tables will need to be adjusted for prefix either locally > re-exported > then imported to match the other site prefix

Or adjust the new site to use the local prefix

You could also try changing the prefix in the wp_config.php file on the server you are trying to move to so it reads it correctly.