MySQL 5.1 Database Reference Guide :: 2.10.1 Upgrading from MySQL 5.0

Previous / Next / Up / Table of Contents

2.10.1. Upgrading from MySQL 5.0

$8.99

When upgrading a 5.0 installation to 5.0.10 or above note that 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 5.5.2, “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 generally 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 to MySQL 5.1 from 5.0:

Check the items in the change lists found later in this section to see whether any of them might affect your applications. Note particularly any that are marked Incompatible change. These result in incompatibilities with earlier versions of MySQL, and may require your attention before you upgrade.
Some releases of MySQL introduce incompatible changes to tables. (Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.) Some releases of MySQL introduce changes to the structure of the grant tables to add new privileges or features.

To avoid problems due to such changes, after you upgrade to a new version of MySQL, you should check your tables (and repair them if necessary), and update your grant tables to make sure that they have the current structure so that you can take advantage of any new capabilities. See Section 5.5.2, “mysql_upgrade — Check Tables for MySQL Upgrade”.
Read the MySQL 5.1 change history to see what significant new features you can use in 5.1. See Section D.1, “Changes in release 5.1.x (Development)”.
If you are running MySQL Server on Windows, see Section 2.3.14, “Upgrading MySQL on Windows”.
If you are using replication, see Section 6.7, “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.

Server Changes:

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 27.2, “The MySQL Plugin Interface”. The plugin API requires the mysql.plugin table. When upgrading from an older version of MySQL, you should run the mysql_upgrade command to create this table. See Section 5.5.2, “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: The structure of FULLTEXT indexes have been changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or greater, call the REPAIR TABLE statement for each table that contains any FULLTEXT indexes.
Before MySQL 5.1.6, the server writes general query log and slow query log entries to log files. As of MySQL 5.1.6, the server's logging capabilities for these logs are more flexible. Log entries can be written to log files (as before) or to the general_log and slow_log tables in the mysql database. If logging is enabled, either or both destinations can be selected. The --log-output option controls the destination or destinations of log output. See Section 5.11.1, “Server Log Tables”.

If you had the server configured for logging to log files formerly, use --log-output=FILE to preserve this behavior after an upgrade to MySQL 5.1.6 or higher.

SQL Changes:

Incompatible change: As of MySQL 5.1.7, TYPE = engine_name is no longer accepted as a synonym for the ENGINE = engine_name table option. (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 5.5.2, “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 9.5, “Treatment of Reserved Words in MySQL”.
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 27.2, “The MySQL Plugin Interface”.

Previous / Next / Up / Table of Contents