…. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT, but…? 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…
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… DELETE+INSERT. But this is something you should not be doing anyway. So how can you suffer from locks originating from Foreign Keys…
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…: 1451 Message: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`t2_c1`) REFERENCES…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…=InnoDB DEFAULT CHARSET=latin1 INSERT INTO parent(parent_id) VALUES(1,”"); INSERT INTO child(parent_id) VALUES(1,”"); set foreign_key_checks=0; delete… 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: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 17 Comments
… you delete a lot of scattered rows you may not see a “free space” in Innodb tablespace to grow significantly. Q6: Does Innodb… 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: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…INNODB STATUS is good enough. As for deadlock information we have similar information about last failed foreign key constraint: ———————— LATEST FOREIGN KEY…
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… INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
…-10 | | 10008 | 52668 | 2000-03-10 | 2000-07-31 | +——–+——–+————+————+ mysql> DELETE FROM salaries WHERE emp_no=10008; The next step is… the InnoDB data we are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY…
Comment: How to calculate a good InnoDB log file size
June 1, 2010 By 33 Comments
…InnoDB: Database physically writes the file full: wait… InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key…
Comment: How to find wrong indexing with glance view
March 8, 2010 By 27 Comments
… that eventually MySQL’s InnoDB becomes close to on par with the competition…is not the case. Updates and deletes of primary key values are instead restricted – that is…id), foreign key(locale_id) references items(locale_id), foreign key(currency_id) references items(currency_id), foreign key(operator…
