Friday, September 26, 2014

Oracle recently published the MySQL 5.7.5 Development Milestone release, a pre-production release providing numerous improvements to the MySQL server. You can download the release here: http://dev.mysql.com/downloads/mysql/5.7.html

This release carries some incompatible changes, as explained in the release notes and in the blog post describing the release. During my work in the Server QA team I have experienced some of these changes first hand already, and we have had to modify some tests and tools to adapt to some of it.

One very big change (well, some may not notice at all, while others may need to adjust their tools and applications) is the new default value of sql_mode:

It is also worth mentioning that the ONLY_FULL_GROUP_BY mode is greatly improved in 5.7.5, as described in Roland Bouman's blog. This is a welcome change by many, including myself :)

Now, down to business...

We have found that one old way of setting SQL mode is no longer considered "safe" for us. We want to test the default values of the server as much as possible, because that is likely what most customers will use. However, in some cases we need to set certain sql_modes in order to get the desired behavior - either for testing that specific behavior, or because this behavior helps making testing other things easier.

The sql_mode variable for a session can be set like this, from a MySQL client:

SET sql_mode=<value>

for example:

SET sql_mode='NO_ENGINE_SUBSTITUTION'

to ensure that the server will not revert to a different storage engine for a table "behind our back".

This works fine as long as you are sure that you do not want any other sql_modes, or as long as the old value of sql_mode is empty. However, now that the default contains no less than three SQL modes, some more care needs to be taken to make sure we are not disabling an sql_mode that we actually want to keep.

The sql_mode variable does not work the same way as e.g. optimizer_switch, where you can set individual flags and the others will remain the same. With sql_mode, what you set is exactly what you get.

It would be nice to be able to easily add or remove a single mode to/from the sql_mode variable, wouldn't it? Here are a couple of hopefully helpful tricks for setting and removing individual modes from the sql_mode variable, without risking the removal of modes that are not mentioned in your statement:

Tuesday, February 18, 2014

I remember from some years ago a "rule" that a new user should be able to get MySQL installed and up and running within 15 minutes. Whether this was and still is easy or not I shall not say... it depends. But it should still be in the back of the mind of any software engineer, that the product's first impression is important, and that usability must be taken seriously.

Thursday, February 14, 2013

In the MySQL QA teams in Oracle we have been using a tool called the Random Query Generator (or "RQG" for short) for some time now. The main RQG testing effort has been on new server development, including regression testing. The recent MySQL 5.6 GA is a result of a couple of years of hard work, including a lot of RQG testing and bugfixing as a result of that.

I can easily say that the RQG has helped making MySQL 5.6 a better release than it would otherwise be. It is of course not our only testing tool, but there are plenty of bugs this tool has uncovered that would likely not have been seen in our other testing. Such issues include:

crashes and asserts

memory management bugs (valgrind errors)

wrong results

The main clue to the power of the RQG is its ability to automatically generate SQL queries based on some grammar file. This makes it possible to produce SQL statements that you would not have been able to cook up manually even if you tried for years.

Note: For more than running just simple test runs, you need a few other Perl modules as well, see docs for details. Note that it pays off having a relatively recent version of Perl, as more modules are part of core Perl then, meaning you won't have to install that many yourself.

The test run should take less than a minute in total. You should see in the output that RQG starts a server, creates a test database, starts some validator (more on that later), starts running queries (the actual queries are not printed in this case), and at the end shuts down the server and reports the end result (should be STATUS_OK).

You can also add the option --sqltrace=MarkErrors to the command line in order to make the RQG print all generated queries to standard output. This is what --sqltrace does, while the MarkErrors setting ensures that any failed statements (i.e. illegal statements that are produced from the grammar but refused by MySQL) are marked (prefixed) with "# [sqltrace] ERROR <errno>:". Example:

Friday, February 8, 2013

Since I started working from home more or less full time I have thought lunch time was one of the definitive downsides... no more office cafeteria with nice hot soups and delicious "wienerbrød" (spandauer/danish) and hot meal on Fridays, no more humorous talks with colleagues, coffee machine, waffles... Well, today was the exception.

As a Norwegian working man I try to enjoy my boring slice of bread for lunch in the kitchen while listening to the radio and reading newspapers and other stuff ending up in the mail box throughout the week. Usually nothing exciting happens during the meal. Today, however, I noticed some movement in the corner of my eye, outside on the lawn behind our house. And there I saw one... no two... no three (!) moose stopping to have a chew on our apple trees (winter time, so no apples) before continuing on towards the next house...

Even though I have seen moose in the wild up-close several times before, I have never seen them in this neighborhood, a relatively densely populated area (yet pretty close to fields and forests). Wow, that was cool. Maybe it's not that bad to eat lunch at home after all?

As part of MySQL Server Quality Assurance I have been "intimate" with parts of this release for a long time... it comes with many new great features, bug fixes and other improvements. While I won't claim it to be bug free (after all, no software is), I feel confident that it is a big step forward from earlier releases for the majority of use cases.

Monday, July 23, 2012

One of the tools we use to qualify MySQL Server is the Random Query Generator (RQG for short). This is an SQL generator (and more) to test database systems such as MySQL. I plan to post more on that later.

The RQG is an open source tool written in Perl, and is available on Launchpad. Up until now the RQG documentation has been part of the MySQL Forge wiki. As you may have seen from this blog post, MySQL Forge is going away soon (August 1st). Unlike other parts of the Forge wiki, the Random Query Generator documentation has moved to github, and will continue to be maintained by the RQG community:

Tuesday, January 3, 2012

This is my very first blog post ever, and as such it is going to be short. Hopefully I will find the time and inspiration to put something here soon that may be of interest to others.

I am a Norwegian with some geeky and some not so geeky interests. I suspect that some of the geeky stuff on this blog will relate to my current job as a QA engineer for the MySQL database at Oracle, but do not be surprised to see other things here as well. My initial hope is that I will be able to post something often enough to not make this blog appear abandoned by the same time next year.