…lock preventing the insertion of data to other sessions. How to troubleshoot gap locks? Is possible to detect those gap locks…locks to prevent that from happening. 2- innodb_locks_unsafe_for_binlog = 1. Disables the gap locks except for foreign-key constraint checking or duplicate-key…
Post: Best kept MySQLDump Secret
March 23, 2012 By 11 Comments
…inserted… (0.00 sec) mysql> select count(*) …LOCK TABLES `C` WRITE; /*!40000 ALTER TABLE `C` DISABLE 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…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… (111,2222), (333,444); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X… and detect orphans early so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the cost of Foreign Keys
Post: Instrumentation and the cost of Foreign Keys
September 20, 2010 By 9 Comments
…KEY (id), KEY (parent_id), CONSTRAINT child_ibfk_1 FOREIGN KEY (parent_id) REFERENCES parent (id) ) ENGINE=InnoDB; INSERT…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…amazing MySQL manual, especially the option and …insert_selectblogpercona.commanual Com_install_pluginblogpercona.commanual Com_killblogpercona.commanual Com_loadblogpercona.commanual Com_lock….commanual flush_timeblogpercona.commanual foreign_key_checksblogpercona.commanual ft_boolean_…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
…perror: [pz@sl1 ~]$ perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed What …different transactional storage engines we surely saw lock related tables being far from consistent. … produced when this limit is reached: mysql> insert into yt values (66666); ERROR 1114 (HY000):…
Post: Percona Server 5.1.59-13.0
October 16, 2011 By Leave a Comment
…has high overhead from locking rows only to …reading the rows for “INSERT“, “UPDATE“ and “DELETE“ …foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key… KEY. #851674 (Alexey Kopytov). Backported fix for MySQL…
Post: Percona Server 5.5.16-22.0
October 16, 2011 By 2 Comments
… Repositories). Based on MySQL 5.5.16, …has high overhead from locking rows only to undo… reading the rows for “INSERT“, “UPDATE“ and “DELETE“ statements…foreign key changes. This unnecessarily delays their creation in a mysqldump output, so –innodb-optimize-keys should ignore foreign key…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 30898, query… log entries 1 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…
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> INSERT…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…
