Call for papers is still open for Percona Live London 2012, but only for few more days. We’re looking for great talks relevant for MySQL Ecosystem it could be about MySQL and its variants as well as technologies which are used together with MySQL. Please consider submitting if you’re actively using MySQL, Percona Server, MariaDB or Drizzle in production – the practical war stories are always very interesting. If you’re practicing MySQL consultant or other service vendor we would like to hear about your experience with multiple environments which gives unique prospective. If you’re working with MySQL or its variants on code Level and can share some prospective about internals or explain how features work in depth it would be a gem.

Do not feel intimidated. Many of the most attended …

Although not perfect, MySQL replication was probably the killer feature that made MySQL the default database for web applications some time ago. Since then, MySQL replication has been improved greatly, with such notable changes as row-based replication. At the same time, the replication engineering team has made MySQL replication more conservative and less forgiving of foot-gun errors. These have gone a long way towards helping users avoid some of the problems that made replication sometimes drift out of sync with the master copy, sometimes silently.

NSFW (audio) “…pipe your data to /dev/null – it will be very fast.” “Does /dev/null support sharding?” NSFW (audio) “…the only thing constructive we could have used their source files for was as random keys for SSL certs.” NSFW (audio) “PHP reeks … Continue reading →

A few months ago we announced the EXPLAIN Analyzer, a simple tool to help you understand how MariaDB / MySQL was running queries. For users of HeidiSQL this is now even easier. As discussed in their news post you can now send a query to the EXPLAIN analyzer with a single click.

We hope this helps both new and experienced users better understand the queries they run.

More information about the EXPLAIN Analyzer and the simple API client authors can use to add support to their apps is available in the AskMonty Knowledgebase:

• …

This blog will explain you how to use Federated tables in MySQL. Just follow the steps mentioned below to use Federated table in your MySQL server:

What is Federated Engine?

About Federated Engine in MySQL:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

Why to use Federated Engine?

Allows a user to create a table that is a local representation of a foreign (remote) table.
Real world example:

                  There are two servers-A and B. Both servers …

Businesses increasingly operate in a 24×7 environment, where complex analytics must be performed on live, continuously incoming “Big Data.” To address this, TokuDB has developed Fractal Tree®  technology, a revolutionary new indexing capability that enables SQL databases running advanced web applications to grow from gigabytes to terabytes while improving insert speed, query performance, compression, and enabling zero-downtime schema changes.

Date: September 5th
Time: 2 PM EST / 11 AM PST

REGISTER TODAY

TokuDB is used by MySQL and MariaDB customers worldwide to increase their database performance by 20x-80x on Big Data applications that conventional RDBMS’s cannot handle. Instead of waiting hours or even days to run queries …

Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key:

explain select * from tbl1
where (col1, col2) in (
      (732727758,102),(732728118,102),(732728298,102),(732728478,102),
      (732735678,102),(962074728,102),(964153098,102),(2027956818,102),
      (2034233178,102),(2034233538,102))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbl1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1379
        Extra: Using where

Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table:

explain select * from tbl1
where (col1=732727758 and …

In a very popular data loss scenario a table is dropped and empty one is created with the same name. This is because  mysqldump in many cases generates the “DROP TABLE” instruction before the “CREATE TABLE”:

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` (
  `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

If there were no subsequent CREATE TABLE the recovery would be trivial. Index_id of the PRIMARY index of table sakila/actor still resides in InnoDB dictionary although marked …

There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.

As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.

What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.

Gotcha #1 : too much noise

I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error log may have way more information than you'd like to get. …

Unfortunately, MySQL 5.5 doesn’t generate a very good query execution plan for IN() queries against a compound (multi-column) index, such as the following query that should be able to use the 2-column primary key: explain select * from tbl1 where (col1, col2) in ( (732727758,102),(732728118,102),(732728298,102),(732728478,102), (732735678,102),(962074728,102),(964153098,102),(2027956818,102), (2034233178,102),(2034233538,102))\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: tbl1 type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1379 Extra: Using where Queries such as this should usually be rewritten to a form such as the following, which accesses only the 10 rows specified instead of scanning the table: