…or a foreign key. This is the output of our tool when run on the same table, …# To shorten this duplicate clustered index, execute: ALTER TABLE `test`.`test` DROP INDEX `redundant`, ADD INDEX `redundant` (`col2`); # ######################################################################## # Summary…
Post: On Character Sets and Disappearing Tables
June 21, 2012 By 1 Comment
… “ALTER TABLE bar DROP j” or “ALTER TABLE bar MODIFY COLUMN j j SMALLINT NOT NULL” will produce an error unless we first remove the foreign key constraint present in table “foo”. Indeed, if we try it, that’s exactly what happens: (root@localhost) [foobar]> ALTER TABLE bar drop…
Post: Hijacking Innodb Foreign Keys
January 2, 2012 By 2 Comments
… bite – dropping foreign keys requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL… (0.00 sec) mysql> alter table parent rename parentx; Query OK, 0 rows affected (0.05 sec) mysql> drop table parentx; Query OK, 0…
Post: How to recover a single InnoDB table from a Full Backup
January 25, 2012 By 10 Comments
… must not drop, truncate or alter the schema of the table after the backup has been taken. The variable innodb_file_per_table must… 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…
Post: Logging Foreign Key errors
September 27, 2012 By Leave a Comment
… it: 1- Create the table First we create the table where we are going to store the information: CREATE TABLE foreign_key_errors ( ts… foreign key in table is “PRIMARY” See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign… the error was: mysql> ALTER TABLE employees DROP PRIMARY KEY; Conclusion If you are having too many deadlock or foreign key errors log them…
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 as if expand_fast_index_creation is OFF to avoid dropping keys that…
Comment: Thinking about running OPTIMIZE on your Innodb Table ? Stop!
February 19, 2013 By 18 Comments
… FOREIGN_KEY_CHECKS = OFF; ALTER TABLE schema.my_parent_table DROP all foreign keys … ALTER TABLE schema.my_parent_table DROP all indexes … OPTIMIZE TABLE schema.my_parent_table; ALTER TABLE schema.my_parent_table ADD back all indexes and foreign…
Comment: Duplicate indexes and redundant indexes
January 19, 2012 By 38 Comments
… === alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint ============= Below is scenario. drop table if… | +————–+————+—————–+—————–+————-+———————–+————————+ alter table emp drop index fk_deptid;ERROR 1553 (HY000): Cannot drop index ‘fk_deptid’: needed in a foreign key constraint +++Query++++ SELECT a.table_schema…
Post: Best kept MySQLDump Secret
March 23, 2012 By 13 Comments
…ALTER TABLE `C` ENABLE KEYS */; UNLOCK TABLES; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN…
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…
