…KEY (`id`), KEY `col2` (`col2`), KEY `col2_2` (`col2`) ) ENGINE=InnoDB…foreign key, and it has the exact column definition in the same order. In other words, it checks for duplicate keys…this duplicate index, execute: ALTER TABLE `test`.`test` DROP INDEX `col2`; # …
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; Query… get foreign key violation error. So Innodb Foreign Keys are pretty good at keeping you hostage. Do you have any better idea how to Hijack innodb Foreign Keys…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
…the 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…error was: mysql> ALTER TABLE employees DROP PRIMARY KEY; Conclusion If you are having too many deadlock or foreign key errors log them. Just…
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…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used Query…, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0… OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY (`id`,`parent_id`); — 0 Tickets Used Query OK…
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
February 19, 2013 By 18 Comments
…, I have an InnoDB table that I want to optimize, but 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…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… the InnoDB data we are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY…/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> SELECT * FROM…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… SHOW INNODB STATUS is good enough. As for deadlock information we have similar information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY …, unless it is ALTER TABLE or similar statement, or if LOCK TABLES was used. In addition to Innodb specific information, …
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 29 Comments
… way to load data (especially if you disable unique key checks and foreign key checks), I recommended against this. There are several problems… in chunks of a million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way…=InnoDB DEFAULT CHARSET=utf8 Here’s the result of loading the entire 4GB file in one chunk: time mysql -e “set foreign_key…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
…null, index(fkto3), index(fkfrom3), foreign key (fkto3) references dos3(fkfrom1), foreign key (fkto2) references dos2(fkfrom1) ) engine=innodb character set=utf8; create table …) [oops4]> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) (root@localhost) [oops4]> alter table dos1 change …
