June 19, 2013

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

November 29, 2011 By 8 Comments

…MySQL version 5.1.22 or newer? If so, you probably have gaps in your auto-increment columns. A simple INSERT…INSERT IGNORE behaviour using a special mutex table, as explained on Baron’s blog to get… Table: foo Create Table: CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, …

Filed Under: MySQL

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

March 4, 2013 By 7 Comments

…against InnoDB on 5.6.10: mysql: SELECT id, title, MATCH(title, body… OK, now I’m starting to get a little worried. …mysql: SET GLOBAL innodb_ft_server_stopword_table=’test/innodb_ft_list2′; Query OK, 0 rows affected (0.00 sec) mysql: INSERT…

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

Post: Implications of Metadata Locking Changes in MySQL 5.5

February 1, 2013 By 9 Comments

… select * from test order by id; +—-+——+————+ | id | x | c | +—-+——+————+ | 1 | foo | dummy_text | …get piled up waiting for table metadata lock bringing down the MySQL server or causing all the connections to get…affected (0.09 sec) session1 > insert into t1 values(null); …

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

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

… (333,444); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) …_key_checks.sql | mysql -N | mysql -E *************************** 1. row *************************** test.Bar.ID: 42 DIY RI We can get information about foreign key …

Filed Under: Insight for DBAs, 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… 1 MySQL thread id 3972, OS thread handle 0x7fddb84e0700, query id 16408 localhost…mysql> commit; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction node1 mysql…

Filed Under: MySQL, XtraDB Cluster

Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80

February 25, 2013 By 10 Comments

… under $80? Introducing the Raspberry Pi with Percona Server: [caption id="attachment_13122" align="aligncenter" width="582"] Raspberry Pi Fully Connected… have your hardware, here is how to get Percona Server for MySQL up and running: Insert the SD card into a slot on… system (to prevent partial writes), take out the card and insert into your Pi. Carefull, the card goes upside down :) Plug…

Filed Under: Hardware and Storage, Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , ,

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

May 17, 2011 By 2 Comments

…,15,15,16) group by val ; +—–+——————–+ | val | count(distinct id) | +—–+——————–+ | -10 | 1 | | -3 | 1 | | -2 | 1 | | 15 | 35417088 | +—–+——————–+ 4 …0.00 sec) mysql> insert into ex2 values (2,1); Query OK, 1 row affected (0.00 sec) mysql> select a.val, …

Filed Under: MySQL

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

… names and lines (which are different in different versions), you get no information what this object is responsible for. You however… 0 sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3… size 368, undo log entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign key…

Filed Under: Insight for DBAs Tagged With:

Post: Test Drive of Solid

September 22, 2006 By 5 Comments

… insert into test2 values (1,’Mysql‘),(2,’Solid’),(3,’MyISAM’); mysql> begin; mysql> update test2 set names=’mysql‘ where id=1; Session 2: mysql> begin; mysql> …where id=2; …waiting… Session2: mysql> update test2 set names=’mysql2′ where id=1; ERROR 1213 (40001): Deadlock found when trying to get…

Filed Under: Uncategorized Tagged With: ,

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

February 26, 2013 By 1 Comment

…_DOC_ID column, however, MySQL apparently has no problem with either 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 fts_doc_id… new FULLTEXT KEY that you create, you’ll get a corresponding FTS_*_DOC_ID.ibd file (but none of the others), and…

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