MySQL as universal server

With all the discussion going on about MySQL Proxy, we may forget that there are alternatives.
Today in the MySQL Dev Zone there is an article about a different proxy. It's more than that, actually. It is a Perl Module implementing the MySQL client/server protocol.
With this tool, you can emulate some of MySQL Proxy features, and you can do something more. For example, you can use Federated tables with non-MySQL database servers. The article features an example where a table from SQLite and a table from PostgreSQL are queried as if they were normal MySQL tables.

Your first macros with MySQL Proxy

You may have noticed the excitement about MySQL Proxy. But perhaps you failed to realize what you can do with it.
Here's a simple tutorial that will guide you through your first Proxy experience. You will be able to use macros with the standard mysql client.

Assuming that you are working on the box where your MySQL server is, do the following:

1. Download and unpack MySQL Proxy
2. download the query rewriting tutorial and save it as rewrite.lua
3. start the proxy
   /path/to/sbin/mysql-proxy --proxy-lua-script=rewrite.lua --proxy-profiling
4. from another terminal, fire the mysql client
   mysql -u USERNAME -pPASSWORD -h 127.0.0.1 -P 4040

Now you can use your client as usual, with a few additions. The rewrite.lua script implements three macros (cd, ls, who, corresponding respectively to USE, SHOW FULL TABLES, and SHOW PROCESSLIST).

mysql> cd test;
Query OK, 0 rows affected (0.00 sec)

mysql> select schema();
+----------+
| schema() |
+----------+
| test     |
+----------+
1 row in set (0.00 sec)

mysql> cd mysql;
Query OK, 0 rows affected (0.00 sec)

mysql> select schema();
+----------+
| schema() |
+----------+
| mysql    |
+----------+
1 row in set (0.00 sec)

Look at that! We send a macro (cd test) to the server. The proxy catches it, and turns it into "use test". The server executes it, unaware of the manipulation in between. Let's try the others:

mysql> ls mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> who;
+----+-------------+-----------------+-------+---------+------+-------+------------------+
| Id | User        | Host            | db    | Command | Time | State | Info             |
+----+-------------+-----------------+-------+---------+------+-------+------------------+
|  4 | datacharmer | localhost       | bugs  | Sleep   | 1522 |       | NULL             |
| 11 | gmax        | localhost:50630 | mysql | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+-------------+-----------------+-------+---------+------+-------+------------------+
2 rows in set (0.00 sec)

And now we add our own macro to the list. Stop the proxy. Edit rewrite.lua, and just before the line saying

elseif command == "who" then

add these three lines, implementing a info macro, which corresponds to "SELECT USER(), NOW()":

                elseif command == "info" then
                       proxy.queries:append(1, string.char(proxy.COM_QUERY) .. "SELECT USER(), NOW()" )
                       return proxy.PROXY_SEND_QUERY

Restart the proxy, and try your new macro:

mysql> info;
+----------------+---------------------+
| USER()         | NOW()               |
+----------------+---------------------+
| gmax@localhost | 2007-06-27 10:24:00 |
+----------------+---------------------+
1 row in set (0.00 sec)

Isn't it cool? And that's just the beginning.

MySQL Proxy - An excellent excuse to learn a new language

If you are as curious as I am about technology, you would have been intrigued when the MySQL proxy entered the radar. So I followed this promising jewel from its first inception as source only (and for internal distribution) until its release with a full series of binaries.

The recent announcement has made it official. MySQL Proxy is a full fledged supported product, and it's ready to use.
You just have to get the binaries and use it.
If you feel like experimenting (and there is no reason to get the proxy if you aren't) you can have a look at the sample lua scripts available on the Forge and play with them. You may notice that there is one snippet that I created. If you are thinking of calling me for lua lessons, think again. When I wrote that example, my entire production with lua amounted to three scripts, and the total length of my lua experience could be measured in hours.
Having used at least a dozen languages (including the usual suspects Pascal, C, C++, and Perl, but also things like Sather), a new scripting language does not scare me. I believe that I am going to see more of this lua, and I believe that lots of MySQL users are going to become proficient lua speakers in the near future.
Good job, Jan!

Users, give the proxy a try. And if you can't figure out what to do with it, stay tuned. I have a couple of article on this subject, and not only about this proxy, coming soon.

Test suites for the masses

Overview

Test suites are hard, but your business needs to have one.
They are hard because modern applications are complex.
Technology can help you. There are tools that can simplify the task of creating a thorough test suite.

Does your main business have a regression test?

The likely answers to the above questions are:

• Yes. - Good chap. Keep up the good work. You may stop reading, but just out of curiosity, read on.
  
• No. I don't need one. - You may stop reading. Most likely your business is already doomed. :)
• No. I don't have time (don't know how) to make one. - Keep reading. There is something for you.

What's a regression test? Is a useful watchdog that tells you if your software is working the way it was intended to.
If you don't have one, you have no quick and reliable way of telling if your application is safe.
If you think that you don't need a regression test suite, either you have found the formula of making perfect software, or your optimism is way off mark.

Tests are hard

If you have a test suite, you know that it requires hard work, dedication, cunning ways of thinking, technical abilities.
If your business includes a MySQL database, you may consider using its specific test framework, which introduces yet another level of difficulty.
MySQL testing framework is designed to allow testing any database scheme (stand alone, replicated, clusters) with just one box, at the price of some language addition that you would need to learn.
Writing your own regression test that covers your application means replaying the data structure and the queries, to make sure that, if you alter the working environment, your application continues working as required.

The most likely scenario occurs when you need to upgrade. Your current server has a bug that was just fixed in a more recent one. You would like to upgrade, but before doing that you would like to be sure that the new environment is capable of running your queries without side effects.
A regression test would solve the problem. You set up a temporary server, install the upgraded software and run the test suite. If you don't get errors, you may confidently upgrade. It depends on how thorough is your test, of course. The more you cover, the more you can trust it.
If you don't have a regression test and you need to upgrade, you are in trouble. If there are inconsistencies between your application and the new version, you will find out when a user complains about a failure. Which is not the most desirable course of action.

Understanding the problem

Why making a test suite for an application is so hard? Mostly because you don't usually have a list of all queries running against your server. Even if you planned a modular application and all your database calls are grouped in one easy accessible part of your code, that won't solve your problem. Modern applications don't use static queries. Most queries are created on the fly by your application server, twisting a template and adding parameters from users input.
And even if your templates are really well planned, so that they leave nothing to imagination, you will have trouble figuring out what happens when many requests access your database concurrently.
Smarter applications can log all database requests and leave a trail that you can use to create your test. But more complex systems count more than one application, and taking trace of all their queries is a real headache.

Making tests easier

Fortunately, there is a central place where all queries go. Your server.
Unfortunately, the server does not create a test case for you. It just records all calls in the general log, and it that is not suitable for further consumption.
Here comes the technology I mentioned earlier. You can make a test script out of your general log.

1. activate the general log;
2. Download the test maker;
3. Inspect the general log and identify the likely period when the most important queries occur;
4. tell the test maker to read the above traffic (which you can identify by line number or by timestamp)
5. edit the resulting test and see if covers your needs.
   

The test maker is a collection of two tools. One that can get a snapshot of your data (like mysqldump, but with a LIMIT clause, so that you can take only a sample of your data) and the test maker itself, which reads the general log and creates a script. You can tune this tool with several options.

 Log Analyzer and test maker,  version 1.0.2 [2007-06-10]
 (C) 2007 Giuseppe Maxia, MySQL AB
 Syntax: ./testing_tools/test_maker.pl [options] log_file test_file
 --add_disable_warnings|w        Add disable warnings before DROP commands. (default: 0))
 --from_timestamp|t = name       Starts analyzing from given timestamp. (default: undefined))
 --to_timestamp|T = name         Stops analyzing at given timestamp. (default: undefined))
 --from_line|l = number          Starts analyzing at given line. (default: first))
 --to_line|L = number            Stops analyzing at given line. (default: last))
 --from_statement|s = name       Starts analyzing at given statement. (default: first))
 --to_statement|S = name         Stops analyzing at given statement. (default: last))
 --dbs = name                    Comma separate list of databases to parse. (default: all))
 --users = name                  Comma separate list of users to parse. (default: all))
 --connections = name            Comma separate list of connection IDs to parse. (default: all))
 --verbose                       Adds more info to the test . (default: 0 ))
 --help                          Display this help . (default: 0 ))

As you can see from the options, you can filter the log by several combinations of connection ID, users list, timestamps, line numbers.
The resulting script is suitable for the MySQL test framework.

Advantages

The test maker is not a panacea, but it helps reducing your test suite development time. Running this tool you will get a passable starting test, which you can then refine to make it closer to your needs.
The main advantage is that you can have a set of instructions that were run against your production server, and you can replay against a testing server when you need to upgrade.
If some features of MySQL 5.1 look appealing and you plan to upgrade, then a test suite is the best way of making sure that the switch will be painless.

More than that, once you have a test suite, consider joining the Community Testing and Benchmarking Program, a project to share test suites from the Community, so that your test case becomes part of MySQL AB suite. Thus, you won't be alone with your upgrade problems. If there are any regression failures, they will be addressed before the next version is released, for the mutual benefit of company and users.

Known issues

There are problems, of course.
The general log is not turned on by default. To switch it on, you need to restart the server, and to switch it off you will do that again (until you upgrade to MySQL 5.1, which has logs on demand).
Moreover, the general log can become huge, so you need additional maintenance, to avoid filling the whole disk with logs.
Furthermore, the general log will record everything sent to the server, even wrong queries. While this is desirable for a test case, because testing failures is as important as testing compliance, you will need to adjust manually the test script, adding "--error" directives.
Again, the test maker records only the test script, but not the result, which you will need to create using the testing framework.
All in all, having a tool writing a big chunk of the test script is much better than doing it all manually. So even if this is not a complete solution, it's a hell of a good start.
Comments welcome.

Use 5.1 partitions in production - Today!

MySQL 5.1, the next major version, has a bunch of juicy features that many people would like to use in production, if only it were already GA. Partitioning for example, is one of those features that all users want to get their hands on. It is about performance. Partitioning can reduce response times dramatically, if applied correctly. I don't need to convince you of this. You can read one of the several articles about the technicalities of partitions (there is one piece about the performance of partitioned archive tables in this very blog).

The only problem is that MySQL 5.1 is still in beta. The number of outstanding bugs is shrinking by the day, and the number of reported bugs is receding, but nonetheless, it's not a GA release, and therefore the conscientious DBA is (justly) reluctant at the idea of using it in production.
Those partitions, though, are really tempting. Perhaps we can play some trick to use them, while keeping our data safe.
There are ways where you could use MySQL 5.1 in production, especially partitions, without endangering your valuable data. The easiest one is with replication.

MySQL replication is a cheap, reliable, fast way of synchronizing data among servers and balancing the query load, if you are willing to split your application logic so that you write on one server (the master) and read from another one, or several ones (the slave).

One nice feature of MySQL replication is that it can work across versions, as long as the most recent one is used in the slave. So you can set your real production server as the master, running MySQL 5.0, the legitimate GA release, while the slave will run MySQL 5.1.



The steps to achieve this feat are simple:

• make sure that your master is using a binary log and has a unique server ID;
  
• create a user with REPLICATION SLAVE privileges;
• on the slave, install MySQL 5.1, with a different server ID, but don't start the replication yet;
• dump the data from master to slave, using the mysqldump options --master-data=1 and --single-transaction;
• On the slave, Alter the structure of the tables that you want to partition;
  
• start the slave.
  

Now you can instruct your application to send expensive queries to the slave, instead of the master. Now your most demanding SELECT statements can take advantage of partitioning, while your data is safely held in the GA master.

Think of the advantages:

• You will speed up some of your expensive queries. (Beware. It is not always easy. You need to check your table design, but in the end, it works spectacularly);
• If you find a bug in 5.1, you can help the development without risking the integrity of your data, which is still safe inside the master.
  

If you still have doubts, I'll let you in a little secret. At MySQL, at least two production databases have been running on 5.1 for one year already. We trust it, even though we can't officially tell you to do the same. But I can recommend you the little replication trick that will let you use the new features with no risks.

Hacking MySQL table logs

Shortly before MySQL Users Conference I announced that I would be cover new ground in table logs management.
I am keeping that promise, and in addition I am also showing some related hacks.

The announced facts from last year usability report were that you can't change log tables at will, as you can do with log files, and you can't change the log table engine to FEDERATED. Both claims, as it turned out, were incorrect. You can do such things, albeit not in a straightforward manner. As a bonus side effect, you can also:

• add triggers to log tables;
• filter log tables depending on user defined criteria, such as query type, user database, or time;
• centralize logs from several servers.

Read the rest of the article at O'Reilly Databases site

MySQL Stored routines at the Users Conference

The Users Conference and Expo is approaching fast. As a last minute assignment, I will be speaking about Stored routines for MySQL administration. The session will cover the basics of stored routines and their specifics for administration.

Coincidentally, today was published a review of an excellent book about MySQL Stored Procedures programming by Guy Harrison and Steven Feuerstein. I warmly recommend this book to anyone using stored routines with MySQL.

Mentoring a Summer of Code project

It's official.
I am now appointed mentor of a Google Summer of Code project.
Congratulations to Charlie Cahoon, who has submitted an intriguing proposal for improving our code coverage and testing tools. The abstract doesn't do justice to the project. The juicy part is in the details. More about it later.
More information on Kaj's announcement.

Thanks, Charlie, for proposing this project!

Thanks, Google, for promoting this great infrastructure!

Handling multiple data sets in stored procedures from Perl

MySQL stored procedures can use SELECT statements in their body, returning a data set to the caller. The manual also explains that multiple data sets can be returned from such a stored procedure.
For example, assume you have this stored procedure:

CREATE PROCEDURE p1(x INT, y INT)
    DETERMINISTIC
    BEGIN
        SELECT 
            x ;
        SELECT 
            x AS first_param, 
            y AS second_param;
        SELECT 
            x, 
            y, 
            x + y AS sum_xy, 
            x * y AS prod_xy; 
        SELECT *  FROM t1;
    END

There are four SELECT statements, with different headers, and a variable number of rows returned.
Early versions of DBD::mysql could not handle multiple data sets. Actually, prior to version 3.0004 they could not even handle one dataset from a stored procedure. Starting with version 4.001, single and multiple datasets from SPs are handled gracefully.
The trick is to call $sth->more_results after retrieving each dataset.
See the complete example at Multiple data sets in MySQL stored procedures


For those interested, the MySQL Users Conference and Expo includes a BoF about DBD::mysql on April 24^th at 7:30pm.

Logs on demand, a DBA's prayer come true

Several months ago I wrote about logs on demand in MySQL 5.1. Now that 5.1 is approaching its maturity stage, I am happy to report that this feature has proven itself very handy and useful. Petr Chardin will talk about this very feature at the MySQL Conference and Expo.

While reviewing the material for this session, I remembered a usability report that I wrote when the feature was announced. Among other things I wrote that you can create ad hoc log files for separate needs, but you can't do the same thing for tables. This reminds me of a general rule of technology: "If a respected scientist says a thing is possible, you can be almost certain he's right. If an established, respected scientist tells you that something is impossible, he's almost certainly wrong." I was wrong. It can be done. To know how, attend the aforementioned session, or wait until I post an a follow-up to this post after the conference.

When Community strikes

A few days ago a bug report was filed to the MySQL bugs system. For a few hours it was just one among the many, although it looked hard to reproduce.
Then, suddenly, two experienced contributors, Martin Friebe and Alexander Fomichev, joined the discussion, offering similar cases, explanations, a workaround, and even a patch.

The public intervention took place within 11 hours after the MySQL Engineer asked for clarifications, and within one day the solution was served!

Thanks guys!
We love it when bugs are solved this way.

Rush to register for the MySQL Summer of Code (extended deadline!)

MySQL has joined Google Summer of Code 2007 and has launched its own Summer of Code branch.

Are you a student and a skilled programmer? Do you use MySQL? Do you have ideas on how to improve it? (Do you want to use this cool logo in your blog? :) )
Here's your chance to participate. Rush to read the announcement and the full instructions in MySQL Forge and then apply!
Time is short. The deadline is Saturday, March 24, 2007 Tuesday, March 27 2007 (extended!)!

Federated, Blackhole, and Contributors at the MySQL Conference and Expo 2007

I will be involved in two sessions at the MySQL Users Conference and Expo 2007.

• The Blackhole and Federated Storage Engines: The Coolest Kids on the Block, a four-hands presentation with Kai Voigt
• Lightning Rounds with Top MySQL Community Contributors where I will be the moderator among a group of valued contributors who will tell their experience with MySQL.

This year conference is going to be more user-oriented than ever before. Many sessions by MySQL employees will be co-presented by external users.
Some examples:

• Jay Pipes and Ask Bjørn Hansen will host a tutorial about Real-World MySQL Performance Tuning;
• Ronald Bradford and Guy Harrison will share their experience on MySQL for Oracle DBAs and Developers
  
• Jan Kneschke and
  Sheeri Kritzer will explain the joy of Monitoring MySQL Servers with MySQL Network
• Another duo, Andy Bang and Sehmuz Bayhan join forces to illustrate Real-world MySQL Watch Dog: Step-by-Step Usage of the MySQL Monitoring and Advisory Service
  

In short, there is a lot to see and do, (and not enough space in this blog). Visit the official site and judge for yourself.

Test driving SHOW PROFILES

SHOW PROFILES is finally available! It has been a long struggle, but finally this important community patch by Jeremy Cole has been integrated into a publicly distributed MySQL source tree (thanks to Chad Miller excellent integration work).
What's SHOW PROFILES? Is a feature to let you peek inside MySQL internals, which tell you what your queries were doing during their execution.
This feature is also remarkable because it makes a significant difference between Community and Enterprise releases.
The binaries will be available soon, but in the meantime, you can build the source code and give it a try.

Building

To build from source, follow the instructions at Installing from the Development Source Tree using the Community tree.

bkf clone bk://mysql.bkbits.net/mysql-5.0-community mysql-5.0

And since this is a community feature, the documentation can be provided by the community as well. I posted a documentation stub in MySQL Forge. Feel free to add to it.

Test driving

Once you have built your community distribution (or after downloading the binaries, if you are reading this when they are already available), it's time to see what SHOW PROFILE can do.
To use this addition, you need to enable it. The session variable profiling is used to enable and disable it. By default, it is disabled, so the first order of business is: turn it on!

select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+

set profiling = 1;

select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+

Cool. Now what?
Now we play around with the system, and see how profiles works.

drop table if exists t1;
create table t1 (id int not null);
insert into t1 values (1), (2), (3);
select * from t1;

#
# AND FINALLY!
#

show profiles;
+----------+----------+-------------------------------------+
| Query_ID | Duration | Query                               |
+----------+----------+-------------------------------------+
|        0 | 0.000045 | set profiling = 1                   |
|        1 | 0.000071 | select @@profiling                  |
|        2 | 0.000087 | drop table if exists t1             |
|        3 | 0.031741 | create table t1 (id int not null)   |
|        4 | 0.000615 | insert into t1 values (1), (2), (3) |
|        5 | 0.000198 | select * from t1                    |
+----------+----------+-------------------------------------+

Here is the first glimpse. Basically, it tells you the duration of each query. Not very exciting so far, but let's keep going. The Query_ID field is a reference for us, so that we can ask more about a specific query. In this list, the table creation is query no. 3, so let's ask some specifics about this one:

SHOW PROFILE FOR QUERY 3;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| checking permissions | 0.000040 |
| creating table       | 0.000035 |
| After create         | 0.031432 |
| query end            | 0.000221 |
| freeing items        | 0.000005 |
| logging slow query   | 0.000006 |
| cleaning up          | 0.000002 |
+----------------------+----------+

That's wonderful! Now we know more about the single tasks this query was performing. But it gets even better. Look at this:

SHOW PROFILE CPU FOR QUERY 3;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| checking permissions | 0.000040 | 0.000000 |   0.000000 |
| creating table       | 0.000035 | 0.000000 |   0.000000 |
| After create         | 0.031432 | 0.001000 |   0.000000 |
| query end            | 0.000221 | 0.000000 |   0.000000 |
| freeing items        | 0.000005 | 0.000000 |   0.000000 |
| logging slow query   | 0.000006 | 0.000000 |   0.000000 |
| cleaning up          | 0.000002 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

If you are debugging code, you may like the information provided by the following option:

SHOW PROFILE source FOR QUERY 3;
+----------------------+----------+-----------------------+--------------+-------------+
| Status               | Duration | Source_function       | Source_file  | Source_line |
+----------------------+----------+-----------------------+--------------+-------------+
| checking permissions | 0.000040 | check_access          | sql_parse.cc |        5256 |
| creating table       | 0.000035 | mysql_create_table    | sql_table.cc |        1737 |
| After create         | 0.031432 | mysql_create_table    | sql_table.cc |        1768 |
| query end            | 0.000221 | mysql_execute_command | sql_parse.cc |        5085 |
| freeing items        | 0.000005 | mysql_parse           | sql_parse.cc |        5973 |
| logging slow query   | 0.000006 | log_slow_statement    | sql_parse.cc |        2178 |
| cleaning up          | 0.000002 | dispatch_command      | sql_parse.cc |        2143 |
+----------------------+----------+-----------------------+--------------+-------------+

It tells you how much time was spent, and which lines of code you should look at if you want to improve it!
More options available in the documentation stub.
Rather than trying all of them here, I will just give you a simple example of what can be hidden behind a query. Check this out:

connect;
set profiling=1;
use information_schema;
select count(*) from columns where table_schema='mysql';
+----------+
| count(*) |
+----------+
|      147 |
+----------+

show profiles;
+----------+----------+---------------------------------------------------------+
| Query_ID | Duration | Query                                                   |
+----------+----------+---------------------------------------------------------+
|        0 | 0.000194 | set profiling=1                                         |
|        1 | 0.000006 | SELECT DATABASE()                                       |
|        2 | 0.005928 | select count(*) from columns where table_schema='mysql' |
+----------+----------+---------------------------------------------------------+

show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| Opening tables       | 0.000051 |
| System lock          | 0.000281 |
| Table lock           | 0.000002 |
| init                 | 0.000006 |
| optimizing           | 0.000020 |
| statistics           | 0.000009 |
| preparing            | 0.000012 |
| executing            | 0.000011 |
| checking permissions | 0.000039 |
| Opening tables       | 0.000136 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000185 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000478 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000107 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000096 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000052 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000051 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000145 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000445 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000395 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000189 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000195 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000053 |
| checking permissions | 0.000006 |
| Opening tables       | 0.000055 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000058 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000064 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000123 |
| checking permissions | 0.000007 |
| Sending data         | 0.000898 |
| end                  | 0.001215 |
| query end            | 0.000007 |
| freeing items        | 0.000003 |
| closing tables       | 0.000010 |
| removing tmp table   | 0.000002 |
| closing tables       | 0.000420 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000002 |
+----------------------+----------+
52 rows in set (0.00 sec)

Now it's your turn to experiment and share your experiences!

What every developer would like to see

A few days ago, MySQL launched the Quality Contribution Program. The main purpose of that program is to improve the quality of submissions from active users. We published the rules, and soon we saw more reports coming our way, with better contents than usual.
The commotion happened yesterday evening, when I noticed a bug report that looked interesting. At a quick glance, it was. A clear description of the problem. Cool. An attached test case. Great! And a result file! Even better. And the closing remark, "See patch", was just too much to believe. So, there was a bug report telling us about a problem, including a clean way of reproducing it, and even saying where the problem is in the code and how to fix it!
It was already late in the day, but I told myself that it wouldn't take me too long to verify the claim. So I used the latest 5.0.38 build in my laptop and I ran the test case. Bingo! The problem was there, just as described. After inspecting the resulting data, I was satisfied that the claim was justified, and I noted that much in the test report. Then I alerted Chad Miller, my colleague in the Community-Engineering team, who in turn informed the leader of the team involved in that particular problem.
One hour later, the bug went from verified to patch pending, meaning that a developer had examined the patch, and it was considered up to company standards, ready for review.
Within two hours from its submission, the problem was virtually fixed!
The author of this exploit is Martin Friebe, who, not by chance, is leading the contributors list in our program.
Our goal in launching the Quality Contribution Program was exactly this: getting better bug reports, and Martin has shown the way.
Thanks Martin! Keep up the good work!

Launching the Quality Contribution Program

After a long period of preparation, my pet project is out! The Quality Contribution Program is here! MySQL wants to reward its most active users who are contributing to the improvement of its products. This is not a lottery, where you submit some contributions, and if you are chosen you get the prize. In this program, you start contributing, and when you reach a given level, you (and everybody else in the same position) will get a free subscription to MySQL Enterprise. The project rules say how much you should contribute to get a Basic, Silver, Gold, or even Platinum subscription.

Building MySQL 5.1 from a source tree on Mac OS X

The objective of this exercise is compiling MySQL from a source tree (BitKeeper) on Mac OS X.

I bought a new laptop, a MacBook running Mac OS X Tiger. I was captivated by the user interface, and I was willing to try this new experience, after a long and successful relationship with Linux (I gave up Windows many years ago). Everything went well, until the moment I tried to compile MySQL from source.
Using the default Xcode tools, I was able to compile the source packages provided with the GA release (5.0.x). But with 5.1 it was a different story. I was not even able to complete the compile part. something was breaking quite soon during the process.

After unsuccessfully trying all the tools made by the MySQL Build Department, I finally decided to take things into my own hands, and to create a reliable compiling environment.

I - Isolating the environment

I decided that the sanest course of action was to get the most recent build tools and then I checked whether I could get some of them via Fink or apt-get. Simply running "fink selfupdate" did most of the necessary work. After that I had a very recent version of gcc/g++.
The GNU tools for building were not available through the standard interface, and then I had to compile them from scratch. I was a bit reluctant, because I could disrupt some existing dependency, and hinder the functionality of the Mac OS X development tools. Thus, I set up a limited environment for building.

• create a directory $HOME/usr/local/bin

• change the PATH to include the new binary directory at the start.

export PATH=$HOME/usr/local/bin:$PATH

Now whenever I start a build task, programs in the new path will take precedence over the built-in ones. This way I can still use the development tools to make Mac specific applications, and at the same time I can use the most recent tools for my job related purposes.

II - Making the building tools

Once the path is set, it's time to build the tools.

• compile the new tools in the following order:
• autoconf-2.61 http://ftp.gnu.org/gnu/autoconf/autoconf-2.61.tar.gz
• libtool-1.5.22 http://ftp.gnu.org/gnu/libtool/libtool-1.5.22.tar.gz
• automake-1.10 http://ftp.gnu.org/gnu/automake/automake-1.10.tar.gz
• byacc-20050813 ftp://invisible-island.net/byacc/byacc.tar.gz
• bison-2.3 http://ftp.gnu.org/gnu/bison/bison-2.3.tar.gz

For each tool, I issued this command:

./configure --prefix=$HOME/usr/local && make && make check && make install

automake took almost 40 minutes (it has a huge regression test suite) but in the end, I managed to get all the tools in working shape. It's important to do the installation in the above order. Testing of automake fails if autoconf and libtool were not already compiled and installed. I know because I tried to build all of them in parallel and it failed.

III - Using them

Now that I have the tools, I can do something practical.

$ bk clone username@bk_repository/mysql-5.1 51
$ cd 51
$ BUILD/compile-dist
$ make test
$ make dist

And it works!

BTW, BUILD/compile-dist and make dist are the commands used by the MySQL build team to create a source distribution. You can do that as well from a development source tree.

Caveat

This material is my personal experience (which is quite limited with Mac OS X). The reason for installing so many packages from source is because the ones that are provided natively fail sooner or later during the task that I needed to perform.
If any Mac OS X expert has a better solution for this particular problem, I will listen.

Update

Being a newbie at Mac OS X, I missed the simple point that I could have installed the needed tools with Darwin Ports. I would have saved time and it would have taken care of the dependency issues. Good to know for next time!

What is a bug?

It happens sometimes, when I report a bug, that I have an argument with someone at the receiving end of the reporting chain. The raw happens over the definition of a bug. For instance, there is a new implementation of a consolidated tool. The new tool does almost all the old one did, except X. Therefore I file a bug report saying that X is missing. The ensuing argument runs along the lines of:
- This is not a bug, says the Verifier. You can't say it's a bug because it does not do what you want. It should be downgraded to feature request.
- This is the recommended replacement of the old tool, I retort, and as such it should do at least what the old tool did, plus the new stuff. I insist it is a bug.
- The manual does not mention feature X for the new tool, and then it is not a bug. It's a feature request"
The Verifier's reasoning is technically correct, and it is within the boundaries of his allowed action, so I see no point in arguing much more.
However, I can start a campaign of educating people on bug evaluation, in hope that these notes reach the ones in charge of writing the guidelines, so that this kind of requests is met more kindly next time.

So, what exactly is a Bug?

I start by borrowing some definitions from Ron Patton's excellent book on Software testing. According to that, it's a bug when ...

1. The software doesn’t do something that the product specification says it should do.
2. The software does something that the product specification says it shouldn’t do.
3. The software does something that the product specification doesn’t mention.
4. The software doesn’t do something that the product specification doesn’t mention but should.
5. The software is difficult to understand, hard to use, slow, or—in the software tester’s eyes—will be viewed by the end user as just plain not right.

(There is an online sample chapter, The realities of software testing containing the above definition)
Let's comment on each point.

Broken promise (positive)

The software doesn’t do something that the product specification says it should do.
This is a no brainer. If you said that the editor would save my file when I hit the "save" button, and it doesn't, then it's a bug.

Broken promise (negative)

The software does something that the product specification says it shouldn’t do.
This is as simple as the previous one. If you say that the program won't overwrite my painfully composed setup file and instead it silently restores the defaults, then it's a bug.

Collateral damage

The software does something that the product specification doesn’t mention.
Here we enter in tricky territory. There is an extra feature not mentioned in the manual. Sometimes you should be just happy and say thank you, for instance if your word processor can handle simple spreadsheet-like calculations in table cells. But if the same word processor makes a hidden copy of every document you edit for backup purposes, you may get quite angry. Either way, is anything of this sort happens, then it's a bug.

Forgotten specs

The software doesn’t do something that the product specification doesn’t mention but should.
At this point, we start bickering loudly. If your word processor's manual does not mention the "save" option, and such option is nowhere to be found in the menu and task bars, anyone faintly computer-literate would complain that this omission is inexcusable. If such a missing feature happens, then it's a bug.

Catch-all clause

The software is difficult to understand, hard to use, slow, or—in the software tester’s eyes—will be viewed by the end user as just plain not right.

And now the bickering becomes open fight. You, the user, have the right to comfortable usage. The specifications warns you that you may get unpleasant side effects when using a killer application. You understand it and run it anyway, but it happens that the mentioned side effects are causing real damage. Guess what? It's a bug. No matter if it was in the docs. It should have been under the "known bugs" section. For instance, the docs may warn you that a system monitor, to keep you informed of the health of a database, will poll the server using a non-existing user. That sounds fine, but later, when your intrusion detection system rings you at 3am saying that the number of failed login attempts has reached the critical level and as a safety measure the database server was shut down, then, I can't help it, it's a bug.

This latest rule must not be used as an excuse to pass any request as a bug report. It's hard to set a clear boundary, but sometimes, without this fifth rule, many critical bugs would remain latent.

MySQL Quality Assurance forum

As preparation for the upcoming Quality Contribution Program, a new MySQL forum was created today.
The new forum is dedicated to Quality Assurance matters. It is not the place where to submit bugs (there is already the bug reporting system for that purpose). It is rather a place where to discuss quality assurance problems, such as:

• How do I report this particular kind of bug?
• How do I make a test case for this specific situation?
• What is the best strategy to report a nasty cluster of bugs?
• Improving testing techniques;

Everything related to Quality assurance can be discussed there. If you have an idea on how to make better test cases, go there and launch the challenge. If you want to experiment a new technique for bug hunting, let's hear it!

The hidden risks of SQL MODE

MySQL 5.0 introduces improved SQL modes, which can fine tune the way your server behaves. If you are a long term MySQL user, you may be familiar with the speed for accuracy trade-off. MySQL has a default for each field, and guesses a value when you don't provide an appropriate one when inserting or updating. If this behavior is not acceptable to you, you can now tell the server to be less permissive. Check out an article by Robin Schumacher, where this concept is explained thoroughly.

If you look at the manual, though, you will see that the SQL modes are quite a few, and you may be tempted to combine some of them to control every tiny part of the server input.
Beware, though. There are some pitfalls that you should be aware of. Let's walk through an example.

SET SQL_MODE='';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+------------+-------------------------------------------+
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
+------------+-------------------------------------------+
|            |                      18446744073709551615 |
+------------+-------------------------------------------+

What is this? It is a subtraction between two unsigned values. There is an overflow in the result, and then the result of 1-2 becomes the highest BIGINT value minus one.
We can control this behavior, and use a specific SQL_MODE, NO_UNSIGNED_SUBTRACTION, to tell the server that it should not allow a subtraction between unsigned values, and treat them as signed instead.

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+-------------------------------------------+
| @@sql_mode              | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION |                                        -1 |
+-------------------------+-------------------------------------------+

Fine. Now we know that we can take control of subtractions. But there is something more to know. The SQL MODE sticks to each procedure, function, or trigger, meaning that each routine is executed using the SQL mode that was active at creation time. This could lead to surprising results.

set sql_mode='';
drop function if exists subtraction;
create function subtraction(x int unsigned, y int unsigned)
returns int
deterministic
return x - y;

SET SQL_MODE='NO_UNSIGNED_SUBTRACTION';

select @@sql_mode, subtraction(1,2), cast(1 as unsigned) - cast(2 as unsigned);
+-------------------------+------------------+-------------------------------------------+
| @@sql_mode              | subtraction(1,2) | cast(1 as unsigned) - cast(2 as unsigned) |
+-------------------------+------------------+-------------------------------------------+
| NO_UNSIGNED_SUBTRACTION |       2147483647 |                                        -1 |
+-------------------------+------------------+-------------------------------------------+

Look here. We set the SQL_MODE to NO_UNSIGNED_SUBTRACTION, because we want to avoid that unpleasant effect, but the subtraction function was created with a different SQL_MODE.
Therefore, the operations inside such function will be affected by the stored SQL_MODE, regardless of the one that is active at the moment.

Whenever your result depends on a specific SQL_MODE, always check which mode is associated with the stored routines or triggers that you are using.

SELECT
  routine_name,. sql_mode
FROM
  information_schema.routines
WHERE routine_schema='test'
  AND routine_name='subtraction'
  AND routine_type='function';
+--------------+----------+
| ROUTINE_NAME | SQL_MODE |
+--------------+----------+
| subtraction  |          |
+--------------+----------+

There is also a more complex example involving triggers.