Welcome, reader, to the 23rd edition of Log Buffer, the weekly, human-edited review of the database blogosphere. No time to waste, so let’s start with some announcements. Xaprb, the producer of innotop, brings to our attention the latest release of mysqlreport, “one of (his) favorite tools for quickly comprehending the overall state of a [...]

Here's a mystery festive stored procedure. Don't run it on any critical servers.

MySQL's user variables have interesting properties that enable the useful techniques I wrote about in recent articles. One property is that you can read from and assign to a user variable simultaneously, because an assignment can be an r-value (the result of the assignment is the final value of the variable). Another property, which sometimes causes confusing behavior, is un-intuitive evaluation time. In this post I'll show you how to make sure your variables get updated at the time they're used, instead of potentially reading and updating them at different stages of query execution. This technique enables a whole new range of applications for user variables. As a bonus, it also avoids extra columns of output created by variable manipulations.

Just a quick note to let you know that I’ve written a new technical in-depth white paper on the MySQL 5.1 server that you can now download from our site. It covers all the new features in 5.1 and provides some technical examples of each enhancement that our new release offers. Check it out when you get a chance and let me know if you have any questions.

I have just committed the initial implementation of foreign keys for the PrimeBase XT storage engine, whew! The implementation is similar to that of InnoDB and supports the RESTRICT, CASCADE and SET NULL options.

Currently the PBXT implementation requires that the data types of the referenced columns are identical. InnoDB allows references between CHAR and VARCHAR and strings of different length. PBXT also requires that the index used by foreign keys have the exact number of components. InnoDB can use indexes that have more components than the number of columns specified in the foreign key declaration.

On the other hand, PBXT supports all SQL syntax that can be used to create foreign keys. This includes the following syntax which is not supported by InnoDB:

CREATE TABLE t2 (
id INT PRIMARY KEY,
fk int REFERENCES t1(id)
) ENGINE=pbxt;

Maybe you are wondering why one …

This bug 15815 a huge problem in thread handling with INNODB-will not be available for the Community Edition. Here are your options:

1. 
   
2. Upgrade to 5.0
   
3. Get a support contract that enables the Enterprise Edition
   
4. Hope that pressure from the community will push MySQL to giving this patch to the masses
   

What sort of compensation would MySQL need to allow this patch into the community edition?

Getting everyone to use 5.0 is good for MySQL, maintaining what 5 versions of mySQL must suck. Also to keep this product alive, it takes money and talented engineers that need money too! Yet, for me personally I like 4.1 so I want to be a bit selfish and get this into the 4.1 community edition :)

One of my slave servers stopped twice in the last couple of days. The error in the error log file was:

061214 21:17:41 [ERROR] Slave: Error 'Lock wait timeout exceeded; try restarting transaction' on query. Default database: 'fl_db1'. Query: 'UPDATE user SET total_photos = total_photos + 1 WHERE user_name = '666damy666'', Error_code: 1205
061214 21:17:41 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'db2-bin.003509' position 342646547

I tried to learn more about the error code using perror and got

-bash-3.00$ perror 1205
Illegal error code: 1205

Information about the error is available on MySQL server error messages page of the manual.


…

MySQL’s user variables have interesting properties that enable the useful techniques I wrote about in recent articles. One property is that you can read from and assign to a user variable simultaneously, because an assignment can be an r-value (the result of the assignment is the final value of the variable). Another property, which sometimes causes confusing behavior, is un-intuitive evaluation time. In this post I’ll show you how to make sure your variables get updated at the time they’re used, instead of potentially reading and updating them at different stages of query execution.

In two previous posts (first here and second here) I reported about Java 5 VM crashes on Linux machines.

Digging deeper into the problem with external support led to some new evidence. Apparently the problem is in some way related to regular garbage collects initiated by the so called "GC Daemon" thread. It gets spawned when you use some fashion or other of RMI and calls full GCs in order to get rid of unreachable remote objects.

One can specify the interval (in milliseconds) between calls to the garbage collector using -Dsun.rmi.dgc.server|client.gcInterval. With our application using RMI to call remote services we reduced this value to as little as 6 seconds. As we expected this let us reproduce the problem we have much more …

In two previous posts (first here and second here) I reported about Java 5 VM crashes on Linux machines.

Digging deeper into the problem with external support led to some new evidence. Apparently the problem is in some way related to regular garbage collects initiated by the so called "GC Daemon" thread. It gets spawned when you use some fashion or other of RMI and calls full GCs in order to get rid of unreachable remote objects.

One can specify the interval (in milliseconds) between calls to the garbage collector using -Dsun.rmi.dgc.server|client.gcInterval. With our application using RMI to call remote services we reduced this value to as little as 6 seconds. As we expected this let us reproduce the problem we have much more …