2.5.1.1 Preparing to Upgrade From Version 4.0 to 4.1

Some visible things 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.

Instead of adding options (and a lot of code) to try to make 4.1 behave like 4.0 we have taken another approach:

We have added to the later MySQL 4.0 releases (from 4.0.12 on) the --new startup option for mysqld, which gives you the 4.1 behaviour for the most critical changes. You can also set this behaviour for a given client connection with the SET @@new=1 command.

If you believe that some of the following changes will affect you when you upgrade to 4.1, we recommend that before upgrading to 4.1, you download the latest MySQL 4.0 version and make sure that your applications work in the --new mode. This way you will have a smooth painless upgrade to 4.1 later.

In MySQL 4.1 we have done some things that may affect applications. The following is a list of things that you have to watch out for when upgrading to version 4.1:

• TIMESTAMP is now returned as a string with the format 'YYYY-MM-DD HH:MM:SS'. If you want to have this as a number (like Version 4.0 does) should add +0 to TIMESTAMP columns when you retrieve them. Different TIMESTAMP display widths are no longer supported. This change was 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.
• 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'
  

• 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 the string as a binary values. With this change, you should use CAST() if you want to compare binary values numerically as integers:

  SELECT CAST(0xFEFF AS UNSIGNED INTEGER) < CAST(0xFF AS UNSIGNED INTEGER)
  

  Using binary items in a numeric context or comparing them using the = operator should work as before. (The --new option can be used to make the server behave as 4.1 in this repect from 4.0.13 on.)
• AUTO_INCREMENT columns cannot take DEFAULT values. (In 4.0 these were just silently ignored; in 4.1, an error occurs).
• SERIALIZE is no longer a valid option 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.
• All column and tables now have a character set, which shows up in SHOW CREATE TABLE and mysqldump. (MySQL versions 4.0.6 and above can read the new dump files; older versions cannot.)
• If you are running multiple servers on the same Windows machine, you should use a different --shared_memory_base_name option on all machines.

Note: 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 an earlier MySQL version, you should use mysqldump. See section 4.8.6 mysqldump, Dumping Table Structure and Data.

If you are running MySQL Server on Windows, please also see section 2.5.7 Upgrading MySQL under Windows.

User Comments

Add your own comment.