How to handle this database error when copying for staging environment?

Question

I wanted to copy my live shop (1.9.0.1) to a staging environment.

The first time i did it it went ok, but i have configured the VAT and tax in admin sales>taxes and that is working ok, but for some reason this gives me an error on copying the database

Here is the error

Error SQL-query: --
-- Limitations for table sales_order_tax_item
--
ALTER TABLE sales_order_tax_item ADD CONSTRAINT FK_SALES_ORDER_TAX_ITEM_ITEM_ID_SALES_FLAT_ORDER_ITEM_ITEM_ID FOREIGNKEY ( item_id ) REFERENCES sales_flat_order_item ( item_id ) ON DELETE CASCADE ON UPDATE CASCADE ,
ADD CONSTRAINT FK_SALES_ORDER_TAX_ITEM_TAX_ID_SALES_ORDER_TAX_TAX_ID FOREIGN KEY ( tax_id ) REFERENCES sales_order_tax (tax_id ) ON DELETE CASCADE ON UPDATE CASCADE ;


MySQL: 

1452 - Cannot add or update a child row: a foreign key constraint fails (webshop_stn15.#sql-60a_9df5d, CONSTRAINT FK_SALES_ORDER_TAX_ITEM_TAX_ID_SALES_ORDER_TAX_TAX_ID FOREIGN KEY (tax_id) REFERENCES sales_order_tax (tax_id) ON DELETE CASCADE ON UPDAT)

I checked the original database and these tables are exactly the same so why am i having this problem?

X SELECT item_id FROM sales_flat_order_item;

X SELECT tax_id FROM sales_order_tax;

these are both empty but so are they in the original database

I have no experience with this kind of stuff so i hope to find someone here that can help me out ;-)

Thanks

EDIT: i created a new subdomain and a new empty database. Than copied all files from live setup to the staging subdomain, changed the lines in local.xml with db password, user etc. and exported the live database which i imported in new database... this gave the error above.

i added this edit because everybody seems to think that i dropped these files in an old setup but that is not true... the first time i created a staging setup i havent had these issues...

EDIT 2 : PROBLEM SOLVED - I only had to check the option on export : check foreign keys

Answer 1

So i'm not 100% sure how you are doing the import and export of your database so I am going to share with you how i do mine (when I do it manually).

1. To reduce database size, I follow this blog from Nexcess on what tables I can "truncate". Depending on how many visitors you have, you can see a reduction of 10MB to 1GB. This will help with the speed of the transfer.

2. Log onto your production server via SSH. Write down your mysql database name, user, password and change this script to match it:

   mysqldump -h yourdbhost.com -u mysqlusername -pPassword databasename | gzip -9 > backup1208.sql.gz

If your host is simply just localhost use this instead:

    mysqldump -u mysqlusername -pPassword databasename | gzip -9 > backup1208.sql.gz

3. After a few moments, your file will be done. Transfer this file to the staging site.

4. Once the file is on the staging site, login via SSH and use this command:

   gunzip < backup1209.sql.gz | mysql -u mysqluser -pPassword databasename

Word of advice, if you have never done this before. I would recommend you try this transfer on a staging database first before attempting in production.

Answer 2

It sounds like there maybe some difference between the staging and live environment:

First you need to make sure your live and staging are the same Next you want to dump your live database using something like the following:

mysqldump -p'$password' -u$user -h$host --single-transaction --quick $dbs | gzip > dumpfile

(The $vars are you variables to fill in) Then to make absolutely sure you don't have data or tables in your old staging database you can drop and recreate your database. This is especially important when doing upgrades.

drop database staging_db;
create database staging_db;

Now you can import your backed up database to staging

gunzip < $dbs | mysql -p'$password' -u$user -h$host staging_db

Finally you need to update the URLs on the staging site

You can find the config_id's using the following query

select * from core_config_data where value like 'http%';

update core_config_data set value = 'http://staging.domain.com/' where config_id in (id1,id2);

The ID's will be the id's you get from your live site values.

Now you staging site should work the same as live