Friday May 21, 2010

MySQL 5.1.47 and 5.0.91 released - Two strong reasons to upgrade

MySQL security MySQL has released security updates for MySQL 5.1.47 and 5.0.91. The most important changes in these releases are fixes of three security bugs. One of them is a problem that had been lurking in the code for many years, and it was found by chance when one of our developers, testing something unrelated, stumbled upon one of the vulnerabilities. Later on, when analyzing the bug, the developers found one more issue, and they fixed it as well.

MySQL 5.1.47

In addition to the security update, MySQL 5.1.47 is also very important for an additional reason. The InnoDB plugin that ships with this version has been updated to 1.0.8, which is considered to be of General Availability (GA) quality.

There are more changes, including some twists to the error log, to make replication administration more robust.

MySQL 5.0.91 security update

Together with MySQl 5.1.47, there is a security update of MySQL 5.0.91.

Since MySQL 5.0 is now in Extended Support state, the binaries are not in the main download pages, but only in the archives. As the MySQL Lifecycle Policy says, only serious security bugs are fixed, and the binaries are provided at the company's discretion.

If you are still using MySQL 5.0, this is a good moment to upgrade to 5.1.

Wednesday Mar 18, 2009

How MySQL tests server binaries before a release

What happens when the binary files of a fresh MySQL version is published on the web?

You may have noticed that the date on the release notes is not the same as the date the downloads are available. Sometimes there is a two weeks gap, sometimes more. Many people in the community have asked what is going on in the meantime.

The answer is a lot of hard work. The code is built for all the operating systems supported by MySQL, and tested in each platform (1). During this process, portability problems, test case glitches, and other things not caught in the normal daily build and test are fixed.

This task involves QA engineers, Build engineers, the Maintenance team, with help and cooperation from the Services, Development, and Community teams.

I asked our Build and QA Teams to tell what happens between the date a release is "branched off" from the Bazaar tree and the date it is available in the downloads page. This is the list of what goes on. It's very impressive that the regression test suite, which looks huge and intimidating to the casual user, is just a tiny bit compared to the rest of the torture tests that the server goes through.

BTW, this is just the tip of the iceberg. QA is a continuous process, not just a set of tests at the end. Sun/MySQL uses a continuous build and test process triggered on code check in, on different products and branches, in total 574 build and test runs a day (more about that in a separate article). The release process adds to that some more testing.

Thanks, MySQL engineers!

The following text was provided by Kent Boortz, Senior Production Engineer in the Build Team.

Tests run during the build process

During the build all packages was tested using the regression test suites. These tests are mainly testing SQL features, from basic queries to replication and partitioning, but also the “mysql” command line client, the “mysqldump” tool and other client tools.

The server can be run in different “modes”, SQL standards and protocols. Because of this, the same test suite is run several times with different combinations of these modes and protocols. As there are time limits not all combinations are run, but what is believed to be a fair sample of combinations to catch regressions. The test runs for each package are

  1. The main suite, except replication tests, was run against the bundled debug enabled server
  2. The main suite was run against the server in default protocol mode
  3. The main suite was run against the server using prepared statements protocol mode
  4. The main suite was run against the embedded server library
  5. The 'funcs_1' suite was run against the server using prepared statements protocol mode
  6. The 'stress' suite was run against the server in default protocol mode

There are some suites not run against all packages, or was disabled

  1. The 'jp' suite was run against the server in normal mode, but not against the Windows server (this is a bug the suite was not run, there is no good reason to skip this)
  2. The 'funcs_2' test suite was not run, disabled because of Bug#20447
  3. The 'nist' test was run using both normal and prepared statements protocol, in all builds but the RPM builds

Tests run after the release binaries are built

Some package tests are run after the packages are built, not in direct connection to the build process.

  1. The package names were verified
  2. RPM packages were checked for unwanted dependencies, like the IA-64 RPMs build using the Intel icc compiler, verified that no icc runtime libraries are needed to install
  3. A simple link test was done against the client and embedded libraries, to try catch missing symbols, missing headers or defects in the output of the “mysql_config” script, that defines the usage

Install Test

The binaries are installed and basic smoke test was done on all supported platforms to catch any install related issues. Testing on some of the platforms was done using automated scripts while on the others it was done manually.

System Test Suite

This is a concurrency/longevity test and can be configured to run with with different storage engines, concurrent users and time.

This test suite contains a number of of Insert/Update/Delete and Selects tests using stored procedures and triggers. The test also has an aspect of Integration testing, since it uses Events, Partitions, etc together and covers scenarios for various storage engines like testing MyISAM and Innodb together, e.g. a trigger on the Innodb table writes into a MyISAM table.

We tested the scenarios that contain inserts, updates, deletes and selects using stored procedures and triggers for innodb and myisam tables, separately. Each of these tests were run for a period of 24 hours with 50 concurrent users


This is also a concurrency/longevity test and executes Insert/Update/Delete scenarios using stored procedures and triggers but with replication. We tested 3 different scenarios one each for replication types RBR, SBR and MBR, Each of these tests were run with 100 concurrent users for 6 each hours with Innodb tables

High Concurrency

This is Concurrency/Longevity/Stress testing and executes an OLTP scenario on one table. The concurrency and the time period can be configured. We ran this test on Linux platform , with 2000 concurrent users over a period of 8 hours using InnoDB table


This is a single user performance benchmark and tests various scenarios, providing one specific angle to performance benchmark. With this test we catch \*performance results / regressions per operation\* (Sometimes "operation" = one SQL query, but often this is block of statements/queries). We ran this test using Prepared statements with MyISAM and InnoDB tables separately, Each test was run 3 times and the average time computed for each operation. The resulting performance numbers were compared with previous versions of MySQL server for identifying regressions..

DBT-2 Benchmark (TPC-C)

This toolkit implements the TPC-C benchmark standard, measuring the performance for OLTP scenario by using the New order transactions per minute (NOTPM). The tests are completely configurable. We ran the tests for CPU bound analysis with 16, 40 and 80 concurrent users with InnoDB tables and then compared the performance with both 5.1.30 and 5.0 version to detect performance regressions on SUSE platform.

Upgrade/Downgrade testing

Executes upgrade/downgrade scenario of the MySQL server, checking that objects can be created/altered/dropped and data can be inserted/updated/deleted/selected between the previous and current versions of MySQL. Objects include Permissions, Tables,Views,Triggers, Stored Procedures, events and partition and a large number of datatypes. Both live and dump/dump scenarios are tested.

We tested minor version upgrades from pervious versions of 5.1 to 5.1.31 and also major version upgrades 5.0.x to 5.1.31


We use the Sysbench tests to measure database server performance (OLTP benchmark) and compare this against the performance of previous mysql server versions.

We ran the OLTP_RO, OLTP_RW and also the atomic queries using InnoDB and MyISAM tables for 4, 16, 64, 128, 256 threads

Large dataset testing

This test suite runs insert/update/delete/select scenarios using indexes against a very large database containing up to 1 billion rows in the tables. We try to uncover areas where Query Analyser or Optimizer perhaps is not using the right approach. Large datasets provide clearer results on if internal query analysis & optimization has been done correctly.

Tests Using Random Query Generator

Additionally, replication tests were run in the 5.1 replication team tree using Random Query Generator tool. These tests were run on myisam tables, with all replication modes, with a "simple" workloads (DML only) and a "complex" workload (DML, DDL, implicit commit, other statements interesting for replication). Few bugs were found and fixed during this process.

How we deal with bugs during the release phase

As we use the "release often" process for enterprise releases, a bug found during the release process might not cause the process to stop to fix that bug, and rebuild it all.

If considered serious enough it will, but in some cases a bug is filed and the correction is targeted for the next or a later "maintenance" release (or what they are named).

Over time we will get better and better at test automation, preventing these kind of bugs to sneak in. But at this time it might, and means a maintenance release could be a regression to an unlucky user hit by that specific bug.

Having said that, it is rare that we find a new bug during the release process that is ignored, but it could happen, as said a side effect of the "release often" release process.

(1) Not all tests are run in all platforms. Some performance tests are only run on the most popular ones, to compare performance across versions.

Sunday Feb 08, 2009

FOSDEM 2009 - Boosting performance with MySQL 5.1 partitions

The MySQL Developers Room at FOSDEM was a success. There was a full room (about 80 seats, and many sitting on the floor) throughout the day. We started at 9am with a talk on PBXT, and ended at 17:00. My talk had a large audience and was very well received.

FOSDEM, the Free and Open Source Software Developers' European Meeting

The enthusiasm was quite high also because of a stunt I did during the presentation. It was a creative way of explaining the difference between physical and logical partitioning. I will repeat this stunt at the MySQL Users Conference, during my Tutorial on Partitioning.
Those who were attending today know about it. For the attendees at the Users Conference it will be a surprise.

The high point of the conference was at 1:15pm, with Kaj Q&A session, where we collected quite a lot of feedback.

For the many enthusiasts who followed my presentation, here are the slides.

Thanks to all participants for their stamina. Thanks to Lenz Grimmer for making this possible, and for the FOSDEM organizers for their kind hospitality.

Monday Dec 08, 2008

What's going on in MySQL land?

Enough of one-sided stories. Let's see a different angle of MySQL 5.1.

First, let me thank my colleague Chris Powers for taking a stand in defense of the management. But saying "everyone does so" is not a good explanation. The truth is much more complex and requires some narrative.

MySQL 5.1 didn't start on the right foot. The effort to produce its features was underestimated, mostly because, at the time when it was designed, the company was still unearthing the architectural bugs that were haunting MySQL 5.0.

MySQL 5.0 was GA in October 2005. One month later, MySQL 5.1 started its alpha stage, while a rain of bugs fell on the freshly released server. When the version was hastily declared beta 6 months later, the implication of the architectural problems weren't even found yet. That's why the beta stage of this version had a long and troubled course.

In September 2007, when 5.1 was declared RC, there were really too many open bugs and instability problems. The decision of going RC had little engineering justification. A fierce internal debate followed, with a prompt decision to review the release criteria, which kept many people busy for several weeks. The desire of shipping 5.1 GA before the Users Conference 2008 was rightfully dwarfed by the discovery of new, more disturbing bugs. Two more RCs were released, while the developers fought to fix a staggering number of bugs.

More than 3500 bugs affected MySQL 5.1, and by June we had fixed 2300 of them. There were still some outstanding critical bugs, and Marketing and Sales were pressing for a release. It was understandable. The economic situation of Sun was not good, the company had just cut 2500 jobs, and we needed the new release to boost sales. However, the outstanding bugs were so bad that the people who were in direct contact with users (Support and Community Team) strongly objected to a GA declaration at that point in time. In a joint effort, we identified 40 critical bugs that needed fixing before going GA. The position of the Support team was clear. They were not going to support row-based replication and partitioning, unless these bugs were fixed. The offending bugs were examined by a committee chaired by Sun Software CTO, and 35 bugs were recommended for immediate fixing.

You can see the result in the changelog. MySQL 5.1.27, which was the intended GA, was not released. Version 5.1.28 fixed 79 bugs, and version 5.1.29 fixed 58 more, and 8 fixes went into 5.1.30. The critical bugs identified by Support as the basis for supporting the new release were all fixed. Of the usability bugs identified by the Community team, only two were deferred to MySQL 5.1.31, and will be available in the next binary release.

The above explanation makes little sense if we don't explain the criteria for triaging bugs. Until MySQL 4.1, the release criteria for GA was "zero known bugs". That rule was artificially ignored in 5.0, with the results that we all know. But in 5.1, there was no way of shipping with such rule. MySQL has become a much more complex product than it was in 4.1, and a flaw doesn't necessarily affect all the users. It is likely the contrary, in fact, that a defect identified in our QA laboratory only affects a small portion of our user base, or none at all, like it happened to a bug in prepared statements, found by a Support engineer, 4 years the feature was released, and so far claimed by no one (The bug was fixed, BTW).

Back to the triage criteria. There were 1000 open bugs when we went RC. The only way of getting rid of them was to prioritize the bugs. Classifying the bugs based on one single parameter, as we did before was not an option. We needed to be more specific. So all the open bugs were reviewed, and classified according to defect class, impact, workaround, risk to fix. Not a perfect system, I grant you, but nonetheless it allowed us to start fixing the most serious bugs with the greatest impact first. If a bug is a crash, but it only happens after a convoluted series of actions that our QA engineers have devised, and has little chance of affecting real users, that bug gets fixed after a similar one that occurs in a common sequence of events. If a bug affects two customers, then its impact is automatically raised, and thus fixed with priority.

The other important point is the risk factor. If a bug fix involves a substantial change of existing feature, it is deferred to the next version. When we went GA, we still had some open bugs, but none that prevented the normal usage of the server. The Community and Support teams are in contact with customers and other users who have been using MySQL 5.1 in production for years, and we made sure that the bugs reported by these brave souls were addressed before the GA release.

You see, there was a lot of work involved in our GA, and the engineering department, together with Support and Community teams, did an outstanding job of bringing this release to the public. Are the processes perfect? No. But they have improved significantly over the ones that led to MySQL 5.0 GA.

And speaking of that, we know that our engineering process leaves a lot of room for improvement.

Despite our claim to be the most popular open source database in the world, our development practices are very much closed source, and our release cycle is definitely in need of a revision. The obstacles towards opening the development model are quite a few, and we have been working hard to meet this goal. The first change was to get rid of the proprietary revision control system that was a real obstacle towards participation. Next, we published the Worklogs. Then we removed the MySQL Contributor License Agreement, which was another serious impediment. Next comes the release cycle and development model. There are many people at work on this issue, but this is work in progress and we don't want to raise your expectations on a quick resolution for these items. The important point is that everyone agrees on the need for change, and we are working toward this goal. It's a team effort, which will eventually bear fruits.

In the meantime, please honor the effort of the developers who created new features like Partitioning, Row-based replication and the Event Scheduler and have squashed thousands of bugs, and give it a try.

Sunday Dec 07, 2008

MySQL 5.1 for impatient Italians

I have just started a blog in Italian. Although Italian is my mother tongue, most of my technical writing is in English. Since my tasks are more widespread than a single country, it was easier and simpler to write in English straight away.

After Sun acquisition, the MySQL presence in Italy is growing, and then it makes sense to employ my birth speaking abilities to spread the word in my country.

Today, thanks to a convoluted introduction, set in motion by Kaj and Google Translate, I started contributing to Giuseppe Guerrasio's blog,, with a post on MySQL 5.1 per l'impaziente.

Thanks to Kaj for the introduction and to Giuseppe for hosting my post in his site!

Thursday Nov 27, 2008

MySQL 5.1-GA is available

MySQL 5.1 finally RELEASED!

MySQL 5.1 GA is now available for download.

The time has come. MySQL 5.1 is ready for production use.

In case you weren't paying attention while it it was still under development, here's what you get:

Happy coding!

Tuesday Nov 25, 2008

Seeding to the mirrors

If you hear a distant rumble in the air, it could be either Dups testing his new mountaineering gear, or, more likely, MySQL 5.1-GA seeding to the mirrors.

We promised that it would come soon, and it's coming. All internal QA automated tests were passed, a batch of additional manual tests with the packaged binaries (called "smoke tests" internally) were performed to make sure that no embarrassing errors were left, and now the train of bits is traveling to the mirrors. Once the mirrors are loaded, expect the announcement, within a few days.

Tuesday Nov 18, 2008

MySQL 5.1-GA is coming

Sheeri has already commented on this, but I want to stress that MySQL 5.1.30 will be GA by December 6th, 2008.

OpenSQLCamp - breaking news

Giuseppe Maxia


« July 2016