As I mentioned on my last post, where I compared the default configurations options in 5.6 and 5.7, I have been doing some testing for a particular load in several versions of MySQL. What I have been checking is different ways to load a CSV file (the same file I used for testing the compression tools) into MySQL. For those seasoned MySQL DBAs and programmers, you probably know the answer, so you can jump over to my 5.6 versus 5.7 results. However, the first part of this post is dedicated for developers and MySQL beginners that want to know the answer to the title question, in a step-by-step fashion. I must say I also learned something, as I under- and over-estimated some of the effects of certain …

MySQL 5.5.40 was recently released (it is the latest MySQL 5.5, is GA), and is available for download here:

http://dev.mysql.com/downloads/mysql/5.5.html

This release, similar to the last 5.5 release, is mostly uneventful.

There were 0 “Functionality Added or Changed” bugs this time, and 18 bugs overall fixed.

Out of the 18 bugs, most seemed rather minor or obscure, but there are 3 I think are worth noting (all 3 are InnoDB-related, regressions, and serious if you encounter them, so best to be aware of them):

• InnoDB: An ALTER TABLE … ADD FOREIGN KEY operation could cause a serious error. (Bug #19471516, Bug #73650)
• InnoDB: With a transaction isolation level less than or equal to READ COMMITTED, gap locks …

I'm not the only one to have noticed this, but I spent a sufficient amount of time banging my head against a wall finding this out that I thought it important to make more people aware of this.

While trying to validate new database hardware we were seeing some serious performance issues in production.  Most MySQL benchmarks using sysbench or pt-playback couldn't reproduce it, but a simple sysbench 16 threaded filio test on the mysql partition showed about 1/3 the throughput we would expect.   The fact that much of the hardware was new as well as the OS we were using made tracking down the cause difficult (changing from CentOS 5.5 to Scientific Linux 6.)

Finally some of our ops people working on different systems started noticing similar issues, and they uncovered the XFS issue.  Sure enough -- when took existing hardware, upgraded to SL6 and ran the same sysbench filio test we immediately saw a …

We just ran into a nasty InnoDB bug that only seems to exist in MySQL 5.5.25:

An InnoDB update that modifies a rows primary key can trigger some recursive behavior that creates new rows until all disk space is exceeded. This does not affect all primary key updates in general but only gets triggered when a few other conditions are also met, so you're not too likely to run into it, but if you do your mysqld server will waste a lot of IO bandwidth and storage space on this and will usually eventually run out of disk space.

read more

For those who saw my previous post about the crashing (regression) bug with SELECT COUNT(DISTINCT) on InnoDB with Primary Key (PK), you’ll be interested to know my test case does not crash in 5.5.18 (which was just released).

I’ve only tested my test case thus far, but it seems fine.

Unfortunately, the fix is not mentioned in the 5.5.18 changelogs though.

And there is no mention (yet, anyway) of a fix in the bug report I filed (though it was designated a ‘duplicate’, so it wouldn’t necessarily be updated).

I’m trying to get confirmation from the MySQL Dev Team on this (via the bug report), and will update this post if/when I hear anything.

I’ll also perform some of the …

In 5.5, a crashing, regression bug exists if you use SELECT COUNT(DISTINCT) *and* one of the WHERE operands is in the Primary Key (or just a unique index).

This simple crash (if only one row is in the table) will crash mysqld.

Of course I’ve filed a bug report, but that has been nearly 3 months and no updates yet.

Here is the bug I filed (which you won’t be able to view):

http://bugs.mysql.com/bug.php?id=61842

Really, the only thing that happened to my bug report was that it was designated a duplicate of another bug (which we also cannot view):

http://bugs.mysql.com/bug.php?id=61101

Based on the id, and the submitted dates of bugs 61100 and 61102, this initial bug (61101) was filed on May 9, 2011. So, in fact, this bug has been present for over …

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 …

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: