June 19, 2013

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… set (0.00 sec) node1 mysql> update autoinc set j=”node1″ where i = 1; … node3 mysql> commit; Query OK, 0 rows affected (0.00 sec) node3 mysql> select * from autoinc; +—+——-+ | i | …

Filed Under: MySQL, XtraDB Cluster

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… table with the new structure is created and then INSERT INTO new_table SELECT * FROM old_table is done and then RENAME old… well for application that use MySQL versions prior to 5.5: I remember a customer case where there is a reporting slave…=InnoDB; Query OK, 0 rows affected (0.09 sec) session1 > insert into t1 values(null); Query OK, 1 row affected (0.21…

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

Post: Using Flexviews - part two, change data capture

March 25, 2011 By 26 Comments

… automatically be captured. Insert data in one transaction (two rows): mysql> insert into test.demo values…in a second transaction: mysql> delete from test.demo where c1=1; Query OK…the source table is `test`.`demo`. mysql> select * from flexviews.test_demo\G *************************** 1. row …

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

Post: Beware of MySQL Data Truncation

February 7, 2009 By 4 Comments

…mysql> insert into article_comment values(4300000002,1); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select…

Filed Under: Insight for DBAs Tagged With:

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…’t appear to be mentioned anywhere in the MySQL 5.6 documentation; every place where it talks about creating one of these…

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

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

… select * from compressed join search_set using (val) where compressed.cnt >= search_set.cnt; mysql> select * from compressed join search_set using (val) where compressed… set (0.00 sec) mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select a.val, b.val…

Filed Under: MySQL

Post: How expensive is a WHERE clause in MySQL?

October 31, 2008 By 17 Comments

… NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into t(a) values(current_date); insert into t select * from t; I repeated the last statement…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a, 10) = ’2008…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

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

August 30, 2012 By 2 Comments

…) mysql> explain select count(*) from trunc where i=4147483647; +—-+————-+——-+——+—————+——+———+——-+——-+————————–+ | id | select_type | table …inserts into one table would happen with correct value, such as 3000000000, while inserting…

Filed Under: Insight for DBAs, MySQL

Post: SELECT LOCK IN SHARE MODE and FOR UPDATE

August 6, 2006 By 51 Comments

… different from normal SELECT statements. Here is simple example: SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst values(1); Query OK, 1 row affected (0.00 sec) SESSION2: mysql> begin ; Query OK, 0… non matched rows Imagine you’re running DELETE FROM USERS WHERE NAME LIKE “%Heikki%”; How any rows do you think will…

Filed Under: Insight for Developers Tagged With:

Post: On Character Sets and Disappearing Tables

June 21, 2012 By 1 Comment

…dos3 | +—————–+ 2 rows in set (0.00 sec) “Oops” indeed. Where we once had three tables, there are …this: CREATE TABLE recovery LIKE `#sql2-3c13-14`; INSERT INTO recovery SELECT * FROM `#sql2-3c13-14`; But your …’s something of an inconsistency present when MySQL won’t allow us to change an …

Filed Under: Insight for DBAs, MySQL
« Older Posts
Newer Posts »