June 18, 2013

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

November 29, 2011 By 8 Comments

… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join foo… 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…

Filed Under: MySQL

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

October 27, 2012 By 6 Comments

…first. So, MySQL checks our INSERT, sees …insert a row at the end of the auto-increment range, and then try to insert another one: (root@localhost) [test]> insert into…INSERT … ODKU? First, recall what’s in our table: (root@localhost) [test]> select * from… INSERT … ODKU on a table with multiple …

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

Post: Wow. My 6 year old MySQL Bug is finally fixed in MySQL 5.6

August 30, 2012 By 2 Comments

… 2147483647) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table | …multiple table and the column type was inconsistent between them… so inserts into…

Filed Under: Insight for DBAs, MySQL

Post: Recovering Innodb table Corruption

July 4, 2008 By 22 Comments

… can you do to recover such a table ? There are multiple things which can get corrupted and I will be looking… next comes trial and error approach: mysql> insert into test2 select * from test; ERROR 2013 (HY000): Lost connection to MySQL server during query You may think…

Filed Under: Insight for DBAs Tagged With:

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… of the following statements: mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, ‘john smith’); mysql> select * from dir_test_innodb3 where… and dropping FT indexes, it’s entirely possible to DROP multiple FT indexes with InnoDB in the same ALTER TABLE statement…

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

Post: Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

…from all the nodes. Since we are projecting results, this is naturally an INSERT-only workload. The insertions into the base table from…multiple levels of result set reduction. Finally, Shard-Query can automatically partition sets into…selected…

Filed Under: MySQL

Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks

August 17, 2012 By 17 Comments

…mysql> insert into autoinc (j) values (‘node2′ ); Query OK, 1 row affected (0.00 sec) node2 mysql> insert into… mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+ | i | j …problem?  Really whenever transactions across multiple nodes can take the same …

Filed Under: MySQL, XtraDB Cluster

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take about…mysql> create table test_concat_part like test_concat; Query OK, 0 rows affected (0.01 sec) mysql> insert into…you can concatenate MyISAM files, even when multiple keys are defined. Not for …

Filed Under: MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…multiple transactions. After each transaction it must wait for FlexCDC to process changes from…from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT” statements from…

Filed Under: Insight for DBAs, MySQL

Post: Moving Subtrees in Closure Table Hierarchies

February 14, 2011 By 18 Comments

…INSERT INTO TreePaths (ancestor, descendant, length) SELECT t.ancestor, ‘D’, t.length+1 FROM TreePaths AS t WHERE t.descendant = ‘C’ UNION ALL SELECT…insert a subtree, you’re inserting multiple…

Filed Under: Insight for Developers, MySQL
« Older Posts