I didn’t know you could actually do this before addressing this problem in a benchmark using 5.0.36.

MySQL allows you to update two tables with a single UPDATE statement. I knew you could reference two tables in an UPDATE statement but not update both. However when working with a client after benchmarking I observed a large number of Created_tmp_disk_tables via SHOW GLOBAL STATUS and found that this query was the offending query only for certain circumstances. Understanding took a little longer.

schema.sql

DROP TABLE IF EXISTS a;
CREATE TABLE a(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL)
ENGINE=INNODB;
DROP TABLE IF EXISTS b;
CREATE TABLE b(
id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
i   INT UNSIGNED NOT NULL,
c   CHAR(1) NOT NULL,
t   TEXT NOT NULL)
ENGINE=INNODB;

data.sql

INSERT INTO a VALUES(1,1,'a');
INSERT INTO a …

If you’re in Kyoto, Japan on the 20-21 July 2007, consider visiting the Open Source Conference 2007 Kansai (page is in Japanese, with no English translation, but never worry, Google Translate Japanese to English Beta means that you and I, now read Japanese - read the English translation).

There are a lot of participating parties, there is no cost to attend the conference (I understand this is very commonplace in Japan), and if you’re a MySQL person, you’ll find Yoshinori Matsunobu giving a couple of interesting talks:

• On the 20 (Friday), his talk is titled “MySQL High Availability, and Scale-out solutions” and will focus largely on replication, and the use of heartbeat and DRBD.
• On the 21 (Saturday), his talk is …

First it was the SlashDot effect, then it was the Digg effect, now it’s the Facebook effect. I have a friend at Facebook and he was talking about the effect of the Facebook Platform API that was released a few weeks ago. Sites were now struggling to cope with the effect of massive amounts of new traffic, ensuring that experienced MySQL Consultants will have plenty of scale out opportunities.

Here is an abstract from an article I read recently. Analyzing the Facebook Platform, three weeks in

Translation: unless you already have, or are prepared to quickly procure, a 100-500+ server infrastructure and everything associated with …

Well, I'm already behind but managed to find time to get KISS entry #2 out.  This is a fairly easy one and one that I've blogged about before.  Consider this screenshot of my installed programs.

Applying the KISS principle to software installation (as Apple generally does) would mean that a single icon would indicate that a piece of software is installed and dragging that icon to the trash would either complete the uninstall or at least start the process.  But, as we've already discovered, Microsoft believes in the secondary KISS meaning (Keep It Simply Stupid).  The installation of Visual Studio 2005 does not produce a single icon but instead we get a whole series of things that are installed.

What in the heck is 64 bit Prerequisites?  Sure, I know technically what it is but why on …

Today I needed the ability to create a Oracle-style sequence generator outside of the normal MySQL auto-increment functionality.

I was performing a table format upgrade and one column needed a unique index and we’re not using auto-increment on this table.

This little bit of SQL should work fine:

SELECT @sequence:=IFNULL( @sequence + 1, 1 );

You’d have to use this construct with INSERT INTO ... SELECT constructs.

So for example:


CREATE TABLE TEST_SOURCE
(
ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(ID));

INSERT INTO TEST_SOURCE (ID)
VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);

… will give us a TEST_SOURCE table with 6 auto-increment values.


mysql> SELECT * FROM …

MySQL AB today announced that all the database applications behind the Skyrock and Skyblog Web sites -- Europe's largest blogging platform -- are based on MySQL. The European leader plans on future international expansion, relying on the world?s most popular open source database.

Today, Lenz was putting together some stats on PlanetMySQL feeds added since January this year, and asked in an email whether he should include totals in the stats. I responded yes, and offered a quick SQL solution to get those numbers out of the database. I thought it might be useful for others, so here goes...

Note: The following views are just that: mine and not my employer's.

News Corp is considering swapping MySpace for 25% share in Yahoo.

Made me think: Is it "the" sign that Yahoo! has given up or is MySpace really worth that much?

Yahoo! holds some key IP assets and it can certainly make a come back, or so I believe. It is just a matter of leadership that can find the right balance between monetization and user experience.

Monetization today is all about contextual. Sure there are many companies today that are pitching behavioral targeting as bigger, but honestly speaking, we are not there yet. Behavioral targeting just cannot monetize as well, at least not now.

Contextual, on the other hand, is a gold mine with relevancy being the key. I cannot …

Note: My views are just that: mine.

1. Real time Query Monitoring

MySQL 5.0 GA provides only 3 ways to look at queries that are executed on a server in some way or another. Slow Query Log, General Query Log and Binary Log. All require a server reboot to activate and de-activate. In a production system, it’s sometimes critical to be able to know “what is going on”, and you simply can’t reboot the server twice (once to turn on, once to turn off). 5.1 goes some way with Log Tables to being able to turn on General and Slow Logs into tables. Question is, as Kevin Burton listed in his points, when is 5.1 going to be out.

Real time query monitoring also needs to have a granularity of operation better then “server”. There needs to be a capacity to assign this on per connection basis. A server is being hammered, …

Jay Pipes, Stewart Smith, Marten Mickos, Frank Mash, and Kevin Burton have all gotten into it, and Marten suggested that I should write my top five. I’m usually not into lists, but this sounds interesting, so here goes!

My top 5 wishes for MySQL are:

1. Restore some of my sanity

OK, well, this actually has several sub-wishes…

a. Global and session INFORMATION_SCHEMA …