The latest from the MySQL community

I was prompted following a few key words by Marten Mickos at the Sun Dinner on Wednesday evening, and subsequent one on one discussion with Marten, to post my thoughts of some significant news this week announced at the MySQL Conference. The decision to provide as it’s been termed is “Enterprise only features”. It is unfortunate this was not discussed in Marten’s opening keynote, having been exposed the evening before in the Partner’s meeting and hitting the blog sphere before the conference officially started.

MySQL, past, present and future as an Open Source company requires a functional business model to succeed. This includes the funding of resources and the technology progression. It is also necessary in this business climate to build a successful business quickly. How do you do this? Well that’s probably the difference between a successful CEO and an unsuccessful one, and what Marten Mickos has produced is clearly very …

In review of some list posts today, I came across the Falcon Preview 6.0.5 downloads available from the MySQL Forge (even that is unclear, but the directory indicates this on the forge).

Microsoft and Novell extend collaboration to China. IDC sees open source growing in importance with end users. rPath to use SUSE Linux for appliances. (and more)

Microsoft and Novell Extend Reach of Interoperability Collaboration to China, Microsoft / Novell (Press Release)

Open Source Software Grows in Importance to End-User Organizations Providing Rising Services Opportunity for Quality Assurance and Testing, IDC Survey Reveals, IDC (Press Release)

rPath to OEM SUSE Linux Enterprise Server from Novell for Appliances, rPath (Press …

Buddy of mine asked me a question over chat today: “how do I show my machines’ IP at login prompt with out logging in?” He is referring to his Virtual Machine in this case. He does not want to have to log in to the server to see what ip it has (since its on dhcp) for him to ssh in or hit it from the browser. I could have answered him with a simple how to but what is the fun in that? So I decided to give some background on how login prompts are done and show what can be done.

When Linux server boots up, it calls a program called mingetty. This program creates that infamous login prompt as show in a screenshot:

You can see how server calls the mingetty program by looking at /etc/inittab. You will see a block like below:

# Run gettys in standard runlevels
1:2345:respawn:/sbin/mingetty tty1
2:2345:respawn:/sbin/mingetty tty2
3:2345:respawn:/sbin/mingetty tty3
…

Have you ever run a query in MySQL and wondered how long it'll take to complete? Many people have had this experience. It's not a big deal until the query has been running for an hour. Or a day and a half. Just when IS that query going to finish, anyway?

There are actually a few ways to estimate how long it'll take for the query to complete, depending on what the query is. One of the simplest is to estimate how many rows the query needs to examine, measure how fast it's working, and do the math.

As an example, I recently worked on a customer's site where a typical data-warehousing query needed optimization. It was a fact table joined to two dimension tables -- a classic star schema query. The fact table was very large, and after some tuning (I'll write more about that later) I convinced MySQL to perform the query as a table scan of the fact table, then an index lookup in each dimension table in turn.

The table structures …

The 2008 Google Summer of Code MySQL Projects are now available. MySQL has 14 listed projects, one of the ~190 different Open Source products listed. Unfortunately there is no summary to see the total number of projects being sponsored across all products.

Media Blob Streaming actually has the luxury of two approved projects, so they have plenty of mentoring work at PrimeBase Technologies.

Raj Kissu Rajandran will be working on BLOB Streaming Support for phpMyAdmin and KishoreKumar Bairi on Streaming Enabled MySQL Driver for PHP. Welcome to world of open source for your respective projects. …

The conference that many of us just went to is called the MySQL Conference and Expo, but a lot of people don’t call it that. They call it by the name it had in 2006 and earlier: MySQL User’s Conference. In fact, some people say (or blog) that they dislike the new name and they’re going to call it the old name, because [… insert reason here…]. I call it by the new name that some people dislike so much.

My wrap up on the MySQL Users Conference 2008:All the rooms were filled for almost all presentations.Any presentations on Falcon, new InnoDB plug in and Maria were packed. The race is on! Falcon drew first blood by being available in the MySQL 6.0 release but the new features in InnoDB look very good.Performance tuning presentations were SRO.I found all the presentations on the query cache,

This is an idea I came across at the MySQL UC last week: How many applications handle failing COMMITs correctly ? And how can we test it ?

COMMITs can fail. The most simple case are deadlocks. The sad side of deadlocks is that they only happen under real load when you application creates concurrency against the same rows. That's usually hard to create in test-setups.

With MySQL Proxy you can create deadlocks easily. Well, more or less.

You can at least fake them nicely and let the application and server think that we have a deadlock. The trick is:

To make it a bit more interesting you don't have to let all the COMMITs fail. Just let 50% of them fail.

--[[

Let a random number of transaction rollback

As part of the QA we have to verify that applications handle
rollbacks nicely. To simulate a deadlock we turn client-side COMMITs
into server-side ROLLBACKs and return a ERROR packet to the client
telling it a …

If you use a transactional storage engine in MySQL like InnoDB some of your transaction may be terminated by the storage engine because of deadlocks. Sadly it is a bit tricky to see what has led to the deadlock. SHOW ENGINE INNODB STATUS gives only a very minimal look into the state when it happened.

With the proxy and a little script we can track what happened in all open transactions before the deadlock happened. A classic example is the cross-over deadlock:

[36] received a ERR(1213, Deadlock found when trying to get lock; try restarting transaction), dumping all active transactions
  [35].1: select * from commit where id = 1 for update
  [35].2: select * from commit where id = 2 for update
  [36].1: select * from commit where id = 2 for update
  [36].2: select * from commit where id = 1 for update

35 and 36 are the two transactions we have open, the last statement in 36 triggered the deadlock.

In the script we stored …