Thursday, December 28, 2006

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!

Thursday, November 30, 2006

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.

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.
select @@sql_mode, cast(1 as unsigned) - cast(2 as unsigned);
| @@sql_mode | cast(1 as unsigned) - cast(2 as unsigned) |
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
return x - y;


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.
routine_name,. sql_mode
WHERE routine_schema='test'
AND routine_name='subtraction'
AND routine_type='function';
| subtraction | |

There is also a more complex example involving triggers.

Monday, November 13, 2006

MySQL testing techniques: comparing tables

This is the first tutorial of a series dedicated to testing techniques.
Soon all this material will find a home in a more appropriate place. In the meantime, enjoy the lecture!

While testing large tables, it is useful to test if two tables have the same contents. For example, if you want to compare performance between two tables using different storage engines, or tables created on different filesystems, you must be sure that both tables have the same content. Having loaded both from the same source is not a guarantee that the contents are the same: a mistake or different SQL modes used during the load may result in substantial differences.

General concepts

Then, you need to compare two, possibly very large tables. There are several methods available. One is to run a query with a LEFT OUTER JOIN. However, this method is likely to take very long or even exhaust your system resources if your tables are really large.
One method that I have been advocating for long time is to run a global CRC on both tables and then compare the results.
And, I hear you asking, how do you get a global table CRC?
There is no predefined SQL feature for this task. Recent MyISAM tables have a built-in CRC, but you can't get it from a SELECT statement, and besides, if you need to compare the contents of such a table with one using a different engine, you are out of luck. Then, we need to use something more general, which can be applied to any table.
The first step to get a global CRC is to get a list of the columns that we can then pass to a CRC function such as SHA1 or MD5.

This list is a string made of the name of the columns, which we will pass to a CONCAT_WS function. However, if you know how SQL functions work, you will know that any NULL value in the list will nullify the whole expression. Therefore, we need to make sure that every nullable column is properly handled by a COALESCE function. The result of this operation, which we delegate to a stored function, is a safe list of column.
The second step towards a global table CRC is to calculate a CRC for each record. We use the above list of columns to create a SELECT statement returning a SHA1 for each record in the table. But, what to do with it? There is no aggregate SQL function available for SHA or MD5. Thus, we need to process the result and calculate our CRC manually.
As noted in a previous post, we can do that in two ways, using cursors or using a blackhole table. Some benchmarks show that the blackhole table is much faster than the cursor, and this is what we do.

We start with an empty CRC. For each row, we compute a CRC of the whole record, plus the existing CRC. Since we are using a SELECT statement, we need to get rid of the output, because we are only interested in the calculation stored in the user variable. For this purpose, a black hole table is very well suited. At the end of the SELECT + INSERT operation, we have in hand two variables, one showing the count and one holding the global CRC for the table.
Repeating this process for the second table we need to compare, we can then compare two simple values, and determine at a glance if we are dealing with comparable data sets.


Let's put the concepts together with a few stored routines.

delimiter //

drop function if exists get_safe_column_list //
create function get_safe_column_list
p_db_name varchar(50),
p_table_name varchar(50),
p_null_text varchar(20)
returns varchar(10000)
reads sql data
if ( @@group_concat_max_len < 10000 ) then
set group_concat_max_len = 10000;
end if;
return (
group_concat( if(is_nullable = 'no', column_name,
concat("COALESCE(",column_name, ", '", p_null_text,"')") ))
table_schema= p_db_name
table_name = p_table_name
end //
The first function returns a safe list of column names.

drop function if exists get_primary_key //
create function get_primary_key (
p_db_name varchar(50),
p_table_name varchar(50)
returns varchar(10000)
if ( @@group_concat_max_len < 10000 ) then
set group_concat_max_len = 10000;
end if;
return (
group_concat(column_name order by ORDINAL_POSITION)
from information_schema.KEY_COLUMN_USAGE
and table_name = p_table_name
and constraint_name = 'PRIMARY' );
end //
The second routine returns a table primary key, as a list of column.
drop procedure if exists table_crc //
create procedure table_crc (
IN p_db_name varchar(50),
IN p_table_name varchar(50),
OUT p_table_crc varchar(100)
reads sql data
declare pk varchar(1000);
declare column_list varchar(10000);
set pk = get_primary_key(p_db_name, p_table_name);
set column_list = get_safe_column_list(p_db_name, p_table_name, 'NULL');
if (column_list is null) then
set p_table_crc = null;
leave main_table_crc;
end if;
set @q = concat(
'INSERT INTO bh SELECT @tcnt := @tcnt + 1, ',
'@tcrc := SHA1(CONCAT(@tcrc, CONCAT_WS("#",', column_list, ')))',
' FROM ', p_db_name, '.', p_table_name,
if (pk is null, '', concat(' ORDER BY ', pk))
drop table if exists bh;
create table bh (counter int, tcrc varchar(50)) engine = blackhole;
set @tcrc= '';
set @tcnt= 0;
prepare q from @q;
execute q;
set p_table_crc = concat(@tcnt,'-',@tcrc);
deallocate prepare q;
end //
The third procedure returns the global CRC of a given table.

drop procedure if exists table_compare //
create procedure table_compare (
IN p_db_name1 varchar(50),
IN p_table_name1 varchar(50),
IN p_db_name2 varchar(50),
IN p_table_name2 varchar(50),
OUT same_contents boolean
declare crc1 varchar(100);
declare crc2 varchar(100);
call table_crc(p_db_name1,p_table_name1, crc1);
call table_crc(p_db_name2,p_table_name2, crc2);
select concat(p_db_name1, '.', p_table_name1) as table_name, crc1 as crc
select concat(p_db_name2, '.', p_table_name2) as table_name, crc2 as crc ;
set same_contents = (crc1 = crc2);
select crc1=crc2 as 'same contents';
end //

delimiter ;
The final routine puts all pieces together, returning a boolean value telling if the two tables have the same contents.


After loading the above code in our database, we can call a "table_crc" procedure to get our coveted value. Let's take the famous world database and let's give it a try.

mysql> use world;
Database changed
mysql> create table City2 like City;
Query OK, 0 rows affected (0.03 sec)

mysql> alter table City2 ENGINE = InnoDB;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into City2 select * from City order by District, population;
Query OK, 4079 rows affected (0.33 sec)
Records: 4079 Duplicates: 0 Warnings: 0

First of all, we create another table, with the same structure of City, but using a different engine, and storing data in a bizarre order to see if our routine is robust enough. In fact our routine will calculate the CRC after sorting the data by primary key, so that there won't be any surprise.

mysql> call table_crc(schema(), 'City', @city_crc);
Query OK, 0 rows affected, 1 warning (0.16 sec)

mysql> select @city_crc;
| @city_crc |
| 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
1 row in set (0.00 sec)

mysql> call table_crc(schema(), 'City2', @city2_crc);
Query OK, 0 rows affected (0.13 sec)

mysql> select @city2_crc;
| @city2_crc |
| 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
1 row in set (0.01 sec)

When we compare the CRC, we can easily see that the two tables are the same. If all these statements are tedious to write, we can use a shortcut:

mysql> call table_compare(schema(), 'City',schema(), 'City2', @same);
| table_name | crc |
| world.City | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
| world.City2 | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
2 rows in set (0.24 sec)

| same contents |
| 1 |
1 row in set (0.24 sec)
Le'ts make it fail, to see if it is true:

mysql> update City2 set population = population + 1 where id = 1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Now the two tables will have at least one difference:
mysql> call table_compare(schema(), 'City',schema(), 'City2', @same);
| table_name | crc |
| world.City | 4079-407840fbf812b81eee55d3a438cf953f81c63bc0 |
| world.City2 | 4079-b3b613b20570024be727ef0454053a96cfc36633 |
2 rows in set (0.23 sec)

| same contents |
| 0 |
1 row in set (0.23 sec)
And our routine finds it. QED.

Sunday, October 29, 2006

Speaking at the Open Source Database Conference 2006

I will be a speaker at the Open Source Database Conference, which is held in Frankfurt from 6th to 8th November 2006, parallel to the International PHP conference.
I will present two sessions, one on Advanced Replication Techniques in MySQL 5 and the other on The MySQL General Purpose Stored Routines Library.
I submitted both proposals long before I started considering joining MySQL, so I will go there with the blessing of my current employer, but I will speak under my former affiliation, to avoid attributing to my current company what are my personal ideas.

Thursday, October 19, 2006

Contributing to MySQL QA - Ideas wanted

MySQL has recently started a campaign of open contribution, inviting the community to participate to the MySQL project in many ways.

The next target, also considering the higer stakes coming from the MySQL Enterprise challenge, will be Quality Assurance.

Quality Assurance

Now what is Quality Assurance (QA)? If you think that it's just bug hunting, then you have a simplistic view of the software generation lifecycle. QA deals with all the steps in the software lifecycle, and at each steps there are actions that can affect the quality of the final outcome. QA components include (but are not limited to) failure testing, statistical control, process and performance control, best practice adoption.

MySQL AB has its own QA department. Those of you who attended the MySQL Users Conference may have had a chance to attend a presentation by senior QA manager Omer BarNir about Internal QA in Open Source Development, where he explains the challenge of being a QA professional in such a dynamic company (presentation slides ).

Software testing

Since MySQL is a software company, finding bugs is important, of course, and software testing is one of the main branches of QA, but the crucial part of finding bugs is not how many you can find, but when you find them. The earlier you find a bug, the less costly will be to fix it.

Looking for bugs as soon as a feature pops up in the development lifecycle is just part of the task. The way you look for bugs is different from person to person, depending on who you are:
  • a developer with an intimate knowledge of the code may find low level bugs that nobody else can catch, with tools like code peer reviews and walkthroughs;
  • a professional tester, even without development experience, may find functional bugs by testing the application with a methodical approach;
  • the final user, who does not have to know any of the above, may find bugs by simply using the application and comparing the results with earlier expectations.

Of all these three methods of finding bugs, the first one is the most effective. Bugs found at that stage are the least expensive to fix. But of course one can't find all the bugs with code inspections. And then there are the other two levels, which catch bugs that will cost more effort to fix.

Obviously, the bugs that affect the final users are the ones for which you care most, and it would be of mutual benefit to find these bugs as early as possible.

Community involvement

From the above explanation, you can see what the problem is. No matter how skilled are the professionals at this job, finding all the bugs is impossible. You can take any book on this subject, and every one of them will tell you the same postulate: You will not find all bugs. One thought is especially discouraging for us. Although the QA professionals find a lot of bugs, those which you neve hear of, because they are fixed before you hit the download button to get the application, there are still the ones that affect you, the final user, the most important party involved.

Therefore, here is the idea. We want to involve the community of users in our Quality Assurance activities.

How can we do that? To tell you the truth, we have plenty of ideas on what we can do and how to promote it. After all, until one month ago I was still just one of the many community members, and I have some ideas.And there are many brilliant people in house who came up with promising ideas.

But at this stage I don't want to tell you what I think it should be done, but rather listening to what users propose.

The questions to answer are:
  • how can MySQL involve the users in earlier bug finding?
  • how can MySQL involve the users in its testing process?
  • what else can community members do for QA? (hints: bugs verification, performance testing, standard compliance testing)
  • What kind of incentive would make you (more) willing to cooperate? (hints: public recognition, free services, discount on services, contests with prizes)

If you have an idea related to this topic, even if it is not an answer to the above questions, write a comment to this post, or drop me a line (giuseppe {mind the spelling} at mysql dot com).

Thanks in advance!

Also published at O'Reilly Databases

Tuesday, October 03, 2006

Take the MySQL Certification in five steps

I recently took two certification MySQL 5 exams. At that time, I was on vacation, but now that I am back I would like to share with the community some advice on how to pass the exams.
Son't worry, it is not about cheating. But read on, and you'll decide if it was worth listening.

1. read the book

So much for the ones who thought I was teaching some tricks. Nothing like that. Let me tell you why you should read the book.
  • You could read the (free) online manual instead. No questions about that. If you read the whole manual, you will know all is needed to pass the exam. But you will have to read twice as much as the book (about 1300 pages of user manual instead of 672 pages of certification book).

  • The book will tell you what is important for the exam and what isn't. True, even if the certification exam does not mention it, it could be important (and usually is), but you really want to pass, don't you? So, the book is better.

  • The book is organized by exams. There are four of them, and the book covers their subjects nicely in a very organized way. In the manual, you either read it all, or you will never be sure that you covered everything.

Notice that, although I am now a MySQL employee, I don't get any share from the book sales. What I am saying here is what I honestly believe. I bought the book long before an employment with MySQL was even faintly suggested. Actually, I should add that everything in this article is my personal opinion, and it is not the official take of MySQL AB.
Summing up this item: Read the book, because it will save you time.

2. Get some hands-on experience

Reading the book (or the manual, or both) is not enough. Even if you commit the whole beast to memory, it won't be enough to pass the exam. To pass it you need to apply your knowledge to some real world problems. I can't tell you the questions you are going to get in the exam, but I can tell you the gist of it.
During the exam you won't get questions like "what does the book say about this matter?"
Instead, you will get questions like "given this problem with this set of conditions, which of the following actions is most likely to solve it?"
If, in addition to reading the book, you have some practical experience, you will be able to apply what you learned and answer the questions. If you have a prodigious memory and remember every word of the user's manual but have never tried some of that stuff in the wild, chances are that you won't pass the exam.

3. answer the sample questions from the book

After you get some experience, then try to answer the sample questions from the book. Be aware that the book ask questions in a way that is different from what you get in the exam. The book my ask you to "list all the methods to solve a given problem," while at the exam you get questions like "which of the following methods will solve the given problem?" and you get a multilple-choices-list. So the questions from the book are actually more difficult than those in the exam itself. That's fair. If you answer all the questions from the book, the ones in the exam will look a lot easier.

4. Participate to a forum and answer questions

When it comes to practice questions, you can't get enough of them. After you answer all the questions from the book, you still feel that some more exercise could do you good. There's an easy way of practicing. Subscribe to a mailing list, a newsgroup, a forum dedicated to MySQL, and read through the questions that people ask every day. Even better answer some question yourself! Start with the easy questions, and then try tackling the though ones. You may not know all the answers, but you can find out, because you have read the book and you know where to look in the manual. If you don't, it's a good moment for starting. The real trick is this: whenever you answer a question about something that you know only in theory, spend a few minutes to do it in practice. This way, you will be sure that your answer is correct (and you'll avoid some embarrassment) and you will add some more experience to your bag of tricks. This whole process will boost your confidence a lot. After a few weeks of answering at least one question per day, you will be a celebrity in that forum of your choice, you will have made somebody happy, and many people will have thanked you. What better way of studying?

5. Play chess

Now, wait a minute! What has this to do with the exam? Don't worry. I am not out of my mind, and I will explain shortly what I mean.
During the exam, you will have to answer 70 questions in 90 minutes (the upgrade exams has a different timing, but if you go for it you will have already taken an exam, and you know already what I am talking about). This is a great source of stress. Having a clock that clicks your time away can have a negative influence on your answers. Talking to some other candidates who took the exam, the greatest concern was that time restriction. But you know what? It was not a problem at all for me. And the reason is that I am a chess player, and therefore I am used to taking decision with a clock ticking at my side, and telling me that my time is near exhaustion. In competition chess games, you are given a double clock with two buttons. When it's your time to move, your button is up and your time is running. When you have decided your move, you make it, and push the button. Then your clock stops and your opponent's start ticking.
If you are used to this stressful way of taking decisions in rapid (30 minutes for the whole game) or blitz games (five minutes!), a simple clock giving you 90 minutes for 70 "moves" looks like a joke.
So, if you play chess, resume your chess club card, or play some Internet game, and get some practice at time management. If you don't play chess, answer the above mentioned questions with a clock that rings after a given time.

Then, get a good night's sleep and take the exam

As a last piece of advice, remember that a certification exam is a stressful experience, no matter how well you have prepared. So you need all your strenght and energy for it. Go to the exam well rested and fresh. If you have to take more than one exams, don't do them in a row. Put at least a few hours before the next one, and in between take a walk, read a book, or do anything to recharge your spirits.

Good luck!

Monday, October 02, 2006

Are logins before download any good?

If you are used to open source products, chances are you have gone through this routine more than once. Search for what you need, find a suitable product, go to its web site, download it, test it. Then, if you like it, you start using it right away, otherwise you dump it without a second thought.
The whole process takes less than one minute for small packages. But anyway, even for larger packages, the total time that this whole business requires your attention is very low. Even if it requires a huge download, it can be left unattended and you can resume the testing task when you feel like it. The bottom line is that we got used to a quick try-and-use process of open source products.

Sometimes, though, while performing the above routine, there is a unexpected obstacle. The product maker requires a free login. You don't have to pay anything, but you have to go through the motions of filling a form that asks you everything about your precious self, your company, education, employment history, financial health, and so forth.
Filling these forms is really annoying for several reasons:

  • You got used to the quick download-and-try business, and this sudden stop is not welcome
  • You can't see any added value in this form filling. Actually, you are sure that your level of spam (both by email and by regular mail) will increase;
  • You think at the waste of time this form is, especially considering that you may be throwing the whole product away after ten minutes.
  • This is contrary to the whole open source spirit, where you achieve success by providing a good product. The register-bedore-downloading strategy, instead, tries to cheat into a let's-grab-a-potential-customer-as-soon-as-he-shows-up utterly losing attitude.
After this problem has bitten you once or twice, you start developing a strong defense strategy. The next time some site asks you for a registration before downloading, you start filling the form with fake information, using a temporary but legitimate email address, claiming to run a multi-million dollar business, and presto! you get away with the download, never to be seen to that site again, unless that product is really a earth-shakening tool (which seldom is, in those cases).

And so here are two reasons not to impose a registration before downloading an open source product:
  • It's useless. If you want to cheat the unfair system, they can't do nothing to prevent it.
  • It's damaging. If they want to propose an open source product, imposing a registration is like screaming: "Hey! We want to play the open source game, but we are totally and hopelessly unaware of how to play the game. Cheat us!"
Which I usually do.

Also published at ITToolbox

Friday, September 29, 2006

Log Buffer #12: a Carnival of the Vanities for DBAs

I fell for it. I commented on the Log Buffer and shortly after that I was offered to host an issue. Here we go, then. Welcome to Log Buffer #12!

Confessions of an IT Hitman by Dratz features a strong message: Don't build a data warehouse, arguing that most of the times a DW is built, it's just because a clueless customers was either listening to buzzwords from a salesman or following the latest trend. The key message, for the ones who missed the build-up of the data warehousing movement during the past decade, is DW is a business solution, not a technology solution. OTOH, there are projects that would really need a data warehouse, and don't get one. But this is a different story.

Mats Kindahl in his MySQL Musings talks about Replication and the disappearing statements, i.e. the risks and gotchas of replicating data in MySQL while limiting the databases involved in the replication process. It is something that the manual states quite clearly, but it bites back quite often nonetheless. Mats explains some unusual points in this old issue. Querying a table with a fully qualified name may result in more than overkill. You may be the next victim of the infamous disappearing statement.

Brian Aker is always a volcano of ideas. His Brian 'Krow' Aker's Idle Thoughts don't show much idleness. Rather, he's often producing some hack at great speed, or he's pondering on a new hack. This one, Firefox, yum, sounds interesting, because the suggestion came from MySQL CEO, who is also a man of vision. It looks like something will come out of this clash between dreamers.

Vadim Tkachenko from MySQL Performance Blog, which he runs together with Peter Zaitsev goes int a wrestiling with the latest beta of SolidDB for MySQL.

Test Drive of Solid is a comparision between the behavior of the SOlid engine compared to InnoDB, to check if a migration from InnoDB to Solid could be done easily. Vadim finds several differences and a few bugs. SolidDB doesn't seem to be solid enough in this beta, but it's getting closer.

A charming set of slides from Lars Thalmann in his Lars Thalmann's Blog talking about the joy and the shock of Being a MySQL Developes. If you thought that a developer for an open source company had an easier job, think again. Go through Lars's slides and discover a new dimension of cooperative work.

Good news from Markus Popp ( blog). For almost one year I have waited for MySQL status and variables to be available as INFORMATION_SCHEMA tables. Now it seems that MySQL 5.1.12 offers New information_schema views that do exactly that. I built MySQL from source a few days ago, and the new features were not there. After seeing Markus's post, I rebuilt it, and spent some time toying with the fresh additions. For example, calculating the key cache efficiency is one task that previously you had to perform with the help of a programming language. Now you can do it in SQL.

A glimpse into the future from Mikael Ronstrom, senior software architect at MySQL AB. The State of MySQL partitioning seems to be close to bug-free, and he's already fiddling with new enhancements in the next version of MySQL. It's almost a pity that I had to submit a new bug for partitioning just hours after his post!

Matt Asay from Open Sources examines The spirit of winning, taking into account the elements tha make a winning team in athletics, and finds that the same elements apply to open source competitiveness. Food for thought.

Bob's World by Bob Field is usually full of interesting concepts. This one, Advanced Features as Crutches is no exception. Advanced features, Bob warns against using an advanced feature just because it's there. Everything has its place, but it is no mandatory to use all the frills a DBMS engine offers. If you do so, security and simplicity may suffer. I fully agree.

In The Oracle Base Blog, Timothy S. Hall turns a joke into a lesson. So many people ask Where's the 'Go faster' switch, and they won't understand the hard work necessary to actually improve the performance. So he makes a surprising proposal. Read on.

Craig Mullings's features two entries that appeal to common sense. Intelligent Database Management lists the tasks of a DBA, stressing the effort that has to be put into the job. Only through intelligent automation of the tasks it is possible to keep up with the chore of managing database systems at a high professional level. Choosing the best DBMS is an old question that does not get a straight answer. Craig goes explains what you need to take into account to reach a sensible decision.

If you liked the previous entries, be aware that Craig writes in different places. DB2PORTAL Blog is one, and there he wrote Sequence Objects and Identity Columns. It is not another chapter of the natural vs surrogate keys saga, but a lucid explanation of what are the differences between identity columns and sequence objects, when each of them is needed and how to handle them. Even if DB2 is not your database of choice, his explanation can help you choose the right solution.

This entry gives a deja vu feeling after you read the one by Craig about Intelligent Database Management. One day in the life of a DBA, as seen by Jeff Hunter is what you get from reading What do you do all day? (a piece of So What Co-Operative). Not a common DBA, mind you, but a very organized one. As seen previously, the good DBA job is a balanced mixture of experience, organization, and an educated choice of technology. That way, even long lasting tasks can be approached with a quiet frame of mind. Jeff's tasks include such things as monitoring the databases, insuring that backups are done, checking for critical conditions. He sounds less worried than the average DBA, because he merges experience and knowledge with the right tools for the job.

Raven Zachary is a contributor to The 451 Group. In Open source database poll highlights barriers to adoption he comments on a survey launched by SolidDB about the acceptance of open source database. It seems that most of the IT professionals attending a recent Linux Expo were timid about adopting open source database.

A different angle is offered by another research, as reported by Zack Urlocker in his The Open Force blog. Linux Database Attach Rate says that 85% of RedHat customers show a strong interest in open source databases. I guess that this research and the survey in the previous item come from different sources. Zack also reports on the Zmanda Recovery Manager, an open source (dual licensed) tool dedicated to backup and recovery of MySQL databases.

Paddy Sreenivasan covers the same topic in Selective recovery of MySQL databases at the O'Reilly database weblogs. Paddy highlights the common needs in a backup solution and explains how Zmanda covers them.

If you jump to Sheeri Kritzer weblog, its title "My-ess-queue-ell vs. My-see-quel" will give away her main area of expertise. One intriguing entry in her blog, Choosing datatypes for fields is a clever hack to improve the quality of data uploads into MySQL tables. So simple, and yet so good. To choose the best datatype, first use a bad one, and then let the system tell you which ones you can have. Well done! If you know Sheeri (I had the pleasure of seeing her at work during the MySQL Users Conference, but reading her blog is enough to get the idea) you will see that she doesn't accept anything blindly, but she needs to explore and dig deeply every concept. In Isolation & Concurrency she takes Jim Starkey's assertion that MVCC could not be serialized as a starting point to an enthusiastic explanation of why you can't do it.

DBMS2 features a piece by Curt Monash, delving into the intricacies of data warehousing terms.
Data warehouse and mart uses - a tentative taxonomy is a quick introduction to the world of business intelligence by comparing the features that each different technology supports or requires. It won't replace a good book, but it sets the record straight in less than a page. Really commendable.

The scintillating Roland Bouman has done that again. He saw an intriguing problem with some clever solutions, and found a way of doing the same job in pure SQL. The matter at hand was Finding Redundant Indexes using the MySQL Information Schema. Others have done this before, using different methodologies. Roland points to the language-independent solution, and he delivers, as he usually does. Browsing Roland Bouman's blog you'll find several witty examples of his analytic approach.

A storm of advice for Oracle developers from ... a DBA. Andy Campbell in his Oracle Stuff I Should Have Known ! gives sensible advice to developers. If Only ... more developers used some nifty features that andy explains in full, their life (and the DBA's as well) would be much easier. This kind of advice (use application metadata to say which parts are being executed) would be good for any DBMS. If only ...

Thomas Kyte grabs the suggestion in The Tom Kyte's Blog and elaborates on his own about Action, Module, Program ID and V$SQL.... This entry, like the previous one, generated quite a stir, with good and bad vibrations on both sides of the Developers-DBA divide.

And finally, a personal closing note. I mentioned in one of my previous posts that I joined MySQL AB as QA developer. A few days in the job, and now I am experiencing the thrill of working in a virtual company

Thursday, September 28, 2006

Working in a virtual company

A few days in the job for MySQL and still, even though I can't say I have mastered the tasks at hand (too much to get used to!) I tasted the feeling of working with perople living in different time zones.
To make this feeling more real, I went to a Chinese shop a few blocks down my street and I bought six cheap clocks. I know that I could use the net but the tangible clocks give me a sense of being in a "real office"!

Monday, September 25, 2006

Riding the Dolphin

I have been a consultant for long time. It is an interesting job, which has given me much satisfaction. The coolest part of the job is the inner pleasure you feel when you solve a difficult problem in a few minutes, earning a reputation for being a wizard.
The negative part of the job is being alone. As a consultant, you are supposed to know everything, and as such you don't have anybody to talk with when you have doubts (and if you are really good you must have doubts from time to time). Well, this condition makes you stronger, and improves your confidence, but your interaction with your co-workers is quite weak.
So I decided to try a new career for a change, and to cash on my experience and knowledge of databases in general and MySQL in particular. And which position should be more suitable than one at MySQL AB itself?
I applied for a couple of jobs at MySQL, got two offers, chose the one that appealed me the most, and here I am. Today I joined the MySQL Quality Assurance department, while turning to a minimum my involvement with my consulting company.
From now on, I'll write a lot about testing and quality assurance. I have talked about testing in the past, but only as a matter of personal taste when developing. Now it's time to involve the community in the testing and QA process. More about this later.
In the meantime, I take a break from consulting and start the new job.

Thursday, September 14, 2006

A fruitful vacation - MySQL 5 certification

Near the end of a two weeks vacation in southern Italy, I combined pleasure and business and took the 4 to 5 upgrade exams, which I passed, to achieve both MySQL 5 DBA and Developer certifications.
I took both exams today in Palermo, one in the morning and one in the afternoon. Now I am back to full-time tourist for a few more days.

Thursday, August 31, 2006

MySQL Sandbox tutorial reprinted by (IN)SECURE magazine

The latest issue of (IN)SECURE magazine, a freely available digital security magazine discussing some of the hottest information security topics, is featuring a reprint of my introduction to MySQL Sandbox.

Friday, August 11, 2006

MySQL Federated Engine: the missing manual

If you have used the MySQL Federated engine, you may share my opinion that it's perhaps the most amazing feature introduced in recent versions, far more interesting and promising than other more advertised ones.
If you have used it, though, you must have noticed how skinny is the official manual. There are countless questions popping from your head while testing this feature, and no answers in sight.
Federated Engine Missing ManualWell, take heart! The missing manual for the Federated Engine was just released. As any missing manual, it is not an official document, but just a collection of many practical things I found out in my daily usage. There must be much more to find and to try, but for now, enjoy this article as the first step toward being productive with the Federated engine.

Errata: you can safely ignore the lines mentioning foreground in the color codes table. The producer has not rendered it the way it should have been. It will be hopefully fixed soon.

Monday, July 31, 2006

MySQL Sandbox tutorial translated in Portuguese

A while ago I wrote a tutorial on the MySQL Sandbox.
Wallace Reis asked me permission to translate it, and I happily agreed.
And now there it is: the tutorial Portuguese translation (the first part, but the rest will come).
Thanks, Wallace!

Friday, July 28, 2006

An ugly query should be a warning bell

A recent question in a newsgroup was

Given this table in MySQL 4.0, how can I run a single query to sort the records by the number of pipes in myfield?
id(int) -------- myfield(varchar)

1 --------- |number1|number2|
2 --------- |number1|
3 --------- |number1|number2|number3|
4 --------- |number1|number2|number3|number4|

Suppressing my urge to say something about the data structure, I suggested this clever solution from a test sample I deduced from the request:

select * from mytable;
| id | myfield |
| 1 | 10|1|12|9 |
| 2 | 1|2 |
| 3 | 19|5|59|91 |
| 4 | 111|31|10|1|12|9 |
| 5 | 1|2|7 |
| 6 | 95|59|91 |
| 7 | 123456789|2000000 |

select id, myfield,
'0','') as so
from mytable
order by so;
| id | myfield | so |
| 2 | 1|2 | | |
| 7 | 123456789|2000000 | | |
| 5 | 1|2|7 | || |
| 6 | 95|59|91 | || |
| 1 | 10|1|12|9 | ||| |
| 3 | 19|5|59|91 | ||| |
| 4 | 111|31|10|1|12|9 | ||||| |

However, the poster added hastily that his request was wrong, and that what he identified as "numbers" are actually character strings of various length, thus making my solution much harder to find. The sample given was

1 --------- |ruote in lega|macchine gialle|
2 --------- |case in affitto|
3 --------- |treni|vagoni|camini|

But then I overcame my desire of showing off with my cunning solutions, and told him that, apart for the initial misunderstanding about numbers and strings, his table was stained with the unforgivable sin of not being even in 1NF.
Therefore, the right course of action would be to split the table:

1 ruote in lega
2 macchine gialle
3 case in affitto
4 treni
5 vagoni
6 camini

ID id_content
1 1
1 2
2 3
3 4
3 5
3 6

This way, the ugly query to sort by "how many pipes" would be reduced to the simple

SELECT id, count(*) as how_many
from mytable inner join contents using (id_content)
group by id
order by how_many

Beware of ugly queries. Often they tell you that something in your structure design may be wrong.

Monday, July 17, 2006

Obscure settings

I remember when MySQL was spreading rapidly, mostly due to its ease of use. That was long time ago, apparently. Now we are entering the dark ages of obscure settings, or so it seems, reading some MySQL Cluster setting definitions.
Quoting from the the manual:


This parameter is an exception in that it does not specify a time to wait before starting a new local checkpoint; rather, it is used to ensure that local checkpoints are not performed in a cluster where relatively few updates are taking place. In most clusters with high update rates, it is likely that a new local checkpoint is started immediately after the previous one has been completed.

The size of all write operations executed since the start of the previous local checkpoints is added. This parameter is also exceptional in that it is specified as the base-2 logarithm of the number of 4-byte words, so that the default value 20 means 4MB (4 × 220) of write operations, 21 would mean 8MB, and so on up to a maximum value of 31, which equates to 8GB of write operations.

Any suggestions for taking this definition down to planet Earth?

Thursday, July 06, 2006

Measuring MySQL resources consumption on Linux

If you have ever felt the need of measuring how much of your resources a MySQL process is eating up, you're welcome to share my experience on the subject, reading Measuring resources for a MySQL server on Linux, which also introduces mysqlresources, a new command line tool that gets your server's statitics from the operating system and prints a nice report.

With MySQL, it's easy to get statistics from the server itself, but sometimes you need a view from the outside. mysqlresources does just that. Read on.

Wednesday, July 05, 2006

A first taste of logs on demand

From time to time I install MySQL from the development tree, to see what's new, and in the latest one I found a pleasant surprise:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {datacharmer} ((none)) > show variables like "%log";
| Variable_name | Value |
| back_log | 50 |
| general_log | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| log | OFF |
| slow_query_log | OFF |
| sync_binlog | 0 |
6 rows in set (0.00 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
Empty set (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > set global general_log=1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
| event_time | user_host | thread_id | server_id | command_type | argument |
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | select * from mysql.general_log |
1 row in set (0.04 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
| event_time | user_host | thread_id | server_id | command_type | argument |
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | select * from mysql.general_log |
| 2006-07-05 17:27:08 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | select * from mysql.general_log |
2 rows in set (0.00 sec)

mysql [localhost] {datacharmer} ((none)) > set global general_log=0;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost] {datacharmer} ((none)) > select * from mysql.general_log;
| event_time | user_host | thread_id | server_id | command_type | argument |
| 2006-07-05 17:26:56 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | select * from mysql.general_log |
| 2006-07-05 17:27:08 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | select * from mysql.general_log |
| 2006-07-05 17:27:11 | datacharmer[datacharmer] @ localhost [] | 3 | 0 | Query | set global general_log=0 |
3 rows in set (0.00 sec)
As you can see, the promised logs on demand are there.
This feature was long overdue. Well done!

Tuesday, June 20, 2006

Talking at FrOSCon 2006

I will be at FrOSCon 2006 on June 24, with a talk about Pivot tables in MySQL 5.
I will arrive in Sankt Augustin on June 23, flying from Alghero Fertilia, Italy to Frankfurt Hahn, Germany and then driving to the conference site (travel plan).
The funny thing is that, when I booked my flight, I had no idea that I was about to get in the middle of the Soccer World Cup. Being no soccer fan, I realized this fact only when the media circus started and I could not avoid being informed. Oh, well, now I understand why it wasn't easy to find a hotel, even booking two months in advance!

The slides and the source code for this talk are available from my site.

Friday, June 02, 2006

Filling test tables quickly

Let's say that you are building a new application, and you need to test it against a large set of data. You would need either to borrow the data from some known source or to create it yourself.

If you have such collection at your disposal, good for you. But more often than not you need some sort of data that is not ready in your repositories, and then you need to have a quick method to create it.

I will tell you three quick tricks to create large datasets of simple data types, namely numbers, words, and dates.

Le's assume that we need at least one million records. Here's how to go for it.


Creating a large table with just a numeric field is quite simple.

You may be tempted to run a loop in your favorite language, or even in a SQL stored procedure, but this approach would run for quite a long time.
There is a better solution, that will fill your large table in a few seconds

drop table if exists numbers;
create table numbers ( id int not null primary key);

delimiter $$

drop procedure if exists fill_numbers $$
create procedure fill_numbers()
declare counter int default 1;
insert into numbers values (1);
while counter < 1000000
insert into numbers (id)
select id + counter
from numbers;
select count(*) into counter from numbers;
select counter;
end while;
end $$
delimiter ;

call fill_numbers();
Rather than inserting 1,000,000 lines, this procedure will insert just one record, and then it will double the table 20 times, until we end up with a table containing 1,048,576 lines (220). This operation runs in less than 8 seconds in my laptop, which is quite slow if compared to my usual servers.
Even without a stored procedure, you could just insert a line manually and then execute 20 times this query:
insert into numbers (id) select id + (select count(*) from numbers) from numbers; select count(*) from numbers;

And it should not take you more than 30 seconds.


If your case calls for a large list of unique words, you could of course build it with a program, but then again the insertion process would be quite slow to complete. A faster method is to load an existing list from as file.

All Unix system include a word list, whose size ranges from a few thousand to half a million. If you don't have such a list available in your box, you can get one (or build it from several ones) from several places. Good places to start looking for words are this and this.
At the end of the exercise, let's say that you've got a list of about half a million distinct words in /usr/share/dict/words. Thus, you can proceed to build your table.

drop table if exists words;
create table words (
id int not null auto_increment primary key,
t varchar(50) not null

load data local infile '/usr/share/dict/words'
into table words (t);

Query OK, 518584 rows affected (4.94 sec)
Records: 518584 Deleted: 0 Skipped: 0 Warnings: 0

select count(*) from words;
| count(*) |
| 518584 |
1 row in set (0.04 sec)
That was quick, but we got only about half of what we needed. We said that we wanted one million records, and here we only have a little more than five hundred thousand.
Since we need unique words, we can ask the database to produce the missing half by reversing the existing ones.
insert into words (t) select reverse(t) from words;
Query OK, 518584 rows affected (3.98 sec)
Records: 518584 Duplicates: 0 Warnings: 0

select count(*) from words;
| count(*) |
| 1037168 |
Almost there. Now we passed the million records mark, but we are not sure that they are unique, since the reverse of one word could be the duplicate of an existing word (think about mood and doom, for example). Thus, to complete the task, let's add a unique index with the IGNORE clause, so that we'll get rid of any duplicate.
alter ignore table words add unique key (t);
Query OK, 1037168 rows affected (46.69 sec)
Records: 1037168 Duplicates: 5791 Warnings: 0

select count(*) from words;
| count(*) |
| 1031377 |
That's it. A million words, without much sweat.


Finally, let's see how to create a large list of dates. Actually, you would seldom need a million dates, since a million days cover more than 2,700 years. A table of just dates, thus, will usually range from 1,000 to 10,000 records. When you need a million records, you are more likely talking about DATETIME values, with intervals of hours, minutes, or seconds. Nothing will prevent you from using this technique to create one thousand DATE values, but let's keep to our original goal of filling a large table.
Then, if we want records with one minute interval, we can proceed like this:
drop table if exists dates;
create table dates (
id int(11) not null auto_increment primary key,
dt datetime not NULL
) engine=myisam;

delimiter $$

drop procedure if exists make_dates $$
CREATE PROCEDURE make_dates( max_recs int)
declare start_dt datetime;
declare numrecs int default 1;
set start_dt = date_format( now() - interval max_recs minute, '%Y-%m-%d %H:%i:00');

insert into dates (dt) values (start_dt );

while numrecs < max_recs
insert into dates (dt)
select dt + interval ( numrecs ) minute
from dates;
select count(*) into numrecs from dates;
select numrecs;
end while;
end $$

delimiter ;
Does it look familiar? It should, since it's the same technique we've used to put a set of numbers into a table.

Only this time we use the record count to calculate the interval in minutes between the existing records and the ones being inserted.
Also in this case, we double the table 20 times, to pass the one million records mark.

call make_dates( 1000000 );
| numrecs |
| 2 |
1 row in set (0.02 sec)

| numrecs |
| 4 |
1 row in set (0.02 sec)

# ... 16 more counts

| numrecs |
| 524288 |
1 row in set (5.99 sec)

| numrecs |
| 1048576 |
1 row in set (10.18 sec)

select count(*), min(dt), max(dt) from dates;
| count(*) | min(dt) | max(dt) |
| 1048576 | 2004-07-07 13:57:00 | 2006-07-05 18:12:00 |
It took a little more than the numbers, because of the overhead of calculating one million date intervals, but it was about ten seconds in total, which is a reasonable time to get a test table.
There are other techniques as well, but these three are techniques that you can use to get the job done even without dedicated tools.

Wednesday, May 24, 2006

Tutorial for a more powerful MySQL Sandbox

The MySQL Sandbox has been enhanced, with a new express installer, capable of setting up a sandbox in just a few seconds, and an interactive wizard, for fine tuning complex installations.
A lengthty tutorial is accompanying the new release.

This tool could be the basis for a long overdue interactive installation tool for Unix systems. What do you think?

Sunday, May 14, 2006

New homes

I have a new web site. I registered the and domains, which are now jointly my main page. (Both addresses point to the same page)

Another change involves the MySQL General Purpose Stored Routines Library, which has evolved to version 1.0 (not alpha anymore). Its home is now at SourceForge.

Wednesday, April 26, 2006

MySQL Community Awards

Second day of the MySQL USers Conference 2006.
At breakfast, Arjen approached me, asking if I could hang nearby the podium during the keynote. "You may end up on stage", he said. I thought that they were going to hand me the iPod that was announced some time ago. So I went there and secured a seat as close as I could, together with Markus, Beat, Roland, and Sheeri.
However, instead of the iPod (I got it after lunch, BTW) I was called on stage to get a Community Award, and the same was granted to Roland, Markus, and Rasmus (!!)
price giving

Looks quite useful. Now when somebody asks difficult questions, I can always consult the crystal ball. :)

Thanks to the MySQL Community for this wonderful surprise, and congratulations to Roland, Markus, and Rasmus!

(Pictures courtesy of Roland. Thanks)

Tuesday, April 25, 2006

Talk at the MySQL UC: Higher Order MySQL

From the highly unorthodox department, a speech about stored routines that create other routines in MySQL.

Higher Order MySQL at the MySQL Users Conference, 17:20, Ballroom C.

Get the source code and examples.

Friday, April 21, 2006

Advanced replication techniques in MySQL 5.x

OnLamp has just published an article I wrote about some interesting things you can do with MySQL 5.x and replication.
  • You can create an array of peer-to-peer nodes that are at the same time master and slave, without much worry about insertion conflicts, thanks to some server variables introduced in MySQL 5.0;
  • You can achieve automatic failover using a combination of federated tables (MySQL 5.0) and events (5.1)
The article is a proof of concept that using such techniques you can achieve your goals without help from external applications.

If you want to get your hands dirty, using the companion package MySQL Replication Playground you can try out the goodies described in the article without risking your valuable servers.

Comments welcome.

Introducing MySQL 5 Replication Playground

As promised when I talked about the MySQL Sandbox and as mentioned in this (very) recent article on replication, I have released a testing environment for replication, called the MySQL 5 Replication Playground.
It's a package that creates in one single server an array of four replicating nodes using MySQL 5.0 or 5.1. The purpose of this package is to help developers and administrators when they need to test applications involving replication. You don't need to use several boxes. One machine is enough. With just one instance of MySQL installed, the Replication Playground will install four data directories and it will create the necessary scripts to start, stop, and using the nodes.
The installation comes in two flavors: standard (i.e. one master and three slaves, as usual) and circular (where each node is at once master and slave, as explained in the above mentioned article).
Once you install it, you can play with it and test your failover procedures, without worrying about your valuable production data. The playground works with separated ports and sockets, and it's completely independent from any other MySQL installation.

The docs in the distribution package contain also some sample sessions with both the standard and the circular installation.

The package requires:
  • Linux, or FreeBSD (theoretically, it should work with any Unix flavor, but it has been tested only with Linux and FreeBSD)
  • MySQL 5.0 or 5.1
  • bash
  • tar
  • gzip
  • Perl (for the installation)
  • Optionally, you need the Perl DBI and DBD::mysql to run some test tools

Wednesday, April 19, 2006

Introducing MySQL Sandbox

Normal MySQL users are going through a flurry of announcements about new versions that make them salivate ath the thought of what can be done with these new goodies. In November, version 5.0 became generally available, thur ready for production, then version 5.1 entered beta.
Ah! It would be nice if I could just install the new versions and test them. Sometimes, though, you can't do that, because you don't have root access, or you have already an older MySQL server running.
For years, due to my consulting duties, I have maintained several non-conflicting, different versions of MySQL. I did that manually, using some scripts that I have developed and tuned over time. For me it is not a big hassle. I am used to the command line, I wrote the tools, I know what to take care of, it's easy. Not so for some of my co-workers, who need to use the same facilities but lack the same degree of confidence that I got with trial-and-error.
So finally I put together all I did for myself and transferred most of my own knowledge into a Perl script that can create a seamless installation of MySQl 5.0 or 5.1 without need of root access, and without disturbing (or being disturbed by) existing MySQL installations.
I called this tool the MySQL Sandbox and I put it on SourceForge.

With this package, achieving your goal of a separated testing installation is quite simple (provided that you are running Linux or some other Unix, that is):
  • Download the binaries for the version of MySQL you want to try, or follow Markus Popp's instructions and compile your own.
  • Unpack MySQL binaries in a convenient place (for the sake of this example, let's say you put it under $HOME/binaries/5.1.10)
  • Download the latest MySQL Sandbox package and unpack it.
  • Run the install program, saying where are the binaries
$ ./ --basedir=$HOME/binaries/5.1.10
The MySQL 5 Sandbox, version 1.1 19-Apr-2006
(C) 2006 Giuseppe Maxia, Stardata s.r.l.

installing with the following parameters:
basedir : /home/gmax/binaries/5.1.10
binbash : /bin/bash
conf_file :
datadir_from : archive
db_password : datacharmer
db_user : datacharmer
force : 0
home_directory : /home/gmax
install_version : 5.1
my_file :
operating_system_user : gmax
sandbox_directory : mysql_sandbox
sandbox_port : 3310
verbose : 0
do you agree? ([Y],n)
If you answer "Y", or just press ENTER, the sandbox will create a $HOME/mysql_sandbox directory, and a data directory under it, containing the minimum for your MySQL installation to work.
The destination directory will also contain some shell scripts to start, stop, and use the test server.
Optionally, you can also change the default configuration file by specifying --my_file={small|large|huge} . The sandbox installer will try to locate the sample files from the basedir you indicated, and will merge the basic parameters into your destination file (my.sandbox.cnf)
Once the installation is over, you go to the newly created directory, and start the server:
$ ./
~/binaries/5.1.10 ~/mysql_sandbox
sandbox server started
After that, using the new server is easy.
$ ./
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.1.10-beta-max

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql [localhost] {datacharmer} ((none)) > exit
As you see, there is also a dedicated prompt that may remind you what you are using.
It has been tested in several Linux boxes. Ideally, it should work on every Unix box. Read the docs, or try
$ ./install --help

Happy testing!

However, that is not the end of it. More goodies are coming in the next days. Stay tuned.

Sunday, April 16, 2006

SQL Easter egg

There is a tentative Obfuscated SQL contest with one entry by Kai Voigt.
SELECT CONCAT(CONV(536,8,20), LPAD(EXPORT_SET(1,'Y','N','',1), 3, "P"), CHAR(32), HEX(234), UNHEX(594939469*POW(3,2)))
Not bad, but ... SQL without data ? Doesn't sound right.
Here's my take (requires MySQL 4.1 or later):
drop table if exists complex;
drop table if exists simple;
CREATE TABLE complex ( simple char(2), complex char(1)) ;
CREATE TABLE simple ( complex char(2), simple char(1));

INSERT INTO complex VALUES ('0','7'),('1','6'),('2','6'),('3','6'),('4','2'),
INSERT INTO simple VALUES ('0','4'),('1','F'),('2','5'),('3','8'),('4','0'),
set @complex = sqrt(sqrt(0x19a1));
set @simple = sqrt(sqrt(sqrt(0x290D741)));
select group_concat(unhex(concat(simple,complex))
order by complex.non_simple separator '' )
from (select simple ^ @complex non_simple, complex simple from complex) simple natural join
(select complex ^ @simple non_simple, simple complex from simple) complex ;


Sunday, April 09, 2006

Sorting data by set of keys

This seems to be a popular problem, since I saw it twice in the past few weeks in two different newsgroups. Somebody complains that, having query with a set of keys like this
SELECT * from mytable where id IN (200, 2 ,100)
they get the results in a order that ius different from the one they specified. For example, they may get something like
id in (200,2,100);
| id | contents |
| 2 | b |
| 100 | aa |
| 200 | bb |
One of the posters complained that the result should come in the specified order [200,2,100], and that MySQL was arbitrarily sorting its resultset.
When I see such requests, I usually explain that there are two misconceptions. The first being that MySQL sorts results without asking. That it does not do, because a DBMS engine is basically lazy by design, and it won't do anything unless explicitly asked for. The second is that you are asking for a sort order in your set of keys. Such list is just an indication of which keys to consider for filtering the results, and it has no influence at all on ordering. If you want ordering, you need to say so explicitly, using the ORDER BY clause.
Once I clarified what the problem is, let's see the solutions.
You can sort it on-the-fly, if your values are reasonably few
id in (200, 2, 100)
order by
case id
when 200 then 1
when 2 then 2
when 100 then 3
end ;
| id | contents |
| 200 | bb |
| 2 | b |
| 100 | aa |
This solution looks like a hack, and in fact it can negatively affect performance, and it is difficult to handle when you need to change the sort order. The best way is to use a support table providing the required sort order.
create table sorting_table (
sort_order int not null auto_increment primary key,
fk_id int,
key (fk_id)
# here we insert the values in the order we want.
# the auto incremented key will insure that this is the correct one
insert into sorting_table (fk_id) values (200), (2), (100);
select * from sorting_table;
| sort_order | fk_id |
| 1 | 200 |
| 2 | 2 |
| 3 | 100 |
Now to get the wanted order we use a join between the main table and the secondary one.
inner join sorting_table on (id=fk_id)
id in (200,2, 100)
order by
sort_order ;
| id | contents |
| 200 | bb |
| 2 | b |
| 100 | aa |
This is a much better solution, which offers flexibility and efficiency at the same time.

Dumping MySQL information schema

I have been invited to join the O'Reilly weblogs on databases.
Today I gave my first contribution, with a piece on dumping MySQL information schema contents.

Sunday, April 02, 2006

Measuring replication speed

I am writing an article about some advanced replication features (coming soon), and I wanted to measure the speed of each specific architecture I am working with.
There seem to be no builtin MySQL solution for this particular problem, and thus I am using a hand made metrics tool, inspired by something that Jeremy Zawodny suggested in his High Performance MySQL a few years ago (see the relevant chapter on replication with code).
The naive method would be to set a timestamp in a master table, and to measure the same record when it shows up in the slave. Unfortunately, (but correctly, I have to say!) this does not work, because the binary log includes information on the current timestamp, so that master and slave in the end have exactly the same contents.
Using a trigger in MySQL 5.0 seems promising, because you can set the timestamp tothe value of SYSDATE(), thus showing the different insertion times in master and slave. However, MySQL imestamps precision is limited to seconds. Therefore, if I insert a record at 12:00:00.9999 and the slave receives it at 12:00:01.0001, MySQL will record a difference of 1 second, even though in this case the difference would be just a couple of milliseconds.

With this in mind, I worked on Zawodny suggestion, which was still dealing with seconds, to imrove the method and measure the performance of a replication system with a much better accuracy (possibly microseconds).

The schema works as follows (see the flow chart):

Get the system time in terms of seconds and microseconds (I am using Perl with the Time::HiRes module, but any major language has a corresponding feature). Insert that time in a table on the master, measuring also the time spent for the insertion operation.
Immediately after, start looping on the slave to get that same record. When you do it, get again the system time (with microseconds precision) and subtract the time you got from the table.
For better precision subtract the time you used for inserting and for fetching, and you get the bare replication time.

Here is an example, using a record of 1000 bytes, in a loop that increases the size of the record by 1000 bytes at each step.
0    41     5   1143983120       992916   1143983120       997036
1    41     5   1143983121       207218   1143983121       211084
2    41     5   1143983121       420654   1143983121       424630
3    41     5   1143983121       634802   1143983121       638909
4    41     5   1143983121       849051   1143983121       853521
5    41     5   1143983122        65003   1143983122        67892
6    41     5   1143983122       279944   1143983122       282933
7    41     5   1143983122       493895   1143983122       498320
8    41     5   1143983122       710443   1143983122       715032
9    41     5   1143983122       927867   1143983122       932013
10    41     5   1143983123       145376   1143983123       149648

This is the raw output used for debugging. The first colum is the step. The second is the ID retrieved from the record, the third one is the sequence number (I inserted 5 records for each steps, and asked to fetch the record with sequence no. 5).
The fourth and fifth columns are the system time that was stored in the table. The remaining two columns are the system time as recorded at fetch time.
With the above data, we can produce a listing of the actual raplication times, in human-readable format.
loop data size master insert slave retrieval total repl. time bare time
---- --------- ------------- --------------- ---------------- ---------
1      1000      0.001655        0.000859         0.004120  0.001606
2      1000      0.001643        0.001268         0.003866  0.000955
3      2000      0.001505        0.000850         0.003976  0.001621
4      3000      0.001604        0.000849         0.004107  0.001654
5      4000      0.001943        0.000851         0.004470  0.001676
6      5000      0.001853        0.000981         0.002889  0.000055
7      6000      0.001964        0.000968         0.002989  0.000057
8      7000      0.003285        0.001081         0.004425  0.000059
9      8000      0.001431        0.000851         0.004589  0.002307
10      9000      0.003076        0.001012         0.004146  0.000058
11     10000      0.001282        0.001600         0.004272  0.001390

So the average replication time is, in this case, 0.001040 seconds.
For this test, I was using a sandbox environment, where both master and slave are in the same server. Thus there is some slight overhead due to the fact that one CPU is handling two instances of MySQL, and the same disk is hosting two logs (binary on the master, relay-log on the slave) in addition to the actual tables.
Using a real production replication, these times go down to an average of about 0.000500 seconds. It depends on several factors, from the speed of your networking equipment to the sheer power of your boxes.

Anyway, this is a tool that can be useful sometimes. You can get the source code from MySQL Forge snippets.

Update Since the Forge is being decomissioned, I add the source code here.

use strict;
use warnings;
use Data::Dumper;
use DBI;
use Time::HiRes qw/ usleep gettimeofday tv_interval/;
use English qw( -no_match_vars ); 

my $username1 = 'user1';
my $password1 = 'user2';
my $username2 = 'pass1';
my $password2 = 'pass2';
my $host1     = 'host_IP1';
my $host2     = 'host_IP2';
my $port1     = '3306';
my $port2     = '3306';

my $dbh1=DBI->connect("dbi:mysql:test;host=$host1;port=$port1",
                $username1, $password1,
                {RaiseError => 1}) 
         or die "Can't connect: $DBI::errstr\n"; 

my $dbh2=DBI->connect("dbi:mysql:test;host=$host2;port=$port2",
                $username2, $password2,
                {RaiseError => 1}) 
         or die "Can't connect: $DBI::errstr\n"; 

my $loops                =     10; # how many times we loop (with size increase)
my $num_of_inserts       =      5; # how many records we insert for each loop
my $initial_blob_size    =  1_000; # how big is the record we start with
my $replica_db           = 'test'; # which database we use for testing

my $master_dbh = $dbh1;
my $slave_dbh = $dbh2;

my ( $exists_db ) = $master_dbh->selectrow_array(qq{SHOW DATABASES LIKE '$replica_db'});
unless ($exists_db) {
    eval {$master_dbh->do(qq{CREATE DATABASE $replica_db}) };
    if ( $EVAL_ERROR ) {
        die "execution error $DBI::errstr\n";

# creating the measurement table
eval {
    $master_dbh->do( qq{
        CREATE DATABASE IF NOT EXISTS $replica_db});
    $master_dbh->do( qq{ 
        USE $replica_db } );
    $master_dbh->do( qq{ 
        DROP TABLE IF EXISTS replica_speed });
    $master_dbh->do( qq{
       CREATE TABLE replica_speed (
        id int(11) NOT NULL auto_increment,
        insert_sequence int not null,
        seconds bigint(20) default NULL,
        microseconds bigint(20) default NULL,
        ts timestamp(14) NOT NULL,
        big_one longtext,
        PRIMARY KEY  (`id`),
        KEY insert_sequence (insert_sequence)
    } );
if ($EVAL_ERROR) {
    die "table creation error $DBI::errstr\n";

# give some time to the table creation to get replicated
my $insert_query = qq{ 
    INSERT INTO $replica_db.replica_speed 
        (insert_sequence, seconds, microseconds, big_one) 
       VALUES ( ?, ?, ?, ?) }; 
my $retrieve_query = qq{
    SELECT seconds, microseconds, id, insert_sequence
    FROM $replica_db.replica_speed 
    WHERE insert_sequence = ?
my $slave_sth = $slave_dbh->prepare($retrieve_query);

# checking max_allowed_packet to make sure that we are not
# exceeding the limits
my ( undef, $master_max_allowed_packet) = $master_dbh->selectrow_array(
        qq{ SHOW VARIABLES LIKE "max_allowed_packet" } );

my ( undef, $slave_max_allowed_packet) = $slave_dbh->selectrow_array(
        qq{ SHOW VARIABLES LIKE "max_allowed_packet" } );

my $max_allowed_packet = $master_max_allowed_packet;
if ( $slave_max_allowed_packet < $master_max_allowed_packet) {
    $max_allowed_packet = $slave_max_allowed_packet;
my @results     = ();

for my $loopcount (0 .. $loops )
    # let's start with an empty table 
    $master_dbh->do( qq{ TRUNCATE $replica_db.replica_speed } );
    my $size   = $initial_blob_size * ($loopcount || 1);
    if ($size > $max_allowed_packet) {
        $size  = $max_allowed_packet - 1000;
    my $master_insert_time  = 0.0;
    my $big_blob            = 'a' x $size;

    # inserting several records in the master
    for my $sequence (1 .. $num_of_inserts ) { 
        my ( $secs, $msecs ) = gettimeofday();
        $master_dbh->do($insert_query, undef, $sequence, $secs, $msecs, $big_blob);
        $master_insert_time = tv_interval( [$secs, $msecs],  [gettimeofday()]);
    my $replication_delay     = 0;
    my $total_retrieval_time  = 0;
    my $baredelay             = undef;
    # fetching data from the slave 
    while ( ! $replication_delay ) # waiting for data to arrive from master to slave
        my $retrieval_start_time = [gettimeofday()];
        $slave_sth->execute( $num_of_inserts);
        my $info                = $slave_sth->fetchrow_arrayref();
        my $retrieval_stop_time = [gettimeofday()];
        my $retrieval_time      = 0.0;
        $retrieval_time         = tv_interval( 
        next RETRIEVAL unless $info->[0];
        # retrieval time is counted only after a successful fetch
        $total_retrieval_time   += $retrieval_time;
        $replication_delay      = tv_interval( [$info->[0], $info->[1]], $retrieval_stop_time); 
        $baredelay              = $replication_delay - $total_retrieval_time - $master_insert_time;
        printf "%4d %5d %5d %12d %12d %12d %12d\n", 
            $loopcount, $info->[2], $info->[3] , $info->[0] , $info->[1] , 
            $retrieval_stop_time->[0], $retrieval_stop_time->[1];

    push @results,
            data_size             => $size,
            master_insert_time    => $master_insert_time,
            slave_retrieval_time  => $total_retrieval_time,
            replication_time      => $replication_delay,
            bare_replication_time => $baredelay,

# displaying results
my @header_sizes = qw(4 9 13 15 16 9);
my @headers = ('loop', 'data size', 'master insert', 'slave retrieval', 'total repl. time', 'bare time');
printf "%s %s %s %s %s %s\n" , @headers;
printf "%s %s %s %s %s %s\n" , map { '-' x $_ } @header_sizes;
my $count = 0;
for my $res (@results) 
    printf "%4d %9d %13.6f %15.6f %16.6f %9.6f\n" , ++$count, 
        map { $res->{$_} } 
            qw/data_size master_insert_time slave_retrieval_time replication_time bare_replication_time/;

Comments welcome.

Vote on Planet MySQL