Upgrade MySQL 5.0 to MySQL

Upgrade MySQL 5.0 to MySQL 4.0 directly from MySQL 5.0 to MySQL as needed. check changelog and find the following major changes:

I. major changes from 4.0 to 4.1

If an InnoDB containing the TIMESTAMP field is created in MySQL from version 4.1.0 to version 4.1.3
Table. When upgrading to 4.1.4 and later, you need to recreate the table because the storage format has changed and the string is compared according to standard SQL: Do not delete the trailing space before, previously, short strings were extended with spaces at the end. The result is

'A'> 'A'. this is not the case in the past. You can use mysqlcheck to check the TIMESTAMP returned by the data table 'yyyy-MM-DD HH: MM: SS' format string. In MySQL

In MySQL 4.0, you can add the option -- new to obtain the features in MySQL 4.1.

4.1.1 ago, the statement parser was not so strict. when processing string-to-time conversion, it ignored other characters before the first number. After 4.1.1, the results of the functions of the DATE, DATETIME, or TIME type are converted to the TIME type.

2. let's look at the major changes from 4.1 to 5.0.

The index order of TEXT fields ending with spaces in InnoDB and MyISAM tables has changed. Therefore, you need to run
"Check table" statement to REPAIR the data TABLE. If an error occurs, run "optimize table" or "REPAIR

TABLE "statement repair, and even re-dumping (using mysqldump) MySQL 5.0.15, how to deal with the value filled in the BINARY field has changed. The filled value is

0x00 instead of space, and the space at the end will not be removed from MySQL 5.0.3 when the value is set. The DECIMAL implementation method has changed, and 5.0 pairs of DECIMAL

The format limit is strictly higher than the DECIMAL created in MyISAM and InnoDB tables between MySQL 5.0.3 and 5.0.5.

After the field is upgraded to 5.0.6, it will crash before, and the lock waiting for timeout will cause InnoDB

Roll back all the current transactions. starting from 5.0.13, only the most recent SQL statements are rolled back. before 4.1.13/5.0.8, add 0 to DATETIME and convert it to YYYYMMDDHHMMSS format.

The YYYYMMDDHHMMSS.000000 format starts from 5.0.3 and DECIMAL uses a more effective format to store 5.0.3. when calculating the DECIMAL value and rounding the exact value, the exact math 4.1 is used, the comparison between FLOAT and DOUBLE happens to be okay, but in 5.0 it may not work starting from 5.0.3. a new startup option innodb_table_locks is not added to the spaces at the end of the VARCHAR and VARBINARY fields, it can also be requested when the TABLE is locked.

InnoDB table lock. This option is enabled by default, but it may be in AUTOCOMMIT = 1 and LOCK TABLES

Application will cause a deadlock

It seems that I only need to focus on the time (

TIMESTAMP, DATETIME <DATE, TIME

) And
Numeric type (

FLOAD, DOUBLE, DECIMAL

.
The upgrade procedure is as follows:

Execute flush tables with read lock;
Directly copy the MyISAM table file

Use
Mysqldump

Export Innodb tables
The whole process went smoothly. after the new system was started, the following two problems were found:

New keyword
INOUT

Therefore, you need to check what other fields in the table structure use keywords.
DATE_FORMAT

More rigorous functions are required. DATE_FORMAT ('2017/24 09:14:00 ',' % Y-% m-% d % t ')
And

DATE_FORMAT ('2014/1/0: 14: 00', '% Y-% m-% d % t ')
The results are completely different. in 4.0, the two formats are compatible, while in 5.0, the former can only be used correctly, and the latter may be faulty. This should also be caused by the changes in the time type mentioned above.

So far, the upgrade has basically ended and it has been roughly checked

DECIMAL

There is no problem with the type, and the rest is to check others.