… on MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT IGNORE…) Insert the same value multiple times. As you will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1 from mutex left…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert…Select_full_joinblogpercona.commanual Select_full_range_joinblogpercona.commanual Select_rangeblogpercona.commanual Select_range_checkblogpercona.commanual Select…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…Then your script can figure out which files it can ignore. What we’re doing here starts to look …server. If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and it’… that work by either using MySQL 5.1′s row-based replication, or in MySQL 5.0 and earlier, doing …
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… mysql> insert ignore into test2 select * from test limit 200; Query OK, 100 rows affected (1.47 sec) Records: 200 Duplicates: 100 Warnings: 0 mysql> insert ignore… sec) 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 test2 select * from…
Post: STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication
February 2, 2012 By 1 Comment
…_17]$ ./master/use test -e “insert into t2 select * from t1″ master [localhost] {msandbox} (test) > SELECT COUNT(*) FROM t1; +———-+ | COUNT(*) | +———-+ | 2000 | +———-+…do not use IGNORE, be critical about your data 2) use ROW* based replication. When using the latter, MySQL will log…
Post: Improved InnoDB fast index creation
November 6, 2011 By 4 Comments
….00 sec) mysql> INSERT INTO t(c) VALUES (RAND()); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO t(c) SELECT RAND() FROM t; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 . . . mysql> INSERT… would actually make the restore slower; mysqldump –innodb-optimize-keys ignores indexes on AUTO_INCREMENT columns, because they must be indexed…
Post: Can MySQL temporary tables be made safe for statement-based replication?
May 26, 2008 By 8 Comments
…insert into test.ins select * from test.t *************************** 6. row *************************** Log_name: mysql…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… or more “CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT” statements from standard input, and outputs the… not useful, simply ignore this column. It is used to prevent wide innodb primary keys on the MV. mysql> select mview$pk as… than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select sum(total…
Post: The story of one MySQL Upgrade
October 8, 2010 By 8 Comments
…other environments. INSERT ON DUPLICATE KEY UPDATE had a unfair share of replication issues in MySQL 5.0… number of tables reporting wrong checksums while running SELECT INTO OUTFILE and diffing these files reported no… not the issue for application and could be ignored. So at this point we were sure …
Post: Replication of MEMORY (HEAP) Tables
October 15, 2010 By 6 Comments
… it specifying replicate-ignore-table=db.memory_table. Note you should not be using STATEMENT level replication with INSERT … SELECT into this memory… STOP; dump all your memory tables using MySQLDump; Restart the MySQL As planned; Load Dumped tables; run SLAVE START; Remove skip…
