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 B.2, “Changes in Release 4.0.x”.

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:

shell> mysql_convert_table_format database db_name

Note that the above command should be used only if all tables in the database are ISAM or MyISAM tables. To avoid converting tables of other types to MyISAM, you can explicitly list the names of the 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:

mysql> ALTER TABLE tbl_name TYPE=MyISAM;

If you are not sure of the storage engine for a given table, use this statement:

mysql> SHOW TABLE STATUS LIKE 'tbl_name';

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 MySQLdb module.

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 --allow-suspicious-udfs option. See Section 18.2.2.6, “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”.

In order 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 SHOW DATABASES, CREATE TEMPORARY TABLES, and LOCK TABLES privileges. SUPER and EXECUTE privileges take their value from PROCESS. REPLICATION SLAVE and REPLICATION CLIENT take their values from FILE.

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 GRANT commands in the scripts, this is a good time to change your scripts to use GRANT instead of modifying the grant tables directly.

From version 4.0.2 on, the option --safe-show-database is deprecated (and no longer does anything). See Section 5.4.4, “Security-Related mysqld Options”.

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 REPLICATION SLAVE (instead of FILE) for new slave servers.

safe_mysqld has been renamed to mysqld_safe. For backward compatibility, binary distributions will for some time include safe_mysqld as a symlink to mysqld_safe.

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 --skip-innodb server startup option. To compile MySQL without InnoDB support, run configure with the --without-innodb option.

Values for the startup parameters myisam_max_extra_sort_file_size and myisam_max_extra_sort_file_size are given in bytes (prior to 4.0.3,they were given in megabytes).

mysqld has the option --temp-pool enabled by default because this gives better performance with some operating systems (most notably Linux).

The mysqld startup options --skip-locking and --enable-locking were renamed to --skip-external-locking and --external-locking. --skip-locking and --enable-locking are deprecated.

External system locking of MyISAM/ISAM files is turned off by default. You can turn this on with --external-locking. (However, this is never needed for most users.)

The following startup variables and options were renamed:

The startup options record_buffer, sort_buffer, and warnings still work in MySQL 4.0 but are deprecated.

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:

The older names still work in MySQL 4.0 but are deprecated.

You must use SET GLOBAL SQL_SLAVE_SKIP_COUNTER=skip_count instead of SET SQL_SLAVE_SKIP_COUNTER=skip_count.

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 (Null and Index_type).

The format of SHOW OPEN TABLES changed.

As of MySQL 4.0.11, ORDER BY col_name DESC sorts NULL values last. In 3.23 and in earlier 4.0 versions, this was not always consistent.

CHECK, LOCALTIME, and LOCALTIMESTAMP are reserved words.

DOUBLE and FLOAT columns honor the UNSIGNED flag on storage (previously, UNSIGNED was ignored for these columns).

The result of all bitwise operators (|, &, <<, >>, and ~) is unsigned. This may cause problems if you are using them in a context where you want a signed result. See Section 11.9, “Cast Functions and Operators”.

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, INSERT INTO ... SELECT always had IGNORE enabled. As of 4.0.1, MySQL stops (and possibly rolls back) by default in case of an error unless you specify IGNORE.

You should use TRUNCATE TABLE 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 deleted. (DELETE FROM tbl_name returns a row count in 4.0 and does not reset the AUTO_INCREMENT counter, and TRUNCATE TABLE is faster.)

You get an error if you have an active transaction or LOCK TABLES statement when trying to execute TRUNCATE TABLE or DROP DATABASE.

To use MATCH ... AGAINST (... IN BOOLEAN MODE) full-text searches, you must rebuild existing table indexes using REPAIR TABLE tbl_name USE_FRM. If you attempt a boolean full-text search without rebuilding the indexes in this manner, the search returns incorrect results. See Section 11.8.6, “Fine-Tuning MySQL Full-Text Search”.

LOCATE() and INSTR() are case sensitive if one of the arguments is a binary string. Otherwise they are case insensitive.

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.

HEX(str) returns the characters in str converted to hexadecimal. If you want to convert a number to hexadecimal, you should ensure that you call HEX() with a numeric argument.

RAND(seed) returns a different random number series in 4.0 than in 3.23; this was done to further differentiate RAND(seed) and RAND(seed+1).

The default type returned by IFNULL(A,B) is set to be the more “general” of the types of A and B. (The general-to-specific order is string, REAL, INTEGER).

The old C API functions mysql_drop_db(), mysql_create_db(), and 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 instead.

In the MYSQL_FIELD structure, length and max_length 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 printf() class of functions.

Multi-threaded clients should use mysql_thread_init() and mysql_thread_end(). See Section 17.6.15.2, “How to Make a Threaded Client”.

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 mysql_drop_db() call.