0

I recently got onto a new project and it seems their database is a mess. Of course they don't have a backup version of it either, so I have been trying to export the database and importing it again so I have a test server to develop on. I have no prior knowledge with SQL, I've just been designing the front end of sites for a while, this is all new to me.

The database is over a gig large, so I used BigDump to load my sql database in batches, it gets to 100%, and then bam. I get a foreign key error. I am at a loss right now trying everything I could think of, so I come to you guys! Any ideas or help will be greatly appreciated!!

Here is the error it gives me;

Error at the line 3266725: 

ADD CONSTRAINT `FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID`  
FOREIGN KEY (`customer_group_id`) 
REFERENCES `customer_group` (`customer_group_id`) 
ON DELETE CASCADE ON UPDATE CASCADE;

Query: --

--

ALTER TABLE `catalogrule_customer_group`
ADD CONSTRAINT `FK_CATALOGRULE_CUSTOMER_GROUP_RULE_ID_CATALOGRULE_RULE_ID` 
FOREIGN KEY (`rule_id`) 
REFERENCES `catalogrule` (`rule_id`) 
ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID` 
FOREIGN KEY (`customer_group_id`) 
REFERENCES `customer_group` (`customer_group_id`) 
ON DELETE CASCADE ON UPDATE CASCADE

MySQL: Cannot add or update a child row: a foreign key constraint fails (johnsoja_magento2.#sql-3a72_5d7aff, 

CONSTRAINT `FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID` 
FOREIGN KEY (`customer_group_id`) 
REFERENCES `customer_group` (`customer_group)

This truly is all like another language to me, and it seems like if I fix one thing here, it'll just show me the next problem along the line. What to do?! Thanks!

Jeff
  • 35
  • 4
  • Why not take a backup and restore it that way. Honestly, it sounds like the data in the database is dirty - it doesn't conform to the business rules that were inferred by the foreign keys. Good luck. – David Griffiths Mar 19 '14 at 15:46
  • This will sound newbish, but I figured the best way to back up was to export the database as is, is there another way I could try and go about backing up and restoring? – Jeff Mar 19 '14 at 15:58

2 Answers2

0

It appears that you'were trying to redefine an existing constraint: To solve it ... It's very simple you just have to remove this contraint using this:

   ALTER TABLE catalogrule_customer_group
   DROP FOREIGNKEY FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID;

Wherever this error shows like : Error at the line 3266725: ADD CONSTRAINT SOMETHING FOREIGN KEY

You use yhis query to remove something CONTRAINT...Good day

ASNAOUI Ayoub
  • 462
  • 2
  • 9
  • Thanks for the help! I may be doing this completely wrong, but I tried that query on the newly installed db, and get this: "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONSTRAINT FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID' at line 2" I am a little hesitant to drop the table on the live db because I don't want it to possibly crash the whole database and put me in the crosshairs. Do I have to do it on the Live database? – Jeff Mar 19 '14 at 15:43
  • No don't drop live tables... try the query withou ";" – ASNAOUI Ayoub Mar 19 '14 at 15:47
  • I tried the query without the ";" and it still returned the same error – Jeff Mar 19 '14 at 15:57
  • Oh sorry it's my fault ... I taught you were using SQL Server...Here is the MySQL version ====> ALTER TABLE catalogrule_customer_group DROP FOREIGN KEY FK_CATRULE_CSTR_GROUP_CSTR_GROUP_ID_CSTR_GROUP_CSTR_GROUP_ID – ASNAOUI Ayoub Mar 19 '14 at 16:06
  • When trying that, it came back with this "#1025 - Error on rename of './johnsoja_magento2/catalogrule_customer_group' to './johnsoja_magento2/#sql2-3a72-5f9bdc' (errno: 152)" – Jeff Mar 19 '14 at 16:10
  • Check this link for this error ... http://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me – ASNAOUI Ayoub Mar 19 '14 at 16:16
0

There are 2 main reasons for foreign key constrain to fail:
- Data type of the two columns do not match;
- There is already data in the tables that conflicts with the rules.

Try to review they data in customer_group and catalogrule_customer_group tables, if the first is empty and you have data in the second, the query will fail.

It is bad idea to chunk the sql export ... if you don't understand it, look for solutions to export/import it as a steam/file.

d.raev
  • 9,216
  • 8
  • 58
  • 79
  • I looked into the two groups, and it appears the only commonality they have between eachother is they both use customer_group_id, and there are instances where they bout would have '1' as one of their id's ... other than that, I don't see any other commonalities – Jeff Mar 19 '14 at 15:57
  • Foreign key constrain means in short, all values mentioned in `catalogrule_customer_group`.`customer_group_id` should exist in `customer_group `.`customer_group_id`. – d.raev Mar 19 '14 at 16:06
  • They seem to be two completely sets of data in that one set has customer id rows that work like 0,0,0,0,1,1,1,1,2,2,2,2,3,3 .. and the other is 1,2,3,6,7,8,9,11,17,19,20 .. should I just add filler rows in the first set to mimic the 6,7,8,9 and so on? Or would that be a terrible, terrible idea? Thanks for the help again – Jeff Mar 19 '14 at 16:13