MySQL Reference Manual :: 2.10.2 Upgrading from Version 4.0 to 4.1

Check 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 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 D.2, “Changes in release 4.1.x (Production)”.

If you are running MySQL Server on Windows, see Section 2.3.15, “Upgrading MySQL on Windows”.

After upgrading, update the grant tables to have 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 2.10.7, “Upgrading the Grant Tables”. If you don't 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 6.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 5.9.8, “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.9, “Post-Installation Setup and Testing”.

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

If you don't 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 doesn't 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 Perl module for MySQL 4.1 from http://forge.novell.com/modules/xfmod/project/showfiles.php?group_id=1126 and 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);

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.

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.

All tables and non-binary string columns (CHAR, VARCHAR, and TEXT) have a character set. See Chapter 10, 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 use 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 10.10.2, “Converting 4.0 Character Columns to 4.1 Format”. Also, database, table, and column identifiers are stored internally using Unicode (UTF8) regardless of the default character set. See Section 9.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 8.8, “The mysqldump 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.

Incompatible change: The interface to aggregate user-defined functions has changed a bit 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 27.2.3.2, “UDF Calling Sequences for aggregate functions”.

As of MySQL 4.1.10a, 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 27.2.3.6, “User-defined Function Security Precautions”.

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

Incompatible change: String comparison works according to 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 wasn't before. If you have any tables where you have 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 as a string in 'YYYY-MM-DD HH:MM:SS' format. See Section 11.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 11.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. For example, if you declare a column as TIMESTAMP(10), the (10) is ignored.

These changes were necessary for SQL standards compliance. In a future version, a further change will be made (backward compatible with this change), allowing the timestamp length to indicate the desired number of digits for fractions of a second.

Incompatible change: Binary values such as 0xFFDF are assumed to be strings instead of numbers. This fixes some problems with character sets where it's 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 don't use CAST(), a lexical string comparison is done:

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.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 more strict 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.

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 get this result:

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

In MySQL 4.0, the result 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.

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 24.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. But run the server with the --old-passwords option to provide backward compatibility that allows pre-4.1 clients to continue to connect to their short-hash accounts. 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 pure-4.1 installation is the most secure.