Wrapped up a few final touches this evening on the memcache engine:
http://tangent.org/index.pl?lnode_id=506

What works:
SELECT, UPDATE, DELETE, INSERT
INSERT into foo SELECT ...

What doesn't work?
Probably ORDER BY operations.
REPLACE (I think)
IN ()
NULL
multiple memcache servers (this would be cake though to add)
table namespace, right now it treats the entire server as one big namespace

There is probably a lot more I haven't thought up that is now working. Right now the version I put up only allows you to have a primary key and one attribute. Doing multiple attributes means coming up with a way to store MySQL row format, aka UNIREG, in something which would be easy for anyone to take apart. Mark suggested XML, but XML would be damn slow to parse.

If …

I've been writing a lot of articles about locks, deadlocks, and transactions recently, and it occurs to me I've neglected to mention the single most important thing to know. If you only learn one thing about transactions in database systems, you should learn this, and learn it thoroughly -- burn it into your brain permanently, if possible.

Red Herring magazine (yes, it still exists) has several articles in the August 21st issue commemorating the 15 year anniversary of Linux.  Linus Torvalds first posted information about his open source operating system on usenet just 15 years ago, the same year that the world-wide web started.  Perhaps it is no coincidence that Linux and the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python) have become the dominant platform for Web applications. 

Linux has continued to be the fastest growing server operating system in recent years.  According to Gartrer, it grew at 35% in 2005, to about $7 billion, outstripping the growth rate of Windows by a wide margin.  While Linux has not established itself on the desktop in any signficant volume, there are signs that even that could change as …

MySQL AB announced today that its internal benchmark team has won an international contest sponsored by the renowned IT industry magazine c´t for the fastest e-commerce database application. The magazine's editors held the contest to evaluate database performance in real-world business use by creating a standard online inventory system.

Peter Zaitsev over at the excellent MySQL Performance Blog recently wrote an article on duplicated and redundant indexes – any indexes which cover exactly the same columns as another index, or cover a leftmost prefix of another index. While there are subtleties, such as FULLTEXT indexes not being the same as non-FULLTEXT, for the most part this is sufficient criteria to raise possible duplicates to a DBA’s attention. I opened my big mouth in the comments and said I could write a quick Perl script to discover possible offenders in just a few lines of code.

Well, it’s Sunday night so I will put this down to being the weekend. The background to being caught out is a request I made to my local Users Group mailing list for some information on people’s environments because I wanted to some empirical data analysis without having any more knowledge of the systems.

In summary (without the surrounding fan-fare, I was seeking):

SELECT VERSION();
SHOW STATUS;
SHOW VARIABLES;  // Optional

I was however perplexed why my first data point analysis (Read/Write ratio) using the Status values Com_insert, Com_update, Com_delete and Com_select was not always giving me expected results. In particular, a number of server results showed 0 for values while I knew the results came from working MySQL environments.

So, sanity check with good friend Morgan and I get the response to answer the dilemma SHOW STATUS defaults to session …

It's three months ago since MySQL 5.1.11 was released and many things have changed since then. Just watch the Change Log for 5.1.12 - I believe it's one of the longest ever:

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html

But I've found another nice and interesting thing: watch this bug report. This says that it's now allowed to do many more things in Prepared Statements inside Stored Procedures:

SHOW BINLOG EVENTS
SHOW (MASTER | SLAVE) STATUS
SHOW (MASTER | BINARY) LOGS
SHOW (PROCEDURE | FUNCTION) CODE (parsable only in debug builds)
SHOW CREATE (PROCEDURE | FUNCTION | EVENT | TABLE | VIEW)
SHOW (AUTHORS | CONTRIBUTORS | WARNINGS | ERRORS)
CHANGE MASTER
RESET (MASTER | SLAVE | QUERY CACHE) …

Got up and went walking this morning. This is probably going to be one of the last few good weekends here in Seattle, and I wanted to make sure that I got in some walking each day.

Came home to discover that Christine had been stung by a Yellow Jacket. Despite the full can of Yellow Jacket killer I sprayed into the nest last night, they seem to be quite alive. I am tempted to run to the store and get some of the instant foam I use for sealing insulation leaks. I wonder how well they could get out of their nest if it was filled full of instant insulation :)

So this afternoon has turned into "fetch her some ice", so I decided to code up a small project I've been meaning to write for a few years:

mysql> INSTALL PLUGIN memcache SONAME 'libmemcache_engine.so';
Query OK, 0 rows affected, 0 warnings (0.06 sec)

mysql> create table foo3 (k varchar(128), val varchar(300)) …

After directing a Devshed poster looking for a way to compute the number of months between two dates to the manual I decided to turn the solution posted in user comments by Isaac Shepard into a function, here it is:

1. DELIMITER $$
2. DROP FUNCTION IF EXISTS `test`.`months_between` $$
3. CREATE FUNCTION `test`.`months_between` (date_start DATE, date_end DATE) RETURNS INT
4. BEGIN
5. SELECT IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) > 0, (((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) - (MID(date_end, 9, 2) < style="color: rgb(102, 204, 102);">(date_start, 9, 2)), IF((((YEAR(date_end) - 1) * 12 + MONTH(date_end)) - ((YEAR(date_start) - 1) * 12 + MONTH(date_start))) < …

Development of a more structured version is in progress. I just committed a set of changes to the repository that changes the current, rather monolithic, single-class design to what will be the first step of a more modular one.

Currently the generation of ALTER TABLE statements has been removed and the output format of the analysis is slightly different. But that is mere cosmetic.

What is much more important is that now the gathering of database schema information is based on a pluggable system, designed around an interface called IndexDescriptorProvider. Up to now I have just ported the MySQL 4 stuff that was already in the first version to this new architecture. Please feel free to have a look at it and tell me what you think.

Next thing I'll do is implement a provider based on the INFORMATION_SCHEMA database available in MySQL 5.x to see if I missed anything.

Go to …