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`
(`role_id`) REFERENCES `admin_role` (`role_id`) ON DELETE CASCADE ON UPDATE 

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.

2 Answers 2


Can you follow this steps:

  • Install magerun in both stages live and staging Link to get magerun.
  • Dump db of live (there are commands for striping db)

    magerun db:dump

  • On the staging server , drop the current database , create the database with the same name as it is on your local.xml

  • Import live db in staging

magerun db:import (name of live db).sql

magerun cache:flush

magerun index:reindex:all

magerun config:set --scope=default --scope-id=0 web/secure/base_url https://staging-url-here.com/

magerun config:set --scope=default --scope-id=0 web/unsecure/base_url https://staging-url-here.com/

Let me know how it goes.


So the issue was that I had same servers for development and live website(i.e both the databases are residing on the same server). When I took the dumps of live database and tried to import it in the development database, same constraints names were used as of live database and that clashed. It was because of this I was getting the above errors. Once I changed the constraints names, everything went fine.

  • Do you mean the names in app/etc/local.xml?
    – Black
    Oct 18, 2018 at 14:32
  • No, not that. local.xml contains config data. I was talking about the sql file containing dump of live database. Oct 18, 2018 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.