Sunday, April 27, 2008

Fixed GA binaries for Windows and Mac


There was a problem with MySQL 5.0.51a binaries for Windows and Mac OSX. They did not work out of the box. On Mac OS X, the installation succeeded, but the start panel did not work and needed a patch. On Windows Vista, the installation sometimes failed.
Both problems were fixed after Colin's heads up.
Thanks to Daniel Fischer, Kent Boortz, and Ignacio Galarza, who fixed the problem very quickly.
The new binaries (5.0.51b) are available from MySQL downloads page:

Thursday, April 24, 2008

Speaking at CommunityOne 2008


I will be speaking at Community One in San Francisco, the Sun event immediately before Java One.
The session is Creative Programming with MySQL, which is an euphemism for "hacking MySQL" :).
I will cover the basics of programming MySQL independently of your language of choice, using stored routines, storage engines, triggers, events, and MySQL Proxy.

Track: Databases
Room: Esplanade 307
Date: 2008-05-05
Start Time: 12:25

If you are in the area, come along!

Wednesday, April 23, 2008

The whole story about online backup

I was not surprised when Jeremy raised his voice about the online backup offering.

Internally, I had expressed similar concerns about the plans, and I agree with much of Jeremy reasoning. However, there is something that has been miscommunicated about the online backup. From Jeremy's article, it looks like the whole online backup will be reserved for paying customers. That is not the case. The online backup is GPL. The community can use it right now. (But careful! it's alpha code, added in version 6.04 and newer)
The online backup, last time I tested it, works mostly like mysqldump, with three main differences:
  • it's faster than mysqldump;
  • the dump is much smaller;
  • the backup and restore are done through the same client. No need for an additional tool.
As it is today, it's a great improvement over mysqldump.
What is missing in the public version are plug-in native drivers for each engine, which allow the online backup to work in non-blocking mode and a few additional plug-in features for encryption and compression. The license for such plug-ins has not been decided yet.
The business reasoning behind the decision to reserve the native modules for paying customers is that only the most demanding users have an urgent need of this feature, and I can see the value of this assessment. No doubt we will need to make sure there is additional testing for these features. Recent hirings in MySQL internal QA are a sign of a will of the company to proceed in that direction.
I believe that the online backup will be a net gain for the majority of the users.
This is the "whole" story as I know it. I may not be aware of all the details, and I will update if more comes up.

Saturday, April 19, 2008

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!

Thursday, April 17, 2008

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!

Wednesday, April 16, 2008

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

Monday, April 14, 2008

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.

Thursday, April 10, 2008

Heading to MySQL Users Conference

Coming from Berlin and Hamburg meetups, I am about to board a plane for San Francisco and the MySQL Users Conference. I am looking forward to see a lot of familiar faces from the community and many still unfamiliar ones that I have been contacting in the latest weeks. New colleagues (plenty of them!) new community people, new business partners.
And the conference will also bring some surprises. The ones coming from MySQL will be old news for me (oh, the joy of being an insider!), but a few ones are still exciting because they will be in the making until the very last minute. Much meat will be on the grill. Stay tuned.
The program itself is interesting and charming. I will attend the sessions on my private schedule as a personal treat, enjoying the technology as a kid in a toy store.
And of course I will enjoy my tutorial on MySQL Proxy and the session on MySQL Sandbox. I will treat them as much as possible as hands on sessions. I feel particularly gratified when I can share knowledge with people willing to get it. Thus, being part of an open source community is quite an enjoyable and rewarding experience. If you want to know the latest tricks on MySQL Proxy or Sandbox, come along! When I was preparing the slides and the handouts for the tutorial, I realized that I can't possibly cover everything I know about the subject (yes, it has been a very productive period from June 2007 onward) but I will try to meet the audience desires by focusing on what seem to be the most popular choices.
Busy days, much excitement, many things in the to do list, I will have a tight schedule until April 17, when I will disappear from the radar for a week of vacation.
See you in California!

Wednesday, April 09, 2008

Hamburg and Berlin Meetups: beer, friends, and technology

Good vibes from Hamburg and Berlin. My colleagues Barbara May, Lenz Grimmer and Jörg Brühe did an excellent job of organizing the event and beating the drum.
Both events were held at the local Sun offices, where we were received warmly with friendship, food, and beverages.
I noticed a common pattern in both places. The audience looked shy and uncooperative in the beginning, and very few questions were asked during the presentation. Shyness? Language barrier? (I was speaking in English while the other speakers were using the local language) Who knows. But things changed dramatically and for the better after the presentation, when all were ushered to the adjoining room where food and beverages were provided generously. The language barrier, if ever existed, disappeared, and questions rained from everywhere.
Perhaps serving beer before the event (like it happened in Paris) is the right path. We'll keep this in mind for the next acquisition speech. :)
Interesting news were dispatched between morsels of food, while the beer kept flowing. I learned about some juicy pieces of technological news that will be announced during the users conference by MySQL colleagues, partners, community people. I am not at liberty of disclosing any of the above (see what you missed by not coming to the meetup?) but I can tell you that some of these info are worth a trip to California.

MySQL Proxy Recipes - Sharing info among sessions

In MySQL Proxy scripts you can define variables and modify them. No surprise here. If you define a variable with script wide scope, you can use such variable and modify it between queries. For example:
local how_many_queries = 0
function read_query(packet)
how_many_queries=how_many_queries + 1
print (how_many_queries)
end
However, when you have two clients connected to the same Proxy instance, each one will have a separated set of local variables. By default, MySQL Proxy protects the variables inside a session. If two clients connect to the Proxy running the above script, there will be a distinct count for each session, not the total of queries, as intended.
To share information among sessions, you must use a dedicated table inside the Proxy, called, aptly enough, proxy.global
proxy.global.how_many_queries = proxy.global.how_many_queries or 0

function read_query(packet)
proxy.global.how_many_queries = proxy.global.how_many_queries + 1
print (proxy.global.how_many_queries)
end
Notice the idiom used to assign the initial value. If the global variable has not been assigned yet, it will be nil by checking for a true value, we make sure that we only initialize the variable if it is being accessed for the first time.

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.

Tuesday, April 08, 2008

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.

Monday, April 07, 2008

Mac OSX - the thrill of graphics

As a hard liner Linux user, I don't care much fro graphics in things like system administration. After switching to a Mac laptop, I basically continued doing the same things in the same ways, just enjoying some benefits of the Mac in terms of multimedia and networking flexibility. Thus, although I was aware of the broken MySQL installer that left the database server unusable, I didn't care much because I found a workaround one minute after being confronted with the unresponsive panel.

The last icon on the bottom right is the MySQL starter.

When launched, it shows a "start mysql" button that the user gladly clicks. With the standard installation package, nothing happens. But, as already noted by Colin, there is a fix that solves the problem.

And now, Mac OS X users can enjoy the MySQL server start and stop in full graphics.

Extra fun at MySQL Users Conference 2008

MySQL Conference & Expo 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.

Saturday, April 05, 2008

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.

Friday, April 04, 2008

Working with Sun: love at first sight

I traveled with Kaj from Paris to Milan, and we went directly to the Sun offices, where we were received with great friendship and keen interest.
Franco Roman, Director of Marketing, explained to us how Sun invests on community activities, from big customers to university, to open source events.
Then we were joined by the directors of the other departments, who engaged us in lively exchanges of ideas, where we saw that our community models are different but easily complement each other.
There is much to do, but with such is the enthusiasm that is shown towards MySQL that I have little doubt we will succeed.
In the afternoon there was the meetup itself. Unlike Paris, it was held in a conference room, with a wide screen, microphones, video cameras (it will be published online. Stay tuned). Again, the participation from Sun employees and managers was really notable.
Kaj delivered his speech in Italian. The day before he spoke in French to the cheering crowd. In both cases the effort of meeting the community at direct human level was highly appreciated. Beer and food followed the technical speeches (yours truly about MySQL openness, and Stefano De Toni about Glassfish and Netbeans projects) and the crowd unwound and started engaging us in chat.
This is my second direct working experience with Sun people, and I have to say that I am impressed. I saw them at community work in Naples, where highly professional speakers delivered the goods to the expecting crowd, and I saw the impressive organizational skills at Milan event.
Kudos to Franco Roman for his vision, and to Emanuela Giannetta for making things happen!
I am really glad to be part of the Sun team.

Meetup in Paris : a MySQL social event

The Paris meetup was a very charming event. As announced, it was held in a pub, where beer was the first priority, and the audience of our (short) speeches were holding glasses and looking relaxed.
The ensuing conversations were definitely friendly and inspiring. We met mostly MySQL community, but also Sun employees and Java developers in search of cross links. Every exchange was lively, some with a touchy angle. The technical questions led to wine choices philosophy, university projects, security policies, and again to the pro and con of living in Paris as an expat. We had a merry time indeed.
Thanks to Michael, Max, Serge, and especially to Veronique, for organizing on such a short notice.

Thursday, April 03, 2008

MySQL Proxy Recipes - Debugging messages on demand

MySQL Proxy, in addition to dealing with the packets sent between client and server, can optionally send text messages to the terminal window where it was launched. These messages, using the built-in function print(), can be very useful when you develop an application, because can give you information on what is going on. However, when the script is well tuned, all these messages can be distracting and even annoying.
OTOH, if you plan to extend the development of the script, leaving the telling messages in place can be very useful. One handy solution is to include conditional print messages, controlled by an environment variable.
  1. Change all occurrences of print to print_debug;
  2. Create a function print_debug that will print the message depending on the value of a local variable DEBUG;
  3. At the start of the script, initialize the DEBUG variable from the environment.
local DEBUG = os.getenv('DEBUG') or 0
DEBUG = DEBUG + 0

function read_query (packet )
if packet:byte() ~= proxy.COM_QUERY then return end
print_debug(packet:sub(2),1)
print_debug('inside read_query', 2)
end

function print_debug(msg, level)
level = level or 1
if DEBUG >= level then
print (msg)
end
end
print_debug accepts two parameters. The second one is optional and defaults to 1. If the global DEBUG variable is equal or higher than the level parameter, the message is printed. With this usage, you can define messages that will be printed only if the debug level is 2 or more, and others that will be printed when the level is 1. If you don't set the DEBUG variable, no messages will be printed. For example:
DEBUG=1 /usr/local/sbin/mysql-proxy --proxy-lua-script=simple_messages.lua
When the proxy is started in this way, you will get the queries (level 1). If you change DEBUG=1 into DEBUG=2, you will also see the "inside read_query" messages.
This technique can be tweaked and hacked to get more interesting results. For example, you can get conditional messages without changing the function name, or you can change the verbosity level at run time. These goodies will come to you - guess when? - during the tutorial mentioned below.

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.

Wednesday, April 02, 2008

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.

Tuesday, April 01, 2008

Meetup mashup in Paris -Milan - Hamburg - Berlin


More user group meetings in the coming days.
The meetup/mashup tour are events where the Sun and MySQL communities will meet, merge, discuss technology, socialize, and drink together.
On April 2nd the meetup is in a pub in Paris, where I expect that the drinking will overcome the technology, but I will let you know if things turn out different.
Milan will follow on April 3rd, in a more conservative venue (Sun conference hall), but the drinking doesn't have to be underestimated.
Hamburg on April 7th and Berlin on 8th will be definitely technical. The technical drink will start after the geeky stuff.

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.