0

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

  • 1
    How are you copying your database to your staging environment? – kab8609 Dec 8 '14 at 19:23
  • ok i have direct admin with phpmyadmin *** i created a new subdomain and a new mysql database *** i copied all the files from the live domain to the staging subdomain *** in the app/etc/local.xml i changed the db name, password and user *** in phpmyadmin i logged in to the database, selected it and clicked export... *** this exported file i imported in the database created for the staging environment *** Than i get the error message – carlo Dec 8 '14 at 20:14
2

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
  1. After a few moments, your file will be done. Transfer this file to the staging site.
  2. 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.

  • I will ask my host if i can do this... i have a vps (apache with directadmin) – carlo Dec 8 '14 at 20:19
  • If your host does not allow SSH access, I would seriously consider getting a new host. It is pretty much a requirement for developing. – kab8609 Dec 8 '14 at 20:20
  • will this procedure not produce the error? I mean is it different from exporting and importing the database? – carlo Dec 8 '14 at 22:38
  • This would be a developers first way to do it. Importing/Exporting through PHPmyadmin is generally not recommended for obvious reasons (especially for larger DBs). – kab8609 Dec 9 '14 at 0:46
  • 1
    hahaha that would be something ... to hold another person responsible ;-) – carlo Dec 9 '14 at 18:05
1

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

  • Strange that there is a difference since the staging environment is a new clean setup – carlo Dec 8 '14 at 20:18
  • When you say "New Clean Setup" what do you mean? – brentwpeterson Dec 8 '14 at 20:52
  • well, i am not adding it to an existing setup... it is a new subdomain and new database in which i want to import the files and db of the live setup – carlo Dec 8 '14 at 21:13
  • And of course you existing live site code? – brentwpeterson Dec 8 '14 at 21:15
  • I don't know where you are trying to go, (i mean i don't understand) but i have set up a staging site for this production site few weeks back without any problems... because of an extension this was corrupted so i decided to delete the subdomain and all of it's files and the database. After that i created brand new subdomain and brand new database following the same steps as before, but i got this error, propably it has something to do with the tax configuration i have set up in the meantime... So i have done it before, no problems, now i have this one and looking for a solution – carlo Dec 8 '14 at 22:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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