2.13.1.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. To perform this
upgrade, run 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.

To upgrade to 5.1 from any previous version,
MySQL recommends that you dump your tables with
mysqldump before upgrading and reload the
dump file after upgrading. Use the
--all-databases option to
include all databases in the dump. If your databases include
stored programs, use the
--routines and
--events options as well.

If you perform a binary (in-place) upgrade without dumping and
reloading tables, you cannot upgrade directly from MySQL 4.1
to 5.1. This occurs due to an incompatible change
in the MyISAM table index format in MySQL
5.0. Upgrade from MySQL 4.1 to 5.0 and repair all
MyISAM tables (see
Section 2.13.4, “Rebuilding or Repairing Tables or Indexes”). Then upgrade from MySQL
5.0 to 5.1 and check and repair your tables.

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

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

The items in the change lists provided later in this
section enable you to identify upgrade issues that apply
to your current MySQL installation. Some
incompatibilities discussed there require your attention
before upgrading. Others should be
dealt with after upgrading.

The MySQL 5.1
Release
Notes describe significant new features you can
use in 5.1 or that differ from those found
in MySQL 5.0. Some of these changes may
result in incompatibilities.

Changes marked as either Known
issue or Incompatible
change are incompatibilities with earlier
versions of MySQL, and 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. Sometimes this involves dumping
and reloading tables, or use of a statement such as
CHECK TABLE or
REPAIR TABLE.

After upgrading to a new version of MySQL, run
mysql_upgrade (see
Section 4.4.8, “mysql_upgrade — Check and Upgrade MySQL Tables”). This program checks your
tables, and attempts to repair them if necessary. It also
updates 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 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.0 to 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: As of MySQL
5.1.70, for new installations, the url
columns in the mysql datatbase help
tables are now created as type
TEXT to accommodate longer
URLs. For upgrades, mysql_upgrade does
not update the columns. Modify them
manually using these statements:

Known issue:
mysql_upgrade attempts to upgrade tables
that are incompatible with the current version of MySQL. (It
invokes mysqlcheck to check tables and,
if necessary, repair them.) However this can fail for
storage engines that do not support
REPAIR TABLE, such as
InnoDB, and leave tables in a
nonupgradable state.

As of MySQL 5.1.42, the server will not open 5.0
ARCHIVE tables at all.

In either case, the solution is to use
mysqldump to dump all 5.0
ARCHIVE tables before
upgrading, and reload them into MySQL 5.1 after upgrading.
This problem is fixed in MySQL 5.6.4: The server can open
ARCHIVE tables created in MySQL
5.0. However, it remains the recommended upgrade procedure
to dump 5.0 ARCHIVE tables
before upgrading and reload them after upgrading.

Known issue: The fix for
Bug #23491 introduced a problem with
SHOW CREATE VIEW, which is
used by mysqldump. This causes an
incompatibility when upgrading from versions affected by
that bug fix (MySQL 5.0.40 through 5.0.43, MySQL 5.1.18
through 5.1.19): If you use mysqldump
before upgrading from an affected version and reload the
data after upgrading to a higher version, you must drop and
recreate your views.

Known issue: Dumps
performed by using mysqldump to generate
a dump file before the upgrade and reloading the file after
upgrading are subject to the following problem:

Before MySQL 5.0.40, mysqldump displays
SPATIAL index definitions using prefix
lengths for the indexed columns. These prefix lengths are
accepted in MySQL 5.0, but not as of MySQL 5.1. If you use
mysqldump from versions of MySQL older
than 5.0.40, any table containing SPATIAL
indexes will cause an error when the dump file is reloaded
into MySQL 5.1 or higher.

For example, a table definition might look like this when
dumped in MySQL 5.0:

Prior to the fix made in 5.1.30, a binary upgrade (performed
without dumping tables with mysqldump
before the upgrade and reloading the dump file after the
upgrade) would corrupt tables. After the fix,
CHECK TABLE ...
FOR UPGRADE properly detects the problem and warns
about tables that need repair.

However, the fix is not backward compatible and can result
in a downgrading problem under these circumstances:

Perform a binary downgrade to a version of MySQL that
does not include the fix.

The solution is to dump tables with
mysqldump before the downgrade and reload
the dump file after the downgrade. Alternatively, drop and
recreate affected indexes.

Known issue: MySQL
introduces encoding for table names that have non-ASCII
characters (see Section 9.2.3, “Mapping of Identifiers to File Names”). After
a binary upgrade from MySQL 5.0 to 5.1 or higher, the server
recognizes names that have non-ASCII characters and adds a
#mysql50# prefix to them.

As of MySQL 5.1.31, mysql_upgrade encodes
these names by executing the following command:

Prior to MySQL 5.1.31, mysql_upgrade does
not execute this command, so you should execute it manually
if you have database or table names that contain
nonalphanumeric characters.

Prior to MySQL 5.1.23, the mysqlcheck
command does not perform the name encoding for views. To
work around this problem, drop each affected view and
recreate it.

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, and then reload the dump file
after upgrading.

Known issue: When upgrading
from MySQL 5.0 to versions of 5.1 prior to 5.1.23, 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:

Known issue: In connection
with view creation, the server created
arc directories inside database
directories and maintained useless copies of
.frm files there. Creation and renaming
procedures of those copies as well as creation of
arc directories has been discontinued
in MySQL 5.1.29.

This change does cause a problem when downgrading to older
server versions which manifests itself under these
circumstances:

Incompatible change: Prior
to MySQL 5.1.51, if you flushed the logs using
FLUSH LOGS
or mysqladmin flush-logs and
mysqld was writing the error log to a
file (for example, if it was started with the
--log-error option), it
renames the current log file with the suffix
-old, then created a new empty log file.
This had the problem that a second log-flushing operation
thus caused the original error log file to be lost unless
you saved it under a different name. For example, you could
use the following commands to save the file:

To avoid the preceding file-loss problem, no renaming occurs
as of MySQL 5.1.51; the server merely closes and reopens the
log file. To rename the file, you can do so manually before
flushing. Then flushing the logs reopens a new file with the
original file name. For example, you can rename the file and
create a new one using the following commands:

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: In
MySQL 5.1.36, options for loading plugins such as pluggable
storage engines were changed from boolean to tristate
format. The implementations overlap, but if you previously
used options of the form
--plugin_name=0
or
--plugin_name=1,
you should instead use
--plugin_name=OFF
or
--plugin_name=ON,
respectively. For details, see
Section 5.1.8.1, “Installing and Uninstalling Plugins”.

Incompatible change: From
MySQL 5.1.24 to 5.1.31, the
UPDATE statement was changed
such that assigning NULL to a
NOT NULL column caused an error even when
strict SQL mode was not enabled. The original behavior
before MySQL 5.1.24 was that such assignments caused an
error only in strict SQL mode, and otherwise set the column
to the implicit default value for the column data type and
generated a warning. (For information about implicit default
values, see Section 11.6, “Data Type Default Values”.)

The change caused compatibility problems for applications
that relied on the original behavior. It also caused
replication problems between servers that had the original
behavior and those that did not, for applications that
assigned NULL to NOT
NULL columns in
UPDATE statements without
strict SQL mode enabled. The change was reverted in MySQL
5.1.32 so that UPDATE again
had the original behavior. Problems can still occur if you
replicate between servers that have the modified
UPDATE behavior and those
that do not.

Incompatible change: As of
MySQL 5.1.29, the default binary logging mode has been
changed from MIXED to
STATEMENT for compatibility with MySQL
5.0.

Incompatible change: In
MySQL 5.1.25, a change was made to the way that the server
handles prepared statements. This affects prepared
statements processed at the SQL level (using the
PREPARE statement) and those
processed using the binary client/server protocol (using the
mysql_stmt_prepare() C API
function).

Previously, changes to metadata of tables or views referred
to in a prepared statement could cause a server crash when
the statement was next executed, or perhaps an error at
execute time with a crash occurring later. For example, this
could happen after dropping a table and recreating it with a
different definition.

Now metadata changes to tables or views referred to by
prepared statements are detected and cause automatic
repreparation of the statement when it is next executed.
Metadata changes occur for DDL statements such as those that
create, drop, alter, rename, or truncate tables, or that
analyze, optimize, or repair tables. Repreparation also
occurs after referenced tables or views are flushed from the
table definition cache, either implicitly to make room for
new entries in the cache, or explicitly due to
FLUSH
TABLES.

Repreparation is automatic, but to the extent that it
occurs, performance of prepared statements is diminished.

An incompatibility with previous versions of MySQL is that a
prepared statement may now return a different set of columns
or different column types from one execution to the next.
For example, if the prepared statement is SELECT *
FROM t1, altering t1 to contain
a different number of columns causes the next execution to
return a number of columns different from the previous
execution.

Older versions of the client library cannot handle this
change in behavior. For applications that use prepared
statements with the new server, an upgrade to the new client
library is strongly recommended.

Along with this change to statement repreparation, the
default value of the
table_definition_cache
system variable has been increased from 128 to 256. The
purpose of this increase is to lessen the chance that
prepared statements will need repreparation due to
referred-to tables/views having been flushed from the cache
to make room for new entries.

A new status variable,
Com_stmt_reprepare, has been introduced
to track the number of repreparations.

Incompatible change: The
-, *, and
/ operators and the functions
POW() and
EXP() could misbehave when
used with floating-point numbers. Previously they might
return +INF, -INF, or
NaN in cases of numeric overflow
(including that caused by division by zero) or when invalid
arguments were used. As of MySQL 5.1.24,
NULL is returned in all such cases.

Incompatible change: As of
MySQL 5.1.23, within a stored routine, it is no longer
permissible to declare a cursor for a
SHOW or
DESCRIBE statement. This
happened to work in some instances, but is no longer
supported. In many cases, a workaround for this change is to
use the cursor with a SELECT
query to read from an INFORMATION_SCHEMA
table that produces the same information as the
SHOW statement.

Incompatible change:
SHOW CREATE VIEW displays
view definitions using an AS
alias_name clause for
each column. If a column is created from an expression, the
default alias is the expression text, which can be quite
long. As of MySQL 5.1.23, aliases for column names in
CREATE VIEW statements are
checked against the maximum column length of 64 characters
(not the maximum alias length of 256 characters). As a
result, views created from the output of
SHOW CREATE VIEW fail if any
column alias exceeds 64 characters. This can cause problems
for replication or loading dump files. For additional
information and workarounds, see
Section D.4, “Restrictions on Views”.

Incompatible change:
Several issues were identified for stored programs (stored
procedures and functions, 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
created, and the
--routines,
--events, and
--triggers options to dump
stored program definitions.

Upgrading for triggers in particular must be handled
carefully, for two reasons:

The output from mysqldump does not
contain a DROP TRIGGER
statement preceding each CREATE
TRIGGER statement, so reloading the dump file
will fail to re-create the triggers unless you manually
drop them after generating the dump file and before
reloading it.

If you are upgrading from a very old version of MySQL
5.0 (before 5.0.10), the trigger upgrade procedure is
different because triggers for those versions were
created using a different namespace (trigger names had
to be unique per table, rather than per schema as is
true now). For instructions on upgrading triggers from
old 5.0 versions, see
Upgrading from MySQL 4.1 to 5.0.

Assuming that you are upgrading from MySQL 5.0.10 to 5.1.20
to MySQL 5.1.21 or later, use the following procedure to
upgrade your triggers:

Use mysqldump to generate a dump file
that contains the trigger definitions:

You might need to add options to specify connection
parameters, such as
--user or
--password. Also, if
you are updating from a version of MySQL 5.1 older than
5.1.21, you may need to include a
--default-character-set option that
specifies the non-ASCII character set that was used for
the definitions when the triggers were originally
created.

Otherwise, invoke mysqldump with
exactly the preceding options to avoid generating a dump
file that will not have the intended effect when
reloaded. For example, if you omit the
--no-create-db option, your databases
will be removed and recreated with no contents when you
reload the dump file.

Drop existing triggers. To see which triggers exist, use
this statement:

The statement uses INTO OUTFILE, so
you must have the FILE
privilege. The file will be created on the server host.
Use a different file name if you like. To be 100% safe,
inspect the trigger definitions in the
drop_triggers.sql file, and perhaps
make a backup of the file. Then execute the statements
in the file:

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. For these reasons, avoid using MySQL 5.1.20.

Incompatible change: As of
MySQL 5.1.18, the plugin interface and its handling of
system variables was changed. Command-line options such as
--skip-innodb
now cause an error if InnoDB is not
built-in or plugin-loaded. You should use
--loose-skip-innodb if you do not want any
error even if InnoDB is not available.
The --loose prefix modifier should be used
for all command-line options where you are uncertain whether
the plugin exists and when you want the operation to proceed
even if the option is necessarily ignored due to the absence
of the plugin. (For a description of how
--loose works, see
Section 4.2.4, “Using Options on the Command Line”.)

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.

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 9.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:

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
nonconflicting name and change invocations of the
function to use the new name.

Incompatible change: For
utf8 columns, the full-text parser
incorrectly considered several nonword 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:

Incompatible change: The
structure of FULLTEXT indexes has been
changed in MySQL 5.1.6. After upgrading to MySQL 5.1.6 or
greater, any tables that have FULLTEXT
indexes must be repaired with REPAIR
TABLE:

REPAIR TABLE tbl_name QUICK;

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. If you are upgrading from MySQL 5.0
to 5.1.21 or higher, no logging option changes should be
necessary. However, if you are upgrading from 5.1.6 through
5.1.20 to 5.1.21 or higher and were using
TABLE logging, use the
--log-output=TABLE option explicitly to
preserve your server's table-logging behavior.

Incompatible change: In
very old versions of MySQL (prior to 4.1), the
TIMESTAMP data type supported
a display width, which was silently ignored beginning with
MySQL 4.1. This is deprecated in MySQL 5.1, and removed
altogether in MySQL 5.5. These changes in behavior can lead
to two problem scenarios when trying to use
TIMESTAMP(N)
columns with a MySQL 5.5 or later server:

When importing a dump file (for example, one created
using mysqldump) created in a MySQL
5.0 or earlier server into a server from a newer release
series, a CREATE TABLE or
ALTER TABLE statement
containing
TIMESTAMP(N)
causes the import to fail with a syntax error.

To fix this problem, edit the dump file in a text editor
to replace any instances of
TIMESTAMP(N)
with TIMESTAMP prior to
importing the file. Be sure to use a plain text editor
for this, and not a word processor; otherwise, the
result is almost certain to be unusable for importing
into the MySQL server.

You should try to handle potential issues of these types
proactively by updating with ALTER
TABLE any
TIMESTAMP(N) columns in your
databases so that they use
TIMESTAMP instead, before
performing any upgrades.

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.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. As of MySQL 5.1.12, there is no longer any
separate mysqld-max server, so no change
should be necessary.

SQL Changes

Known issue: 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
permitted 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 perform a binary upgrade (without dumping and
reloading your databases), existing handlers that contain
the construct still are invalid and should be rewritten
even if they appear to function as you
expect.

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

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:
Multiple-table DELETE
statements containing ambiguous aliases could have
unintended side effects such as deleting rows from the wrong
table. Examples:

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

To avoid ambiguity, declaration of aliases other than in the
table_references part of the
statement should be avoided:

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

As of MySQL 5.1.23, alias declarations outside the
table_references part of the
statement are disallowed for the USING
variant of multiple-table
DELETE syntax. (In MySQL 5.5,
alias declarations outside
table_references are disallowed
for all multiple-table DELETE
statements.) Statements containing aliases that are no
longer permitted must be rewritten.

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 removed altogether in MySQL 5.5 and
produces a syntax error.

TYPE has been deprecated since MySQL 4.0.

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 by
running mysql_upgrade. This will assign
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.