adding set FOREIGN_KEY_CHECKS=0; to the beginning seems to work
Post: The small improvements of MySQL 5.6: Duplicate Index Detection
May 31, 2013 By Leave a Comment
… not a foreign key, and it has the exact column definition in the same order. In other words, it checks for duplicate keys, but… a foreign key. This is the output of our tool when run on the same table, correctly identifying the redundancy: $ pt-duplicate-key… query optimizer and the MySQL version, we always recommend to check manually the optimizations proposed by Percona Toolkit. The MySQL server…
Post: Implementing SchemaSpy in your MySQL environment
June 4, 2013 By 2 Comments
…(10) unsigned DEFAULT NULL, PRIMARY KEY (`id`), KEY `parent_id` (`parent_id`), CONSTRAINT `child_B_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent… zero in on a particular table Constraints – lists the explicit Foreign Key constraints in the database (this does not include constraints identified… actual SQL NULL value. This is basically a quick sanity check of your schema for any significant errors or items requiring…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… with –innodb-optimize-keys option it produced invalid SQL for cases when there was an explicitly named foreign key constraint which implied an… by detecting such cases and omitting the corresponding secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server… Bychko). Reduced the overhead from innodb_pass_corrupt_table value checks by optimizing them for better CPU branch prediction. Bug fixed…
Post: Percona Server for MySQL 5.5.30-30.2 now available
April 10, 2013 By 10 Comments
… with –innodb-optimize-keys option it produced invalid SQL for cases when there was an explicitly named foreign key constraint which implied an… by detecting such cases and omitting the corresponding secondary keys from deferred key creation optimization. Bug fixed #1081016 (Alexey Kopytov). Percona Server… Bychko). Reduced the overhead from innodb_pass_corrupt_table value checks by optimizing them for better CPU branch prediction. Bug fixed…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…) VALUES(1,”"); INSERT INTO child(parent_id) VALUES(1,”"); set foreign_key_checks=0; delete from parent; After these actions we have child… all columns whenever they have changed or not: mysql> set foreign_key_checks=1; Query OK, 0 rows affected (0.00 sec) mysql… to satisfy foreign key conditions (have column child table is referencing and have it of proper type and indexed): mysql> set foreign_key_checks=0…
Post: Innodb locking and Foreign Keys
December 12, 2006 By 2 Comments
… most people do not think about foreign keys overhead in terms of locking. The overhead about checking referenced table is usually considered but… work with foreign keys in Innodb. I’ve only done a quick check so could be missing some details. All Innodb Foreign Key related operations… updates are affecting foreign key relationships. So if you will update any column which is not part of FOREIGN KEY constraint – no foreign key originated locks…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
…, database test and table foreign_key_errors. It will run as a daemon for 3600 seconds and will check the last error every… the problem is. 4- Let’s check what the tool has logged: mysql> select * from test.foreign_key_errors\G *************************** 1. row *************************** ts: 2012…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON… or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1… | +———-+ 1 row in set (0.00 sec) Now let’s check the slave. [revin@forge rsandbox_5_5_17]$ ./node1/use…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… healthy discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response has always… NOT NULL, bogus_column char(32), PRIMARY KEY (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ) ENGINE… on a lock. Want to know where that lock is? Check information_schema.innodb_locks: mysql> SELECT * FROM information_schema.innodb…
