Why You Want to Switch to MySQL 5.1

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

• online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
• Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.

• InnoDB AUTO_INCREMENT lock handling. Simplistically, you can change the locking behavior of AUTO_INCREMENT with InnoDB tables to make your application more scalable. There are a lot of details in how this is implemented and when you would want to change the locking mode versus when you would not, so I will leave it to the reader to check out the very thorough documentation.

These last two items do not have anything to do with online operations, but “Two words: online operations” makes a much better soundbyte than if I had added these last two into the mix.

• Prepared statements now work with the query cache. Mostly. Check the documentation for the limitations, but I will answer the big question everyone has; “Are the statements cached only with the expansion in place? Or is the prepared statement cached with the unknown variable parameter?” The answer is “textual calls of prepared statements are cached without query expansion, but binary calls of prepared statements are cached with query expansion.”
  If that does not make sense, I assure you, the documentation will.
• INFORMATION_SCHEMA.PROCESSLIST. This one is also not an online operation, but gone are the days of using

  for $i in `mysql -e "show full processlist" | grep [something] | cut -f1`
  do
  mysql -e "kill $pid"
  done

  in order to kill a certain class of queries (ie, queries in Sleep status, queries on a database, queries from a user). And of course seeing a distinct list of who is connected is easier.

  Here is how it works; just as you would expect. Here is a SHOW FULL PROCESSLIST, followed by a read of the PROCESSLIST table, followed by showing the schema — note that the “Info” column is longtext, so the full query will be shown — followed by an actual use case of selecting some columns based on a filter:

  mysql> SHOW FULL PROCESSLISTG
  *************************** 1. row ***************************
       Id: 4
     User: root
     Host: localhost:4213
       db: NULL
  Command: Query
     Time: 0
    State: NULL
     Info: SHOW FULL PROCESSLIST
  1 row in set (0.02 sec)
  
  mysql> SELECT * FROM INFORMATION_SCHEMA.PROCESSLISTG
  *************************** 1. row ***************************
       ID: 4
     USER: root
     HOST: localhost:4213
       DB: NULL
  COMMAND: Query
     TIME: 0
    STATE: preparing
     INFO: SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST
  1 row in set (0.02 sec)
  
  mysql> SHOW CREATE TABLE INFORMATION_SCHEMA.PROCESSLISTG
  *************************** 1. row ***************************
         Table: PROCESSLIST
  Create Table: CREATE TEMPORARY TABLE `PROCESSLIST` (
    `ID` bigint(4) NOT NULL DEFAULT '0',
    `USER` varchar(16) NOT NULL DEFAULT '',
    `HOST` varchar(64) NOT NULL DEFAULT '',
    `DB` varchar(64) DEFAULT NULL,
    `COMMAND` varchar(16) NOT NULL DEFAULT '',
    `TIME` bigint(7) NOT NULL DEFAULT '0',
    `STATE` varchar(64) DEFAULT NULL,
    `INFO` longtext
  ) ENGINE=MyISAM DEFAULT CHARSET=utf8
  1 row in set (0.01 sec)
  
  mysql> SELECT id,host,time FROM INFORMATION_SCHEMA.PROCESSLIST WHERE user='root';
  +----+----------------+------+
  | id | host           | time |
  +----+----------------+------+
  |  4 | localhost:4213 |    0 |
  +----+----------------+------+
  1 row in set (0.02 sec)

I must have missed any hype about the MySQL 5.1 Use Case Competition, because when I was on the MySQL website today I saw a banner for it and clicked it, wondering what it was.

In case you are wondering too, it is a contest where you submit your use case for MySQL 5.1 — your review can be positive or negative. You have a chance to win a free pass to the 2009 MySQL User Conference.

At the end of that article they have a list of some of the features they are working on, taken directly from Jay Pipes’s summary of 5.1 features, MySQL 5.1 Article Recap:

• Partitioning
• Row-Based Replication
• The Plugin interface
• Event Scheduler
• Logs on demand/table logging
• XML functions
• Other improvements

Only the logging issue is on my list (although I believe the PROCESSLIST table came about because of the plugin interface, so maybe that’s two). The online ALTER TABLE is a feature that has been so often considered the Achilles’ heel of MySQL that I am very surprised it is not on the list.