Pythian Group

In a nutshell: What’s New in MySQL 5.1.

Release notes: Changes in release 5.1.x (Production).

And yes, very early on (at about two minutes in), I talk about my take on Monty’s controversial post at Oops, we did it again.

To play the video directly, go to http://www.youtube.com/watch?v=Hs4S7vONGMQ. Or watch it embedded inline here:

For the purpose of this article, I am going to use “in MySQL 5.1″ to mean “In MySQL 5.1.30 or higher”, because I am assuming that interested persons reading this want to upgrade to at least the GA release.

Upgrading is almost as easy as just upgrading the software. As always, make sure you backup before upgrading, and upgrade a test machine first, making sure to test your application before upgrading production!

The most important things to know when upgrading

• The mysql database has added the plugin table, and the TRIGGER privilege. After upgrading, make sure to run the mysql_fix_privilege_tables script so these are created in the mysql database.
• EDITED 2/2/2009: Due to http://bugs.mysql.com/bug.php?id=30731, stored procedures, functions, triggers and views should be logically exported (as they are when using mysqldump) before the upgrade, and imported after the upgrade. From the bug: “Invoke mysqldump with a --default-character-set option that names the non-ASCII character set that was used for the definitions when the objects were originally defined.”

• The mysql database has added a script to check compatibility, including checks for table versions (i.e., SELECT VERSION FROM INFORMATION_SCHEMA.TABLES). After upgrading, make sure to run the mysql_upgrade script. Note that this script will check the table FOR UPGRADE and do a REPAIR TABLE if necessary, which means that this may take longer than you think, and it also means that your data may be locked with a write lock for some time. (Have I mentioned doing this on a test system?)
• log is deprecated. Instead, use general_log and if desired, general_log_file See the 5.1 manual’s General Query Log for more details.
• log-slow-queries is deprecated. Instead, use slow_query_log and if desired, slow_query_log_file See the 5.1 manual’s The Slow Query Log for more details.
• mysqld-debug used to be a separate release, now the mysqld-debug binary is included in mysqld distributions.
• safe_mysqld has been an alias to mysqld_safe. safe_mysqld is now out of the installation (it had been deprecated since 4.0).
• Speaking of mysqld_safe, it only checks for and uses mysqld, not mysqld-max.
• The system variable table_cache has been renamed to table_open_cache.
• The system variable table_definition_cache has been created. From the system variables manual page:
  

  The number of table definitions that can be stored in the definition cache. If you use a large number of tables, you can create a large table definition cache to speed up opening of tables. The table definition cache takes less space and does not use file descriptors, unlike the normal table cache….The minimum and default are both 256.

  The maximum value is 524288.

• BDB is unsupported in 5.1. If you have skip-bdb in your my.cnf, you will need to remove that, as well as any bdb-specific options. The SHOW [BDB] LOGS statement has also been deprecated.
• Federated is not enabled by default in 5.1. If you want it, put federated in your my.cnf
• have_isam is removed.
• have_raid is removed.
• innodb_log_arch_dir is removed (it has been deprecated since MySQL 5.0.24).
• master-* is deprecated — finally, no more hard-coding replication parameters!
• one-thread is removed, and thread-handling has been added. You can now specify thread-handling=one-thread instead of one-thread
• have_isam is removed.
• A change has been made in the handling of prepared statements; you may need to upgrade your client library. See Changes in MySQL 5.1.25 for more details.
• FULLTEXT search now ignores apostrophes, so that “Jerry” will now match “Jerry’s”. This is very important in French, where “l’” is prepended to many words. When you upgrade, you must do a REPAIR TABLE ... QUICK on tables with FULLTEXT indexes to rebuild the indexes so they are suitable for these matches. Note that the mysql_upgrade script takes care of all the REPAIR TABLE statements you need.
• mysqldumpslow is a tool now found in the server package. Before 5.1, it was part of the mysql-client package.
• MySQL Cluster is a separate download.

Important bugfixes

• Bug 15406 has been fixed. This was a bug where BLACKHOLE transactions were written to the binary log when they were rolled-back. (If a transaction is rolled back, it should not be written to the binary log!)
• Bug 37051 has been fixed. This was a bug where the --replicate-%-table options were not applied correctly in replicating multi-table updates.
  
• The INFORMATION_SCHEMA has been cleaned up, and now uses less memory.

Other important changes

• ALTER TABLE statements are non-locking for table metadata changes—for example, ADD COLUMN, ADD INDEX (on variable-width columns only), and DROP INDEX (on variable-width columns only) as well as adding an ENUM value at the end of an ENUM list.
• Identifiers such as table names and database names are now escaped, and can support non-traditional characters such as . and #. Any scripts that you have that depend on foo.bar (meaning database foo, table bar) need to be changed if this is to be taken advantage of. (During the presentation, audience members really, really wanted this to be configurable, and off by default (as is the behavior in 5.0 and below versions).)
• RAND() can now take non-constant initializers, and the seed is initialized every time RAND() is called (as opposed to once per query, which is how the TIMESTAMP() and NOW() functions act).
• The ARCHIVE storage engine now supports the AUTO_INCREMENT data type.
• Row-based replication brought to light some issues with system variables and replication. These can be seen at Replication and System Functions.
• mysqlbinlog --verbose for row-based binary log formats, will print out a statement in a comment, see mysqlbinlog Row Event Display
• You can set the hostname in the my.cnf with the hostname parameter.
• Error logging can log to syslog. By default, skip-syslog is used, but you can use syslog to log to syslog. The default tags used in syslog are mysqld_safe and mysqld, but you can use syslog-tag=tagname to add to the mysqld_safe and mysqld tags. Why would you want to add to the tags? If you have multiple instances of mysql on one machine, or if you are logging to a centralized syslog server, adding host and port information to the syslog tag can be very, very useful.
• myisam_use_mmap: Use memory mapping for reading and writing myisam table, false by default.
• binlog_format can now be STATEMENT (or 1), ROW (or 2), or MIXED (or 3)..
• binlog_row_event_max_size sets the maximum size of one binlog row event (similar in principle to max_allowed_packet)
• max_prepared_stmt_count: A global system variable to limit the number of prepared statements in mysqld.
• Prepared_stmt_count: A global status variable showing the current number of prepared statements.
• innodb_stats_on_metadata: When this is enabled, using metadata statements such as SHOW will update statistics. It is enabled by default, which mirrors current 5.0 functionality, but in 5.1 you can turn it off, perhaps making the server faster and more efficient.
• old---reverts to "certain behaviors in previous version". Currently it reverts to previous index hints. In the future it may do more. Currently the default value is disabled.
• mysqlcheck has a new option: --skip-write-binlog will not write ANALYZE TABLE, OPTIMIZE TABLE, and REPAIR TABLE statements to the binary log.
• Stack traces have been improved to be more meaningful.
• The loose prefix modifier can be used in front of plugin-specific parameters, so mysqld will start up even if there are errors generated by plugin-specific configuration options when the plugin is disabled.

  For example, loose-skip-plugin-innodb instead of skip-plugin-innodb if the innodb plugin isn't built.

• slave-exec-mode: This is a new mode to deal with conflict resolution in replication scenarios such as circular replication and master/master replication. STRICT mode is the default, which acts as we are used to---errors are not suppressed. IDEMPOTENT suppresses some errors, including duplicate-key errors (1062) and no-key-found errors. Information about the slave-exec-mode is at the manual page: http://mirror.facebook.com/mysql/doc/refman/5.1/en/server-system-variables.html#option_mysqld_slave_exec_mode.
• Uptime_since_flush_status: a new global server status, provided by Jeremy Cole a long time ago.
• report-%: Global system variables are ways to easily report information to a master when the host is registered as a slave. On the master, doing a SHOW SLAVE HOSTS will show these variables for a slave, if the show-slave-auth-info server variable is set on the master. The parameters are report-user, report-host, report-passsword, and report-port. It's probably a bad idea to report the password.
• You can now change the character set of the connection without having to reconnect, using charset or \C. For example: charset utf8 or \C utf8
. Note that charset utf8; will result in an error, because the semicolon is considered part of the word "utf8"---in other words, the semicolon is unnecessary.
• SHOW PROFILE and SHOW PROFILES: Two very very useful tools submitted by Jeremy Cole. Read up about them and the corresponding INFORMATION_SCHEMA.PROFILING table at the SHOW PROFILES manual page. The video goes through the information these tools can give you.
• Stored routines now allow you to specify the DEFINER, for use with SQL SECURITY DEFINER/INVOKER.
• CHANGE MASTER TO now supports a MASTER_SSL_VERIFY_SERVER_CERT statement. Set it equal to 1 to verify SSL certificates, so that replication between a master and slave will be safer from a man-in-the-middle attack.
• By default, trailing spaces are trimmed from CHAR column values on retrieval. If PAD_CHAR_TO_FULL_LENGTH is enabled, trimming does not occur and retrieved CHAR values are padded to their full length. This mode does not apply to VARCHAR fields.
• It is now possible to set long_query_time in microseconds or to 0. Setting this value to 0 causes all queries to be recorded in the slow query log.

  In the user group, questions that came up were, "How the microsecond slow query log work if the server does not use microseconds? Is that for the file logging only, not the table log?" Why You Want to Switch to MySQL 5.1 was a blog post I wrote back in August that talked about new features; it has some comments, but still leaves the first question unanswered.

• log-slow-slave-statements will log slow queries that are executed by a replication slave (system user).
• Setting min_examined_row_limit=N will only log queries in the slow query log if they examine more than N rows. This is very useful to get rid of some of the "known but good" slow queries that might be logged, especially in conjunction with the log_queries_not_using_indexes option.
• There has been a change from the default behavior of how InnoDB locks when assigning AUTO_INCREMENT values. InnoDB can avoid the table-level lock needed to retrieve an AUTO_INCREMENT value in some cases. The innodb_autoinc_lock_mode parameter and its options are described at the manual page: AUTO_INCREMENT Handling in InnoDB -- and yes, you can set it to "traditional" mode (the way it currently works in versions prior to 5.1.22), though MySQL recommends that only for backwards compatibility and testing purposes. There is also a third mode that is better for scalability and speed, as it does less locking, but it is not as safe.
• Deprecated (will give a warning if used): SHOW INNODB STATUS. Use SHOW ENGINE INNODB STATUS instead.
• Deprecated (will give a warning if used): SHOW INNODB MUTEX. Use SHOW ENGINE INNODB MUTEX instead.
• Deprecated (will give a warning if used): TYPE as a synonym for ENGINE in CREATE TABLE statements, the @@table_type variable, and SHOW TABLE TYPES. Use ENGINE, @@storage_engine and SHOW [STORAGE] ENGINES instead.
• Deprecated (will give a warning if used): skip-thread-priority turned out to be a pretty dangerous option, as can be seen in bug 35164 and bug 37536.

Some not-so-important changes

• The default value for the tmp_table_size system variable has been changed from 32M to 16M. The max_heap_table_size default is 16M, and the maximum temporary table size is dependent on the lower value of both of these variables, so having a higher default value did not make sense.
• The default for max_connections has been changed to 151. This is due to Apache's MaxClient value usually being 150, plus 1 for administrative uses. See bug #23883.
• FULLTEXT matching now allows the keywords IN NATURAL LANGUAGE MODE and IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION the default is natural language mode, and you have always been able to specify IN BOOLEAN MODE. Now you can explicitly state the default.
• SHOW AUTHORS and SHOW CONTRIBUTORS show major developers to MySQL, and folks who have contributed to causes near and dear to MySQL's heart (I am very excited about this as I am actually listed in SHOW CONTRIBUTORS).
• New collations: cp1250_polish_ci, utf8_hungarian_ci, and ucs2_hungarian_ci. The Hungarian collations contain the correct order for vowels, but not consonant contractions (this is being fixed).

Notes and URLs

(This is just a list of links referred to in the presentation, not a complete list.)

Tables using the FEDERATED storage engine cannot be partitioned, according to http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html.

Send messages using John David Duncan's MesgApi_Spread utility, built on top of the Spread Toolkit.

Questions that came up

What is the speed of row-based replication as compared with the speed of statement-based replication?

Does dropping foreign keys take time? Does dropping foreign keys still lock the table? Why?