I. Major changes from 4.0 to 4.1
If you create a InnoDB table that contains TIMESTAMP fields in MySQL 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 ' > ' a\t ', which was not the case 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 TEXT field index order for the end of the InnoDB and MyISAM table has changed. So you need to run
The CHECK table statement fixes the datasheet and, if an error occurs, runs the OPTIMIZE table or REPAIR
TABLE "statement fixes, even dumps (with mysqldump)
MySQL 5.0.15 started, how to handle the value of the fill in the BINARY field has changed. The padding value is now
0x00 instead of spaces, and does not remove trailing spaces when values are taken
Starting with the MySQL 5.0.3, the decimal implementation has changed, with 5.0 to decimal
The format limit is much stricter.
DECIMAL created in MyISAM and InnoDB tables between MySQL 5.0.3 to 5.0.5
A crash occurs after the field is upgraded to 5.0.6
In the past, a lock waiting to be timed out would cause InnoDB
Rolls back all current transactions, starting with 5.0.13, only the most recent SQL statements are rolled back
Before 4.1.13/5.0.8, DATETIME added 0 to the YYYYMMDDHHMMSS format and now becomes
yyyymmddhhmmss.000000 format.
Starting with 5.0.3, DECIMAL is stored in a more efficient format
5.0.3 starts with precise math when calculating DECIMAL values and rounding exact values
In 4.1, the comparison between FLOAT or DOUBLE happens to be fine, but in 5.0 it's probably not.
Starting with 5.0.3, the spaces at the end of the VARCHAR and VARBINARY fields are no longer deleted
Added a new startup option Innodb_table_locks, which causes the LOCK table to also request
InnoDB table lock. This option is turned on by default, but may be in autocommit=1 and LOCK TABLES
Deadlock is caused in application
It seems that I only need the main focus on time (TIMESTAMP, datetime< DATE, Times) and
Numeric (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.
So far, the upgrade is basically over, check the type of DECIMAL is not a problem, the rest is to check the other.
