Skip navigation links

Additional languages
2.11.1.2 Upgrading from MySQL 3.23 to 4.0 »
Section Navigation      [Toggle]

2.11.1.1. Upgrading from MySQL 4.0 to 4.1

Note

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.

To upgrade to 4.1 from any previous version, MySQL recommends that you dump your tables with mysqldump before upgrading and reload the dump file after upgrading.

In general, you should do the following when upgrading from MySQL 4.0 to 4.1:

If your MySQL installation contains a large amount of data that might take a long time to convert after an in-place upgrade, you might find it useful to create a “dummy” database instance for assessing what 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.0 and MySQL 4.1 to fix some critical bugs and make MySQL more compatible with standard SQL. These changes may affect your applications.

Some of the 4.1 behaviors can be tested in 4.0 before performing a full upgrade to 4.1. We have added to later MySQL 4.0 releases (from 4.0.12 on) a --new startup option for mysqld. See Section 5.1.2, “Server Command Options”.

This option gives you the 4.1 behavior for the most critical changes. You can also enable these behaviors for a given client connection with the SET @@new=1 command, or turn them off if they are on with SET @@new=0.

If you believe that some of the 4.1 changes affect you, we recommend that before upgrading to 4.1, you download the latest MySQL 4.0 version and run it with the --new option by adding the following to your config file: 

[mysqld-4.0]
new

That way you can test the new behaviors in 4.0 to make sure that your applications work with them. This helps you have a smooth, painless transition when you perform a full upgrade to 4.1 later. Putting the --new option in the [mysqld-4.0] option group ensures that you do not accidentally later run the 4.1 version with the --new option.

The following lists describe changes that may affect applications and that you should watch out for when upgrading to version 4.1.

Server Changes:

The most notable change is that character set support has been improved. The server supports multiple character sets, and all tables and nonbinary 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.

Note

This change in character set support results in the potential for table damage if you do not upgrade properly, so consider carefully the incompatibilities noted here.

  • 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.

    • If you have created or used InnoDB tables with TIMESTAMP columns in MySQL versions 4.1.0 to 4.1.3, you must rebuild those tables when you upgrade to MySQL 4.1.4 or later. The storage format in those MySQL versions for TIMESTAMP columns was incorrect. If you upgrade from MySQL 4.0 to 4.1.4 or later, no rebuild of tables with TIMESTAMP columns is needed.

    • Starting from MySQL 4.1.3, InnoDB uses the same character set comparison functions as MySQL for non-latin1_swedish_ci character strings that are not BINARY. This changes the sorting order of space and characters with a code < ASCII(32) in those character sets. For latin1_swedish_ci character strings and BINARY strings, InnoDB uses its own pad-spaces-at-end comparison method, which stays unchanged. Note that latin1_swedish_ci is the default collation order for latin1 in 4.0. If you have an InnoDB table created with MySQL 4.1.2 or earlier, with an index on a non-latin1_swedish_ci character set and collation order column that is not BINARY (in the case of 4.1.0 and 4.1.1, with any character set and collation), and that column may contain characters with a code < ASCII(32), you should do ALTER TABLE or OPTIMIZE TABLE on it to regenerate the index, after upgrading to MySQL 4.1.3 or later. You can also rebuild the table from a dump.

      MyISAM tables also have to be rebuilt or repaired in these cases. You can use mysqldump to dump them in 4.0 and then reload them in 4.1. An alternative is to use OPTIMIZE TABLE after upgrading, but this must be done before any updates are made in 4.1.

    • As of MySQL 4.1.2, string comparison works according to the SQL standard: Instead of stripping end spaces before comparison, we now extend the shorter string with spaces. The problem with this is that now 'a' > 'a\t', which it was not before. If you have any tables where you have indexes on CHAR, VARCHAR or TEXT column in which the last character in index values may be less than ASCII(32), you should rebuild those indexes to ensure that the table is correct.

    • If you have used column prefix indexes on UTF-8 columns or other multi-byte character set columns in MySQL 4.1.0 to 4.1.5, you must rebuild the tables when you upgrade to MySQL 4.1.6 or later.

    • If you have used accent characters (characters with byte values of 128 to 255) in database names, table names, constraint names, or column names in versions of MySQL earlier than 4.1, you cannot upgrade to MySQL 4.1 directly, because 4.1 uses UTF-8 to store metadata. Use RENAME TABLE to overcome this if the accent character is in the table name or the database name, or rebuild the table.

    • MyISAM tables now use an improved checksum algorithm in MySQL 4.1. If you have MyISAM tables with live checksum enabled (you used CHECKSUM=1 in CREATE TABLE or ALTER TABLE), these tables appear to be corrupted following an upgrade. Use REPAIR TABLE to recalculate the checksum for each such table.

  • 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 18.2.2.2, “UDF Calling Sequences for Aggregate Functions”.

  • Incompatible change: 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 nonbinary 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.11.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.

  • 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.8, “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 18.2.2.6, “User-Defined Function Security Precautions”.

Client Changes:

SQL Changes:

  • 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 using 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.

  • 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.

C API Changes:

  • 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 nonzero value instead: 

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

Password-Handling Changes:

The password hashing mechanism changed in 4.1 to provide better security; this may cause compatibility problems if you have clients using the client library from 4.0 or earlier. (It is very likely that you have 4.0 clients in situations where clients connect from remote hosts that have not yet upgraded to 4.1.) The following list indicates some possible upgrade strategies. They represent various tradeoffs between the goals of compatibility with old clients and security.

  • 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.6.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.

Further background on password hashing with respect to client authentication and password-changing operations may be found in Section 5.6.6.3, “Password Hashing in MySQL”, and Section A.1.2.4, “Client does not support authentication protocol”.