MySQL : ERROR 1452 (23000) at line 11426: Cannot add or update a child row: a foreign key constraint fails

-
So i am trying to restore a database from one server to another. Something like this:

mysql -u username -p -D dbname < backup.sql

Somehow there is this error:

[root@ns46 mysql]# mysql -u haocheap -p -D haocheap < /tmp/ayam.sql Enter password: ERROR 1452 (23000) at line 11426: Cannot add or update a child row: a foreign key constraint fails (`haocheap/#sql-bb2_5ef8a`, CONSTRAINT `FK_EAV_ENTITY_ATTRIBUTE_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE) So where do i get the backup.sql file? i dump it from the old server's phpmyadmin export. using method "INSERT'.

Open the file, add this line at top:

SET AUTOCOMMIT=0;
SET FOREIGN_KEY_CHECKS=0;

and these lines at bottom:

SET FOREIGN_KEY_CHECKS=1;
COMMIT;
SET AUTOCOMMIT=1;

The theory is, the restore is restoring a database that need some foreign key constraints from another table which has NOT YET been restored. thus the "SET FOREIGN_KEY_CHECKS=0;" option.

Hope this help someone.

//alak

Comments

Post a Comment

Popular posts from this blog

Quick Dirty Script for Checking Oracle Data Gurad Gap and Emailing it

-
 Situation: primary DB: prd_CCC standby DB: prd_WWW file 1: gap-check.bat @echo off cls echo. echo. echo This script checks for Archive Logs at prd_ccc and Applied Logs at prd_www echo. echo Querying prd_ccc (Primary) echo select max(sequence#) "Primary" from v$archived_log where archived='YES';|sqlplus -s / as sysdba echo Querying prd_www (Standby) echo select max(sequence#) "Standby" from v$archived_log where applied='YES';|sqlplus -s / as sysdba echo. DGMGRL / "show configuration" date /t && time /t && hostname && whoami file 2: emailer.ps1 $Username      = "username" $EmailPassword = "password" $today = Get-Date $Username = $Username $EmailTo = "adam@jasabyte.com"  $EmailFrom = "server@jasabyte.com" $Body = Get-Content .\status.log -Raw $Subject = "PRIMARY/STANDBY DB GAP - $today" $SMTPServer = "mail.smtp2go.com"  $SMTPMessage = New-Object System.Net.Mai...
Read more

SAP SWPM Software Provision Manager SL Toolset 1.0 SP22 Startup Options

-
This is just a rough note on the options and properties we can set upon running apinst when installing a SAP system. Version: [root@host SWPM]# ./sapinst -v [==============================] - extracting...  done! INFO       2018-04-19 11:54:56.315 (mainThread) [sixxcreate.cpp:346] ******************************************************************************** Initial log directory: /root/.sapinst/falconheavy.vantage-intl.com/12345 ******************************************************************************** SAPinst build information: -------------------------- Version:         749.0.47 Build:           1832063 Compile time:    Mar 27 2018 - 13:22:27 Make type:       optU Codeline:        749_REL Platform:        linuxx86_64 Kernel build:    749, patch 426, changelist 1830485 SAP JRE build:   SAP...
Read more

SAP SystemCopy - Activation of TCI Notes, DigiSIgn Gone Wrong

-
Backgroud: Source was ECC6 EHP6 / Oracle 11.2 / Windows / Non-Unicode, and TCI and DigiSig and dS/4 RC Note already implemented Target: ECC6 EHP6 / Sybase ASE 16 / Windows / Unicode Issue: Post system copy, ABAP dumps all over the place, SE11, SE38, SNOTE... Related notes: 2750714 - SYNTAX_ERROR: Type "CWBCI_T_ATTR" is unknown 2861287 - Fixing syntax errors caused by missing types And below are the objects needed ABAP Debugger and activate. cutoff is at CWBNTREASON and after that can use SE11 Actuvate using SE84: Domains CWBCIATTR_ID STricture IMP_TEST_CORR_INSTRUCTION Table CWBCINSTATTR - x Table Type CWBCI_T_ATTR Structure CWBNTKEYTSTAMP Data Element CWBNTREASON -- beyond this, can remove all breakpoints and use SE11 Table CWB_FILE_NAME Table CWB_SIZE_IN_BYTE Table CWB_NOTE_URL Table CWB_DWNLD_PROC Table CWB_PROC Table CWB_T_NOTE Data Type SCWB_S_NO_PATCHLEVEL Data Type SCWB_T_NO_PATCHLEVEL Table TT_SCWB_PATCH Data Type CWBCI_S_LIFECYCLE_OBJECT Data Type CWBCI_T_LIFECYCLE_O...
Read more