I wanted to try out something that first seemed to be quite simple, but then I had to see that I didn't manage to get the desired result in one single query. Maybe one of you has an idea how to do it.

Here's the table that you need for this example and a little Stored Procedure to quickly fill the table:

CREATE TABLE tt (
  id int unsigned NOT NULL auto_increment,
  d1 int unsigned NOT NULL,
  d2 int unsigned NOT NULL,
  PRIMARY KEY (id),
  KEY d1 (d1)
) ENGINE=InnoDB;

... and the Stored Procedure ...

DELIMITER //

DROP PROCEDURE IF EXISTS insertIntoTT //
CREATE PROCEDURE insertIntoTT(in _rows int)
begin
declare counter int default 0;

while counter < _rows do
  set @a := floor(rand() * 20) + 1;
  set @b := floor(rand() * 1000000);
  insert into tt (d1, d2) values (@a, @b);
  set counter := counter + 1;
end …

Dear MySQL users, MaxDB users and friends,

The previous “please wait” posting was caused by a new guideline on series postings. The new guideline says that postings must be written one week before they get published. The week between the writing and the publication is used for corrections. I’m writing these lines on April, 4th but the article won’t be published before Wednesday April, 12th. We hope that in the time between writing and publishing we can catch most of the Gotchas to further improve the quality of the series. However, this series will continue to be more like a collection of blog postings than a book, a magazine article or any other “official documentation”. We try to do our best, but unfortunately we cannot apply the same amount of quality assurance and proof-reading to the MaxDB series that we do for magazine articles, official documentation or official class materials.

In this issue

In …

Dear MySQL users, MaxDB users and friends,

The previous “please wait” posting was caused by a new guideline on series postings. The new guideline says that postings must be written one week before they get published. The week between the writing and the publication is used for corrections. I’m writing these lines on April, 4th but the article won’t be published before Wednesday April, 12th. We hope that in the time between writing and publishing we can catch most of the Gotchas to further improve the quality of the series. However, this series will continue to be more like a collection of blog postings than a book, a magazine article or any other “official documentation”. We try to do our best, but unfortunately we cannot apply the same amount of quality assurance and proof-reading to the MaxDB series that we do for magazine articles, official documentation or official class materials.

In this issue

In …

It's nothing new to me and probably nothing new to most folks who work with MySQL on a regular basis that the username alone does not identify a MySQL user, but a combination of username@host.

But if you forget about it for a moment, you can easily begin to wonder about special behaviors. That just happened to me, so I just thought that I have discovered a very evil bug - but looking twice, it became quite clear to me why MySQL behaved this way.

I have two computers, both with MySQL servers, both with users called mpopp - but each server has two of them - one is mpopp@localhost and the other is mpopp@10.0.0.1__ (both with the same permissions), so it allows me to access user mpopp from each of my PCs. I created a little stored procedure on one computer and then copied this procedure to the other one - together with the new DEFINER option that's new in MySQL 5.0.20. But I didn't think about it (and didn't look …

First they ignore you, then they laugh at you, then they fight you, then you win.

Mohandas Gandhi

Linux is in the "win" phase. Apache webserver is in the "won" phase. MySQL and JBoss are in the stage where the laughter turns bitter and the pushing starts. Big wins are on the horizon.

Open source applications? We're at the point the ignorance is breeding laughter. SugarCRM, Alfresco, JasperSoft, Plone, Compiere, etc. These are all applications that used to be ignored, but ignorance is no longer serving proprietary competitors well.

As a case in point, InformationWeek just ran a story on Boise Cascade's use of Alfresco for invoice management. Big customer, big need, big value.

Documentum's response? Completely off-base, ill-founded commentary ("Boise's need …

MySQL QA

In the 5.1 release MySQL Cluster will support replication to another MySQL installation. The replication is handled using Row Bases Replication instead of Statement Based Replication.

I have been testing it for about a year now and it has gotten really hard for me to break at this point, but I am still trying.

So what is so cool about Cluster Replication? Good question!

One answer:

Cluster Replication really gives a company five 9's (99.999%) in up time. You can have a Master Cluster in the main site supporting the business and have another Slave Cluster in a total different site that can be used as a backup for times there are issues with main site. In addition, the Slave Cluster can also be used for reporting or data mining to take that traffic off of the main Cluster.

How hard is it to …

In about 30 hours I'm hitting the road again and heading first to Philadelphia, then New York, Seattle, and finally to Santa Clara, where the next MySQL Users Conference is held. I'm a speaker, and my talk is about Dynamic SQL in stored procedures. I'll make the examples available online at MySQL Forge. Right now the state of my slides is pretty much the same as of everything else, that is, disastrous.

Well I fear it is not that simple. As avid readers of my blog may have noticed I have some beef with the Zend PHP IDE proposal that was recently accepted by the Eclipse Foundation. After studying the Eclipse Foundation governance rules, looking at the facts and talking to people from Zend, I am reminded once again that it is important that people realize that open source is not about licenses.

Of course the OSI might intentionally or unintentionally make you think that licenses is all that matters. I thought it was very telling that one of the comments in my "What open source is not" post noted that the OSI in fact makes it clear that the term "open source" is a marketing term. A way to supersede the "free software" term that is so closely associated with …

I believe it was 1992, UNIX on PC's was less then stellar. 386BSD
required a math co-processor and everything else cost an arm and a leg.

Enter IBM with OS2. The install was clean, and other then having to
pay for a driver to run my CDROM drive it was great. I was able to
hack out a chunk of the GUI so that I could just have terminal
screen. Someone had ported bash to the OS so it was just like having
a cheap UNIX. I even rigged a character based terminal up to one of
my serial ports so that I can a real terminal to use!

Its fourteen years later and MySQL is working on 5.1. The OS2 code is
bit rot at this point. It complicates our include files, and it makes
a chunk of code in our portability layer a rat's nest to understand.
Someone even made the mistake of using two different flags to
represent the OS2 in the include files. Its time for it to …

We're starting on a new project using MySQL that will bulk load CSV data once a day and then users can report on it whenever they want. In the days of old (ie. Oracle), we'd simply setup a load job using SQL*Loader or use external tables. In MySQL, loading data is just an extension of SQL using the LOAD DATA command. Since my data was going to be distributed, I wanted
to explore Mike Hillyer's suggestion of using the LOCAL option of LOAD DATA.

First, I created a small table called ?testload? :

mysql> create table testload (empid integer, name varchar(20),
bonus integer);
Query OK, 0 rows affected (0.02 sec)

I then decided to try it from the server to verify everything worked as expected:

mysql> -u …