… 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_CHECKS…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
…: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`parent_id`)) Renaming parent table (even with foreign_ley… and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; Query OK…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 7 Comments
… recover only the “salaries” table: Discard the tablespace of the salaries table: mysql> set FOREIGN_KEY_CHECKS=0; mysql> ALTER TABLE salaries DISCARD TABLESPACE; Copy the…/ 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 salaries…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
… in ALTER TABLE are ignored to enforce uniqueness where necessary when copying the data to a temporary table; ALTER TABLE and OPTIMIZE TABLE always process tables containing foreign keys… 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 TABLE…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… NULL | | test | emp | fk_deptid | FOREIGN KEY | deptid | dept | deptid | | test | emp | PRIMARY | PRIMARY KEY | empid | NULL | NULL | +————–+————+—————–+—————–+————-+———————–+————————+ alter table emp drop index fk_deptid…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
… table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table… on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used….01 sec) mysql> CREATE TABLE child (id INT, parent_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id) REFERENCES parent(id…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…alter_db_upgradeblogpercona.commanual Com_alter_eventblogpercona.commanual Com_alter_functionblogpercona.commanual Com_alter_procedureblogpercona.commanual Com_alter_serverblogpercona.commanual Com_alter_tableblogpercona.commanual Com_alter…foreign_key…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…they need to be. Consider the following ALTER statement against the table in Approach #1: ALTER TABLE `t1` DEFAULT CHARSET=utf8, MODIFY COLUMN `c1… be using strings as foreign keys anyway …) – Allow throttling of the number of threads created (currently creates one per table) Usage: convert_charset…
Comment: Innodb locking and Foreign Keys
August 4, 2008 By 2 Comments
Do You know sth. about altering table with drop foreign key with constraint ?? Because i got only errors. Querys: ALTER TABLE `lpq` drop index `id_lpc`; ALTER TABLE `lpq` DROP FOREIGN KEY id_lpc ; ALTER TABLE `lpq` DROP Index id_lpc ; ALTER TABLE `lpq` DISABLE KEYS And so on. Only Error 150 or 1005
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… into child values(2,2) Foreign key constraint fails for table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` …tables locked by transactions. Innodb does not lock tables for normal operation so number of tables locked normally stays 0, unless it is ALTER TABLE…
