Upgrading from MySQL 5.4 to 5.5


It is good practice to back up your data before installing any new version of software. Although MySQL works very hard to ensure a high level of quality, you should protect your data by making a backup.

To upgrade to 5.5 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading.

In general, you should do the following when upgrading from MySQL 5.4 to 5.5:

If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a “dummy” database instance for assessing what conversions might be needed and the work involved to perform them. Make a copy of your MySQL instance that contains a full copy of the mysql database, plus all other databases without data. Run your upgrade procedure on this dummy instance to see what actions might be needed so that you can better evaluate the work involved when performing actual data conversion on your original database instance.

The following lists describe changes that may affect applications and that you should watch out for when upgrading from MySQL 5.4 to 5.5.

Configuration Changes
  • Incompatible change: In MySQL 5.5, the server includes a plugin services interface that complements the plugin API. The services interface enables server functionality to be exposed as a “service” that plugins can access through a function-call interface. The libmysqlservices library provides access to the available services and dynamic plugins now must be linked against this library (use the -lmysqlservices flag). For an example showing what Makefile.am should look like, see Section 23.2.6, “MySQL Services for Plugins”.

Server Changes
  • Incompatible change: As of MySQL 5.5.7, the server requires that a new grant table, proxies_priv, be present in the mysql database. If you are upgrading from a previous MySQL release rather than performing a new installation, the server will find that this table is missing and exit during startup with the following message:

    Table 'mysql.proxies_priv' doesn't exist

    To create the proxies_priv table, start the server with the --skip-grant-tables option to cause it to skip the normal grant table checks, then run mysql_upgrade. For example:

    shell> mysqld --skip-grant-tables &
    shell> mysql_upgrade

    Then stop the server and restart it normally.

    You can specify other options on the mysqld command line if necessary. Alternatively, if your installation is configured so that the server normally reads options from an option file, use the --defaults-file option to specify the file (enter each command on a single line):

    shell> mysqld --defaults-file=/usr/local/mysql/etc/my.cnf
             --skip-grant-tables &
    shell> mysql_upgrade

    With the --skip-grant-tables option, the server does no password or privilege checking, so any client can connect and effectively have all privilges. For additional security, use the --skip-networking option as well to prevent remote clients from connecting.

  • Incompatible change: As of MySQL 5.5.3, the Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features:

    • utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters.

    • The utf8mb4 character set has been added. This is similar to utf8, but its encoding allows up to four bytes per character to enable support for supplementary characters.

    • The ucs2 character set is essentially unchanged except for the inclusion of some newer BMP characters.

    In most respects, upgrading to MySQL 5.5 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. These are the primary areas of concern:

    • For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters is less for utf8mb4 columns than for utf8 columns.

    • For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters that can be indexed is less for utf8mb4 columns than for utf8 columns.

    Consequently, if you want to upgrade tables from utf8 to utf8mb4 to take advantage of supplementary-character support, it may be necessary to change some column or index definitions.

    For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.10, “Unicode Support”, and Section 9.1.11, “Upgrading from Previous to Current Unicode Support”.

  • Incompatible change: As of MySQL 5.5.3, the server includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers.

    Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.

    For additional information about the properties of dtoa conversions, see Section 11.2, “Type Conversion in Expression Evaluation”.

  • Incompatible change: In MySQL 5.5, several changes were made regarding the language and character set of error messages:

    • The --language option for specifying the directory for the error message file is now deprecated. The new --lc-messages-dir and --lc-messages options should be used instead, and --language is handled as an alias for --lc-messages-dir.

    • The language system variable has been removed and replaced with the new lc_messages_dir and lc_messages system variables. lc_messages_dir has only a global value and is read only. lc_messages has global and session values and can be modified at runtime, so the error message language can be changed while the server is running, and individual clients each can have a different error message language by changing their session lc_messages value to a different locale name.

    • Error messages previously were constructed in a mix of character sets. This issue is resolved by constructing error messages internally within the server using UTF-8 and returning them to the client in the character set specified by the character_set_results system variable. The content of error messages therefore may in some cases differ from the messags returned previously.

    For more information, see Section 9.2, “Setting the Error Message Language”, and Section 9.1.6, “Character Set for Error Messages”.

SQL Changes
  • Incompatible change: Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.

  • Some keywords may be reserved in MySQL 5.5 that were not reserved in MySQL 5.4. See Section 8.3, “Reserved Words”.

Copyright © 2010-2023 Platon Technologies, s.r.o.           Home | Man pages | tLDP | Documents | Utilities | About
Design by styleshout