I am moving my live database to a staging server in order to setup a development environment. I took two dumps of my live database
- structure without data dump
- data dump excluding the log tables
After creating my database, I can easily import the structure dump without any problem. When I try and import the data dump into staging database it throws an error. I have set foreign_key_check to 0 in the top line of the data dump file.
1005 - Can't create table 'staging_magento.#sql-a2b7_3b99c8a'
Here is the line of sql that throws this error
ALTER TABLE `admin_rule` ADD CONSTRAINT `FK_ADMIN_RULE_ROLE_ID_ADMIN_ROLE_ROLE_ID` FOREIGN KEY (`role_id`) REFERENCES `admin_role` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE;
I have looked into this issue and found that it may have occurred because admin_rule and admin_role might not be InnoDB engine tables but I have checked and their engine is set to InnoDB.