…ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE…kind of constraints? For …foreign key. This is the output of our tool when run on the same table, correctly identifying the redundancy: $ pt-duplicate-key-checker –tables…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)) Renaming parent table (even with foreign_…indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; …
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
…foreign key in table is “PRIMARY” See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. InnoDB: Renaming table… was: mysql> ALTER TABLE employees DROP PRIMARY KEY; Conclusion If you are having too many deadlock or foreign key errors log …
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce an error unless we first remove the foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens: (root@localhost) [foobar]> ALTER TABLE bar drop…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… KEY | empid | NULL | NULL | +————–+————+—————–+—————–+————-+———————–+————————+ alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint +++Query…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… avoid dropping keys that are part of a FOREIGN KEY constraint; mysqldump –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…Foreign key constraint fails for table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add in child table…
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
February 19, 2013 By 18 Comments
… another table (a child table) has a foreign key constraint pointing into this table. I tried this: SET FOREIGN_KEY_CHECKS = OFF; ALTER TABLE schema.my_parent_table DROP all foreign keys … ALTER TABLE schema.my_parent_table…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table… on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used… to most users? INSERT w/PRIMARY KEY defined: Number of rows inserted – 1 INSERT w/FOREIGN KEY constraint: Number of rows inserted – 1 SELECT…
Comment: How to recover a single InnoDB table from a Full Backup
January 31, 2012 By 10 Comments
Good point 
ALTER TABLE customer DISCARD TABLESPACE; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails () Added to the blog post, thank you.
