… 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 19 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…
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…
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…=InnoDB DEFAULT CHARSET=utf8 Here’s the result of loading the entire 4GB file in one chunk: time mysql -e “set foreign_key…: while [ -e /tmp/my-fifo ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks…
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… the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> SELECT * FROM…
Comment: How to calculate a good InnoDB log file size
June 1, 2010 By 38 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 constraint system tables created 100601 9:08:30 InnoDB: Started; log…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
… popular myth that MyISAM is faster than InnoDB in reads, as InnoDB is transactional, supports Foreign Key and has an operational overhead. As you… MyISAM / InnoDB: libexec/mysqld –no-defaults –user=root –key-buffer-size=1500M –innodb-buffer-pool-size=1500M –innodb-log-file-size=100M –innodb-thread-concurrency…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…? 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…
