In general, you should do the following when upgrading from MySQL 3.23 to 4.0:
Read all the items in Section 2.11.1, “Upgrading MySQL”, to see whether any of them might affect your applications.
Read all the items in the change list found later in this section to see whether any of them might affect your applications. Note particularly any that are marked Known issue or Incompatible change; these result in incompatibilities with earlier versions of MySQL.
Read the 4.0 changelog to see what significant new features you can use in 4.0. See Section C.2, “Changes in Release 4.0.x (Lifecycle Support Ended)”.
If you run MySQL Server on Windows, see Section 2.3.14, “Upgrading MySQL on Windows”.
After upgrading, update the grant tables to add new privileges and features. This procedure uses the mysql_fix_privilege_tables script and is described in Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”.
If you use replication, see Section 14.6, “Upgrading a Replication Setup”, for information on upgrading your replication setup.
Edit any MySQL startup scripts or option files so that they do not use any of the options described as deprecated later in this section.
Convert your old
ISAM tables to
MyISAM format. One way to do this is with
the mysql_convert_table_format script.
(This is a Perl script; it requires that
DBI be installed.) To convert all of the
tables in a given database, use this command:
Note that the above command should be used only if
all tables in the database are
To avoid converting tables of other types to
MyISAM, you can explicitly list the names
ISAM tables following the database
name on the command line.
Individual tables can be changed to
MyISAM by using the following
ALTER TABLE statement for
each table to be converted:
If you are not sure of the storage engine for a given table, use this statement:
SHOW TABLE STATUS LIKE '
Ensure that you do not have any MySQL clients that use
shared libraries (like the Perl
DBD::mysql module). If you do, you should
recompile them, because the data structures used in
libmysqlclient.so have changed. The
same applies to other MySQL interfaces such as the Python
MySQL 4.0 works even if you do not perform the preceding
actions, but you cannot use the new security privileges in MySQL
4.0 and you may run into problems when upgrading later to MySQL
4.1 or newer. The
ISAM file format still
works in MySQL 4.0, but is deprecated and is not compiled in by
default as of MySQL 4.1.
MyISAM tables should
be used instead.
Old clients should work with a MySQL 4.0 server without any problems.
Even if you perform the preceding actions, you can still downgrade to MySQL 3.23.52 or newer if you run into problems with the MySQL 4.0 series. In this case, you must use mysqldump to dump any tables that use full-text indexes and reload the dump file into the 3.23 server. This is necessary because 4.0 uses an improved format for full-text indexing that is not backward-compatible.
The following lists describe changes that may affect applications and that you should watch out for when upgrading from MySQL 3.23 to 4.0.
As of MySQL 4.0.24, the server by default no longer loads
user-defined functions unless they have at least one
auxiliary symbol defined in addition to the main function
symbol. This behavior can be overridden with the
option. See Section 22.214.171.124, “User-Defined Function Security Precautions”.
MySQL 4.0 has many new privileges in the
mysql.user table. See
Section 5.5.1, “Privileges Provided by MySQL”.
For these new privileges to work, you must update the grant
tables. The procedure for this is described in
Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. Until you do
this, all accounts have the
EXECUTE privileges take their
REPLICATION SLAVE and
REPLICATION CLIENT take their
If you have any scripts that create new MySQL user accounts,
you may want to change them to use the new privileges. If
you are not using
commands in the scripts, this is a good time to change your
scripts to use
of modifying the grant tables directly.
If you get
Access denied errors for new
users in version 4.0.2 and up, you should check whether you
need some of the new grants that you did not need before. In
particular, you need
SLAVE (instead of
FILE) for new slave servers.
InnoDB support is included by default in
binary distributions. If you build MySQL from source,
InnoDB is configured in by default. If
you do not use
InnoDB and want to save
memory when running a server that has
InnoDB support enabled, use the
server startup option. To compile MySQL without
InnoDB support, run
configure with the
External system locking of
ISAM files is
turned off by default. You can turn this on with
this is never needed for most users.)
The following startup variables and options were renamed:
|Name in 3.23||Name in 4.0 (and above)|
The startup options
warnings still work in MySQL 4.0 but are
Some keywords are reserved in MySQL 4.0 that were not reserved in MySQL 3.23. See Section 8.3, “Reserved Words”.
The following SQL variables have been renamed:
|Name in 3.23||Name in 4.0 and above|
The older names still work in MySQL 4.0 but are deprecated.
You must use
SQL_SLAVE_SKIP_COUNTER=skip_count instead of
SHOW MASTER STATUS returns an
empty set if binary logging is not enabled.
SHOW SLAVE STATUS returns an
empty set if the slave is not initialized.
SHOW INDEX has two more
columns in 4.0 than in 3.23 (
The format of
As of MySQL 4.0.11,
ORDER BY col_name
NULL values last. In
3.23 and in earlier 4.0 versions, this was not always
LOCALTIMESTAMP are reserved words.
The result of all bitwise operators (
unsigned. This may cause problems if you are using them in a
context where you want a signed result. See
Section 11.10, “Cast Functions and Operators”.
When you use subtraction between integer values where one
is of type
UNSIGNED, the result is
unsigned. In other words, before upgrading to MySQL 4.0,
you should check your application for cases in which you
are subtracting a value from an unsigned entity and want a
negative answer or subtracting an unsigned value from an
integer column. You can disable this behavior by using the
option when starting mysqld. See
Section 5.1.6, “Server SQL Modes”.
You should use integers to store values in
BIGINT columns (instead of
using strings as in MySQL 3.23). Using strings still works,
but using integers is more efficient.
In MySQL 3.23,
... SELECT always had
enabled. As of 4.0.1, MySQL stops (and possibly rolls back)
by default in case of an error unless you specify
You should use
when you want to delete all rows from a table and you do not
need to obtain a count of the number of rows that were
returns a row
count in 4.0 and does not reset the
AUTO_INCREMENT counter, and
TRUNCATE TABLE is faster.)
MATCH ... AGAINST (... IN BOOLEAN
MODE) full-text searches, you must rebuild
existing table indexes using
you attempt a boolean full-text search without rebuilding
the indexes in this manner, the search returns incorrect
results. See Section 11.9.6, “Fine-Tuning MySQL Full-Text Search”.
STRCMP() uses the current
character set when performing comparisons. This makes the
default comparison behavior not case sensitive unless one or
both of the operands are binary strings.
The old C API functions
mysql_connect() are no
longer supported in MySQL 4.0 unless MySQL is compiled with
CFLAGS=-DUSE_OLD_FUNCTIONS. It is
preferable to change client programs to use the new 4.0 API
have changed from
unsigned int to
unsigned long. This should not cause any
problems, except that they may generate warning messages
when used as arguments in the
class of functions.
Multi-threaded clients should use
Section 126.96.36.199, “Writing C API Threaded Client Programs”.
If you want to recompile the Perl
DBD::mysql module, use a recent version.
Version 2.9003 is recommended. Versions older than 1.2218
should not be used because they use the deprecated