I have found myself using UNION in MySQL more and more lately. In this example, I am using it to speed up queries that are using IN clauses. MySQL handles the IN clause like a big OR operation. Recently, I created what looks like a very crazy query using UNION, that in fact helped our MySQL servers perform much better.

With any technology you use, you have to ask yourself, "What is this tech good at doing?" For me, MySQL has always been excelent at running lots of small queries that use primary, unique, or well defined covering indexes. I guess most databases are good at that. Perhaps that is the bare minimum for any database. MySQL seems to excel at doing this however. We had a query that looked like this:

select category_id, count(*) from some_table

where

    article_id in (1,2,3,4,5,6,7,8,9) and

    category_id in (11,22,33,44,55,66,77,88,99) and

    some_date_time > now() - interval 30 day
 …

This comes from an issue that I worked on recently, wherein a customer reported that their application was working fine under stock MySQL 5.6 but producing erroneous results when they tried running it on Amazon RDS 5.6. They had a table which, on the working server, contained two TIMESTAMP columns, one which defaulted to CURRENT_TIMESTAMP and the other which defaulted to ’0000-00-00 00:00:00′, like so:

CREATE TABLE mysql56 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ts2 TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
);

However, under Amazon RDS, the same table looked like this:

CREATE TABLE rds56 ( 
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  ts1 TIMESTAMP NULL DEFAULT NULL,
  ts2 TIMESTAMP NULL DEFAULT NULL, 
);

They mentioned that their schema contains TIMESTAMP column definitions without any modifiers for nullability or …

A few weeks ago we announced the availability of the MySQL Central @ OpenWorld content catalog.

We're now pleased to announce additional keynotes delivered by MySQL power users. They will follow the "State of The Dolphin" keynote address by Oracle's Chief Corporate Architect Edward Screven and VP of MySQL Engineering Tomas Ulin, and include:

High Speed Event Logging at Booking.com
Nicolai Plum, Senior Systems Architect, will provide an overview of Booking.com's MySQL-based event logging application, recording data from all customer interactions in order to develop the best customer …

While there are many graphing tools out there and we’ve used Munin for a while now.

The MySQL plugin for Munin had fallen out of date and the show engine innodb status output changed in 5.5 making some bits of the plugin simply not work any more. Also the show global status has some extra variables so there was a need to create new graphs.

All of these are now in the 2.1.8+ development releases of Munin.

Here are samples of the new/updated graphs.

Tables

Table …

It was a year ago, on a nice Sunday night of the English Summer (apologies for the oxymoron), that Mark Riddoch came to see me and together we headed to the Vansittart Arms, our local family pub round the corner. A pint of London Pride on one side and a Honey Dew on the other were the perfect add-on to Mark’s MacBook Pro, on which Mark was showing me the 0.1 version of MaxScale. It was the result of the joint efforts of Mark’s team, Massimiliano and Vilho, who had worked hard to bring to life the first version of something that I believe will be a natural addition to clusters of MySQL/Percona/MariaDB servers in the near future.

A year ago, Mark showed me a …

With an ear to the interwebs, you’ll hear a few things about Docker. Docker is an open platform for developers and sysadmins to build, ship, and run distributed applications. In this blog post, I’m going to outline a very basic example of how to use Sphinx from within a Docker container. What is Docker? This [...]

Yay! My first blog post! As long as at least 1 person finds it useful, I’ve done my job. Recently, one of my long-term clients was noticing that while their INSERTs were succeeding, a particular column counter was not incrementing. A quick investigation determined the column was of type int(11) and they had reached the maximum value of 2147483647. We fixed this by using pt-online-schema-change to change the column to int(10) unsigned, thus allowing values up to 4294967295.

My client was now concerned about all his other integer-based columns and wanted me to check them all. So I wrote a quick-n-dirty script in Go to check all integer-based columns on their current value compared to the maximum allowed for that column type.

You …

MySQL Cluster enables high availability by storing data replicas on multiple hosts. MySQL Cluster maintains connections between data nodes by using high-speed interconnects over TCP/IP - standard or direct connections - or SCI (Scalable Coherent Interface) sockets.

To learn more about MySQL Cluster, take the MySQL Cluster training course.

This course is currently scheduled for the following locations:

After MySQL 5.1 was released work started on MySQL 5.2, but then this was renamed to MySQL 6.0. There were many new features introduced in 6.0. But then stabilizing this branch became as huge task. Eventually the decision was made to start of with a stable branch and backport the new features from 6.0. This is how many of the 6.0 features landed in 5.5 and 5.6.

So let's see which features 6.0 brought and were they landed. I'll use the What Is New in MySQL 6.0 section of the MySQL 6.0 Reference Manual for this.

• The Falcon storage engine. This never landed anywhere as far as I know. It's not even included in the …

Critical MySQL 5.6 bug: any user with GRANT privileges can unwillingly cause all replicas to break

The latest major release of MySQL brought us a lot of new and exciting features. As always, new features come with brand new bugs waiting to bite you in the least expected way.

I was implementing a monitoring system for a client and needed to create a new dedicated user. I went ahead and granted the usual basic privileges but later, I discovered that this user also needed the REPLICATION CLIENT privilege because the monitoring needed it to check for replication status.

No big deal, let’s grant this as well. Unfortunately I did not get it right at first:

grant replication client on database.* to user@’10.%';
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

Ouch! My bad! This is a global privilege and I should grant it on “*.*”:

grant replication client on *.* to …