MySQL 4.0 Upgrade to 5.0

Because of the need, from 4.0 direct upgrade to 5.0, look at the changelog, found that there are mainly the following changes:

I. Major changes from 4.0 to 4.1

If you created a InnoDB that contains TIMESTAMP fields in MySQL 4.1.0 to version 4.1.3

Table. You need to rebuild the table when you upgrade to 4.1.4 and higher, because the storage format has changed. Strings are compared against standard SQL: The trailing spaces are not deleted before the comparison, and shorter strings are previously extended with trailing spaces. Now the result is

' A ' > ' A ' is not the case before. You can use Mysqlcheck to check the data table TIMESTAMP return a string in the format ' Yyyy-mm-dd HH:MM:SS '. In MySQL

4.0, you can add option--new to get MySQL 4.1 features in this area in MySQL

Before 4.1.1, the statement parser is not so strict that it ignores other characters before the first number when processing string-time conversions. After 4.1.1, it's more rigorous. Returns the result is a date, DATETIME, or a function of a time type that is converted to a temporal 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 start, how to handle the value of the fill in the BINARY field has changed. The padding value is now

0x00 instead of whitespace, and does not remove the trailing spaces when the value is taken from the MySQL 5.0.3, the decimal implementation has changed, 5.0 pairs of decimal

The format limit is much stricter than the DECIMAL created in the MyISAM and InnoDB tables between MySQL 5.0.3 to 5.0.5.

field upgrades to 5.0.6 crashes occur before, a lock that waits for a timeout can cause InnoDB

Rolls back all current transactions, starting with 5.0.13, only rolling back the most recent SQL statement. Before 4.1.13/5.0.8, DATETIME was converted to YYYYMMDDHHMMSS format after adding 0, and now becomes

The yyyymmddhhmmss.000000 format begins with the 5.0.3, and DECIMAL stores the 5.0.3 in a more efficient format, using precise math 4.1 when calculating DECIMAL values and rounding exact values, FLOAT or DOUBLE The comparison happens to be fine, but it's probably not going to happen in 5.0. Starting with 5.0.3, the spaces at the end of the VARCHAR and VARBINARY fields are no longer deleted. Adds 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 to focus on time (

TIMESTAMP, datetime< DATE, time

) 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:

Executes flush TABLES with READ LOCK;

Direct copy of MyISAM table file

Use

Mysqldump

To export a table of type Innodb

The whole process was smooth, and after the new system was launched, 2 questions were found:

New keyword

INOUT

, so you need to check that there are other fields in the table structure that use the keyword.

Date_format

The function requirements are much more rigorous, date_format (' 2006/11/24 09:14:00 ', '%y-%m-%d%T ')

And

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, and the general check

DECIMAL

The type is fine, and the rest is to check the rest.