I hope this is my last post about this topic. It seems we've resolved all of the issues and I'll now describe the user-visible consequences.

To recall, we're talking about subquery predicates in form

(oe1, ..., oeN) [NOT] IN (SELECT ie1, ..., ieN FROM ... )

that are located in a context where it matters if the predicate's result is NULL or FALSE. The name "oe" stands for "outer expression", ie stands for "inner expression".

MySQL evaluates queries "from outside to inside", i.e. we first get the values of (oe1, .. oeN) and then we run the subquery and capture the rows it produces. An apparent and very useful optimization is to "inform" the subquery that we're looking only for rows that have "ie1=oe1", "ie2=oe2" and so on. This is done by injecting appropriate equalities into subquery's WHERE (or HAVING) clause. That is,

(oe1, ..., oeN) [NOT] IN (SELECT ie1, ..., ieN FROM ... )

becomes …

I was working with customer today which has MySQL on a system with some 64GB or RAM running MyISAM, so they set key_buffer_size to 16G... and every few days MySQL crashes.

Why ? Because key_buffer_size over 4GB in size is not really supported (checked with latest and greatest MySQL Enterprise 5.0.34). It works just fine until you have less than 4GB worth of key cache used and then it would crash.

If you do not know about this limitation it may be quite complicated to figure out what the problem is as MySQL will be crashing on random statements associated to random tables.

What I find interesting is this bug was known for years and not only fix was not made available but even maximum key_buffer_size was not restricted to safe limit. Former would be close to changing single line of code with variable values limits, might be a bit more than that but it should …

MySQL Replication is asynchronous which causes problems if you would like to use MySQL Slave as it can contain stale data. It is true delay is often insignificant but in times of heavy load or in case you was running some heavy queries on the master which not take time to replicate to the slave replication lag can be significant. Also even very small lag can cause the problems - for example you've posted comment on the blog and on next page reload you do not see it as it was read from the slave millisecond later.... this is something you would not like to happen.

I'll list some techniques here which I found to be helpful for offloading load to the slave without causing application to be have crazy. The same approach can be used in Master-Master replication in Active-Passive mode, just think about passive node as a slave.

Query type based distribution Elect some queries as "time critical" and other queries as "non …

I thought “svn”, I typed “cvs”. Hrrm… sounds about right.

In other revision control news, using quilt to manage work-in-progress patches in conjunction with BK is proving really, really great. I feel like an idiot having lived this long and not worked this way.

I have a feeling that if git was being used I’d just do everything there as it’s so quick anyway. I haven’t used bzr on these sorts of size of repos yet, but it should be good too.

I was feeling pretty ill on Monday but I managed to recover enough to present today's webinar on developing applications for MySQL with Visual Studio.  We had a terrific turnout of +150 people and I've been told the presentation came off pretty well.  As a reminder, if you attended the webinar and have any question that you were not able to ask during the session, please email me directly and I'll get you an answer.

Even more exciting is the community feedback.  I received an email just a couple of hours ago from Jeffrey McManus who blogs about MySQL and .NET (among other things).  He wrote up a nice little article on installing MySql.Data into the GAC.  You can read it here.  Thanks Jeffrey, and keep them coming!

One of the main features that are unique to MySQL is that of pluggable storage engines. The most common ones are MYISAM for speed, InnoDB for transactions as well as ARCHIVE, CVS etc that can help with different problems. There are a few newer engines that people may not know about including Falcon (still alpha), PBXT and the SolidDB storage engine (by Solid Information Technology (www.solidtech.com).

Well, the SolidDB storage engine is one that I have been looking at recently as it supposedly scales very well due to multithreaded implementation and architectural design. I wanted to check the benchmarks and went to the website to find that the only binaries were for Linux and MS Windows. They did offer source however and I tried to compile MySQL with SolidDB storage engine support for Mac OS X (PPC) platform.

During the last days and weeks new releases of PHP (4.4.5 and 5.2.1), a new version of MySQL (5.0.33), and a new Apache (2.2.4) saw the light of the day. We're currently working on the new XAMPP version and invite you to take a closer look at the beta versions of the next XAMPP release:

http://www.apachefriends.org/en/xampp-beta.html

XAMPP BETA versions are always for testing purposes only. There will be no upgrade packages from and to beta versions. To all testers: Many thanks in advance!!

One of the strengths of mondrian's design is that you don't need to do any processing to populate special data structures before you start running OLAP queries. More than a few people have observed that this makes mondrian an excellent choice for 'real-time OLAP' -- running multi-dimensional queries on a database which is constantly changing.

The problem is that mondrian's cache gets in the way. Usually the cache is a great help, because it ensures that mondrian only goes to the DBMS once for a given piece of data, but the cache becomes out of date if the underlying database is changing.

This is solved with a new set of APIs for cache control in mondrian-2.3. Before I explain the API, let's understand how mondrian caches data.

How mondrian's cache works

Mondrian's cache ensures that once a multidimensional cell -- say the Unit Sales of Beer in Texas in Q1, 1997 -- has been retrieved from …

I'm starting to get into Django more and more, forgetting PHP. And some nice stuff is coming up.

Yesterday there was a post about a Django Cheat Sheet published by folks at Mercurytide. Still work in progress, but all tools are welcome for a 'starting' project.

And then there is the Django Book. It's getting quite heavy already in pages and you can leave comments to make it even better!
Of course, there are the usual PostgreSQL posts.. Well, they are funny. Like the one on Chapter 2 suggesting to indeed sort the database engines alphabetically because they should be equally good, but keep PostgreSQL first in the list.

Other comments suggest the MySQL python module MySQLdb doesn't work with Python …

I'm starting to get into Django more and more, forgetting PHP. And some nice stuff is coming up.

Yesterday there was a post about a Django Cheat Sheet published by folks at Mercurytide. Still work in progress, but all tools are welcome for a 'starting' project.

And then there is the Django Book. It's getting quite heavy already in pages and you can leave comments to make it even better!
Of course, there are the usual PostgreSQL posts.. Well, they are funny. Like the one on Chapter 2 suggesting to indeed sort the database engines alphabetically because they should be equally good, but keep PostgreSQL first in the list.

Other comments suggest the MySQL python module MySQLdb doesn't work with Python …