MySQL Reference Manual - 4.0.20

[ < ]

[ > ]

[ << ]

[ Up ]

[ >> ]

[Top]

[Contents]

[Index]

[ ? ]

2.5.2 Upgrading from Version 4.0 to 4.1

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

Check the items in the change lists found later in this section to see whether any of them might affect your applications.
Read the 4.1 news items to see what significant new features you can use in 4.1. See section Changes in release 4.1.x (Alpha).
If you are running MySQL Server on Windows, see Upgrading MySQL Under Windows.
Important note: Early alpha Windows distributions for MySQL 4.1 do not contain an installer program. See Installing a Windows Binary Distribution for instructions on how to install such a distribution.
After upgrading, update the grant tables to have the new longer Password column that is needed for secure handling of passwords. The procedure uses mysql_fix_privilege_tables and is described in Upgrading the Grant Tables. Implications of the password-handling change for applications are given later in this section. If you don't do this, MySQL will not us the new more secure protocol to authenticate.
If you are using replication, see 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 read back the data.
Character set support has been improved. If you have table columns that store character data represented in a character set that the 4.1 server now supports directly, you can convert the columns to the proper character set using the instructions in Converting 4.0 Character Columns to 4.1 Format.
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 commands (such as DBI->do()) will not notice error conditions correctly.
Option --defaults-file=option-file-name will now give an error if the option file doesn't exists.

Several visible behaviors have changed between MySQL 4.0 and MySQL 4.1 to fix some critical bugs and make MySQL more compatible with the ANSI SQL standard. 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 mysqld Command-Line 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 will 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 will help 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 don't 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:

All tables and string columns now have a character set. See section Character Set Support. 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.
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 mysqldump.
Important note: If you upgrade to InnoDB-4.1.1 or higher, it is difficult to downgrade back to 4.0 or 4.1.0! That is because earlier versions of InnoDB are not aware of multiple tablespaces.
If you are running multiple servers on the same Windows machine, you should use a different --shared_memory_base_name option for each server.
The interface to aggregated UDF functions has changed a bit. You must now declare a xxx_clear() function for each aggregate function XXX().

Client Changes:

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

SQL Changes:

String comparison now 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 a CHAR or VARCHAR column in which the last character in the column may be less than ASCII(32), you should use REPAIR TABLE or myisamchk to ensure that the table is correct.
When using multiple-table DELETE statements, you have to 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 ...
TIMESTAMP is now returned as a string in 'YYYY-MM-DD HH:MM:SS' format. (The --new option can be used from 4.0.12 on to make a 4.0 server behave as 4.1 in this respect.) If you want to have the value returned as a number (as MySQL 4.0 does) you should add +0 to TIMESTAMP columns when you retrieve them:
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.
Binary values such as 0xFFDF now 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 will be 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.)
For functions that produce a DATE, DATETIME, or TIME value, the result returned to the client now 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.

C API Changes:

Some C API calls such as mysql_real_query() now 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"); }

Password-Handling Changes:

The password hashing mechanism has changed in 4.1 to provide better security, but this may cause compatibility problems if you still have clients that use the client library from 4.0 or earlier. (It is very likely that you will 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 goal of compatibility with old clients and the goal of security.

Only upgrade the client to use 4.1 client libraries (not the server). No behavior will change (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 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.
Upgrade to 4.1 and run the mysql_fix_privilege_tables script to widen the Password column in the user table. If you know that all clients also have been upgraded to 4.1, don't run the server with the --old-passwords option. Instead, change the passwords on all existing accounts so that they have the new format. A pure-4.1 installation is the most secure.

Further background on password hashing with respect to client authentication and password-changing operations may be found in Password Hashing in MySQL 4.1 and Client does not support authentication protocol.