Back-story: A developer came to me and wanted explanation for a weird behavior in MySQL. They inserted a record (to InnoDB table), committed, and after receiving a message (on another application) tried to read that inserted record immediately, but the newly inserted record was not found. Problem only happened in production, but not always (quite frequently).

After comparing the MySQL parameter files between production and development environments I discovered that in production autocommit was disabled to make MySQL behave more like Oracle. This setting was removed from development after we rebuilt the environment (to use multiple MySQL instances with Oracle Clusterware, instead of one large MySQL instance), but the rebuild was not yet done in production.

The default transaction level for MySQL InnoDB is REPEATABLE READ (unlike Oracle, that has READ COMMITTED as default), that means that the SELECT query always returns the …

Contrary to initial reports here and here, further investigation has revealed that under some specific and limited circumstances, Percona Server and Percona XtraDB Cluster binaries, similar to other MySQL variants, are susceptible to the security vulnerability in MySQL/MariaDB sql/password.c:

• 64bit Ubuntu Oneiric (11.10) binaries are vulnerable in Percona Server ONLY on some hardware/virtualization platforms (confirmed on Amazon EC2 but not on HP Cloud).
• Neither older nor newer Ubuntu versions are affected.
• Oneiric is not a LTS distribution. Most …

Read the original article at Road War Story – Hacking Inflight Solutions

The 2am phone call

Last summer I got my call from the president at 2am.  Actually it was my former boss at Hollywood Reporter.  I had worked there three months previous, and they had since hired an outsourced DBA solution.  Big outsource, big chops.  And big fail.

12 hours to liftoff

I was scrambling to pack my luggage to go on summer vacation.  I was bound for SF at the moment and my flight was leaving in the morning.  I was trying to wrap up loose ends and my former boss was entreating me – “Can you help us?  …

Come join the Los Angeles MySQL Users group on August 1st for a look at MySQL 5.6 and help make some pizza disappear. The presentation will start at 7PM and all are welcome. I will be speaking earlier in the day at the LA Moodle Moot and then heading down Highway 5 the next day for the San Diego Users Group.

200 N. Sepulveda Blvd.
Suite 400
El Segundo, CA 90245

Innodb can indexes built by sort since Innodb Plugin for MySQL 5.1 which is a lot faster than building them through insertion, especially for tables much larger than memory and large uncorrelated indexes you might be looking at 10x difference or more. Yet for some reason Innodb team has chosen to use very small (just 1MB) and hard coded buffer for this operation, which means almost any such index build operation has to use excessive sort merge passes significantly slowing down index built process.
Mark Callaghan and Facebook Team has fixed this in their tree back in early 2011 adding innodb_merge_sort_block_size variable and I was thinking this small patch will be merged to MySQL 5.5 promptly, yet it has not happen to date.

Here is example of gains you can expect (courtesy of Alexey Kopytov), using 1Mil rows Sysbench table.

Buffer Length   |  alter table sbtest add key(c)
1MB                      34 sec
8MB …

Oracle OpenWorld 2012

I’m lucky enough to be involved in a number of sessions across Oracle OpenWorld as well as the (new for this year) MySQL Connect session that precedes it. MySQL Connect runs on Saturday 29th and Sunday 30th September and the Oracle OpenWorld on through Thursday October 4th.

The sessions I’ll be involved with are:

• MySQL Cluster – From Zero to One Billion in Five Easy Steps: Of course it takes more than five steps to scale to more than one billion queries per minute, …

Changes (as compared to 10.0) include:

Features:
* It is now optional whether DATA, INFO and HISTORY tabs shall display in ‘upper pane’ (as in 10.0) or in ‘lower pane’ (as before 10.0). The setting is available in tools .. preferences .. general. Note that for DATA tab this applies to the ‘non-sticky’/non-persistent DATA tab. A ‘sticky’/persistent DATA tab will always display in ‘upper pane’.
* DATA, INFO and HISTORY tabs appearance will now be persistent across sessions when configured to display in upper pane. If visible when program was closed they will open when program starts next time – if hidden when program was closed they will not. When configured to display in lower pane they will always be visible.
* The checkbox to ‘select all’ as added in DATA and RESULT tab in 10.0 is now implemented all over the program, including SJA wizards.
* The …

My talk for MySQL Connect has been accepted. This is the MySQL specific 2 day conference just before the big Oracle OpenWorld and JavaOne. It is kind of a new conference by Oracle. They've had something called MySQL Sunday at this spot before, but this year it is a bit bigger and with a bigger spin on it. I'm glad it is on a weekend because otherwise I wouldn't be able to attend.

I will give the popular Evaluating MySQL High Availability Alternatives talk that I have been refining in two Percona Live conferences already. With MySQL 5.6 in the oven now, there's something new again to talk about.

read more

Introduction

In MySQL Enterprise Backup v3.7.0 (MEB 3.7.0) a new option '–incremental-base' was introduced. Using this option a user can take in incremental backup without specifying the '–start-lsn' option. Description of this option can be found here. Instead of '–start-lsn' the user can provide the location …

An Initial Review is a good way to get to know the people at Open Query and the way we work. It's a packaged 4 hour Ad-Hoc Consulting arrangement where we do the following:

1. Discuss with you whether there are any particular current concerns you'd like us to investigate.
2. Arrange remote access to your database server, optionally slaves, and possibly web servers or other related systems.
3. Take a look around on the servers, both on the OS level as well as the specific services such as MySQL, Apache/lighttpd/nginx.
4. We report back to you on our findings, and prioritise work. There might be improvements/changes that should be implemented immediately (within the initial review timeframe), and some that can be addressed later (additional ad-hoc work, or over time within a …