…indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild. In …of proper type and indexed): mysql> set foreign_key_checks=0; Query OK, 0 rows affected (0.00 sec) mysql> alter table parent rename parentx; …
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…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…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the option and variable reference table…foreign_key…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 4 Comments
…, a table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table ADD… now on to a more interesting scenario: foreign keys mysql> CREATE TABLE parent (id INT NOT NULL, -> PRIMARY KEY (id) -> ) ENGINE=INNODB; — 0 Tickets Used…
Post: Converting Character Sets
March 17, 2009 By 16 Comments
…TABLE `t1` ( ->Â Â `c1` text NOT NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1; Query OK, 0 rows affected (0.02 sec) mysql> ALTER TABLE…foreign keys (currently fails, but you probably shouldn’t be using strings as foreign keys…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key…keys, omitting key specifications and then adding them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… on MySQL 5….table rebuild for foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key…keys, omitting key specifications and then adding them back in ALTER TABLE doesn’t work for them. mysqldump –innodb-optimize-keys…
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…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…
Comment: Database problems in MySQL/PHP Applications
August 11, 2006 By 12 Comments
… managable, your ALTER TABLE or OPTIMIZE TABLE > now locks small table for few seconds rather than giant 100GB table > for… more in my > Why MySQL Could be slow with Large Tables article. Deciding whether or not… foreign key references on the slave(s) as long as you are certain that all the changes…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
…from the load — but from other transactions’ changes too; the purge thread cannot purge …. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way…mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘infile.txt’ into table…
