MySQL 4.1 upgrade MySQL 5 method

I. Major changes from 4.0 to 4.1

If you created a InnoDB table containing timestamp fields in the MySQL tutorial 4.1.0 to version 4.1.3. You need to rebuild the table when upgrading to 4.1.4 and higher because the storage format has changed
Strings are compared according to standard SQL: The trailing spaces are not removed before the comparison, and shorter strings are previously extended with trailing spaces. Now the result is ' a ' > ' at ', not before. You can check the datasheet with Mysqlcheck.
Timestamp returns a string in the ' Yyyy-mm-dd hh:mm:ss ' format. In MySQL
4.0, you can add option--new to get this feature in MySQL 4.1
Before mysql4.1.1, the statement parser is not so strict that it ignores other characters before the first number when processing string-time conversions. After the 4.1.1, it's more rigorous.
Returns the result is a date, datetime, or a function of the time type that is converted to a type

Second, look at the major changes from 4.1 to 5.0

The index order of the text field at the end of the

InnoDB and MyISAM tables has changed. Therefore, you need to run the
Check table statement to repair the datasheet, and if an error occurs, run the Optimize table or repair
Table statement to fix it, or even dump it (with mysqldump)
MySQL 5.0 .15 start, how to handle the value of the fill in the binary field has changed. The value of the fill is now
0x00 instead of a space, and will not remove the trailing space when the value is taken
from the MySQL 5.0.3, the decimal implementation has changed, and the 5.0 decimal
is limited in format.
in MySQL 5 .0.3 to 5.0.5 The Decimal
field that was created in the MyISAM and InnoDB tables is upgraded to 5.0.6 Crashes
Previously, a lock that waits for a timeout causes the InnoDB
to roll back all current transactions, starting with 5.0.13, and returning only Roll the most recent SQL statement
before 4.1.13/5.0.8, the datetime added 0 to the YYYYMMDDHHMMSS format and now becomes the
yyyymmddhhmmss.000000 format
Starting with 5.0.3, Decimal uses a more efficient format for storing the
5.0.3 starts with precise math when calculating decimal values and rounding exact values
4.1, the comparison between float or double happens to be fine, but it may not be possible in 5.0 starting from 5.0.3, the space at the end of the varchar and varbinary fields is no longer removed
adds a new startup option innodb_table_locks that can also request
InnoDB table locks when the lock table is created. This option is turned on by default, but may cause deadlocks in the autocommit=1 and lock tables
Applications
It seems, I just need to focus on time (timestamp, datetime< date, times) and
Numeric type ( Fload, double, decimal) These two types of changes; In addition, I do not need to refer to the character set issue during the upgrade process, so it is relatively easy.

Upgrade steps are as follows:

Perform

Copy code code as follows:
Flush tables with Read Lock;/[code]
Direct copy of MyISAM table file

To export a table of InnoDB types with mysqldump
The whole process was smooth, and after the new system was launched, 2 questions were found:

New keyword inout, so you need to check what other fields in the table structure are using the keyword
The Date_format function requires a lot more rigor,
[Code]date_format (' 2006/11/24 09:14:00 ', '%y-%m-%d%t ')

And

Program code
Date_format (' 2006/11/2409:14:00 ', '%y-%m-%d%t ')

The result is completely different, in 4.0, can be compatible with both formats, and in 5.0, only the correct use of the former, the latter will have problems. This should also be due to changes in the type of time mentioned above.