The latest from the MySQL community

Mark Webbink, Red Hat's deputy general counsel and secretary, looks at the Microsoft/Novell agreement through a historic lens...

As a history buff, reading the Novell and Microsoft open letters this morning conjured up the image of British Prime Minister Neville Chamberlain standing in front of 10 Downing Street in 1938 and declaring: "My good friends this is the second time in our history that there has come back from Germany to Downing Street peace with honor. I believe it is peace in our time."

We all know how well that turned out.

...and discovers that all that glitters is not gold:Microsoft's principal objective in this exercise was to get someone ostensibly from the free and open source software community to acknowledge the tacit validity of Microsoft's patent portfolio. And despite Hovsepian's protestations to the contrary, …

The FEDERATED storage engine is a MySQL feature that lets you access the tables (and views) on a remote MySQL server. In terms of purpose and functionality, it best compares to a "linked table" in MS Access, and to a lesser extent, to Oracle database links.

Creating a federated table is tedious, because in the local database, you need to define a table with exactly the same structure as the remote table. That includes the index definitions, because these are used by the local optimizer to create a query plan (although the statistics for the index will not be locally available)

I created a procedure to make life easier (well, at least as far as creating FEDERATED/ tables is concerned). You can grab if here:

…

There is a poll up on strict mode:
http://dev.mysql.com/tech-resources/quickpolls/strict-mode.html

You need to go to this URL to post:
http://dev.mysql.com/tech-resources/quickpolls/strict-mode.html

So what is up with the poll? It comes from a conversation where myself and Monty were wondering how many people are using it/know about it. I asked the marketing people (thanks Edwin!) to post it. Strict mode is the mode inside of MySQL that keeps "bad data" from being inserted into tables. It was added inn 5.0.

It should be obvious that someone from Marketing didn't write it just because of the language (look at previous topics, and then ask yourself about which of them look more Slashdottish).
…

"So is this call about an S3 or a Big Table knockoff?"

That was my question to the recruiter.

Let us wind back history for a moment.

Greg Linden of Findory posted in September of 2005 wrote up this post:
http://glinden.blogspot.com/2005/09/googles-bigtable.html

This sparked a lunch conversation about these sorts of technologies. Big Table to me is an implementation of the problem that all large systems eventually hit. I refer to it as the "image server" problem, because the image servers for most companies are where it normally first manifests itself. If you read Brad's notes over the years on LJ you can see one of the more publicly available evolutions of a system to solve this problem.

My interest in the problem is that I've …

Since the recent demise of my Thinkpad, and the musical hard drive game leading the Thinkpad's hard drive being installed in the Toshiba, the Toshiba has been operating with an insufficiently sized swap partition. This has led to severe performance problems, particularly when doing MySQL builds. So I enlarged the swap partition, deleting the operating system partition and in fact replacing it with two equally sized partitions, similar to the partition layout on the Toshiba's original hard drive.

So of course this required me to reinstall Slackware, MySQL, and PHP. I have found the Apache bundled with Slackware 10.2 to be ample for my needs so far. So first I ran my pulls script to pull and build all the different MySQL source trees, and installed the six instances I normally operate. So far so good. Then I turned my attention to installing PHP.

The first time I installed PHP (5.1.4) I must have had an extraordinary …

MySQL told me that it had a problem renaming a file. So I really thought I would go and help it by removing the file on the command line. But somehow I really felt that it didn’t really mean to rename a file. So I did a bit of googling and in the mysql bug tracker I found the problem.
The real problem occurs when you are using foreign key constraints with InnoDB. Even though I had only created an additional unique index on one of two columns where one of them was a foreign key. And when I tried to remove it mysql told me it had problems renaming a file :-(.
The solution was to remove the foreign key using ALTER TABLE tablename DROP FOREIGN KEY fkname. Now I was able to remove my index and then I added the foreign key constraint again.
Really ugly.

Stumbled into this today over at squarebits, using MySQL to generate bar graphs:

mysql> select Name, REPEAT("#", Value/20.0+1) Graph FROM Month;
+-----------+------------------------------------------------+
| Name      | Graph                                          |
+-----------+------------------------------------------------+
| January   | ####################                           |
| February  | ############################################## |
| March     | …

Spoke with Brian the other day on what was required to get NDB to be a pluggable engine - and started hacking.

The tricky bits invole dependencies of things like mysqldump and ndb_restore on some headers to determine what tables shouldn’t be dumped (hint: the cluster database used for replication).

Also, all those command line parameters and global variables - they’re fun too. It turns out InnoDB and PBXT are also waiting on this. In the meantime, I’ve done a hack that puts config options in a table.

Currently blocked on getting the embedded server (libmysqld) to build properly - but i now have a sql/mysqld binary with pluggable NDB. All libtool foo too.

Hopefully i’ll be able to post soon with a “it works” post

Inspired by PeterZ’s Opening Tables scalability post, I decided to try a little benchmark. This benchmark involved the following:

• Create 50,000 tables

• CREATE TABLE t{$i} (i int primary key)

I wanted to test file system impact on this benchmark. So, I created a new LVM volume, 10GB in size. I extracted a ‘make bin-dist’ of a recent MySQL 5.1 tree, did a “mysql-test-run.pl –start-and-exit” and ran my script, timing real time with time.

For a default ext3 file system creating MyISAM tables, the test took 15min 8sec.

For a default xfs file sytem creating MyISAM tables, the test took 7min 20sec.

For an XFS file system with a 100MB Version 2 log creating MyISAM tables, the test took 7min …

Have been meaning to post about the cluster setup I'm using for *functional* testing (not appropriate for performance testing or production environments).

The gist is that I'm using Parallels on OS X, running 4 nodes (1 management, 2 & 3 storage, 4 SQL) on 4 virtual machines. Each machine has between 128Mb and 256Mb of allocated RAM which isn't much for a cluster, but is as much as I can give from the 2Gb on the MacBook Pro. Each virtual machine has a version of MySQL 5.1.12 compiled from source. Actually, I built one VM with the compiled and installed source and then cloned that for the other 3 machines. The preferred flavor of Linux for these is Ubuntu server.

As I was setting up for a screenshot I walked through bringing …