Making the case to support +2 version upgrades

In the MySQL team, we have always had a requirement to support upgrades from one major version. For example:

  • Upgrading from MySQL 5.5 to 5.6 is supported.
  • Upgrading from MySQL 5.1 to 5.6 is not supported.

Downgrades are also supported for one major version. For example, if a user upgrades to 5.6 but discovers that it is not working as expected, they have the safety knowing that there is a way to step back to MySQL 5.5. This may come with some limits; for example when new features (such as new row formats or page checksums) are enabled, this may no longer be possible.

Today I wanted to discuss a current non-requirement. We do not support skipping major versions, such as upgrading from MySQL 5.1 to 5.6. Justin however makes the case that despite not being supported, it has often worked (at least when used with mysqldump).

We believe that Justin's bug report has a lot of merit, and are considering extending our requirements to support a +2 version upgrade (i.e. 5.5 to 5.7). This will have a noticeable impact on our QA team, and the effort required to expand testing to handle additional upgrade scenarios will have to be carefully evaluated.

We are seeking feedback from our users in the community in response to BUG #76264! The specific questions we would like to ask you are:

  • Are you currently running MySQL 5.5 and planning to upgrade directly to MySQL 5.7?
  • If so; are there any constraints that make it too to be able to step through 5.6 as part of the upgrade process?
  • Would it be acceptable if a 5.5 to 5.7 upgrade was only supported via mysqldump?
  • Do you have a requirement for a >+2 version upgrade?

Please leave a comment, or get in touch!

Plan to remove sql-bench from MySQL 5.7

In the MySQL team, we are planning to remove sql-bench from the MySQL 5.7 sources.

As the README file denotes, SQL Bench is a benchmark utility that supports MySQL 3.20 and 3.21, PostgreSQL 6.#, mSQL 2.# and Solid Server 2.2. It has not been well maintained for recent versions, and we are no longer using it internally as part of our testing process.

Our aim in making this change is to:

  • Improve the manageability of our git repository by reducing its size.
  • By keeping unmaintained code bundled with MySQL, it can lead users into negative impressions of our product. We have worked very hard at improving stability, and want to remove lurking pitfalls for our users.

The source code to sql-bench will remain in the 5.6 and earlier source trees.

Will you be affected by this change? Please get in touch!

Proposal to deprecate "compatibility" SQL Modes

In the MySQL team, we are currently discussing deprecating several of the SQL mode options which are used by mysqldump to change the output format. From the mysqldump command:

$ mysqldump --help
..
--compatible=name
 Change the dump to be compatible with a given mode. By
 default tables are dumped in a format optimized for
 MySQL. Legal modes are: ansi, mysql323, mysql40,
 postgresql, oracle, mssql, db2, maxdb, no_key_options,
 no_table_options, no_field_options. One can use several
 modes separated by commas. Note: Requires MySQL server
 version 4.1.0 or higher. This option is ignored with
 earlier server versions.

To explain the rationale for this proposal:

  1. The options mysql323, mysql40 are designed to allow mysqldump to create an output format that can be restored on a MySQL Server of version 3.23 or 4.0. While we aim to support the upgrade case from these versions, supporting a downgrade is not something we support, as restoring data to a 10 year old release poses a number of challenges.
  2. The options postgresql, oracle, mssql, db2, maxdb are 'special' SQL modes, in that they are not really modes themselves but aliases to switch on other SQL modes. For example:
    mysql> set sql_mode = 'mssql';
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select @@sql_mode\G
    *************************** 1. row ***************************
    @@sql_mode: PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,MSSQL,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS
    1 row in set (0.00 sec)
    

    Having aliases creates a number of interesting challenges:

    • It complicates training and support as users may have multiple options in order to achieve the same outcome (either listing all sql modes individually or via one of the aliases). In a similar example we removed unique option prefixes in MySQL 5.7 to reduce this confusion.
    • The options that we enable for each alias imply (but have not measurably offered) compatibility with the other database that is mentioned. Furthermore, as other databases will release newer versions, the singularity of the alias name does not account for this.
    • Related to the above; if newer versions of other database products desire new sql modes enabled, it is more flexible (and future proof) to have the list of which behaviour options that should be enabled for each other database in documentation or client programs rather than the server itself. This allows us to not change behavior in a server GA release.
  3. The options no_key_options, no_field_options and no_table_options remove MySQL-specific meta data which I have highlighted below in bold:
    # no_key_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    # no_field_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    
    # no_table_options
    CREATE TABLE `t` (
      `i` int(11) NOT NULL AUTO_INCREMENT,
      `b` varchar(20) CHARACTER SET utf8 DEFAULT NULL,
      KEY `i` (`i`) USING BTREE KEY_BLOCK_SIZE=2048 COMMENT 'My comment'
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
    

    Note that no_field_options did not remove the character set attribute for the column b and thus is incomplete in its current implementation. no_field_options also disables the same meta-data which is disabled by no_key_options.

    The no_key_options and no_table_options remain useful, although it should be noted that information_schema may better support a custom view of schema to match the capabilities of the destined database. We are working on making information_schema queries much faster in the future, via our native data dictionary project.

To summarize this proposal:

  • Our plan is to deprecate the following options in MySQL 5.7, for removal in a later version:
    mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb.
  • We are also seeking input from those that use the following SQL modes, as we may decide to deprecate these in the future:
    no_field_options, no_key_options, no_table_options.

Will these changes impact you? Please leave a comment or get in touch!

Proposal to change additional defaults in MySQL 5.7 (February Edition)

Following on from my two earlier posts, in the MySQL team we are proposing a new set of changes to defaults for MySQL 5.7:

Setting Old Default New Default
log_slow_admin_statements OFF ON
log_slow_slave_statements OFF ON
long-query-time 10 2
log-queries-not-using-indexes OFF ON
min-examined-row-limit 0 1000
MySQL Command Line Client - +show_warnings
group_concat_max_len 1024 1M
max_allowed_packet 4M 64M
symbolic-links - OFF
temp-pool 1 0
table_open_cache_instances 1 16

To explain these changes in more details:

  • The slow query log remains disabled by default, but when enabled our hope is that we can make it immediately useful. A query exceeding 2 seconds will now be considered slow, and since queries that do not use indexes have the highest chance of causing future problems, they will also be logged unless they examine fewer than 1000 rows. The number of 1000 was chosen because it should eliminate the majority of false positives from queries retrieving a static set of records such as navigation menu items, which are well suited to tablescans.

    In proposing this change, we considered the defaults that other products use for considering a query slow. A popular web performance tool uses a default of 0.5 seconds for an acceptable time to render a full page server side, with 2 seconds (4x) slow enough to warrant logging. Our own MySQL Enterprise Monitor considers 100ms acceptable, and 400ms (4x) as slow. We also considered that setting the min-examined-row-limit to a non zero value will now require an additional step for those that set their long-query-time to zero seconds. We would like to thank Daniel Black for suggesting we change our slow query log options.

  • The MySQL command line client has the ability to automatically print warnings out to screen as they occur. We feel like ON is the more useful default, as a user will typically not intend to execute statements that cause warnings. This behavior applies to both interactive and batch modes, and can be disabled with show_warnings=0. We would like to thank Ryuta Kamizono for this suggestion.
  • We find the GROUP_CONCAT() function in MySQL to be incredibly useful for summarizing results in an aggregate query. We also believe that the default maximum length for grouping values has not kept pace with the larger amounts of memory available on modern hardware. We would like to thank Shlomi Noach for this suggestion.
  • In MySQL 5.6 we increased the max_allowed_packet from 1M to a conservative 4M. We have received a lot of feedback on this change, and are proposing to increase the default to 64M for MySQL 5.7. We would like to thank Shlomi Noach for this suggestion.
  • Many of our configuration files for MySQL packages (as well as those that ship with Linux distributions) default to disabling symbolic links for security reasons. We feel that changing a de facto default to a compiled default will improve user experience (as well as security). We would like to thank Honza Horak for this suggestion.
  • The temp-pool option was originally created as a means to work around potential filesystem issues on Linux when using internal MyISAM based temp tables. The underlying OS issues have since been resolved, and disabling this option removes mutex contention for all disk based temp table engines (MyISAM and InnoDB).
  • We are proposing to increase the default table_open_cache_instances to 16 in order to reduce contention on internal mutexes when opening tables.

For those of you who would like to test out these changes (along with previous changes proposed), I have sample configuration files available:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Proposal to change additional defaults in MySQL 5.7

Following on from my earlier proposal to change Replication + InnoDB settings, in the MySQL team, we are proposing to make the following additional changes to defaults in MySQL 5.7:

Setting Old Default New Default
binlog_error_action IGNORE_ERROR ABORT_SERVER
innodb_checksum_algorithm INNODB CRC32
innodb_page_cleaners 1 4
innodb_purge_threads 1 4
innodb_strict_mode OFF ON
innodb_log_file_size 48M 128M
innodb_buffer_pool_dump_at_shutdown OFF ON
innodb_buffer_pool_load_at_startup OFF ON
innodb_buffer_pool_dump_pct 100 25
innodb_file_format Antelope Barracuda
innodb_large_prefix OFF ON
binlog_gtid_recovery_simplified OFF ON
sync_binlog 0 1
slave_net_timeout 3600 60
P_S config Enable events_statements_history and events_transactions_history consumers by default
MySQL CLI Prompt mysql> prompt="\u@\h [\d] > "
sql-mode +NO_AUTO_CREATE_USER

To explain the motivation behind these changes:

  • The binlog_error_action variable was introduced in MySQL 5.6 after the GA release, and for compatibility defaulted to previous behavior (ignore errors). Since some applications depend on reading from slaves after writing to masters, we feel that the better behavior is to abort.
  • The InnoDB CRC32 checksum was introduced in MySQL 5.6, and offers CPU accelerated page checksum computation. Since previous versions of MySQL were unable to read the new checksum, enabling this feature would have restricted downgrades to dump-and-restore. With MySQL 5.6 supporting the CRC32 checksum, the timing now makes sense to make this the default in 5.7.
  • In some workloads the InnoDB purge and page cleaning operations will not be able to keep up with modifications made inside InnoDB. In combination with newer storage devices that offer very high throughput and accommodate parallel writes, we feel that having multiple threads is a better default.
  • Currently the InnoDB storage engine ignores CREATE TABLE options that it does not understand (innodb_strict_mode=0). We feel this negatively impacts user experience, and are proposing to enable InnoDB strict mode in advance. The downside of this change is that some DDL statements that worked in 5.6 failing by default in 5.7. Thank you to Daniël van Eeden for providing this suggestion.
  • Since the InnoDB buffer pool now supports online resizing (and the innodb log files do not), we believe it makes sense to increase the size of the log files slightly in anticipation of an increased buffer pool size. This comes at a small cost of additional disk space usage. We felt that 128M was a good balance in size, since while larger log files are better for performance, there is also a trade off point where the log files benefit from filesystem caches. Thank you to Justin Swanhart for providing this suggestion.
  • With the increased availability of large memory, waiting for caches to warm back up after restarts has been an increased operational concern. MySQL 5.6 introduced a feature to be able to dump buffer pool page addresses (at a small fraction of total size) to assist in warming caches after restarts. MySQL 5.7 introduces an additional feature to dump only a percentage of the buffer pool, and we felt that by limiting this to 25% was a good tradeoff to enable this by default. Thank you to Daniel Black and Daniël van Eeden for this suggestion.
  • The InnoDB Barracuda file format was first introduced in the InnoDB plugin for MySQL 5.1, and enables InnoDB to use the new DYNAMIC row format (with improved BLOB handling) and COMPRESSED tables. Setting the InnoDB file format to Barracuda depended on innodb-file-per-table=1 (default changed in MySQL 5.6), and introduced an incompatibility that would prevent downgrades to earlier releases. With both MySQL 5.5 and 5.6 supporting Barracuda, the timing now makes sense to enable this by default. Thank you to Daniël van Eeden for providing this suggestion.
  • The innodb_large_prefix prefix option was introduced for Barracuda tables as an option to support much longer index key prefixes (up to 3072 bytes). It was disabled by default so that users did not accidentally create Barracuda tables that would be incompatible on Antelope systems (which would not allow downgrading). With Barracuda becoming the default, and our friends at WordPress and Drupal wanting to create much larger indexes to support utf8mb4 encoded columns, we decided to change this to default to on.
  • The binlog_gtid_recovery_simplified option was released after MySQL 5.6 was released as GA, and defaulted to OFF for backwards compatibility. We feel that ON is the better default, and plan to make this change for MySQL 5.7.
  • With the addition of binary log group commit in MySQL 5.6, the impact from enabling sync_binlog=1 on many non-synthetic workloads should be reduced considerably. This change makes MySQL binary logs durable/crash safe by default.
  • The slave_net_timeout defaults to one hour, which is too long for most modern systems. In addition: MySQL 5.7 now uses this value divided by two to configure the default master heartbeat period. We believe that a one minute default is more appropriate. Thank you to Miguel Angel Nieto for providing this suggestion.
  • In MySQL 5.7, performance_schema can now instrument transactions, which can be incredibly useful when enabled by default. In addition, enabling statements history (introduced in 5.6) provides a lot of useful meta-data for diagnosing performance problems. Thank you to Daniël van Eeden for providing this suggestion.
  • The MySQL command line client has long supported the ability to change the default prompt from "mysql >" to include additional details such as the username and database selected. This is mostly a cosmetic change, but it is useful when you are managing multiple servers. Thank you to Daniël van Eeden for providing this suggestion.
  • In MySQL 5.7 we have already made changes to the default SQL mode: enabling ONLY_FULL_GROUP_BY and STRICT_TRANS_TABLES by default. There is a long standing usability issue with user grants, where a typo in the username could result in a new user being created. We have decided that it is best to disable this behavior by enabling the NO_AUTO_CREATE_USER sql mode by default. Thank you to Daniël van Eeden for providing this suggestion.

In addition to these changes, we have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED. We will re-evaluate this decision for future releases.

For those wishing to test out these new configuration settings, I have configuration file available to try out:

Please let us know what you think of these changes!
You can leave a comment here, or get in touch via email.

Proposal to change Replication and InnoDB Settings in MySQL 5.7

In the MySQL Team, we are currently evaluating potential changes to the default server configuration for MySQL 5.7. For more context, please see my earlier post here.

One of the specific changes we would like to make is with the following three related replication and InnoDB settings:

Setting Old Default New Default
binlog_format STATEMENT ROW
transaction_isolation REPEATABLE-READ READ-COMMITTED
innodb_autoinc_lock_mode 1 ("consecutive" lock mode) 2 ("interleaved" lock mode)

We are considering these changes as a package, for the following reasons:

  • Enabling row based binary logging is the safer option for new applications. It allows all types and combinations of statements to be replicated safely (deterministically), and makes slaves more crash-resilient when using features such as temporary tables.

  • InnoDB requires row-based replication in order to provide the READ-COMMITTED isolation level. In making these two changes together, InnoDB performance is improved:

    • There is no InnoDB gap locking except for foreign-key constraint checking and duplicate-key checking. Also, record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition (link to the manual here).

    • INSERT INTO T SELECT ... FROM S WHERE ... sets an exclusive index record without a gap lock on each row inserted into T. If the transaction isolation level is READ COMMITTED, InnoDB does the search on S as a consistent read (no locks). Otherwise, InnoDB sets shared next-key locks on rows from S. InnoDB has to set locks in the latter case: In roll-forward recovery from a backup, every SQL statement must be executed in exactly the same way it was done originally.

      CREATE TABLE ... SELECT ... performs the SELECT with shared next-key locks or as a consistent read, as for INSERT ... SELECT.

      When a SELECT is used in the constructs REPLACE INTO t SELECT ... FROM s WHERE ... or UPDATE t ... WHERE col IN (SELECT ... FROM s ...), InnoDB sets shared next-key locks on rows from table s." (link to the manual here)

    It is important to note that READ-COMMITTED represents a relaxed consistency over REPEATABLE-READ, but is also the default isolation level chosen by SQL Server, PostgreSQL and Oracle.

    For some additional context, Peter Zaitsev recently blogged about the advantages of READ-COMMITTED.

  • Setting innodb_autoinc_lock_mode to 2 also requires row based replication, and improves concurrency since the Auto Increment number no longer needs to be locked to provide a consecutive set of numbers for a bulk insert operation. The MySQL manual describes this new lock mode as "interleaved".

It is important to remember that these represent changes to the default configuration, and reflect what we believe is the best default choice for new applications. We will of course continue to fully support the previous configuration settings should a user prefer these.

With that being said, we are seeking feedback from the community: Do you agree that these represent the best set of defaults for new applications?

Please leave a comment, or get in touch!

Update Jan 23 2015: We have decided to withdraw our proposal to change the default isolation level to READ-COMMITTED for MySQL 5.7. We will reevaluate this decision in the the future.

What defaults would you like to see changed in MySQL 5.7?

In MySQL 5.6, one of the most well received changes was improving the default configuration to be safer, and easier to use.

We are seeking community feedback for improvements that can be made to the default configuration in MySQL 5.7. Please leave a comment, or get in touch with me via email.

For elimination of confusion, please state both the setting you would like changed, and the new desired value.

Thanks!

MySQL 5.6.22 Community Release Notes

Thank you to the MySQL Community, on behalf of the MySQL team @ Oracle. Your bug reports, testcases and patches have helped create a better MySQL 5.6.22.

In particular:

  • Thank you to Ramesh Sivaraman for reporting that setting the innodb_limit_optimistic_insert_debug debug configuration option to 1 caused an infinite B-tree page split. Ramesh also provided a minimal testcase to reproduce. Bug #74605.
  • Thank you to Jeremy Cole for reporting that InnoDB pages with a checksum value of zero were incorrectly treated as empty pages. Bug #73689.
  • Thank you to Zhai Weixiang for reporting that the MySQL server would fail to shutdown when started with innodb-force-recovery = 6. Bug #73341.
  • Thank you to Bart Butler for reporting that InnoDB would shutdown unclean on systems with lower_case_table_names=2 and containing tables using uppercase. Bug #72043.
  • Thank you to Laurynas Biveinis for reporting two assertions that could occur in InnoDB using debug builds. Bug #71343, Bug #68116.
  • Thank you to Stewart Smith for reporting that InnoDB was missing a memory barrier when setting up asynchronous I/O (AIO). Stewart was also kind enough to provide a patch addressing the issue. Bug #72809.
  • Thank you to Jean-François Gagné for reporting an out of memory situation when using row-based replication and having different data types on the slave. Jean-François also provided an example shell script, which saved us a lot of time in reproducing the issue. Bug #72610.
  • Thank you to Jean-François Gagné for reporting that relay log recovery did not correctly handle the case where Relay_Master_Log_File could be empty. Bug #73039.
  • Thank you to Santosh Praneeth Banda for reporting that replication masters did not correctly handle the case when slaves requested GTIDs which had already been purged. Bug #73032.
  • Thank you to Domas Mituzas for reporting that mysqlbinlog incurred significant kernel mutex contention by calling localtime() for every event read. We have since modified mysqlbinlog to use localtime_r instead. Bug #72701.
  • Thank you to Jeremy Cole for reporting that the variable SQL_LOG_BIN having global scope is dangerous. We agree with Jeremy and have modified the behaviour so that it may only be set on a session basis. Bug #67433.
  • Thank you to Jean-François Gagné for reporting that the mysqld startup script would fail on CentOS when the socket option was relative. Bug #74111.
  • Thank you to Eugene Zheganin for reporting that MySQL would not compile with DTrace on Solaris 11.2. Bug #73826.
  • Thank you to Nikola Glavinceski for reporting that certain queries for which subquery materialization or UNION DISTINCT was used together with a hash index on a temporary table could produce incorrect results or cause a server exit. Bug #73368.
  • Thank you to Hartmut Holzgraefe for reporting that the ENABLED_LOCAL_INFILE cmake option was incorrectly enabled by default. Bug #72106.
  • Thank you to Jason Rider for reporting that the use of ODBC-format date literals could produce incorrect query results. Bug #69233.
  • Thank you to Nicholas Bamber for reporting that mysql_setpermission failed to properly quote user names in SQL statements that it generated. Bug #66317.

Thank you again to all the community contributors listed above. In addition, the MySQL team would like to thank the community for their feedback on Bug #67433.

If I missed a name here, please let me know!

- Morgan

Proposal to deprecate collation_database and character_set_database settings

In the MySQL team, we are currently discussing deprecating the ability to change the collation_database and character_set_database settings. As part of our plan, mysql clients will still be able to access these variables, but they will be read-only.

Introduction

The MySQL manual summarizes these two variables as saying:

This option is dynamic, but only the server should set this information. You should not set the value of this variable manually.

This is to say that usage is already discouraged, but these two variables originally had a purpose of allowing a DBA to change the default character-set temporarily for the current session only. For example:

SET SESSION character_set_database=latin5;
LOAD DATA infile 'foo.txt' ... ;
-- foo.txt is interpreted as if it was in latin5 character set

The most obvious use case for this is in loading data from a text file which contains an arbitrary character set. However, it is worth noting that the LOAD DATA INFILE syntax has since added the ability to specify a character-set.

Known Issues

The functionality of collation_database and character_set_database differs somewhat from the original design goal, and presents usability issues for our users. Specifically:

  1. Both variables can be set GLOBAL; yet it mostly does not apply

    The original feature was designed for these two variables to be set on a per-session basis. However, an undocumented feature is that they can both be set on a GLOBAL basis without error. For example:

    mysql> SET GLOBAL character_set_database=latin5;
    Query OK, 0 rows affected (0.01 sec)
    

    However, when set on a GLOBAL basis these variables will have largely no effect. New sessions will always copy character set information from the current database unless a database is not selected:

     SET GLOBAL @@character_set_database=latin5;
    
     [disconnect]
     [connect, without choosing the current database]
    
     SELECT @@character_set_database; -- session counterpart takes
     its initial value from the global counterpart
    

    We consider any behaviour which will only apply in specific circumstances like this as confusing, and potentially harmful to user experience.

  2. Behaviour broken by fix for Bug #2326

    As part of the fix for Bug #2326, the collation_database variable no longer affects the CREATE TABLE statement. That is to say that setting character_set_database and collation_database does not apply for all statements!

  3. Variables are not replicated prior to fix for Bug#15126

    Before Bug#15126 was fixed, setting of a local variable would not be replicated; thus relying on this feature would break replication. While this is no longer a current issue, it helps support our current understanding that the usage of this feature is minimal.

  4. Does not apply to stored programs

    Setting these variables has no effect on stored programs (triggers, functions, procedures), base tables or views -- all these objects use the character set and collation of the database they belong to.

Known bugs:

  • Bug #35357 - "character_set_database does not effects CREATE DATABASE without character set"
  • Bug #27208 If no current database, character_set_database !=character_set_server
  • Bug #27687 ALTER TABLE ... CONVERT TO uses @@collation_database

Our Proposal

Our plan is to deprecate both SESSION and GLOBAL variables collation_database and character_set_database starting with MySQL 5.7. In MySQL 5.8, we plan to remove the GLOBAL variables and make accessing the SESSION variables read-only.

We are currently seeking feedback relating to this plan:

  • Do you currently use the collation_database and character_set_database variables?
  • Do you have a use-case that we have not currently considered?

Please get in touch! You can either leave a comment, or contact me via email!

New Data Dictionary - upgrading from earlier MySQL releases

In July, I first wrote that we are currently working on a new data dictionary for MySQL. We have since shipped a labs release where you can try it out for yourself and then send us feedback!

Today, I wanted to write about our upgrade plan from the old data dictionary to the new data dictionary. Or more specifically, explain one of the nuances that we currently have with encoding characters that are not safe on all filesystems.

To quote from the MySQL manual:

For example, if a database in MySQL 5.0 has the name a-b-c, the name contains instances of the - (dash) character. In MySQL 5.0, the database directory is also named a-b-c, which is not necessarily safe for all file systems. In MySQL 5.1 and later, the same database name is encoded as a@002db@002dc to produce a file system-neutral directory name.

In order to focus our efforts, we are planning to only support upgrading from the 5.1+ database directory encoding. That is to say that users with data directories originating from 5.0 or earlier and have special characters will need to:

  • mysqldump and restore their data
  • or Step through an intermediate release and upgrade to the newer encoding:

    ALTER DATABASE #mysql50#a-b-c UPGRADE DATA DIRECTORY NAME;

It is worth noting that the ALTER TABLE .. UPDATE DATA DIRECTORY NAME step is automatically run by mysql_upgrade which has always been a recommended step to perform following any upgrade.

We are seeking feedback regarding the limitations of our proposed upgrade path. If this will impact you, please leave a comment, or get in touch!