New in MySQL 5.1: The Presentation

What's new, in a nutshell: http://dev.mysql.com/doc/refman/5.1/en/mysql-nutshell.html.

Release notes: http://dev.mysql.com/doc/refman/5.1/en/news-5-1-x.html (In the video, it's the page entitled "Changes in release 5.1.x").

And yes, very early on (at about 2 minutes in) I talk about my take on Monty's controversial post at http://monty-says.blogspot.com/2008/11/oops-we-did-it-again-mysql-51-released.html

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

The slides can be downloaded as a PDF or in Open Office presentation (odp) formats.

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.
• The mysql database has added a script to check compatibility, including checks for table versions (ie, 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 http://dev.mysql.com/doc/refman/5.1/en/query-log.html for more details.

• log-slow-queries is deprecated, instead, use slow_query_log and if desired, slow_query_log_file
. See http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html 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 <strong>table_cache</strong> has been renamed to <strong>table_open_cache</strong>.
• The system variable <strong>table_definition_cache</strong> has been created. From the 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 http://dev.mysql.com/doc/refman/5.1/en/news-5-1-25.html 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 that is 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 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 http://dev.mysql.com/doc/refman/5.1/en/replication-features-functions.html


• mysqlbinlog --verbose for row-based binary log formats, will print out a statement in comment, see http://dev.mysql.com/doc/refman/5.1/en/mysqlbinlog-row-events.html


• 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 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 -- global system variable to limit the number of prepared statements in mysqld
• Prepared_stmt_count -- global status variable showing the current number of prepared statements
• innodb_stats_on_metadata -- using metadata statements such as SHOW will update statistics when this is enabled. 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</CODE> will not write <code>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</CODE> mode is the default, which acts as we are used to -- errors are not suppressed.<CODE>IDEMPOTENT</CODE> suppresses some errors, including duplicate-key errors (1062) and no-key-found errors.  Information about the <code>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, 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 manual page: http://dev.mysql.com/doc/refman/5.1/en/show-profiles.html. 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 an 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?" http://www.pythian.com/blogs/1168 was a blog post I wrote back in August that talked about new features, which 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: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html -- and yes, you can set it to "traditional" mode (the way it currently works in versions prior to 5.1.22), though that is only recommended by MySQL 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 (http://bugs.mysql.com/bug.php?id=35164) and bug 37536 (http://bugs.mysql.com/bug.php?id=37536).
  
• Deprecated (will give a warning if used):

Some not-so-important changes:


• The default value for the <strong>tmp_table_size</strong> system variable has been changed from 32M to 16M. The <strong>max_heap_table_size</strong> 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 <strong>max_connections</strong> has been changed to 151. This is due to Apache's MaxClient value usually being 150, plus 1 for administrative uses. See bug #23883, http://bugs.mysql.com/bug.php?id=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, 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, it is 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?