Don’t put a NULL in the IN clause in 5.1

There seems to be an optimizer problem in 5.1, if you put a NULL in the IN clause of a SELECT. For example, given the following table:

Compare these two EXPLAINs:

In the query with the NULL, it does a full table scan. So, if you’ve run into this problem under MySQL 5.1, […]

Upgrading MySQL with minimal downtime through Replication

Problem With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths: Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have […]

Replication with InnoDB and MyISAM Transactions

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

After this, the Master innodb_tbl would look like this: item val 1 1 2 […]