Upgrading MySQL
145
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.
Several visible behaviors have changed between MySQL 4.1 and MySQL 5.0 to make MySQL more
compatible with standard SQL. These changes may affect your applications.
The following lists describe changes that may affect applications and that you should watch out for
when upgrading from MySQL 4.1 to 5.0.
Server Changes
• Incompatible change: Character set or collation changes may require table indexes to be rebuilt.
In MySQL 5.0, these occurred in version 5.0.48. For details, see
Section 2.19.3, “Checking Whether
Tables or Indexes Must Be Rebuilt”
.
• 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.0.52, 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 E.4, “Restrictions on Views”
.
• Incompatible change: Beginning with MySQL 5.0.42, when a
DATE
value is compared with a
DATETIME
value, the
DATE
value is coerced to the
DATETIME
type by adding the time portion as
00:00:00
. Previously, the time portion of the
DATETIME
value was ignored, or the comparison
could be performed as a string comparison. To mimic the old behavior, use the
CAST()
[948]
function to cause the comparison operands to be treated as previously. For example:
date_col
= CAST(NOW() AS DATE)
• 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.0 older
than 5.0.36 to version 5.0.36 or newer.
• Incompatible change. For
BINARY
columns, the pad value and how it is handled has changed as of
MySQL 5.0.15. The pad value for inserts now is
0x00
rather than space, and there is no stripping of
the pad value for retrievals. For details, see
Section 11.1.6.2, “The
BINARY
and
VARBINARY
Types”
.
• Incompatible change: As of MySQL 5.0.13,
InnoDB
rolls back only the last
statement on a transaction timeout. As of MySQL 5.0.32, a new option,
--
innodb_rollback_on_timeout
[1249]
, 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: The namespace for triggers 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 version of MySQL 5 older than 5.0.10 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. (This should work even for pre-5.0.10 triggers.)
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
