As a tool author, I really look forward to working with MySQL 5.6. Many of the improvements will make life significantly easier for Percona Toolkit.

One illustration of this is in figuring out what the optimizer is doing when it plans a statement, and how it intends to use indexes. Compound indexes present challenges in some situations. Many of our tools have extensive checks to try to avoid executing queries that have bad execution plans. If the optimizer intends to use only a few of the columns in an index, how will we know?

Of course I have answers for this, but they aren’t as simple as they seem at first glance. The obvious method is to look at key_len in EXPLAIN. But what is the length of the full index? That’s a tricky thing to figure out. The simple case is always easy, but character sets, nullability, and so on make it much harder.

Having a lot more information about the process the planner uses to choose an …

A few days ago Sergei Golubchik of Monty Program sent an e-mail to the Open Source Security mailing list informing about a security vulnerability in MySQL authentication system. Under certain circumstances a remote attacker may easily gain access to MySQL database as any user and all they need to know is a valid user name (e.g. root user exists in nearly all installations). The problem has only been addressed in the most recent database versions.

The full details are covered in Sergei’s post linked above. Not all MySQL releases are affected as the cause appears to be related to the build environment and the options used in the binary build process. For instance binaries distributed by Oracle appear to be safe as well as those available from RedHat’s repository.

We encourage you to test this against your database …

We are as busy as ever at the Mozilla DB team. We are currently in the middle of conference season, and in the past two weeks I have spoken at OUG Harmony in Finland and at the SouthEast LinuxFest in Charlotte, NC. Here are the PDF slides from my presentations (video will be coming sometime later on):

• MySQL Security
  
• Optimizing MySQL JOINs and Subqueries
  
• Intermediate MySQL Administration
  
• …

“In short, if you try to authenticate to a MySQL server affected by this flaw, there is a chance it will accept your password even if the wrong one was supplied. The following one-liner in bash will provide access to an affected MySQL server as the root user account, without actually knowing the password.”

$ for i in `seq 1 1000`; do mysql -u root --password=bad -h 127.0.0.1 2>/dev/null; done
mysql>

The following are confirmed distributions that are vulnerable:

• Ubuntu Linux 64-bit ( 10.04, 10.10, 11.04, 11.10, 12.04 ) ( via many including @michealc )
• OpenSuSE 12.1 64-bit MySQL 5.5.23-log ( via @michealc )
• Debian Unstable 64-bit 5.5.23-2 ( via @derickr )
• Fedora ( via hexed and confirmed by Red Hat )
• Arch Linux (unspecified version)

Full details can be found at …

Oracle is the official provider of MySQL Training.

To learn more about MySQL Cluster, you can register for the MySQL Cluster training at a large selection of locations and often you will find the course delivery in your local language! For example:

Modern file systems are well equipped to deal with large writes. One area that remains challenging however is to efficiently write out “microdata”, such as metadata and small portions of large files, while showing good I/O utilization when the data is read back. This challenge is evident with mount options like “noatime” which disables updating file access time on reads. This kind of solution avoids the problem altogether. Another approach, delayed allocation, is meant to coalesce small writes in memory as long as possible before writing it out to disk. Filesystems like ext4 and Btrfs use delayed allocation to make a best-effort at reducing fragmentation and random I/O.

Isn’t there a way to fundamentally solve filesystem fragmentation and random I/O?

This week, I’ll be speaking at HotStorage 2012 in Boston. My talk …

I found time to continue my previous work on group commit for the binary log in MariaDB.

In current code, a (group) commit to InnoDB does not less than three fsync() calls:

1. Once during InnoDB prepare, to make sure we can recover the transaction in InnoDB if we crash after writing it to the binlog.
2. Once after binlog write, to make sure we have the transaction in the binlog before we irrevocably commit it in InnoDB.
3. Once during InnoDB commit, to make sure we no longer need to scan …

As a tool author, I really look forward to working with MySQL 5.6. Many of the improvements will make life significantly easier for Percona Toolkit. One illustration of this is in figuring out what the optimizer is doing when it plans a statement, and how it intends to use indexes. Compound indexes present challenges in some situations. Many of our tools have extensive checks to try to avoid executing queries that have bad execution plans.

The SQL that Mondrian generates is, until now, different than the SQL that most people would write by hand. Most people don't use spaces or punctuation in table and column names, and don't enclose identifiers in quotation marks when writing SQL DDL, DML or queries. Mondrian, on the other hand, religiously quotes every identifier, whether it needs it or not.

The two styles are not compatible because on many databases (Oracle is one example) unquoted identifiers are implicitly converted to upper-case. If you use lower-case table and column names in Mondrian's schema, they will not match the upper-case identifiers created during DDL.

For instance, if you create a table in Oracle using

CREATE TABLE emp (
  empno INTEGER,
  ename VARCHAR2(30),
  deptno INTEGER);

then Oracle creates a table called EMP with columns EMPNO, ENAME and DEPTNO. When you query it …

My colleague who is a Windows guru taught me how to do very basic things with the Windows profiler. While this stuff is primitive, the sad truth is that many people don’t know even this little about Windows, me including. I’ll keep the hints here, and hopefully will learn more with time.

• Use RelWithDebInfo builds, Debug build will screw the picture, it will tell you _db_enter is the most expensive function;

• Start mysqld;

• Use x64 VS command prompt:

Start => All Programs => MS Visual Studio 2010 => Visual Studio Tools => Visual Studio x64 Win64 command prompt

• Set _NT_SYMBOL_PATH to the package bin directory, so mysqld.pdb can be found, and start the IDE:

set _NT_SYMBOL_PATH=... && devenv.exe

• In the VS command prompt, run

vsperfcmd /start:sample …