Skip navigation links

Additional languages
2.11.2 Copying MySQL Databases to Another Machine »
Section Navigation      [Toggle]

2.11.1. Upgrading from MySQL 5.0 to 5.1

After upgrading a 5.0 installation to 5.0.10 or above, it is necessary to upgrade your grant tables. Otherwise, creating stored procedures and functions might not work. The procedure for doing this is described in Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

Note

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. MySQL recommends that you dump and reload your tables from any previous version to upgrade to 5.1.

In general, you should do the following when upgrading from MySQL 5.0 from 5.1:

  • Read all the items in the following sections to see whether any of them might affect your applications:

  • Note particularly any changes that are marked Known issue or Incompatible change. These incompatibilities with earlier versions of MySQL may require your attention before you upgrade.

    Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases. If any upgrade issue applicable to your installation involves an incompatibility that requires special handling, follow the instructions given in the incompatibility description. Often this will involve a dump and reload, or use of a statement such as CHECK TABLE or REPAIR TABLE.

    A dump and reload is done like this:

    • Dump the affected tables with mysqldump before upgrading

    • Reload the dump file (for example, with mysql) to recreate the tables after upgrading

    Any procedure that involves REPAIR TABLE with the USE_FRM option must be done before upgrading. Use of this statement with a version of MySQL different from the one used to create the table (that is, using it after upgrading) may damage the table. See Section 12.5.2.6, “REPAIR TABLE Syntax”.

  • After you upgrade to a new version of MySQL, run mysql_upgrade (see Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”). This program will check your tables, and repair them if necessary. It will also update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. (Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.)

  • If you are running MySQL Server on Windows, see Section 2.3.14, “Upgrading MySQL on Windows”.

  • If you are using replication, see Section 17.3.3, “Upgrading a Replication Setup”, for information on upgrading your replication setup.

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

Configuration Changes:

  • Before MySQL 5.1.11, to build MySQL from source with SSL support enabled, you would invoke configure with either the --with-openssl or --with-yassl option. In MySQL 5.1.11, those options both have been replaced by the --with-ssl option. By default, --with-ssl causes the bundled yaSSL library to be used. To select OpenSSL instead, give the option as --with-ssl=path, where path is the directory where the OpenSSL header files and libraries are located.

Server Changes:

  • Known issue: MySQL introduces encoding for table names that have non-ASCII characters (see Section 8.2.3, “Mapping of Identifiers to Filenames”). After a live upgrade from MySQL 5.0 to 5.1, the server recognizes names that have non-ASCII characters and adds a #mysql50# prefix to them. Running mysqlcheck later (or mysql_upgrade, which runs mysqlcheck) to upgrade these names encodes them with the new format and removes the #mysql50# prefix. However, although this is done for tables, it is not done for views. To work around this problem, drop each affected view and recreate it.

    This problem is fixed as of MySQL 5.1.23.

  • Known issue: When upgrading from MySQL 5.0 to 5.1, running mysqlcheck (or mysql_upgrade, which runs mysqlcheck) to upgrade tables fails for names that must be written as quoted identifiers. To work around this problem, rename each affected table to a name that does not require quoting: 

    RENAME TABLE `tab``le_a` TO table_a;
RENAME TABLE `table b` TO table_b;

    After renaming the tables, run the mysql_upgrade program. Then rename the tables back to their original names: 

    RENAME TABLE table_a TO `tab``le_a`;
RENAME TABLE table_b TO `table b`;

    This problem is fixed as of MySQL 5.1.23.

  • Incompatible change: MySQL 5.1 implements support for a plugin API that allows the loading and unloading of components at runtime, without restarting the server. Section 30.2, “The MySQL Plugin Interface”. The plugin API requires the mysql.plugin table. After upgrading from an older version of MySQL, you should run the mysql_upgrade command to create this table. See Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”.

    Plugins are installed in the directory named by the plugin_dir system variable. This variable also controls the location from which the server loads user-defined functions (UDFs), which is a change from earlier versions of MySQL. That is, all UDF library files now must be installed in the plugin directory. When upgrading from an older version of MySQL, you must migrate your UDF files to the plugin directory.

  • Incompatible change: The table_cache system variable has been renamed to table_open_cache. Any scripts that refer to table_cache should be updated to use the new name.

  • Incompatible change: Several issues were identified for stored programs (stored functions and procedures, triggers, and events) and views containing non-ASCII symbols. These issues involved conversion errors due to incomplete character set information when translating these objects to and from stored format.

    To address these problems, the representation for these objects was changed in MySQL 5.1.21. However, the fixes affect all stored programs and views. (For example, you will see warnings about “no creation context.”) To avoid warnings from the server about the use of old definitions from any release prior to 5.1.21, you should dump stored programs and views with mysqldump after upgrading to 5.1.21 or higher, and then reload them to recreate them with new definitions. 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.

  • Incompatible change: As of MySQL 5.1.20, mysqld_safe supports error logging to syslog on systems that support the logger command. The new --syslog and --skip-syslog options can be used instead of the --log-error option to control logging behavior, as described in Section 4.3.2, “mysqld_safe — MySQL Server Startup Script”.

    In 5.1.21 and up, the default is --skip-syslog, which is compatible with the default behavior of writing an error log file for releases prior to 5.1.20.

    In 5.1.20 only, the following conditions apply: 1) The default is to use syslog, which is not compatible with releases prior to 5.1.20. 2) Logging to syslog may fail to operate correctly in some cases, so we recommend that you use --skip-syslog or --log-error. To maintain the older behavior if you were using no error-logging option, use --skip-syslog. If you were using --log-error, continue to use it.

  • Incompatible change: As of MySQL 5.1.15, InnoDB rolls back only the last statement on a transaction timeout. A new option, --innodb_rollback_on_timeout, causes InnoDB to abort and roll back the entire transaction if a transaction timeout occurs (the same behavior as in MySQL 4.1).

  • Incompatible change: As of MySQL 5.1.15, the following conditions apply to enabling the read_only system variable:

    • If you attempt to enable read_only while you have any explicit locks (acquired with LOCK TABLES or have a pending transaction, an error will occur.

    • If other clients hold explicit table locks or have pending transactions, the attempt to enable read_only blocks until the locks are released and the transactions end. While the attempt to enable read_only is pending, requests by other clients for table locks or to begin transactions also block until read_only has been set.

    • read_only can be enabled while you hold a global read lock (acquired with FLUSH TABLES WITH READ LOCK) because that does not involve table locks.

    Previously, the attempt to enable read_only would return immediately even if explicit locks or transactions were pending, so some data changes could occur for statements executing in the server at the same time.

  • Incompatible change: The number of function names affected by IGNORE_SPACE was reduced significantly in MySQL 5.1.13, from about 200 to about 30. (For details about IGNORE_SPACE, see Section 8.2.4, “Function Name Parsing and Resolution”.) This change improves the consistency of parser operation. However, it also introduces the possibility of incompatibility for old SQL code that relies on the following conditions:

    • IGNORE_SPACE is disabled.

    • The presence or absence of whitespace following a function name is used to distinguish between a built-in function and stored function that have the same name (for example, PI() versus PI ()).

    For functions that are no longer affected by IGNORE_SPACE as of MySQL 5.1.13, that strategy no longer works. Either of the following approaches can be used if you have code that is subject to the preceding incompatibility:

    • If a stored function has a name that conflicts with a built-in function, refer to the stored function with a schema name qualifier, regardless of whether whitespace is present. For example, write schema_name.PI() or schema_name.PI ().

    • Alternatively, rename the stored function to use a non-conflicting name and change invocations of the function to use the new name.

  • Incompatible change: For utf8 columns, the full-text parser incorrectly considered several non-word punctuation and whitespace characters as word characters, causing some searches to return incorrect results. The fix involves a change to the full-text parser in MySQL 5.1.12, so as of 5.1.12, any tables that have FULLTEXT indexes on utf8 columns must be repaired with REPAIR TABLE: 

    REPAIR TABLE tbl_name QUICK;

  • Incompatible change: Storage engines can be pluggable at runtime, so the distinction between disabled and invalid storage engines no longer applies. As of MySQL 5.1.12, this affects the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.6, “SQL Modes”.

  • Incompatible change: The structure of FULLTEXT indexes has been changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or greater, use the REPAIR TABLE ... QUICK statement for each table that contains any FULLTEXT indexes.

  • Incompatible change: In MySQL 5.1.6, when log tables were implemented, the default log destination for the general query and slow query log was TABLE. As of MySQL 5.1.21, this default has been changed to FILE, which is compatible with MySQL 5.0, but incompatible with earlier releases of MySQL 5.1 from 5.1.6 to 5.1.20. If you are upgrading from MySQL 5.0 to this release, no logging option changes should be necessary. However, if you are upgrading from 5.1.6 through 5.1.20 to this release and were using TABLE logging, use the --log-output=TABLE option explicitly to preserve your server's table-logging behavior.

  • Incompatible change: For ENUM columns that had enumeration values containing commas, the commas were mapped to 0xff internally. However, this rendered the commas indistinguishable from true 0xff characters in the values. This no longer occurs. However, the fix requires that you dump and reload any tables that have ENUM columns containing true 0xff in their values: Dump the tables using mysqldump with the current server before upgrading from a version of MySQL 5.1 older than 5.1.15 to version 5.1.15 or newer.

  • As of MySQL 5.1.12, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. See Section 9.7, “MySQL Server Locale Support”.

  • As of MySQL 5.1.6, special characters in database and table identifiers are encoded when creating the corresponding directory names and filenames. This relaxes the restrictions on the characters that can appear in identifiers. See Section 8.2.3, “Mapping of Identifiers to Filenames”. To cause database and table names to be updated to the new format should they contain special characters, re-encode them with mysqlcheck. The following command updates all names to the new encoding: 

    shell> mysqlcheck --check-upgrade --fix-db-names --fix-table-names --all-databases

    mysqlcheck cannot fix names that contain literal instances of the @ character that is used for encoding special characters. If you have databases or tables that contain this character, use mysqldump to dump them before upgrading to MySQL 5.1.6 or later, and then reload the dump file after upgrading.

  • As of MySQL 5.1.9, mysqld_safe no longer implicitly invokes mysqld-max if it exists. Instead, it invokes mysqld unless a --mysqld or --mysqld-version option is given to specify another server explicitly. If you previously relied on the implicit invocation of mysqld-max, you should use an appropriate option now.

SQL Changes:

  • Incompatible change: Multiple-table DELETE statements containing ambiguous aliases could have unintended side effects such as deleting rows from the wrong table. Example: 

    DELETE FROM t1 AS a2 USING t1 AS a1 INNER JOIN t2 AS a2;

    As of MySQL 5.1.23, alias declarations can be declared only in the table_references part. Elsewhere in the statement, alias references are allowed but not alias declarations. Statements containing aliases that are no longer allowed must be rewritten.

  • Important note: Prior to MySQL 5.1.17, the parser accepted invalid code in SQL condition handlers, leading to server crashes or unexpected execution behavior in stored programs. Specifically, the parser allowed a condition handler to refer to labels for blocks that enclose the handler declaration. This was incorrect because block label scope does not include the code for handlers declared within the labeled block.

    As of 5.1.17, the parser rejects this invalid construct, but if you upgrade in place (without dumping and reloading your databases), existing handlers that contain the construct still are invalid even if they appear to function as you expect and should be rewritten.

    To find affected handlers, use mysqldump to dump all stored functions and procedures, triggers, and events. Then attempt to reload them into an upgraded server. Handlers that contain illegal label references will be rejected.

    For more information about condition handlers and writing them to avoid invalid jumps, see Section 21.2.8.2, “DECLARE Handlers”.

  • Incompatible change: The parser accepted statements that contained /* ... */ that were not properly closed with */, such as SELECT 1 /* + 2. As of MySQL 5.1.23, statements that contain unclosed /*-comments now are rejected with a syntax error.

    This fix has the potential to cause incompatibilities. Because of Bug#26302, which caused the trailing */ to be truncated from comments in views, stored routines, triggers, and events, it is possible that objects of those types may have been stored with definitions that now will be rejected as syntactically invalid. Such objects should be dropped and re-created so that their definitions do not contain truncated comments.

  • Incompatible change: As of MySQL 5.1.8, TYPE = engine_name is still accepted as a synonym for the ENGINE = engine_name table option but generates a warning. You should note that this option is not available in MySQL 5.1.7, and is to be removed altogether in MySQL 5.2, where it will produce a syntax error.

    TYPE has been deprecated since MySQL 4.0.

  • Incompatible change: The namespace for triggers has changed in MySQL 5.0.10. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used).

    When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers and re-create them or DROP TRIGGER will not work after the upgrade. Here is a suggested procedure for doing this:

    1. Upgrade to MySQL 5.0.10 or later to be able to access trigger information in the INFORMATION_SCHEMA.TRIGGERS table. (It should work even for pre-5.0.10 triggers.)

    2. Dump all trigger definitions using the following SELECT statement: 

      SELECT CONCAT('CREATE TRIGGER ', t.TRIGGER_SCHEMA, '.', t.TRIGGER_NAME,
              ' ', t.ACTION_TIMING, ' ', t.EVENT_MANIPULATION, ' ON ',
              t.EVENT_OBJECT_SCHEMA, '.', t.EVENT_OBJECT_TABLE,
              ' FOR EACH ROW ', t.ACTION_STATEMENT, '//' )
INTO OUTFILE '/tmp/triggers.sql'
FROM INFORMATION_SCHEMA.TRIGGERS AS t;

      The statement uses INTO OUTFILE, so you must have the FILE privilege. The file will be created on the server host; use a different filename if you like. To be 100% safe, inspect the trigger definitions in the triggers.sql file, and perhaps make a backup of the file.

    3. Stop the server and drop all triggers by removing all .TRG files in your database directories. Change location to your data directory and issue this command: 

      shell> rm */*.TRG

    4. Start the server and re-create all triggers using the triggers.sql file: For example in my case it was: 

      mysql> delimiter // ;
mysql> source /tmp/triggers.sql //

    5. Check that all triggers were successfully created using the SHOW TRIGGERS statement.

  • Incompatible change: MySQL 5.1.6 introduces the TRIGGER privilege. Previously, the SUPER privilege was needed to create or drop triggers. Now those operations require the TRIGGER privilege. This is a security improvement because you no longer need to grant users the SUPER privilege to enable them to create triggers. However, the requirement that the account named in a trigger's DEFINER clause must have the SUPER privilege has changed to a requirement for the TRIGGER privilege. When upgrading from a previous version of MySQL 5.0 or 5.1 to MySQL 5.1.6 or newer, be sure to update your grant tables as described in Section 4.4.8, “mysql_upgrade — Check Tables for MySQL Upgrade”. This process assigns the TRIGGER privilege to all accounts that had the SUPER privilege. If you fail to update the grant tables, triggers may fail when activated. (After updating the grant tables, you can revoke the SUPER privilege from those accounts that no longer otherwise require it.)

  • Some keywords are reserved in MySQL 5.1 that were not reserved in MySQL 5.0. See Section 8.3, “Reserved Words”.

  • The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 12.6.2.2, “LOAD DATA FROM MASTER Syntax”, for recommended alternatives.

  • The INSTALL PLUGIN and UNINSTALL PLUGIN statements that are used for the plugin API are new. So is the WITH PARSER clause for FULLTEXT index creation that associates a parser plugin with a full-text index. Section 30.2, “The MySQL Plugin Interface”.

C API Changes:

  • Incompatible change: As of MySQL 5.1.7, the mysql_stmt_attr_get() C API function returns a boolean rather than an unsigned int for STMT_ATTR_UPDATE_MAX_LENGTH. (Bug#16144)