New in MySQL 5.1: Sheeri’s Presentation

Jan 2, 2009 / By Sheeri Cabral

Tags:

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.
  • 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.
  • 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?

7 Responses to “New in MySQL 5.1: Sheeri’s Presentation”

  • Sheeri Cabral says:

    Correction: In the video I say that the way MySQL used to determine a GA release was “no critical bugs.” I was reminded that the policy was “no known critical bugs”.

    Releasing with critical bugs is not as awful as it sounds, because:

    1) there have been more tests developed, so internal tests catch more than they did. This includes regression testing.

    2) More people used 5.1 in beta, so there was more external testing going on.

    Previous releases have gone GA and had critical bugs in it, though they weren’t known (whether some were known in 5.0 is a bit iffy, but definitely before 5.0 there were no known critical bugs). I’ve seen the list of the handful of critical bugs left, and they’re not bugs that affect common features — they’re somewhat obscure.

  • Hi Sheeri.
    I listed some specific Q&A related to this presentation in a blog post of mine:
    http://datacharmer.blogspot.com/2009/01/q-on-mysql-51.html

    Cheers

    Giuseppe

  • [...] Giuseppe Maxia, the Data Charmer, has a Q&A on MySQL 5.1, which clears up some unanswered questions in Sheeri’s post on what’s new in 5.1. [...]

  • Heidi Schmidt says:

    A FYI regarding the use of Federated tables in 5.1.30
    If the target and source don’t match up for whatever reason. Say, the engine –federated, wasn’t passed on startup and a table was created with the default engine defined in my.cnf
    It can cause havoc where queries on the information_schema meta data will fail with vague data source errors. And it will also trip up mysqldump as well unless the tables are ignored.
    It’s been making a challenge for me to report on InnoDB “data_free” aka data used space usage.
    If anyone is using 5.1.31 and has any intel on Federated tables behavior and consistency checking I’m all ears.
    Thanks, Heidi hschmidt@online-buddies.com

  • CaptTofu says:

    Heidi,

    I was one of the developers on Federated. I’m not sure exactly what the problem is that you are having when you say “–federated, wasn’t passed on startup” do you mean that federated is not enabled on MySQL startup and that you create a table with “engine=’federated’” and it silently fails and creates instead using the default engine, though information_schema still thinks that it’s a federated table?

    As far as consistency checks, the only consistency check that is performed is whether the table exists or not, both when the table is created and when it is opened. There’s nothing to ensure that columns match up- which is part of the Federated spec that says a Federated table doesn’t know how it’s remote table is stored. Though, maybe a that’s something that could be added as an option down the line.

    Feel free to shoot me an email at patg at patg dot net. I intend Next month to jump-start federated development. I’m currently finishing a book after 10 months and have neglected Federated.

  • Heidi Schmidt says:

    Thanks CaptTofu. Much appreciated. I will email you follow up offline that fills in what I attempt to explain below.
    To explain… Yes, we pass the federated flag on startup. A developer created tables that were meant to be federated in dev, yet without the federated engine enabled the object in dev defaulted to InnoDB (the engine specified in our my.cnf that defines what any object defaults to unless specified)
    The parent database has the correct federated setup for the table and has the engine enabled on startup. When the developer created the dev target table in dev it defaulted to InnoDB. A side effect is that the information_schema meta data gets skewed and will intermittently give an error on read of some (not all) information_schema tables “ERROR 1431 (HY000) at line 1: The foreign data source you are trying to reference does not exist. Data source error: error: 1054 ‘Unknown column ‘column_name’ in ‘field list’” and then no metrics are able to be gathered as a result.

  • [...] presentation was on “What’s new in MySQL 5.1″. Here are the slides in PDF, and notes via blog post. If you’re thinking about upgrading, check it out. I had read the release notes earlier but [...]

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>