Solved

Foreign Key Constraints fail dumping and restoring MySQL db

Posted on 2013-01-25
6
1,111 Views
Last Modified: 2013-03-11
Hi all,

When I dump a MySQL db data to a .sql file from phpmyadmin and try to restore to the loca version of the db on my machine i get the following error;

Error Code 1452. Cannot add or update child row. a foreign key constraint fails.


Now I assume this is because it is trying to insert the data out of sequence (for example trying to insert a relationship table such as userole before user table is populated??)

Is there a way of clmbatting this when we dump the data?
0
Comment
Question by:flynny
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38819411
Either you create the constraints after the data loading, or you make indeed shure you load in the correct order. You might find that you can disable the constraints during the loading.
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 500 total points
ID: 38821750
Open the dump file with a text editor and put this somewhere at the top:

SET FOREIGN_KEY_CHECKS=0;

Remember to do a SET FOREIGN_KEY_CHECKS=1; after.
0
 

Author Comment

by:flynny
ID: 38821772
ok temporarily releasing the constraints sounds the most straight forward idea.

How is it possible to do this using mysql workbench please?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 24

Expert Comment

by:johanntagle
ID: 38821839
You can type in that line and run it like any SQL statement.

But I suggest you make it part of the dump file, which you can open as an SQL script in Workbench or using by any text editor.
0
 

Author Comment

by:flynny
ID: 38885476
ok could you provide the code to remove and then readd these constraints?

i.e. I assume I would use the code to remove constrainsts

Insert the data,

dump the constraints from the original db and re-add these back tot the copy db?
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38886604
Why remove and readd when you can just disable and re-enable them with the commands I gave above?
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
PHP Undefined Index in HTML Form? 2 31
Php pie charts 3 26
How many transactions can mysql handle? 3 18
Exchange 2010 Database wont mount 3 15
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now