I'm sitting in the back of the training room in Brisbane, following Sebastian Bergmann on the first day of his Quality Assurance in PHP Projects workshop. He is of course the right man for that job, since he wrote PHPUnit.

I have to run off after lunch to catch my flight to Adelaide (SAGE-AU 2008 Conference), where I'll be doing a tutorial on MySQL Backup and Replication strategies, and a session on managing e-mail and task overload ;-)

Here are three short routines that raise tough questions. I’ll bet that many MySQL stored-procedures experts will fail to answer all three.

Question 1. The Ambiguous Identifier

Given one table and one stored procedure:
DELIMITER //
CREATE TABLE t (x INT)//
INSERT INTO t VALUES (1)//
CREATE PROCEDURE p ()
BEGIN
DECLARE x INT DEFAULT 2;
SELECT x FROM t;
END//
CALL p()//
Notice that x is both a column and a variable.
MySQL will return
(a) ‘1′ because that’s the value of column x.
(b) ‘2′ because that’s the value of variable x.
(c) an error message because x is ambiguous.
?

Question 2. The same-level handler

Given one table and one stored procedure:
DELIMITER //
SET @@sql_mode=”//
CREATE TABLE t (x SMALLINT)//
CREATE PROCEDURE p ()
…

I was just reading Sheeri’s post Why You Want to Switch to MySQL 5.1, and my favorite 5.1 feature jumped out at me.

Online, table-based logging.

It doesn’t get a lot of press, which is sad, because it’s hella useful. I can’t tell you how many times I’ve showed up to a client’s site to help with performance tuning, started with “well, let’s look at the slow log” and (as I’m sure we all know) been foiled by the fact that they don’t have it on and can’t do a server restart. I was even at a client a couple of weeks ago where they were actually using the CSV engine support of this… although I’d never really found that all that interesting myself.

Online logging. It’ll make all the ($gender_of_sexual_preference) love you!

…

IT WORKS!!!

And not a moment too soon.
I've finished my day job for the summer and have been pouring over the code the last few days trying to isolate the last two bugs.
I've also received word that I don't have 8 days left, I have 1...so I'm a bit rushed but still hopeful.

As part of my bug fixing for the hit code (which works beautifully now) I was able to remove all of the unnecessary mutex locking from within the cache. This is a nice speed boost as there is no longer any need to lock and unlock when working with the cache. It just works (tm).

I have a fairly rough implementation of the invalidate code that looks something like this:

if(anything changed) {
nuke_the_cache();
}

It works, and it allows for multiple instances to use the same cache.
My next task is to make this a bit more refined (per table invalidation) by using …

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

• online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
• Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.

(more…)

I have changed the scripts a bit.

Now everything is "self-contained" within the package so no directories outside mysqlcluster-63 is created.

You can now install this as any user! Before you needed to be root, but this is no longer needed! However, you should avoid installing it on an NFS mounted volume.

There is also a README that describes the directory and file structure and how to get going.

Quite commonly in the applications you would need to use some kind of "status" field - status of order - "new", "confirmed", "in production", "shipped" status of job, message etc. People use variety of ways to handle them often without giving enough thought to the choice which can cause problems later.

Perhaps worst, though quite common thing is to define such field as VARCHAR(255) . Even though the stored value is often short the full specified length can be used for internal processing, such as when creating temporary table or sorting.

Before we go to the list of variants which can be used lets talk about two important types of these status fields. First is BOOLEAN or YES/NO type. You would frequently see it as columns VISIBLE, DELETED , CAN_ACCESS etc. In case things are as easy as YES/NO using TINYINT UNSIGNED NOT NULL is quite a good idea, …

I hope you're all enjoying the 1.2.6 stable release of memcached. Don't want to hear no whining about it crashing!

One of the most common questions in memcached land is the ever obnoxious "how do I put my sessions in memcached?". The long standing answer is usually "you don't", or "carefully", but people often walk the dark path instead. Many libraries do this as well, although I've seen at least one which gets it.

This isn't as huge of a deal as people make it out to be. I've been asked about this over the mailing list, in IRC, in person, and even in job interviews. What people end up doing gives me the willies! Why! Why why why... Well, I know why.

So what is the deal with sessions? Why does everyone want to jettison them from mysql/postgres/disk/whatever? Well, a session is:

- Almost always larger than 250 bytes, …

I hope you're all enjoying the 1.2.6 stable release of memcached. Don't want to hear no whining about it crashing!

One of the most common questions in memcached land is the ever obnoxious "how do I put my sessions in memcached?". The long standing answer is usually "you don't", or "carefully", but people often walk the dark path instead. Many libraries do this as well, although I've seen at least one which gets it.

This isn't as huge of a deal as people make it out to be. I've been asked about this over the mailing list, in IRC, in person, and even in job interviews. What people end up doing gives me the willies! Why! Why why why... Well, I know why.

So what is the deal with sessions? Why does everyone want to jettison them from mysql/postgres/disk/whatever? Well, a session is:

- Almost always larger than 250 bytes, …

If you deal with databases for a living, eventually you’ll come across cases where you’ll need to migrate a lot of data from one schema to another. I am not just talking about migrating from one different type of database to another, like from Oracle to MySQL, but from, for instance, a badly-designed schema to one more expertly crafted.

If there are minor differences between the source and target schema, this is a trivial affair. On the other hand, if the schema is completely different, this can be quite a challenge. Moreover, the database being migrated might represent a high-demand website that will need to be done with little or no downtime, with lots of planning and preparation to boot. You may be interacting with the application developers, the systems crew, and juggling tight deadlines as well.

Well, as you may have guessed, I have described some of the roles I now play at a leading social networking company. We are …