Wrapping up the MySQL UC2008

The Users Conference 2008 is over.

Had a quiet day in Monterey with Jay and Beat, where we enjoyed a few hours of walking in the woods and some spectacular views, like these baby seals.

Back at the hotel, it was business as usual, where coders and beers coexist peacefully.

Thanks to the attendees, and thanks Jay Pipes for another splendid performance!

The lost art of self joins- awesome talk at the UC 2008

This was my reason #1 to attend the UC and it lived up to my expectations!
The Lost art of the self join was a truly enjoyable experience. Beat Vontobel delivered with confidence and humor a talk about a subject that most people would consider dull.
It was a feast for all attendees with an inclination for hacking.

The truly amazing thing in this presentation was not that Beat explained how to solve a Sudoku puzzle with one query which is a truly amazing feat, but that he explained how to use the join mechanism to implement a backtracking machine, similar to a regular expression algorithm.
The presentation was concluded with a live solution of a 6x6 sudoku puzzle. The reason for not using a 9x9 puzzle is because MySQL has a limitation of 61 joins, and thus it can't solve the full puzzle.
At the end of the session, Beat delivered the punch line, and showed how the 9x9 puzzle can be solved with ... PostgreSQL!
That will be inspirational for MySQL developers. Bravo Beat!

Slides from MySQL Sandbox talk (UC 2008)

The slides for the Users Conference talk about MySQL Sandbox are now published online.

http://datacharmer.org/downloads/Sandbox_uc2008.pdf

Thanks to all attendees and to Sheeri and Keith for blogging live during the session!

MySQL Sandbox 1.20 - multiple version server groups

MySQL Sandbox 1.20 was released yesterday.
The new feature, as suggested by Ronald Bradford, is the ability of creating a group of servers from different versions. So now you can have a group of N servers in a group, each one having a distinct version. The cool thing is that using the Sandbox built-in scripts you can send a command to all the servers at once, thus being able to check the answer from different versions.

Need to know more? I will be speaking about the Sandbox at the MySQL Users Conference and Expo. TODAY! 2007-04-16 at 10:50

Community dinner with Jonathan Schwartz and Rich Green

The pre-conference Community Dinner was organized at a conflicting time when the MySQL Community Team was participating to the traditional party at Marten's residence. That was unfortunate, but it did not stop us from participating.
Although the party at Marten's was going awesomely, the Community Team, plus some willing volunteers, went on to crash Arjen's party, with a bag of gifts and a few important guests. The intended PCC (Party Crash Commando) included Kaj, Giuseppe, Colin, and Lenz. Then, Brian Aker, Mark Atwood, and Eric Herman announced that they were attending as well, and while we were on our way, Monty Widenius joined the crashing team.

That would have been enough to make a sensation, but Kaj mentioned the covert operation to Rich Green, who proposed to include Jonathan Schwartz in the operation.
That worked pretty well. The Sun executives arrived at the restaurant together with the attendees, and soon were joined by the Community Team with the exceptional driver Barton George. Two more MySQLers (Jay and Timour) were at the restaurant, making a total of about 50 attendees.
The event went smoothly, with friendly discussions and difficult questions being thrown from all directions.
It was really a memorable evening. Thanks to everyone for participating and for making this event the greatest fun of the week!
BTW, does the list of the attendees look suspicious to you? Who are these guys?

• Don Tajmanov
• Philip Keres
• Kostantin Bronstein
• Ksawery Tartakower

Those were simply the names under which the Community Team registered for the dinner. :)
See also Colin's and Kaj's reports.

MySQL Proxy Recipes - Returning a non dataset result

Working with MySQL Proxy, besides returning a dataset and an error you could also return a status of successfully executed query, without a dataset being involved. For example, every data modification query (INSERT, UPDATE, DELETE, CREATE ..., DROP ...) returns such a result.
The procedure is similar to returning an error. You must return a different response type and fill in the appropriate fields.

function affected_rows (rows, id)
   proxy.response.type = proxy.MYSQLD_PACKET_OK
   proxy.response.affected_rows = rows
   proxy.response.insert_id = id
   return proxy.PROXY_SEND_RESULT
end

If you call the above function with

 return affected_rows(3000,20)

your client will receive

Query OK, 3000 rows affected (0.01 sec)

Notice that you can't check the LAST_INSERT_ID with MySQL command line client, but you must use an API call from your favorite language to access that value.

This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.

Extra fun at MySQL Users Conference 2008

With the Sun acquisition, MySQL becomes part of a bigger company. Some beneficial effects will be felt at the MySQL Users Conference. There will be a passport giveaway program, which is basically a prize drawing with minimal effort from attendees.

MySQL Conference & Expo attendees will have the opportunity to enter a drawing for cool prizes by having an expo hall passport brochure stamped at each participating sponsor and exhibitor booth.
Once attendees have obtained all "stamps" they will be entered into the drawing and eligible to win one of several prizes.

And the prizes will be really juicy.

• Grand Prize Free enrollment in the O'Reilly School of Technology's ENTIRE four- course PHP/MySQL Certificate series plus 1 year free lab. (Value over $1,700)
• First Prize Sun 24.1-inch LCD Flat Panel Monitor (Value $795)
• Second Prize $500 in O'Reilly books
• Third Prize Make Magazine Special Prize Pack (Value $130)

Information leaked about giveaways at a Sun party, where a Play Station 3 and a Sun Server will be given to the happy audience. How? I honestly don't know, but if you register to the conference, you will soon find out.

MySQL Sandbox 1.18 - server groups and Proxy support

MySQL Sandbox 1.18 was released today. The changes log has mostly bug fixes, but also two major additions:

• The ability of creating a group of unrelated servers. Previously, there was only the set_replication.pl command, to create a set of master+slaves. Now the set_many.pl command will create unrelated servers;
• The creation of a start_proxy script with replication installations. This script starts MySQL Proxy using as backends all the servers in the group.

If you missed the previous episodes, MySQL Sandbox is a tool used to install an isolated MySQL server, independently from existing instances in the host. It can also install a replicated system of 1 master + N slaves. All the above happens in seconds.

I will be speaking about the Sandbox at the Hamburg meetup and at the MySQL Users Conference and Expo.

MySQL Proxy Recipes - Conditional query execution

MySQL Proxy allows you to execute multiple queries, by inserting them into the query queue.
What happens if one of the queries in the pipeline generates an error?
If you don't handle this occurrence, the Proxy will continue sending to the server all the queries in the pipeline, regardless of the result. It's easy to understand that, if the second query depends on the execution of the first one, checking the result of each query is essential.
We know already how to return an error. We only need to apply that knowledge in the right place.
The read_query_result() from the previous recipe (executing multiple queries) should be modified as follows:

function read_query_result (inj )
 local res = assert(inj.resultset)
 if res.query_status and (res.query_status < 0 ) then
    proxy.queries:reset()
    return error_result('ERROR IN LOOP ' .. tostring(inj.id),
       9999, 'XLOOP')
 end
 -- do something with the result
 if inj.id ~= max_loop then
    return proxy.PROXY_IGNORE_RESULT
 end
end

The error status is inside the resultset, which is contained in the injection packet received by the function. If that status is negative, some error occurred.

This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.

MySQL Proxy Recipes - Executing multiple queries

One of the goodies of MySQL Proxy is the execution of multiple queries in response of a single query from the client.
One example given in the manual is a normal query sent by the user, with a SHOW STATUS executed before and after the query to evaluate the efficiency of the query itself.
Another common possibility is to create a loop, i.e. the client sends a query with appropriate commands for the Proxy, and the query is executed N times.
Both cases are accomplished using the query queue, a data structure that the Proxy uses to process user defined queries.
The tricky part in multiple query execution is that the client has only sent one query, and thus it expects only one result. If we execute multiple queries, we will get multiple results from the server. Thus, we need to handle the results that we want to process within the Proxy and prevent them from reaching the client, while making sure that the client receives one and one only result.

1. within read_query()
1. insert the queries in the query queue;
2. return an appropriate result (proxy.PROXY_SEND_QUERY), so the Proxy knows that it has to process the query queue
2. within read_query_result()
1. check the query ID
2. based on the query ID, return the result to the client, or discard it

For example, to create a simple loop, where each query starting with the "LOOP N" prefix will be executed N times, we can do the following:

local max_loop = 0

function read_query (packet )
 if packet:byte() ~= proxy.COM_QUERY then return end
 local query = packet:sub(2)
 local loop_no, repeat_query = query:match('^LOOP%s+(%d+)%s+(.+)')
 if loop_no then
     loop_no = loop_no + 0
     if loop_no < 2 then return end
     for x = 1, loop_no do
         proxy.queries:append(x, string.char(proxy.COM_QUERY) .. repeat_query )
     end
     max_loop = loop_no
     return proxy.PROXY_SEND_QUERY
 end
 return
end

function read_query_result (inj )
 -- do something with the result
 if inj.id ~= max_loop then
     return proxy.PROXY_IGNORE_RESULT
 end
end

Some interesting points:

• loop_no must be transformed to a number before using it;
• The query to be appended must be prefixed by proxy.COM_QUERY;
• if no explicit return is given on read_query(), the default behavior is to send the query from the client to the server without changes;
• if no explicit return is given on read_query_result(), the default behavior is to send the result from the server to the client without changes.

This recipe is quite complex and it exposes the developers to many pitfalls. Most of the nuances will be discussed in the incoming Users Conference tutorial.

This post is part of a set of recipes that will eventually become a long article.
Want to hear more? Attend MySQL Proxy - The complete tutorial at the MySQL Users Conference 2008.

Additional reasons to attend the MySQL UC2008

I was not totally sincere. When I said that I had seven reasons to attend the UC2008, I was just using a cute title. In reality, the main reason to attend the Users Conference is because I will meet a bunch of friends, and probably make new ones. Yes! Let's not forget that the Users Conference, in addition to the conference rooms, has many corridors where you can start casual conversations with most anyone, and learn things as amazing as the ones that are said from the podiums. And the social events! There is lunch by the pool, with ample tables, where you can be seated between a system architect and a stellar developer, and spend the whole meal talking about the subtleties of threading or the latest trend in distributed storage.

I can't say that I look forward to any tutorial, since my tutorial day will be filled with my own, but I should mention some more sessions that I look forward to. For example,

• Baron Schwartz's Explain demystified
• Both talks by Paul McCullagh on PBXT and the blob streaming architecture
• Damien Seguy's MySQL Hidden Treasures
• the whole keynotes list is enough to encourage anyone to attend!

Since I was in the reviewing committee, I know all the sessions, and 98% of them have been accepted with my enthusiastic approval (2% of the times my opinion was outvoted, but it happens to all of us), so the only problem is that there is too much choice, and every time slot involves tough decisions about what to attend and what to miss.
More reasons:

• #1 The lost art of the Self Join
• #2 Astronomy, Petabytes, and MySQL
• #3 Testing PHP/MySQL Applications with PHPUnit/DbUnit
• #4 A Tour of External Language Stored Procedures for MySQL
• #5 Database Security Using White-Hat Google Hacking
• #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
• #7 MySQL Proxy: the complete tutorial

Reason #1 to attend the MySQL UC 2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #1 The lost art of the Self Join

When you work in the same field for several years, you risk to become effective but unimaginative. You may be good at coding queries or designing tables, but sometimes you lose track with your origins, when you were a creative programmer, who used to tweak the intricacies of C++ or Perl to create marvelous useless brilliancies.
If you recognize yourself in this picture, and wish you could have a spark of that enthusiastic force that made you learn new languages and idioms, despair not. Beat Vontobel session will be like a fresh wind that will clean your mind of the dull tasks and reconcile you to the beauty of programming.
If you are a programmer, come to the Users Conference and don't miss this one!

Don't forget this: Every speaker can give discount codes! Do you want one? drop me a note by email. Do you know another speaker? ask him/her for the discount code!

More reasons:

• #2 Astronomy, Petabytes, and MySQL
• #3 Testing PHP/MySQL Applications with PHPUnit/DbUnit
• #4 A Tour of External Language Stored Procedures for MySQL
• #5 Database Security Using White-Hat Google Hacking
• #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
• #7 MySQL Proxy: the complete tutorial

Reason # 2 to attend the MySQL UC2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #2 Astronomy, Petabytes, and MySQL

Petabytes! Have you heard that before? Perhaps you have, during some theoretical IT class. But have you used anything that could be remotely close to petabytes?
A petabyte is one thousand Terabyte, or one million Gigabyte.
If you struggle with databases with a few dozen Gigabytes, and you have headaches when you think that you have to deal with Terabytes of data, you can't possibly imagine how to store petabytes of data, and retrieving it!
This session is a challenge. At Stanford they are not morons, and if they are planning to use MySQL to store petabytes of data, there must be a good technical story behind it, which needs to be told. And you must not possibly miss it!

More reasons:

• #3 Testing PHP/MySQL Applications with PHPUnit/DbUnit
• #4 A Tour of External Language Stored Procedures for MySQL
• #5 Database Security Using White-Hat Google Hacking
• #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
• #7 MySQL Proxy: the complete tutorial

Reason #3 to attend the MySQL UC2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #3 Testing PHP/MySQL Applications with PHPUnit/DbUnit

Old school technologists don't think kindly of PHP. Its adepts are believed to be sloppy programmers that create brittle applications. It takes programmers like Sebastian Bergman to level the score and to show that a good programmer is shown by best practices, not by the choice of language.
The best thing a good programmer can do while developing an application is testing. Sebastian shows why you should do it, and how. Not only that, it will show you how easy it is. At the end of this session you will wonder how could you have survived for years without unit testing.
PHP programmers, mark your calendars!


More reasons:

• #4 A Tour of External Language Stored Procedures for MySQL
• #5 Database Security Using White-Hat Google Hacking
• #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
• #7 MySQL Proxy: the complete tutorial

Want a discount to attend the UC? Call a speaker!

Every speaker at the Users conference has a 20% discount code for friends and relatives who want to attend the UC2008. Would you like to attend the UC, and save 20%? Send me a message! (my_first_name AT mysql DOT com). If you don't know me, but you know another speaker, ask him or her for the discount code! And then, when you register for the conference, enter the discount code in the registration form.

Notice that I disabled comments to this post, so nobody will be tempted to ask for the code here. If you need the discount code, don't post comments. Send an email!

Reason #5 to attend the MySQL UC 2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #5 Database Security Using White-Hat Google Hacking

Sheeri always amazes me. When I think that I have seen all it has to be seen in the field of databases, she will always come with some innovative way of looking at old things.
This session is a series of aha! ideas. Some of them can have come to you. You may have heard of some others, but Sheeri does not stop at that. When she hears about a promising practice, she digs in, until she gets all is there to be learned.
She's a captivating speaker. Once you attend one of her sessions (or if you just talk to her in a corridor about anything), you are addicted. Highly recommended!

More reasons:

• #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community
• #7 MySQL Proxy: the complete tutorial

Reason #6 to attend the MySQL UC 2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #6 Optimizing MySQL and InnoDB on Solaris 10 for World's Largest Photo Blogging Community

Farhan Mashraqi knows his job. He must be good at it, because he has on his hands a very busy and heavily loaded server. Fotolog is one of those servers that can take away all your resources very quickly if you don't pay attention. If you want to stay online, you have to make sure that the server is always healthy and efficient.
The above is theory. Farhan tells you the practice behind this simple rule. He will show how to manage such a monster application and still ride the waves.
Don't miss it!

More reasons:

• #7 MySQL Proxy: the complete tutorial

Reason #7 to attend the MySQL UC 2008

Disclaimer: Forget about my affiliation, this is my personal list of things that I am going to enjoy at the UC. #7 MySQL Proxy : the complete tutorial I shall start with a shameless plug, of course. I am going to enjoy this tutorial for several reasons.

For starters, it's the first tutorial I get to host at the Users Conference, and this is understandably satisfactory in itself.
Then, because it is going to give me the technical room that I was longing for. I have been writing and speaking about MySQL Proxy for 10 months, and on every occasion I felt that I had time or space limitations. There was not enough time to explain all that we can do with Proxy, or not enough time to write a longer article.
Now we can remedy. I and Jan Kneschke will have 6 full hours to explain Proxy beauty and intricacies, ranging from simple wizardry to replication goodies.
We will cover the basics of Lua language, with some internals, so that the attendees will leave the tutorial with the basic know-how to use the Proxy effectively.
There are still available seats. Hurry up and register!

Users Conference 2008 - latest schedule changes

If you plan to attend the MySQL Users Conference, hurry up! The early bird registration ends on February 26. It's your chance to save a few hundred dollars. If you need some more reasons for attending, notice these late additions to the schedule:

• Falcon from the beginning by Jim Starkey and Ann Harrison;
• Falcon for InnoDB Users By Kevin Lewis and Ann Harrison;
• MySQL Sandbox - Easily using multiple database servers in isolation by yours truly
  

The Falcon sessions were added just today, and my session is a replacement of "MySQL Proxy Wizardry" (but don't worry: some of its contents will be included in the MySQL Proxy Tutorial.
Did I mention that you should hurry up to save some bucks? Register now!

BTW, as a speaker, I can give a 20% discount to my friends. If you want to get one, please drop me a line by email at my_first_name AT mysql dot com.