… backup for their Innodb tables without making …WRITE; /*!40000 ALTER TABLE `C` DISABLE KEYS */; /*!40000 ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY…
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: 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 4 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…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 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 28 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: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…foreign_key…innodb_change_bufferingblogpercona.commanual innodb_checksumsblogpercona.commanual innodb_commit_concurrencyblogpercona.commanual innodb_concurrency_ticketsblogpercona.commanual innodb_data_file_pathblogpercona.commanual Innodb_data_fsyncsblogpercona.commanual innodb…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
…InnoDB requires a full table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key…
