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:

CREATE TABLE foo (
    a INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (a)
);

Compare these two EXPLAINs:

mysql> EXPLAIN * FROM foo WHERE a IN (160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 3
        Extra: Using where
1 row in set (0.06 sec)

mysql> EXPLAIN SELECT * FROM foo WHERE a IN (NULL, 160000, 160001, 160002)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: foo
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 327680
        Extra: Using where
1 …

Look what arrived in the mail! MySQL 5.1 Release t-shirts!

The front

The back - with all our names

We made something similar for the MySQL 5.0 release and 10th anniversary, and sold that. I don’t know if we have plans to sell the MySQL 5.1 t-shirts, but if you think you’d like to buy one, maybe drop me a comment and if there’s enough requests for it, I’m sure we’ll try to sort something out.

…

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 a large data set.
• mysql_upgrade: A much faster method, but it can still be slow for very large data sets.

I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.

Preparation

First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:

• If you haven’t already, enable …

MySQL 5.1 has a lot of nice new features, partitioning, event scheduler, dynamic logging, etc... Most of these require changing some application code or design logic to make use of them. However, there is a few new features that almost every user of InnoDB can take advantage of right away and that is the ability to set MySQL so that less locking is required for many statements in InnoDB. There are a few changes which make this possible.

The first one is the new auto_increment locking for InnoDB. There is a good article which talks about this here and a large section the in the manual.

In MySQL 5.0 and previous, InnoDB used a special query-duration table level lock to acquire the AUTO-INC …

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:

Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: START TRANSACTION;
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START TRANSACTION;
Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
Transaction 2: COMMIT;
Transaction 1: COMMIT;

After this, the Master innodb_tbl would look like this:

So being that I am a support engineer with Sun/MySQL, I figured I would chime in with my opinion of MySQL 5.1 GA so far since I have been using it for a while. For history, I have been with MySQL AB/Sun since the 4.0 release cycle. This post does not represent any official views of my employer, Sun Microsystems, and all such disclaimers.

First off, I want to say that Sun assisted greatly with MySQL 5.1. I think it is a much better release than it would have been with MySQL AB doing the release. I do think this will continue in the future from everything I have experienced and seen so far. Sun knows how to make solid software.


So do I think that 5.1 was released too early? Not at all from my experiences of the MySQL releases. 5.1 is fairing quite well, and I think everyone that has a reason to upgrade to it (such as less locking for InnoDB auto_increment, partitioning, dynamic logging, etc...) should certainly …

Do you remember Guy Adams? He was one of the winners of the “5.1 Use Case Competition”, ending up on position #2. Guess what: He has a webinar coming up tomorrow, by the title Deploying MySQL in a High Performance Satellite Network Management Environment by Parallel.
 
Guy works with Parallel Ltd. in Milton Keynes in the UK. You may also want to read up on Guy’s DevZone article. This is what you can expect of the webinar:

Join us for this informative technical webinar with Guy Adams, CTO at Parallel, whose flagship product SatManage is the worldwide …

Open source the way it ought to be. Today, Percona announced a replacement for InnoDB that improves performance and fixes bugs. The new engine is called XtraDB.

According to Vadim at Percona:

It's 100% backwards-compatible with standard InnoDB, so you can use it as a drop-in replacement in your current environment. It is designed to scale better on modern hardware, and includes a variety of other features useful in high performance environments.

The release is pure GPL (v2) and commercial support is available from Percona. If percona keeps this up, they just might become the new MySQL.

The source is available from Launchpad and from …

Here is my personal recipe for celebrating MySQL 5.1 GA (called “Five shot one“):

Ingredients:

• Kalashnikov vodka from Izhevsk, Udmurtia (

Rick Deckard is an Engineering Manager at MySQL. Rick is one of the most senior engineers in the company, he led the team during the long delivery of the newest GA version of the server, 5.1.

Soon after the release of 5.1, the Internet Village experienced shocking hours. A wind of disappointment spread all over the Net, blown by news of serious issues that affected 5.1. Many users were concerned, they feared for their mission critical applications based on 5.1, although they had already been in production for months without a glitch.

With great disappointment, the Net pointed the finger at Rick, asking for a clarification on these serious issues. "Is that so?" was all he would say.

After a few days, some wise users spent time reviewing the issues that affected 5.1. They discovered that some issues were not different by the ones that were affecting 5.0, and that version was currently in production. The …