With the GA announcement of MySQL 5.1 coming up, we have picked the winners in the MySQL 5.1 Use Case Competition.

To keep you in suspense, let me first announce those on positions 5 to 10:

5. Fourat Zouari (TriTUX.com, Tunis, Tunisia): Using Partitioning for Data Warehousing. See Fourat’s DevZone article, and his blog entry from May 2008.

6. Ryan Thiessen (Big Fish Games, Seattle, …

Dear MySQL fans,

we have done many webinars in these months with good attendance. While thanking everyone for participating, it's my pleasure to announce that now everything has been published and available on demand. You can download a webinar and watch it offline in case you have missed it or you like to view it again. Have a look at http://www-it.mysql.com/news-and-events/on-demand-webinars/

See you soon!

…

I have written a new book on SQL DML. This is a total beginner book: how to commit and rollback, how to query, how to add data, etc.

Probably not of interest to most of the people who read this blog but if you know of anyone completely new to SQL, this would make a great Christmas present. Only 14.95. It is completely vendor agnostic, although the examples all use Oracle and MySQL.

You can view the Table Of Contents, Preface and Index here. I plan to release some of the chapters for free on the blog and will make the PDF of the book available at a discount. I have several more books like this (DDL, Intro to Relational Databases and Cloud Computing) under construction. I also plan to do some …

It’s the FRM file format version number.

It’s not the version of the table as one might expect (i.e. after CREATE it’s 1. Then, if you ALTER, it’s 2. Alter again 3 etc).

In Drizzle, we now return 0.

In future, I plan that Drizzle will allow the engine to say what version it is (where 0 is “dunno”).

This’ll be a good step towards being able to cope with multiple versions of a table in use at once (and making sense of this to the user).

So… in removing the FRM file in Drizzle, I found a bit of a nugget on how drop table works (currently in the MySQL server and now “did” in Drizzle).

If you DROP TABLE t1; this is what happens

• open the .frm file
• read first 10bytes (oh, and if you get EIO there, in a SELECT * FROM INFORMATION_SCHEMA.TABLES you’ll get “Error” instead of “Base Table”)
• if (header[0] != (unsigned char) 254 || header[1] != 1 ||
  (header[2] != FRM_VER && header[2] != FRM_VER+1 &&
  (header[2] < FRM_VER+3 || header[2] > FRM_VER+4)))
  return true;
  Which means that you probably (well, should have) set your enum legacy_db_type to DB_TYPE_UNKNOWN in the caller of bool mysql_frm_type(Session *, char *path, enum legacy_db_type *dbt) otherwise you end up in some form of pain.
• Else, *dbt= (enum legacy_db_type) (uint) *(header + 3);
  return …

This is a quick informal poll. I’d like to know how much network traffic your server receives (not sends), in bytes per second. Give both avg and max if you have them. I’m especially interested in hearing about big, heavily loaded systems. I’ll start things off: just by looking at a couple of customer systems, I see one customer’s server is at 56k and 79k per second. Another slightly larger one is pulling 76k average and 200k max.

Wikimedia's Wikipedia.org is one of the most visited websites approaching 250 million unique visitors per month.  New content is pushed in by the over 100,000 volunteers who edit the articles.  This announcement "Wikimedia Selects Sun Microsystems to Enhance Multimedia Experience..." describes how users can now upload large video files using the servers and open storage environment provided by Sun and other open source organizations.

The solution is built up in an open source paradise:  MySQL, Apache, Sun's Open Storage infrastructure all on Sun's X4500 and X4150 Servers connected to Sun StorageTek arrays.

An article by …

Wikimedia's Wikipedia.org is one of the most visited websites approaching 250 million unique visitors per month.  New content is pushed in by the over 100,000 volunteers who edit the articles.  This announcement "Wikimedia Selects Sun Microsystems to Enhance Multimedia Experience..." describes how users can now upload large video files using the servers and open storage environment provided by Sun and other open source organizations.

The solution is built up in an open source paradise:  MySQL, Apache, Sun's Open Storage infrastructure all on Sun's X4500 and X4150 Servers connected to Sun StorageTek arrays.

An article by …

Dear MySQL fans, 

we have done many webinars in these months with good attendance. While thanking everyone for participating, it's my pleasure to announce that now everything has been published and available on demand. You can download a webinar and watch it offline in case you have missed it or you like to view it again. Have a look at http://www-it.mysql.com/news-and-events/on-demand-webinars/

See you soon! 

Today there was a question on the Freenode MySQL channel about a classical problem: Rows holding group-wise maximum of a column. This is a problem that I keep encountering every so often, so I thought I would write up something about it.

A good example of the problem is a table like the following holding versioned objects:

CREATE TABLE object_versions (
  object_id INT NOT NULL,
  version INT NOT NULL,
  data VARCHAR(1000),
  PRIMARY KEY(object_id, version)
) ENGINE=InnoDB

Now it is easy to get the latest version for an object:

SELECT data FROM object_versions WHERE object_id = ? ORDER BY version DESC LIMIT 1

The query will even be very fast as it can use the index to directly fetch the right row:

mysql> EXPLAIN SELECT data FROM object_versions
WHERE object_id = 42 ORDER BY version DESC …