(mysql.info) upgrading-from-4-1
Info Catalog
(mysql.info) upgrading-from-5-0
(mysql.info) upgrade
(mysql.info) upgrading-to-arch
2.10.2 Upgrading from MySQL 4.1 to 5.0
--------------------------------------
* 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.0.
In general, you should do the following when upgrading to MySQL 5.0
from 4.1:
* 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 mysql-upgrade.
* Read the MySQL 5.0 change history to see what significant new
features you can use in 5.0. See news-5-0-x.
* If you are running MySQL Server on Windows, see
windows-upgrading.
* MySQL 5.0 adds support for stored procedures. This support
requires the `mysql.proc' table. To create this table, you should
run the `mysql_upgrade' script as described in
mysql-upgrade.
* MySQL 5.0 adds support for views. This support requires extra
privilege columns in the `mysql.user' and `mysql.db' tables. To
create these columns, you should run the `mysql_upgrade' script as
described in mysql-upgrade.
* If you are using replication, see replication-upgrade, for
information on upgrading your replication setup.
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 to MySQL 5.0.
*Server Changes:*
* *Incompatible change*: The indexing order for end-space in `TEXT'
columns for `InnoDB' and `MyISAM' tables has changed. Starting from
5.0.3, `TEXT' indexes are compared as space-padded at the end
(just as MySQL sorts `CHAR', `VARCHAR' and `TEXT' fields). If you
have a index on a `TEXT' column, you should run `CHECK TABLE' on
it. If the check reports errors, rebuild the indexes: Dump and
reload the table if it is an `InnoDB' table, or run `OPTIMIZE
TABLE' or `REPAIR TABLE' if it is a `MyISAM' table.
* *Warning: 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 selects. For details, see
binary-varbinary.
* *Incompatible change*: The implementation of `DECIMAL' has changed
in MySQL 5.0.3. You should make your applications aware of that
change, which is described in
precision-math-decimal-changes.
A consequence of the change in handling of the `DECIMAL' and
`NUMERIC' fixed-point data types is that the server is more strict
to follow standard SQL. For example, a data type of `DECIMAL(3,1)'
stores a maximum value of 99.9. Before MySQL 5.0.3, the server
allowed larger numbers to be stored. That is, it stored a value
such as 100.0 as 100.0. As of MySQL 5.0.3, the server clips 100.0
to the maximum allowable value of 99.9. If you have tables that
were created before MySQL 5.0.3 and that contain floating-point
data not strictly legal for the data type, you should alter the
data types of those columns. For example:
ALTER TABLE TBL_NAME MODIFY COL_NAME DECIMAL(4,1);
* *Incompatible change*: `MyISAM' and `InnoDB' tables created with
`DECIMAL' columns in MySQL 5.0.3 to 5.0.5 will appear corrupt
after an upgrade to MySQL 5.0.6. (The same incompatibility will
occur for these tables created in MySQL 5.0.6 after a downgrade to
MySQL 5.0.3 to 5.0.5.) If you have such tables, check and repair
them with `mysql_upgrade' after upgrading. See
mysql-upgrade.
* *Incompatible change*: As of MySQL 5.0.3, the server by default no
longer loads user-defined functions (UDFs) unless they have at
least one auxiliary symbol (for example, an `xxx_init' or
`xxx_deinit' symbol) defined in addition to the main function
symbol. This behavior can be overridden with the
-allow-suspicious-udfs option. See udf-security.
* *Incompatible change*: The update log has been removed in MySQL
5.0. If you had enabled it previously, you should enable the
binary log instead.
* *Incompatible change:* Support for the `ISAM' storage engine has
been removed in MySQL 5.0. If you have any `ISAM' tables, you
should convert them _before_ upgrading. For example, to convert an
`ISAM' table to use the `MyISAM' storage engine, use this
statement:
ALTER TABLE TBL_NAME ENGINE = MyISAM;
Use a similar statement for every `ISAM' table in each of your
databases.
* *Incompatible change*: Support for `RAID' options in `MyISAM'
tables has been removed in MySQL 5.0. If you have tables that use
these options, you should convert them before upgrading. One way
to do this is to dump them with `mysqldump', edit the dump file to
remove the `RAID' options in the `CREATE TABLE' statements, and
reload the dump file. Another possibility is to use `CREATE TABLE
NEW_TBL ... SELECT RAID_TBL' to create a new table from the `RAID'
table. However, the `CREATE TABLE' part of the statement must
contain sufficient information to re-create column attributes as
well as indexes, or column attributes may be lost and indexes will
not appear in the new table. See create-table.
The `.MYD' files for `RAID' tables in a given database are stored
under the database directory in subdirectories that have names
consisting of two hex digits in the range from `00' to `ff'. After
converting all tables that use `RAID' options, these `RAID'-related
subdirectories still will exist but can be removed. Verify that
they are empty, and then remove them manually. (If they are not
empty, there is some `RAID' table that has not been converted.)
* In MySQL 5.0.6, binary logging of stored routines and triggers was
changed. This change has implications for security, replication,
and data recovery, as discussed in
stored-procedure-logging.
*SQL Changes:*
* *Incompatible change:* Previously, a lock wait timeout caused
`InnoDB' to roll back the entire current transaction. As of MySQL
5.0.13, it rolls back only the most recent SQL statement.
* *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 the file created earlier, use these
commands in the `mysql' program:
mysql> delimiter // ;
mysql> source /tmp/triggers.sql //
5. Use the `SHOW TRIGGERS' statement to check that all triggers
were created successfully.
* *Incompatible change:* As of MySQL 5.0.15, the `CHAR()' function
returns a binary string rather than a string in the connection
character set. An optional `USING CHARSET_NAME' clause may be used
to produce a result in a specific character set instead. Also,
arguments larger than 256 produce multiple characters. They are no
longer interpreted modulo 256 to produce a single character each.
These changes may cause some incompatibilities:
* `CHAR(ORD('A')) = 'a'' is no longer true:
mysql> SELECT CHAR(ORD('A')) = 'a';
+----------------------+
| CHAR(ORD('A')) = 'a' |
+----------------------+
| 0 |
+----------------------+
To perform a case-insensitive comparison, you can produce a
result string in a non-binary character set by adding a
`USING' clause or converting the result:
mysql> SELECT CHAR(ORD('A') USING latin1) = 'a';
+-----------------------------------+
| CHAR(ORD('A') USING latin1) = 'a' |
+-----------------------------------+
| 1 |
+-----------------------------------+
mysql> SELECT CONVERT(CHAR(ORD('A')) USING latin1) = 'a';
+--------------------------------------------+
| CONVERT(CHAR(ORD('A')) USING latin1) = 'a' |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
* `CREATE TABLE ... SELECT CHAR(...)' produces a `VARBINARY'
column, not a `VARCHAR' column. To produce a `VARCHAR'
column, use `USING' or `CONVERT()' as just described to
convert the `CHAR()' result into a non-binary character set.
* Previously, the following statements inserted the value
`0x00410041' (`'AA'' as a `ucs2' string) into the table:
CREATE TABLE t (ucs2_column CHAR(2) CHARACTER SET ucs2);
INSERT INTO t VALUES (CHAR(0x41,0x41));
As of MySQL 5.0.15, the statements insert a single `ucs2'
character with value `0x4141'.
* *Incompatible change:* Beginning with MySQL 5.0.12, natural joins
and joins with `USING', including outer join variants, are
processed according to the SQL:2003 standard. The changes include
elimination of redundant output columns for `NATURAL' joins and
joins specified with a `USING' clause and proper ordering of
output columns. The precedence of the comma operator also now is
lower compared to `JOIN', `LEFT JOIN', and so forth.
These changes make MySQL more compliant with standard SQL.
However, they can result in different output columns for some
joins. Also, some queries that appeared to work correctly prior to
5.0.12 must be rewritten to comply with the standard. For details
about the scope of the changes and examples that show what query
rewrites are necessary, see join.
* *Incompatible change:* Before MySQL 5.0.13, `GREATEST(X,NULL)' and
`LEAST(X,NULL)' return X when X is a non-`NULL' value. As of
5.0.3, both functions return `NULL' if any argument is `NULL', the
same as Oracle. This change can cause problems for applications
that rely on the old behavior.
* *Incompatible change:* Before MySQL 4.1.13/5.0.8, conversion of
`DATETIME' values to numeric form by adding zero produced a result
in `YYYYMMDDHHMMSS' format. The result of `DATETIME+0' is now in
`YYYYMMDDHHMMSS.000000' format.
* Some keywords are reserved in MySQL 5.0 that were not reserved in
MySQL 4.1. See reserved-words.
* As of MySQL 5.0.3, `DECIMAL' columns are stored in a more
efficient format. To convert a table to use the new `DECIMAL'
type, you should do an `ALTER TABLE' on it. The `ALTER TABLE' also
will change the table's `VARCHAR' columns to use the new `VARCHAR'
data type. For information about possible incompatibilities with
old applications, see precision-math.
* MySQL 5.0.3 and up uses precision math when calculating with
`DECIMAL' values (64 decimal digits) and for rounding exact-value
numbers. See precision-math.
* Comparisons made between `FLOAT' or `DOUBLE' values that happened
to work in MySQL 4.1 may not do so in 5.0. Values of these types
are imprecise in all MySQL versions, and you are _strongly
advised_ to avoid such comparisons as `WHERE COL_NAME=SOME_DOUBLE',
_regardless of the MySQL version you are using_. See
problems-with-float.
* As of MySQL 5.0.3, trailing spaces no longer are removed from
values stored in `VARCHAR' and `VARBINARY' columns. The maximum
lengths for `VARCHAR' and `VARBINARY' columns in MySQL 5.0.3 and
later are 65,535 characters and 65,535 bytes, respectively.
* If you create a table with new `VARCHAR' or `VARBINARY'
columns in MySQL 5.0.3 or later, the table will not be usable if
you downgrade to a version older than 5.0.3. Dump the table before
downgrading and reload it after downgrading.
* As of MySQL 5.0.3, `BIT' is a separate data type, not a synonym
for `TINYINT(1)'. See numeric-type-overview.
* MySQL 5.0.2 adds several SQL modes that allow stricter control
over rejecting records that have invalid or missing values. See
server-sql-mode, and constraint-invalid-data. If
you want to enable this control but continue to use MySQL's
capability for storing incorrect dates such as `'2004-02-31'', you
should start the server with
-sql_mode=TRADITIONAL,ALLOW_INVALID_DATES.
* As of MySQL 5.0.2, the `SCHEMA' and `SCHEMAS' keywords are
accepted as synonyms for `DATABASE' and `DATABASES', respectively.
(While `schemata' is grammatically correct and even appears in
some MySQL 5.0 system database and table names, it cannot be used
as a keyword for input.)
* User variables are not case sensitive in MySQL 5.0. In MySQL 4.1,
`SET @x = 0; SET @X = 1; SELECT @x;' created two variables and
returned `0'. In MySQL 5.0, it creates one variable and returns
`1'.
* A new startup option named innodb_table_locks was added that causes
`LOCK TABLE' to also acquire `InnoDB' table locks. This option is
enabled by default. This can cause deadlocks in applications that
use `AUTOCOMMIT=1' and `LOCK TABLES'. If you application
encounters deadlocks after upgrading, you may need to add
`innodb_table_locks=0' to your `my.cnf' file.
*C API Changes:*
* *Incompatible change*: Because the MySQL 5.0 server has a new
implementation of the `DECIMAL' data type, a problem may occur if
the server is used by older clients that still are linked against
MySQL 4.1 client libraries. If a client uses the binary
client/server protocol to execute prepared statements that
generate result sets containing numeric values, an error will be
raised: `'Using unsupported buffer type: 246''
This error occurs because the 4.1 client libraries do not support
the new `MYSQL_TYPE_NEWDECIMAL' type value added in 5.0. There is
no way to disable the new `DECIMAL' data type on the server side.
You can avoid the problem by relinking the application with the
client libraries from MySQL 5.0.
* *Incompatible change*: The `ER_WARN_DATA_TRUNCATED' warning symbol
was renamed to `WARN_DATA_TRUNCATED' in MySQL 5.0.3.
* The `reconnect' flag in the `MYSQL' structure is set to 0 by
`mysql_real_connect()'. Only those client programs which did not
explicitly set this flag to 0 or 1 after `mysql_real_connect()'
experience a change. Having automatic reconnection enabled by
default was considered too dangerous (due to the fact that table
locks, temporary tables, user variables, and session variables are
lost after reconnection).
Info Catalog
(mysql.info) upgrading-from-5-0
(mysql.info) upgrade
(mysql.info) upgrading-to-arch
automatically generated byinfo2html