… on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE… on AUTO-INC. We have recovered the concurrency and the performance but with a small cost. Queries like INSERT … ON DUPLICATE KEY UPDATE produce gaps on… years. You can mimic the INSERT IGNORE behaviour using a special mutex table, as explained on Baron’s blog to get rid…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…on this table, we see the following: (root@localhost) [test]> insert into update_test (username,host_id) values (‘foo’,1) on duplicate key update…KEY is always going to be considered as being defined first. So, MySQL… updated. The secondary UNIQUE on username is, for all intents and purposes, ignored….
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
… in number of other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0. There are number of… this functionality is used. Happily there are only few INSERT ON DUPLICATE KEY UPDATE query instances, and only one of them into table with…. This was not the issue for application and could be ignored. So at this point we were sure the write traffic…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… used. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also possible to fix… not-indexed UPDATE which has to lock all rows in Innodb right now) I however did not get exact plans on this… on systems with 1-2 SATA drives so they are far from optimal for server grade IO subsystems. These are for MySQL…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
…MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE…NULL AUTO_INCREMENT, `update_date` datetime NOT NULL, PRIMARY KEY (`i`), KEY `i` (`i`,`update_date`) ) ENGINE… able to duplicate the situation – running “SELECT MIN(update_time) FROM foo” on my table…
Post: Announcing Percona Server for MySQL version 5.5.29-30.0
February 26, 2013 By Leave a Comment
…MySQL version 5.5.29-30.0 on February 26th, 2013 (Downloads are available here and from the Percona Software Repositories). Based on MySQL…67504 that caused spurious duplicate key errors. Errors …on slave server while there are updates on the replicated table on… the replicate-ignore-table. Bug fixed #…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
…are updated …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…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…
Post: Conflict Avoidance with auto_increment_increment and auto_increment_offset
January 12, 2011 By 2 Comments
…MySQL Master-…updated or deleted is not found in addition to duplicate key errors, if you set slave_exec_mode=IDEMPOTENT both duplicate key error and row not found errors will be ignored… get different updates on master and …
