Read all the items in Section 2.11, “Upgrading MySQL”, to see whether any of them might affect your applications.

Read all 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 Known issue or Incompatible change. These result in incompatibilities with earlier versions of MySQL and you should consider the implications of these incompatibilities before you upgrade. Note particularly the items under “Server Changes” that related to changes in character set support.

Read the 4.1 news items to see what significant new features you can use in 4.1. See Section B.1, “Changes in release 4.1.x (Production)”.

If you are running MySQL Server on Windows, see Section 2.3.14, “Upgrading MySQL on Windows”. You should also be aware that two of the Windows MySQL servers were renamed in MySQL 4.1. See Section 2.3.8, “Selecting a MySQL Server Type”.

After upgrading, update the grant tables to obtain the new longer Password column that is needed for more secure handling of passwords. The procedure uses mysql_fix_privilege_tables and is described in Section 4.4.5, “mysql_fix_privilege_tables — Upgrade MySQL System Tables”. If you do not do this, MySQL does not use the new more secure protocol to authenticate. Implications of the password-handling change for applications are given later in this section.

If you are using replication, see Section 14.6, “Upgrading a Replication Setup”, for information on upgrading your replication setup.

The Berkeley DB table handler is updated to DB 4.1 (from 3.2) which has a new log format. If you have to downgrade back to 4.0 you must use mysqldump to dump your BDB tables in text format and delete all log.XXXXXXXXXX files before you start MySQL 4.0 and reload the data.

MySQL 4.1.3 introduces support for per-connection time zones. See Section 9.6, “MySQL Server Time Zone Support”. To enable recognition of named time zones, you should create the time zone tables in the mysql database. For instructions, see Section 2.10, “Post-Installation Setup and Testing”.

If you are using an old DBD-mysql module (Msql-MySQL-modules) you must upgrade to the newer DBD-mysql module. Anything above DBD-mysql 2.xx should be satisfactory.

If you do not upgrade, some methods (such as DBI->do()) do not notice error conditions correctly.

The --defaults-file=option_file_name option gives an error if the option file does not exist.

Some notes about upgrading from MySQL 4.0 to MySQL 4.1 on Netware: Make sure to upgrade Perl and PHP versions. Download Perl 5 for Netware from http://forge.novell.com/modules/xfmod/project/?perl5 and PHP from http://forge.novell.com/modules/xfmod/project/?php. Download and install the Perl module for MySQL 4.1 from http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1126 and the PHP Extension for MySQL 4.1 from http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1078.

Incompatible change: There are conditions under which you should rebuild tables. In general, to rebuild a table, dump it with mysqldump and reload the dump file. Some items in the following list indicate alternatives means for rebuilding.

Incompatible change: MySQL interprets length specifications in character column definitions in characters. (Earlier versions interpret them in bytes.) For example, CHAR(N) means N characters, not N bytes.

For single-byte character sets, this change makes no difference. However, if you upgrade to MySQL 4.1 and configure the server to use a multi-byte character set, the apparent length of character columns changes. Suppose that a 4.0 table contains a CHAR(8) column used to store ujis characters. Eight bytes can store from two to four ujis characters. If you upgrade to 4.1 and configure the server to use ujis as its default character set, the server interprets character column lengths based on the maximum size of a ujis character, which is three bytes. The number of three-byte characters that fit in eight bytes is two. Consequently, if you use SHOW CREATE TABLE to view the table definition, MySQL displays CHAR(2). You can retrieve existing data from the table, but you can only store new values containing up to two characters. To correct this issue, use ALTER TABLE to change the column definition. For example:

ALTER TABLE tbl_name MODIFY col_name CHAR(8);

Incompatible change: As of MySQL 4.1.2, handling of the FLOAT and DOUBLE floating-point data types is more strict to follow standard SQL. For example, a data type of FLOAT(3,1) stores a maximum value of 99.9. Before 4.1.2, the server allowed larger numbers to be stored. That is, it stored a value such as 100.0 as 100.0. As of 4.1.2, the server clips 100.0 to the maximum allowable value of 99.9. If you have tables that were created before MySQL 4.1.2 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 FLOAT(4,1);

Incompatible change: In connection with the support for per-connection time zones in MySQL 4.1.3, the timezone system variable was renamed to system_time_zone.

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 4.1 older than 4.1.23 to version 4.1.23 or newer.

Incompatible change: The interface to aggregate user-defined functions changed as of MySQL 4.1.1. You must declare a xxx_clear() function for each aggregate function XXX(). xxx_clear() is used instead of xxx_reset(). See Section 20.2.4.2, “UDF Calling Sequences for Aggregate Functions”.

Important note: MySQL 4.1 stores table names and column names in utf8. If you have table names or column names that use characters outside of the standard 7-bit US-ASCII range, you may have to do a mysqldump of your tables in MySQL 4.0 and restore them after upgrading to MySQL 4.1. The symptom for this problem is that you get a table not found error when trying to access your tables. In this case, you should be able to downgrade back to MySQL 4.0 and access your data.

Important note: If you upgrade to MySQL 4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0. That is because, for earlier versions, InnoDB is not aware of multiple tablespaces.

All tables and non-binary string columns (CHAR, VARCHAR, and TEXT) have a character set. See Section 9.1, “Character Set Support”. Binary string columns (BINARY, VARBINARY, and BLOB) contain strings of bytes and do not have a character set.

Character set information is displayed by SHOW CREATE TABLE and mysqldump. (MySQL versions 4.0.6 and above can read the new dump files; older versions cannot.) This change should not affect applications that use only one character set.

If you were using columns with the CHAR BINARY or VARCHAR BINARY data types in MySQL 4.0, these were treated as binary strings. To have them treated as binary strings in MySQL 4.1, you should convert them to the BINARY and VARBINARY data types, respectively.

If you have table columns that store character data represented in a character set that the 4.1 server supports directly, you can convert the columns to the proper character set using the instructions in Section 9.1.9.2, “Converting 4.0 Character Columns to 4.1 Format”. Also, database, table, and column identifiers are stored internally using Unicode (UTF-8) regardless of the default character set. See Section 8.2, “Database, Table, Index, Column, and Alias Names”.

The table definition format used in .frm files has changed slightly in 4.1. MySQL 4.0 versions from 4.0.11 on can read the new .frm format directly, but older versions cannot. If you need to move tables from 4.1 to a version earlier than 4.0.11, you should use mysqldump. See Section 4.5.4, “mysqldump — A Database Backup Program”.

Windows servers support connections from local clients using shared memory if run with the --shared-memory option. If you are running multiple servers this way on the same Windows machine, you should use a different --shared-memory-base-name option for each server.

As of MySQL 4.1.21, the lc_time_names system variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output from the DATE_FORMAT(), DAYNAME() and MONTHNAME() functions. See Section 9.7, “MySQL Server Locale Support”.

As of MySQL 4.1.10a, the server by default no longer loads user-defined functions (UDFs) 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 20.2.4.6, “User-Defined Function Security Precautions”.

mysqldump has the --opt and --quote-names options enabled by default. You can turn these off using --skip-opt and --skip-quote-names.

Incompatible change: In MySQL 4.1, string comparison works according to the SQL standard: Instead of stripping end spaces before comparison, the shorter string is extended using spaces. This means that 'a' > 'a\t', which it was not previously. If you have any tables containing an indexed CHAR, VARCHAR or TEXT column in which the last character in the index may be less than ASCII(32), you should use REPAIR TABLE or mysqlcheck to ensure that the table is correct.

Incompatible change: TIMESTAMP is returned in MySQL 4.1 as a string in 'YYYY-MM-DD HH:MM:SS' format. (See Section 10.3.1.2, “TIMESTAMP Properties as of MySQL 4.1”.) From 4.0.12 on, the --new option can be used to make a 4.0 server behave as 4.1 in this respect. The effect of this option is described in Section 10.3.1.1, “TIMESTAMP Properties Prior to MySQL 4.1”.

When running the server with --new, if you want to have a TIMESTAMP column returned as a number (as MySQL 4.0 does by default), you should add +0 when you retrieve it:

mysql> SELECT ts_col + 0 FROM tbl_name;

Display widths for TIMESTAMP columns are no longer supported in MySQL 4.1. For example, if you declare a column as TIMESTAMP(10), the (10) is ignored.

Incompatible change: Binary values such as 0xFFDF are assumed to be strings instead of numbers. This fixes some problems with character sets where it is convenient to input a string as a binary value. With this change, you should use CAST() if you want to compare binary values numerically as integers:

mysql> SELECT CAST(0xFEFF AS UNSIGNED INTEGER)
    ->        < CAST(0xFF AS UNSIGNED INTEGER);
        -> 0

If you do not use CAST(), a lexical string comparison is made instead:

mysql> SELECT 0xFEFF < 0xFF;
        -> 1

Using binary items in a numeric context or comparing them using the = operator should work as before. (The --new option can be used from 4.0.13 on to make a 4.0 server behave as 4.1 in this respect.)

Incompatible change: Before MySQL 4.1.13, 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.

Incompatible change: In MySQL 4.1.12, the behavior of LOAD DATA INFILE and SELECT ... INTO OUTFILE has changed when the FIELDS TERMINATED BY and FIELDS ENCLOSED BY values both are empty. Formerly, a column was read or written the display width of the column. For example, INT(4) was read or written using a field with a width of 4. Now columns are read and written using a field width wide enough to hold all values in the field. However, data files written before this change was made might not be reloaded correctly with LOAD DATA INFILE for MySQL 4.1.12 and up. This change also affects data files read by mysqlimport and written by mysqldump --tab, which use LOAD DATA INFILE and SELECT ... INTO OUTFILE. For more information, see Section 12.2.5, “LOAD DATA INFILE Syntax”.

Incompatible change: Before MySQL 4.1.1, the statement parser was less strict and its string-to-date conversion would ignore everything up to the first digit. As a result, invalid statements such as the following were accepted:

INSERT INTO t (datetime_col) VALUES ('stuff 2005-02-11 10:17:01');

As of MySQL 4.1.1, the parser is stricter and treats the string as an invalid date, so the preceding statement results in a warning.

Incompatible change: In MySQL 4.1.2, the Type column in the output from SHOW TABLE STATUS was renamed to Engine. This affects applications that identify output columns by name rather than by position.

Incompatible change: The syntax for multiple-table DELETE statements that use table aliases changed between MySQL 4.0 and 4.1. In MySQL 4.0, you should use the true table name to refer to any table from which rows should be deleted:

DELETE test FROM test AS t1, test2 WHERE ...

In MySQL 4.1, you must use the alias:

DELETE t1 FROM test AS t1, test2 WHERE ...

We did not make this change in 4.0 to avoid breaking any old 4.0 applications that were using the old syntax. However, if you use such DELETE statements and are using replication, the change in syntax means that a 4.0 master cannot replicate to 4.1 (or higher) slaves.

Some keywords are reserved in MySQL 4.1 that were not reserved in MySQL 4.0. See Section 8.3, “Reserved Words”.

The LOAD DATA FROM MASTER and LOAD TABLE FROM MASTER statements are deprecated. See Section 12.6.2.2, “LOAD DATA FROM MASTER Syntax”, for recommended alternatives.

When using multiple-table DELETE statements, you should use the alias of the tables from which you want to delete, not the actual table name. For example, instead of doing this:

DELETE test FROM test AS t1, test2 WHERE ...

Do this:

DELETE t1 FROM test AS t1, test2 WHERE ...

This corrects a problem that was present in MySQL 4.0.

For functions that produce a DATE, DATETIME, or TIME value, the result returned to the client is fixed up to have a temporal type. For example, in MySQL 4.1, you obtain the following:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
       -> '2001-01-01 00:00:00'

In MySQL 4.0, the result of the stement is different:

mysql> SELECT CAST('2001-1-1' AS DATETIME);
       -> '2001-01-01'

DEFAULT values no longer can be specified for AUTO_INCREMENT columns. (In 4.0, a DEFAULT value is silently ignored; in 4.1, an error occurs.)

LIMIT no longer accepts negative arguments. Use some large number (maximum 18446744073709551615) instead of -1.

SERIALIZE is no longer a valid mode value for the sql_mode variable. You should use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE instead. SERIALIZE is no longer valid for the --sql-mode option for mysqld, either. Use --transaction-isolation=SERIALIZABLE instead.

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.

Incompatible change: The mysql_shutdown() C API function has an extra parameter as of MySQL 4.1.3: SHUTDOWN-level. You should convert any mysql_shutdown(X) call you have in your application to mysql_shutdown(X,SHUTDOWN_DEFAULT). Any third-party API that links against the C API library must be modified to account for this change or it will not compile.

Some C API calls such as mysql_real_query() return 1 on error, not -1. You may have to change some old applications if they use constructs like this:

if (mysql_real_query(mysql_object, query, query_length) == -1)
{
  printf("Got error");
}

Change the call to test for a non-zero value instead:

if (mysql_real_query(mysql_object, query, query_length) != 0)
{
  printf("Got error");
}

Only upgrade the client to use 4.1 client libraries (not the server). No behavior changes (except the return value of some API calls), but you cannot use any of the new features provided by the 4.1 client/server protocol, either. (MySQL 4.1 has an extended client/server protocol that offers such features as prepared statements and multiple result sets.) See Section 17.2.4, “C API Prepared Statements”.

Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table so that it can hold long password hashes. However — to provide backward compatibility allowing pre-4.1 clients to continue connecting to their short-hash accounts — run the server with the --old-passwords option. Eventually, when all your clients are upgraded to 4.1, you can stop using the --old-passwords server option. You can also change the passwords for your MySQL accounts to use the new more secure format. A 4.1 installation using only the improved authentication protocol is the most secure one.