…): mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD… a foreign key, and it has the exact column definition in the same order. In other words, it checks for duplicate keys, but…, similar to pt-duplicate-key-checker, but it does not detect all cases. For example: mysql> alter table test add index redundant (col2, id…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… back to HardCore MySQL business – foreign Keys. MySQL supported Foreign Keys for Innodb for many years, yet rudimentary support initially added in MySQL 3.23.44 have… mysql> update child set parent_id=1 and v=’BBB’; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint… proper type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; Query…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…-transaction dumptest > dump.sql SESSION1: (before dump has completed) mysql> alter table C add i int not null; Query OK, 1 row affected… 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…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…sec) mysql> ALTER TABLE t ADD KEY (c), ADD KEY(c); Query OK, 0 rows affected (36.42 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER …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 after…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
… on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used…: 0 mysql> DELETE FROM child WHERE 1; — 6 Tickets Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY…
Post: Converting Character Sets
March 17, 2009 By 22 Comments
…OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE `t1` CONVERT TO CHARACTER SET …Add FULLTEXT indexes back For those of us routinely waiting hours, if not days, for a single alter…handling of string foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys anyway …) …
Post: Using innodb_sys_tables and innodb_sys_indexes
June 15, 2012 By Leave a Comment
…ALTER TABLE such as: “alter table sbtest add z int not null;” which I expected would create table with primary key…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key constraint fails for table `test/child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE Trying to add…unless it is ALTER TABLE or …
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 13 Comments
…traditional database background. > Things are different with MySQL. Good design will help dictate how … benefits – your data > becomes managable, your ALTER TABLE or OPTIMIZE TABLE > now locks …deciding when / how to create indexes. Foreign key references may add processing time to inserts, updates, …
