The latest from the MySQL community

Well, it’s that time of the year once more. The time when someone starts planning for the Users Conference, having received an acceptance notice of a session proposal. This year it came earlier than usual, because the CfP was started one month earlier, and thus we have a list of tutorials ready before Christmas.

So, let me do some advertising.
I am going to co-present a full day tutorial with Jan Kneschke. MySQL Proxy, the complete tutorial which will cover all about MySQL Proxy, and it will guide users to the creation of Lua scripts. When I proposed it, Jay asked me and Jan if we have enough material to cover 6 hours. After having been in three presentations on this subject, I’d rather ask myself if I can manage to cover all we know (and all that Jan’s volcanic production is …

The “sla” in mysqlsla stands for “statement log analyzer”. This does a much better job than mysqldumpslow of analyzing your slow query log. In fact, you can sort by many different parameters — by sheer number of times the query shows up in the slow query log, by the total or average query [...]

Ever since I submitted the "External Language Stored Procedures" project which Eric and myself have been working on to be listed on FreshMeat, it has gained a small number of interested users and we have just started to get some feedback. Yay! Always nice to know when people are using your code. Okay, so one of the emails contained a feature request: Support for PHP stored procedures. I think I

The second release, version 0.2 of Memcached functions for MySQL has been released. You can download them at:

http://download.tangent.org/memcached_functions_mysql-0.2.tar.gz

Developed by Brian Aker and Patrick Galbraith, These are a number of MySQL user defined functions based on libmemcached (http://tangent.org/552/libmemcached.html) mirroring libmemcached client functions.

This latest release introduces two new functions, memc_servers_behavior_set and memc_list_behaviors. Additionally, this version also has some enhancements and better error handling.

Previously Included are:

memc_servers_set() - sets list of memcached servers to use
memc_set(hash, value) - sets a value keyed by hash in …

My latest quiz was quite popular, and some interesting ideas were submitted.
There was an interesting development. A colleague called and asked me for advice on how to insert 4 billion rows in a table with a simple structure.

create table t1 (
id tinyint not null
);

Very simple. No primary key, no indexes. It is needed to perform some specific tests.
Actually, not 4 billion, but 2^32 records are needed, i.e. 4,294,967,296.
The classical method used in these cases is doubling the table contents:

insert into t1 values (1),(1),(1),(1),(1),(1),(1),(1);
insert into t1 select * from t1;
insert into t1 select * from t1; # and so on

My solution was similar to the one from my quiz.

CREATE VIEW `v4` …

Imagine you want to do something like this in a stored procedure:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
END CASE;

And it works fine for a while until you get and error:

ERROR 1339 (20000): Case not found for CASE statement

This happens due to the fact there is no WHEN clause to handle this specific value. It's unlike the regular switch clause logic in C, but it seems reasonable. So I've tried adding an empty ELSE clause that would do nothing:

SET @var = SELECT var FROM t; --some generic value
CASE
WHEN @var = 1 THEN CALL do_something();
WHEN @var = 2 THEN CALL do_something_else();
ELSE --or ELSE; or ELSE NULL; or any combination you might think of
END CASE;

Nope, we get the usual "You have an error in your SQL syntax; check the …

MySQL Storage Engines implementing Multi Version Concurrency Control have several internal identifiers related to MVCC. I see a lot of people being confused what they are and why they are needed so I decided to take a time to explain it a bit. This is general explanation, it does not corresponds to Innodb in particular and some implementation can be different but I hope this will let you to understand MVCC a bit better.

Transaction ID As the name says this is transaction identifier. It can be used by the engine for many things - for lock handling to see which transaction holds the lock and possibly kill it in case of deadlock, for proper isolation mode handling - transaction should see its own uncommitted changes but other transactions typically do not see them as well as MVCC implementation. It also can be used for recovery so you can see to which transaction given change corresponds so you can roll back or redo changes for …

Looking through the output of the latest benchmark run of libmemcached I just noticed this:

Nonblock
Testing mdelete_generate 1.119 [ ok ]
Testing delete_generate 0.983 [ ok ]

This means that the non-blocking version of the library is a tad faster then the multi delete support.

So I ask myself, is the multi delete support of value then? Does it matter if the above is true to have multi methods?

Semantic sugar is fine, but I am really wondering if it makes an ounce of sense.

I have the multi-set functions done, but I am wondering now if I should bother with committing them.

They add 3.5 K to the size of the library, which is not much.

More important is the question of whether or not they extend the library just a little too much. Each function just increases complexity. If someone can get the same performance by just writing a …

My latest post on generating one million records received many comments, with interesting solutions.
The challenge required the insertion of 1 million records in a simple table, with a few constraints.

create table t1 (
dt datetime not null,
primary key (dt)
);

The solutionThe official solution is straightforward:

create view v3 as select null union all select null union all select null;
create view v10 as select null from v3 a, v3 b union all select null;
create view v1000 as select null from v10 a, v10 b, v10 c;
set @n = 0;
insert into t1 select now()-interval @n:=@n+1 second from v1000 a,v1000 b;

You can appreciate the title …

I will present a session on Testing PHP/MySQL Applications with PHPUnit/DbUnit at the MySQL Conference & Expo 2008 that is held April 14-17 2008 in Santa Clara, CA, US.Testing PHP/MySQL Applications with PHPUnit/DbUnit
In the last decade, PHP has developed from a niche language for adding dynamic functionality to small websites to a powerful tool making strong inroads into large-scale Web systems. Critical business logic like this needs to work correctly. But how do you ensure that it does? You test it, of course.

To make code testing viable, good tool support is needed. This is where PHPUnit comes into play. …