… tables on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…MySQL manual, especially the…ansiblogpercona.commanual auto_increment_incrementblogpercona.commanual auto_increment_offsetblogpercona….insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert…commanual replicate_ignore_dbblogpercona.commanual replicate_ignore_…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…insert a row at the end of the auto-increment range, and then try to insert another one: (root@localhost) [test]> insert…auto-increment max value, which is the one that got updated. The secondary UNIQUE on username is, for all intents and purposes, ignored…
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… others due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be sequential. To… auto increment values are assigned but never used. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL…
Post: Conflict Avoidance with auto_increment_increment and auto_increment_offset
January 12, 2011 By 2 Comments
… people are running MySQL Master-Master replication pairs in Active-Passive mode…happen to insert data to 2 masters at once. This can be solved with auto_increment_increment and auto_increment_offset, however…error and row not found errors will be ignored. Strict Mode with row level replication will …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
… NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (`id`) -> ) ENGINE=MYISAM; Query OK, 0 rows affected (0.03 sec) mysql> insert into test2 select *…) mysql> insert ignore into test2 select * from test where id>250; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> insert ignore into…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…create(‘demo’, ‘dashboard_customer_sales’, ‘INCREMENTAL‘); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid… special column `mview$pk` which is an auto_increment BIGINT surrogate key for the table. For… ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql>…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…t1` ( `t1_c1` int(10) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`t1_c1`) ) ENGINE=InnoDB; CREATE TABLE `…_5_5_17]$ ./master/use test -e “insert into t2 select * from t1″ master [localhost] {msandbox} …IGNORE, be critical about your data 2) use ROW* based replication. When using the latter, MySQL…
Post: When is MIN(DATE) != MIN(DATE) ?
September 4, 2012 By 5 Comments
… like this: CREATE TABLE foo ( id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, uid INT UNSIGNED NOT NULL, update_time DATETIME… force MySQL to use a different index. Imagine our surprise when we tried a FORCE INDEX on (bar) or an IGNORE INDEX… NULL AUTO_INCREMENT, `update_date` datetime NOT NULL, PRIMARY KEY (`i`), KEY `i` (`i`,`update_date`) ) ENGINE=InnoDB and then inserted a few…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…(day, ad). Don’t use an auto-increment primary key, and don’t put ad … can figure out which files it can ignore. What we’re doing here …to-scale database server for the initial insertion, because you can write CSV files with…by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and …
