Mysql Error 1452 Replication
Contents |
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss cannot add or update a child row a foreign key constraint fails mysql the workings and policies of this site About Us Learn more about mysql error 1452 foreign key constraint fails Stack Overflow the company Business Learn more about hiring developers or posting ads with us Database Administrators Questions
Mysql Replication Foreign Key Constraint Fails
Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn
Mysql Replication Error_code 1452
from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top MySQL Replication question up vote 0 down vote favorite I have setup a master-slave replication between two MySQL servers using mysql Version 14.12 mysql drop foreign key Distrib 5.0.51a, for debian-linux-gnu. Recently the replication errored out with this in the logs: 111128 17:41:12 [ERROR] Slave: Error 'Cannot add or update a child row: a foreign key constraint fails (test/bookmap_item, CONSTRAINT bookmapitem_bookmap FOREIGN KEY (map_id) REFERENCES bookmap (map_id) ON DELETE CASCADE ON UPDATE CASCADE)' on query. Default database: 'test'. Query: 'INSERT INTO bookmap_item (map_id, ezcontentobject_id, ezcontentnode_id, section, sub_section) VALUES(21514, 20205, 27601, 252, 291)', Error_code: 1452 My question is, when using MySQL's replication, is there anyway to keep the slave from enforcing foreign keys? Or is there something else I could try? mysql replication share|improve this question asked Nov 30 '11 at 11:37 Craig Efrein 6,07952562 add a comment| 1 Answer 1 active oldest votes up vote 3 down vote accepted On the slave, you can configure MySQL to skip certain errors by using the slave-skip-errors configuration item. In the case above, you'd add the following to your my.cnf; slave-skip-errors = 1452 But I'd be extremely wary about doing this, as the referential integrity is there for a reason, and without it, your slave cou
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Mysql error 1452 - Cannot http://dba.stackexchange.com/questions/8590/mysql-replication-question add or update a child row: a foreign key constraint fails up vote 179 down vote favorite 53 I'm having a bit of a strange problem, I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign http://stackoverflow.com/questions/1253459/mysql-error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fa key on a different table referencing the one I am trying to reference. Here is a picture of my table relationships, generated via phpMyAdmin: Relationships I've done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table. CREATE TABLE `sourcecodes` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `language_id` int(11) unsigned NOT NULL, `category_id` int(11) unsigned NOT NULL, `title` varchar(40) CHARACTER SET utf8 NOT NULL, `description` text CHARACTER SET utf8 NOT NULL, `views` int(11) unsigned NOT NULL, `downloads` int(11) unsigned NOT NULL, `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`), KEY `language_id` (`language_id`), KEY `category_id` (`category_id`), CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 CREATE TABLE `sourcecodes_tags` ( `sourcecode_id` int(11) unsigned NOT NULL, `tag_id` int(11) unsigned NOT NULL, KEY `sourcecode_id` (`sourcecode_id`), KEY `tag_id` (`tag_id`), CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE
Reporter: Miguel Angel Nieto Email Updates: Status: Closed Impact on me: None Category:MySQL Server: Replication https://bugs.mysql.com/bug.php?id=69861 Severity:S2 (Serious) Version:5.5.32, 5.6.12/13 OS:Any Assigned to: View Add Comment http://serverfault.com/questions/675317/mysql-replication-ignoring-binlog-statements-for-certain-table Files Developer Edit Submission View Progress Log Contributions [28 Jul 2013 9:37] Miguel Angel Nieto Description: If you have a replication environment that uses replication filters (replicate-ignore-table in this example), the value of LAST_INSERT_ID could not get correctly replicated. In the foreign key environment described in "how to repeat" this is the LAST_INSERT_ID value that the slave server receives without replicate-ignore-table: SET LAST_INSERT_ID=1/*!*/; And the same query with replicate-ignore-table writes the following on slave's relay log: SET LAST_INSERT_ID=0/*!*/; How to repeat: We have the following servers: A<->B | C Where A and B foreign key constraint are in master-master and C replicates from A. Now run the following on B: CREATE TABLE `sometable_apps` ( `id` int(11) NOT NULL AUTO_INCREMENT, `appname` varchar(50) DEFAULT NULL, `createdate` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `sometable` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rundt` datetime DEFAULT NULL, `appid` int(11) DEFAULT NULL, `username` varchar(20) DEFAULT NULL, `machine` varchar(20) DEFAULT NULL, `finishdt` datetime DEFAULT NULL, `pid` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `sometable_apps` (`appid`), CONSTRAINT `sometable_apps` FOREIGN KEY (`appid`) REFERENCES `sometable_apps` (`id`) ) ENGINE=InnoDB; insert into sometable_apps(appname, createdate) values ('xxxxx', '2013-07-23 15:56:59'); insert into sometable (rundt, appid, username, machine, pid) values ('2013-07-23 15:56:59', LAST_INSERT_ID(), 'xxxxx', 'xxxxxxx', 2736); The C's replication will fail: "Error 'Cannot add or update a child row: a foreign key constraint fails". If you remove the replicate-ignore-table and repeat the process it won't fail and the LAST_INSERT_ID will be correctly logged on slaves relay log. A
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top MySQL replication ignoring BINLOG statements for certain table up vote 0 down vote favorite I'm trying to make MySQL Replication work, but my slave is constantly stopping because of constraint fails. This errors occurs because the replication of one particular table is not working. If I check the Relay Log File, I can see several BINLOG statements that should be inserting rows to that table, but they don't. Here's an example: BINLOG ' sQn+VBMLAAAAYQAAAOkmaCUAAOnamUgBAAEADWRtXzc1NF9kYmJ1ZDkACnBlcnNvbmFzX18AEgMD AwP+Dw8PDw8P/g/+AQcDART3AZYAlgBaACwBAwA8APcBPAD3AQ5+Ag== sQn+VBcLAAAAWwAAAEQnaCUAAOnamUgBAAEAEv///w56/AEAAAADCHJlaW5hbGRvA2dvbgASAHJl eWdvbmNsQGdtYWlsLmNvbQg5MTkwNzAzNrEJ/lQBAAAAAA== '/*!*/; ### INSERT INTO `dm_754_dbbud9`.`personas__` ### SET ### @1=1 /* INT meta=0 nullable=0 is_null=0 */ ### @2=NULL /* INT meta=0 nullable=1 is_null=1 */ ### @3=NULL /* INT meta=0 nullable=1 is_null=1 */ ### @4=NULL /* INT meta=0 nullable=1 is_null=1 */ ### @5=3 /* ENUM(1 byte) meta=63233 nullable=0 is_null=0 */ ### @6='reinaldo' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */ ### @7='gon' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */ ### @8='' /* VARSTRING(90) meta=90 nullable=0 is_null=0 */ ### @9='xxxxxx@