You need to upgrade from 4.0 to 5.0. Check changelog and find the following major changes. If you need to upgrade mysql, refer.
You need to upgrade from 4.0 to 5.0. Check changelog and find the following major changes. If you need to upgrade mysql, refer.
I. Major changes from 4.0 to 4.1
If an InnoDB table containing the TIMESTAMP field is created in MySQL from version 4.1.0 to version 4.1.3. You need to recreate the table when upgrading to 4.1.4 and later, because the storage format has changed.
String comparison based on standard SQL: Compares spaces at the end of a string that are not deleted before, and extends short strings with spaces at the end. The result is 'A'> 'a \ t', which is not the same in the past. You can use mysqlcheck to check the data table.
TIMESTAMP returns a string in 'yyyy-MM-DD HH: MM: ss' format. In MySQL
In MySQL 4.0, you can add the option -- new to obtain this feature in MySQL 4.1.
Before MySQL4.1.1, the statement parser is not so strict. It ignores other characters before the first number when processing string-to-time conversion. After 4.1.1, It is strict.
The return result of a DATE, DATETIME, or TIME function is converted to a 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, or even re-dumping (using mysqldump)
Starting from MySQL 5.0.15, how to handle the value filled in the BINARY field has changed. The filled value is
0x00 instead of space, and the trailing space is not removed when the value is set.
The DECIMAL implementation method has changed since MySQL 5.0.3, 5.0 for DECIMAL
The format is much more restrictive.
DECIMAL created in MyISAM and InnoDB tables between MySQL 5.0.3 and 5.0.5
The field will crash after being upgraded to 5.0.6.
Previously, the lock waiting for timeout caused InnoDB
Roll back all the current transactions. From 5.0.13 onwards, only the most recent SQL statements will be rolled back.
Before 4.1.13/5.0.8, add 0 to DATETIME and convert it to YYYYMMDDHHMMSS format.
YYYYMMDDHHMMSS.000000 format
DECIMAL is stored in a more effective format starting from 5.0.3.
Starting from 5.0.3, precise mathematics is used to calculate the DECIMAL value and the exact rounding value.
In 4.1, the comparison between FLOAT and DOUBLE is normal, but it may not work in 5.0.
Starting from 5.0.3, spaces at the end of the VARCHAR and VARBINARY fields are not deleted.
A new startup option innodb_table_locks is added, which can also be requested during table lock.
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
The numeric type (FLOAD, DOUBLE, DECIMAL) changes. In addition, I do not need to involve Character Set issues during the upgrade, so it is relatively easy.
The upgrade procedure is as follows:
Run
The Code is as follows:
Flush tables with read lock;/[code]
Directly copy the MyISAM Table File
Use mysqldump to export Innodb tables
The whole process went smoothly. After the new system was started, the following two problems were found:
The keyword INOUT is added, so you need to check the table structure for other fields using the keyword.
The DATE_FORMAT function is much more rigorous,
[Code] DATE_FORMAT ('2017/24 09:14:00 ',' % Y-% m-% d % t ')
And
Program code
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 is okay to check the DECIMAL type. The rest is to check other items.
