The latest from the MySQL community

previous post

http://consoleninja.net/code/dpm/rel/dpm-r3.tar.gz - tarball of r3
git clone http://consoleninja.net/code/dpm/dpm.git - to get the latest code, always
http://consoleninja.net/code/dpm/dpm-export.tar.gz - a tarball of the latest code, for those unwilling to git it.

It's been way, way too long. Next release will be within a week and a half, and will try to stick to the once-a-week schedule from then on. There've been over 210 commits to the repo at this point. The speed has been picking up.

Notable changes:

- Resultsets are fully parseable and writeable! This …

I must be moving to another shard, having outgrown my current one. Its a manual operation.

Flickr tells me they’re moving my stuff around Incidentally, I can’t sign out. Or view the main page. Or any of the groups I’m subscribed to. Its completely locked. But it does take under-15 minutes… (at least for the load I have - ~11,500 images).

Update: Read John Allspaw’s comments at the HighScalability link to this blog post. He’s in-charge of operations, and an all round nice guy, and great presenter.

Technorati Tags: flickr, …

I've had a deadline in my head for the last week or so, though the
start of the deadline began months ago.

I was asked a question "why doesn't Archive prefetch blocks of data
from disk while decompressing the current block".

Excellent question which demanded that, I well... fix it. So... I do
some reading.

I have some options:

1) Write my own AIO package.
2) Use the Posix AIO.
3) Support native AIO.

Writing less code is always good. Tricking out a library around every
vendors native Posix AIO is well... a lot of work. Not terribly
rewarding either since the vendors aren't very careful about
compatibility with their native solutions.

Write my own? Well... posix! Posix has AIO, lets use that.

So I did. As previous posts pointed out, it worked great.

Well sort of.
…

I’m not one to use the phrase ‘full of win’ too often, but today’s xkcd certainly qualifies for anyone with a little SQL knowledge:

Via WWDN

I was very pleased when I heard about Oracle adding pivot functionality in select statements. Finally — we wouldn’t have to copy the data to a spreadsheet or code a ton of sum(case when col1 = 'X' then amount else 0 end) total_X for each column we would want to display.

I am basically looking for three things in a pivot-style query:

1. the ability to specify which column will be pivoted as one or more columns in the resulting query
2. row subtotals
3. column subtotals

The first item is the only one that really matters. I can work around the other two, so let’s get started.

Create a test table with a few rows:

create table bob_pivot_test( id number, cost number, tx_date date, product_type varchar2(50));
insert into bob_pivot_test values (1,10.99,sysdate-10,'Toy');
insert into bob_pivot_test values (1,10.99,sysdate-9,'Toy');
insert into bob_pivot_test values …

I am considering taking some time off work to concentrate deeply on MySQL Table Sync, which has been getting usage in very large companies whose names we all know. There are a lot of bugs and feature requests outstanding for it. It is overly complex, needs a lot of work, and I can't do it in one-hour or even three-hour chunks. I need to focus on it. I'm considering asking for a bounty of $2500 USD for this. Please let me know what you think of this; it seems to be a successful way to sponsor development on some other projects, like Vim.

For the amount of time I think this will take, $2500 is far below my per-hour consulting rate; I considered setting the bounty higher, but I think this will be a fair amount.

I would not begin this project before December at the earliest, so there's some time to raise funds and time for me to continue working on High Performance …

This release of MySQL Toolkit updates MySQL Parallel Dump. Together you and I found a few bugs in it (table locking, argument quoting, exit status code). The restore utility is in progress.

I haven't done a serious learning project for a while so this week I decided to play around with Windows Server 2008 and PowerShell. I'll post some thoughts about Server 2008 soon (at first glance it looks terrific once you get past the fact that IIS is now a "role" instead of something you install). But I wanted to post a PowerShell example first because I wanted to do something with it that I couldn't find information about anywhere -- specifically, perform a database query and dump the contents to the console. (Later we'll get fancy and dump it to HTML or send it somewhere via email, etc.)

PowerShell can invoke any .NET object, so I knew it was possible to do database stuff with a PowerShell script, but I couldn't find any examples anywhere, so here's what I cooked up via trial and error.

The first step is to create a database connection:

[system.reflection.assembly]::LoadWithPartialName("MySql.Data")
$cn …

How does INNODB in mysql 5.0 tablespace per file grow?

I can just add data and hope to catch when the table space grows, but I really want to understand the internals of INNODB and build an accurate modal on how big the table space will grow when X rows are added.

• Some things that need to be known.
  
• Data stored in INNODB is stored as pages with a default size of 16KB.
  
• INNODB adds a page for every secondary INDEX
  
• INNODB has some overhead of about 18 bytes (maybe more) per row
  

First let's set up a test environment

delimiter //

CREATE PROCEDURE resettest()

 begin  
        DROP TABLE IF EXISTS testvarchar;
        CREATE TABLE `testvarchar` (
                        `id` int(10) unsigned NOT NULL,
                        `data_key` varchar(255) default …

Today’s xkcd is just wonderful. I think we need giant ones of these on the wall of offices around the world.