…? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec 4 lock struct(s… happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most… information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
… use mysqldump –single-transaction to get consistent backup for their Innodb tables without making database read only. In most cases it… 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…
Comment: How to recover a single InnoDB table from a Full Backup
April 11, 2012 By 7 Comments
Hi, consider this comment about foreign key and transportable tablespaces : “To export tables that are in a foreign key relationship you must export and import all the tables together” Source : http://blogs.innodb.com/wp/2012/04/innodb-transportable-tablespaces/
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… blog readers ! Now back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in… 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: Innodb locking and Foreign Keys
December 12, 2006 By 2 Comments
… was working with application which uses Innodb and foreign keys and got into locking problems possibly due to foreign keys, so I did a little… to 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… be performed with row lock performed. Innodb is smart enough to detect which updates are affecting foreign key relationships. So if you will…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
… KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `t2` ( `t2_c1` int(10) unsigned NOT NULL, PRIMARY KEY (`t2_c1`), CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES `t1` (`t1_c1`) ON UPDATE CASCADE ) ENGINE=InnoDB; [revin@forge…
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
… discussions with students about whether or not to use Foreign Key constraints on InnoDB tables. My standard response has always been: “it…_column char(32), PRIMARY KEY (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ) ENGINE=InnoDB; INSERT INTO parent…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… different CPUs. Q25: In InnoDB, the referenced columns of a foreign key constraint need not form a primary key or a unique constraint: it… that suppors foreign key constraints)? Any concrete examples are appreciated. HT: Yes, it is sufficient that a foreign key and the referenced key appear as…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… foreign keys as if expand_fast_index_creation is OFF to 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…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… from SHOW INNODB STATUS is good enough. As for deadlock information we have similar information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY ERROR… – ;; 2: len 7; hex 80009c40050084; asc @ ;; Innodb will print statement which caused error. Definition of foreign key which failed as well as the…
