In MySQL 5.6 we have two new INFORMATION_SCHEMA tables for InnoDB that are likely going to cause confusion: INNODB_SYS_FIELDS and INNODB_SYS_COLUMNS. You may think these are likely to just be aliases of each other in order to make your life easier. However…

These are not the same thing. The INNODB_SYS_FIELDS table is all about key columns (fields) of InnoDB indexes, while INNODB_SYS_COLUMNS is about actual columns. This is even more confusing as within the MySQL source code, there is the Field set of objects that manipulate fields (columns) in a row.

Blegh. I’m glad it’s Friday.

Percona Server for MySQL version 5.6.10-60.2

Percona is glad to announce the release of Percona Server for MySQL 5.6.10-60.2 on March 14, 2013.  (Downloads are available here and from the experimental Percona Software Repositories). Based on MySQL 5.6.10, including all the bug fixes in it, Percona Server 5.6.10-60.2 is the third ALPHA release in the Percona Server 5.6 series. All of Percona‘s …

This week we discuss changed behavior of variables in MySQL 5.6. In ear candy we talk about a cloud-based service to play with SQL on different databases, and At the Movies is Stewart Smith of Percona talking about MySQL in the Cloud as a Service during Linux Conf Australia in Canberra.

Changed behavior of variables in 5.6
boolean variables can be set to ON/OFF 1/0 TRUE/FALSE

Variables with changes:
log_slave_updates

read more

Download PDF Presentation

There is a long list of new replication features in the latest MySQL 5.6 release including:

• Binary log group commit
• Multi-threaded slaves
• Crash-save slaves
• Global Transaction Identifier (GTID)
• Replication checksums
• Optimized row-based replication
• Time-delayed replication
• Remote binary log backup
• UUID

This presentation from the MySQL Tech Tour events in New York and Boston provides an overview and syntax examples of many of these features. The Effective MySQL: Replication Techniques in Depth also discusses many of these features in detail. Chapter 3 is …

I really don’t like running my database in “I Love Garbage” mode, so I set the following SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO, NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE, NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY Guess what WordPress does with that? It doesn’t install. If you set the SQL_MODE to empty and install WordPress, then restore the SQL_MODE, WordPress will run, but if you try to create a post you’ll see an error page that says “You are not allowed to edit this post.

Download PDF Presentation

At the recent MySQL Tech Tour Events in New York and Boston I gave a presentation on MySQL integration with NoSQL,Big Data and the Cloud.

This covered discussion on topics including:

• Memcached API for InnoDB
• InnoDB Online Alter
• InnoDB Full Text Search (FTS)
• Partitioning inprovements for import/export
• SSD Optimisations
• Replication Improvements
• And much more …

With the introduction of metadata locking in MySQL 5.5, it is much more common to see queries stuck in the “Waiting for table metadata lock” state.

If you see a query stuck in the “Waiting for table metadata lock” state, waiting for a MyISAM table, then it is because this table has been included as part of some uncommitted transaction (whether intentionally or not).

In fact, it could be as simple as issuing SET AUTOCOMMIT=0 followed by a SELECT against a MyISAM table (a transaction can be started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0). After that, the table will be locked to other DDL statements until the transaction is committed (or ended).

This metadata lock is meant for DDL statements, and so that is what it blocks. However, once a DDL statement is blocked and waiting, then all queries …

I am currently finishing some features to make a program highly resilient to occasional crashing bugs. A particular function was found to crash on queries of the form WHERE x IN(NULL), and that crashed the entire program. Now we have a framework for intelligently recovering from arbitrary crashes. I will write more on this in the future, because I think it’s a very interesting thing to share.

In this episode, I want to focus on a related topic: how do you test a program that is supposed to be resilient to bugs you can’t predict? Many new problems are caused by writing clever code that is supposed to detect, avoid, or recover from problems, even known problems. Unknown problems are even riskier.

The approach that has given me a great deal of confidence in the “resilience framework” is to write a “crash injection …

Yesterday I wrote about what the diffstat between MySQL 5.5 and MariaDB 5.5 was, and previously to that, about the MariaDB code size as reported by sloccount. Let’s look at MySQL 5.6.

A naive wc based “lines of code” for MySQL 5.6 sql/ directory is ~490kLOC which contasts with MySQL 5.5 being ~375kLOC by the same measure. If we diffstat the sql/ directory like I did for MariaDB 5.5 we get:

357 files changed, 172871 insertions(+), 67922 deletions(-)

Versus, as you remember from yesterday for MariaDB 5.5 over MySQL 5.5:

250 files changed, 83639 insertions(+), 23090 deletions(-)

The MySQL 5.5 to 5.6 sql/ changes line up with What I found in my post …

As we continue to test our Fractal Tree Indexing with MongoDB, I’ve been updating my benchmark infrastructure so I can compare performance, correctness, and resource utilization.  Sysbench has long been a standard for testing MySQL performance, so I created a version that is compatible with MongoDB.  You can grab my current version of Sysbench for MongoDB here.

So what exactly is Sysbench?  According to the Sysbench homepage, “Sysbench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS [Operating System] parameters that are important for a system running a database under intensive load.”

• Sysbench schema
  • 16 copies of the same collection, named sbtest1 … sbtest16, each with 10 …