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:
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 row in set (0.00 sec)
In the query with the NULL, it does a full table scan. So, if you’ve run into this problem under MySQL 5.1, the workaround is to remove the NULL. This doesn’t affect MySQL 4.x or 5.0.
You can also follow along with Bug #33139.
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 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 Binary Logging on S1. We will need it to act as a replication Master.
- Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
- If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
- If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
- For other backup methods, you will probably need to get the Binary Log position manually:
mysql> FLUSH TABLES WITH READ LOCK; mysql> SHOW MASTER STATUS; (Perform backup now...) mysql> UNLOCK TABLES;
Once you have a backup with the corresponding Binary Log position, you can setup S2:
- Install MySQL 5.1 on S2.
- Restore the backup from S1 to S2.
- Create the Slave user on S1.
- Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
- Enable Binary Logging on S2. We’ll need this during the upgrade process.
- Setup S2 as a Slave of S1:
- If you used mysqldump for the backup, you will need to run the following query:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';
- For any other method, you’ll need to specify the Binary Log position as well:
mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;
- If you used mysqldump for the backup, you will need to run the following query:
- Start the Slave on S2:
mysql> START SLAVE;
The major pre-upgrade work is now complete.
Upgrade
Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.
mysql> STOP SLAVE; mysql> SHOW MASTER STATUS;
Once the backup is complete, restart S2 and let it catch up with S1 again.
When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.
- Stop the Slave process on S2:
mysql> STOP SLAVE; - Stop S1.
- Upgrade S1 to MySQL 5.1.
- Move the S1 data files to a backup location.
- Move the backup from S2 into S1’s data directory.
- Start S2.
- Setup S1 as a Slave to S2, same as when we made S2 a Slave of S1.
- Let S1 catch up with S2. When it has caught up, stop your application, and make sure S1 is still caught up with S2.
- Switch your application back to using S1.
Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:
mysql> STOP SLAVE; mysql> CHANGE MASTER TO MASTER_HOST='';
Conclusion
You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.
Replication with InnoDB and MyISAM Transactions
February 13, 2009 · Posted in MySQL · 4 CommentsThere’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:
item val 1 1 2 2 And the Master myisam_tbl will look like this:
item val 1 2 In 5.1.30 and earlier, the Slave tables will be correct. However, in 5.1.31, the Slave myisam_tbl will be correct, but the innodb_tbl will look like this:
item val 1 0 2 1 As a bonus, there’s no workaround. Statement Based Replication has never worked for this case. For an SBR Slave (In MySQL 5.0.x and 5.1.x), the Slave myisam_tbl will be correct, but the Slave innodb_tbl will look like this:
item val 1 2 2 2 And so, we come to the moral of the story. Don’t use non-transactional tables in the middle of a transaction. Ever. You will only cause yourself more pain than you can possibly imagine. Instead, move the writes to the non-transactional tables outside of the transaction.
-
Gary Pendergast, noun: Gamer, developer, MySQL, WordPress, web and music geek. Currently taking a leave of absence from life as a MySQL Support Engineer for Oracle to tour around Europe. Can be observed in his natural habit at pento.net, or usually in Bologna, Italy.
