June 18, 2013

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… 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…

Filed Under: MySQL

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…

Filed Under: Insight for DBAs, MySQL

Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

July 25, 2012 By 6 Comments

…mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql> select * from percona; +—-+—————+——–+ | id | inserted…

Filed Under: MySQL, XtraDB Cluster

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

… considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc … try an INSERT … ODKU? First, recall what’s in our table: (root@localhost) [test]> select * from update_test; … username is, for all intents and purposes, ignored. For the customer whose database served as …

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…) mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT INTO innodb_ft_list2 SELECT * FROM innodb_myisam_stopword; Query OK, 543 rows… the data and building the word list, it’s completely ignoring Mr. Smith’s middle initial. To test this hypothesis, I…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: pt-online-schema-change and default values

June 21, 2012 By 1 Comment

When I’m doing conventional ALTER TABLE in MySQL I can ignore default value and it will be assigned based on the… a MySQL error 1364: Level: Warning Code: 1364 Message: Field ‘v’ doesn’t have a default value Query: INSERT LOW_PRIORITY IGNORE INTO `sbtest`.`_sbtest_new` (`id`, `k`, `c`, `pad`) SELECT `id`, `k`, `c`, `pad` FROM…

Filed Under: Insight for DBAs, MySQL

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 …

Filed Under: Insight for Developers Tagged With: , , , ,

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 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…

Filed Under: Insight for DBAs Tagged With:

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…

Filed Under: Insight for DBAs

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…

Filed Under: Benchmarks, Insight for DBAs, MySQL
« Older Posts