… InnoDB data we are going to recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql… /var/lib/mysql/data/employees/ Import the new tablespace: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries IMPORT TABLESPACE; mysql> set FOREIGN_KEY_CHECKS=1; mysql> SELECT…
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
…mysql> select * from innodb_sys_tables; +———-+——–+——————+——+——–+——-+ | TABLE_ID | SCHEMA | NAME | FLAG | N_COLS | SPACE | +———-+——–+——————+——+——–+——-+ | 11 | | SYS_FOREIGN | 0 | 7 | 0 | | 12 | | SYS_FOREIGN…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL…commanual 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: InnoDB's gap locks
March 27, 2012 By 15 Comments
… happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key checking. The most… source of information: http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the…
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…Savepoints | +——————–+———+—————————————————————————-+————–+——+————+ | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
The MySQL manual tells us that regardless of whether or not we use “SET FOREIGN_KEY_CHECKS=0″ before making schema changes, InnoDB will… null, index(fkto3), index(fkfrom3), foreign key (fkto3) references dos3(fkfrom1), foreign key (fkto2) references dos2(fkfrom1) ) engine=innodb character set=utf8; create table dos2…
Post: Heikki Tuuri Innodb answers - Part I
October 26, 2007 By 19 Comments
… little known new features in MySQL 5.1 Q15: How frequently does Innodb fuzzy checkpointing is activated HT: InnoDB flushes about 128 dirty… 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
…key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql… 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…
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…entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key …
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 OK…), -> FOREIGN KEY (parent_id) REFERENCES parent(id) -> ON DELETE CASCADE -> ) ENGINE=INNODB; — 0 Tickets Used Query OK, 0 rows affected (0.00 sec) mysql…
