After some time here is another post concerning MySQL. It is not as if I had not had anything to do with MySQL in the meantime, but most of it was mailing back and forth with their customer support (which is really quite good) to get some issues resolved we stumbled over in our MySQL 4.1 to 5.0 migration.

Before those were fixed we could not use MySQL 5 with our application, because there were some incompatible changes we could not work around.

One of them was the unsatisfactory precision when querying DECIMALs I wrote about earlier. This is fixed in 5.0.32-enterprise and the just released 5.0.33 community edition (see Bug #23260). In fact I was just about to write about the new release policy which I find somewhat strange (enterprise and community editions with the …

It happens sometimes, when I report a bug, that I have an argument with someone at the receiving end of the reporting chain. The raw happens over the definition of a bug. For instance, there is a new implementation of a consolidated tool. The new tool does almost all the old one did, except X. Therefore I file a bug report saying that X is missing. The ensuing argument runs along the lines of:
- This is not a bug, says the Verifier. You can't say it's a bug because it does not do what you want. It should be downgraded to feature request.
- This is the recommended replacement of the old tool, I retort, and as such it should do at least what the old tool did, plus the new stuff. I insist it is a bug.
- The manual does not mention feature X for the new tool, and then it is not a bug. It's a feature request"
The Verifier's reasoning is technically correct, and it is within the boundaries of his allowed action, so I see no point …

This article explains how to create a fixed-size FIFO (first-in, first-out) queue in SQL, where rows added after a threshold will cause the oldest row to be deleted. There are several ways to do this, but MERGE on Oracle and DB2, and MySQL's non-standard extensions to SQL, make an elegant solution easy.

Goal:

Getting rid of filesorts and temporary tables by tuning MySQL queries.

Background:

Filesorts and temp tables are a necessary evil in MySQL, used when MySQL must sort the data before returning the output to the user. They are the most common issue with slow queries in MySQL, the main reason being that if the output is too large, you can kiss goodbye in-memory performance, and say hello to disk access.

Common User and Manager symptoms:

1. Sore throats due to excessive swearing at poor database performance.
2. Sore hip pockets due to lack of scalability requiring continuous hardware purchases.
3. Sore users who are sitting on high speed bandwidth and have to wait more than 1.5 secs for a response from any web page. Google has raised the bar, time to ditch the straddle technique and go for the Fosbury Flop.

Appropriate Medicine:

The quickest way to …

In my last post (see it for details) about stored procedure replication I was wrong.
I'm using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual :-)

But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4\G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

In my last post (see it for details) about stored procedure replication I was wrong.
I’m using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual
But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

Colin has posted the program for the MySQL Miniconf @ LCA2007, featuring (in no particular order) Laura Thomson, Guy Harrison, Jonathan Oxer, Morgan Tocker, Jonathon Coombes, myself, with Stewart Smith, Colin Charles and many other MySQL users also there.

Plenty of interesting stuff and good fun to be had.
Hope to meet you there!

LinuxWorld Magazine ran this article yesterday about open source's move up the stack. (Thanks, Russ, for pointing me to it.) Rather than wondering whether open source has arrived (it has), the article asks, "Where?":

“Open source has won the first battle: It is now listed among the default platform decisions,” says Dave Jenkins, CTO at online outdoor sporting goods retailer Backcountry.com in Park City, Utah. The next step, open source users agree, is moving up the stack and figuring out which open source tools are ready for enterprise deployments.

“Infrastructure open source products are essentially a no-brainer at this point, but the adoption of enterprise applications has been slow,” says Curtis Edge, CIO at The Christian Science Monitor, which revamped its …

Hi -

You can now download MySQL 5.2 Alpha binaries that contain the new Falcon storage engine from the MySQL web site. Right now, we’ve got Linux and Windows builds only, but will be expanding that support shortly. So give our new transactional storage engine a try and let us know what you think via this site or the new Falcon forum.

Thanks!

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …