How does MySQL change from 4.0 to 5.0? _ MySQL

How does MySQL change from 4.0 to 5.0? As needed, we upgraded from 4.0 to 5.0. after checking changelog, we found the following major changes:

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'. this is not the case 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 4.0, you can add the option -- new to obtain the features in ySQL 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 more strict. The return result of a function of the DATE, DATETIME, or TIME type is 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 the "check table" statement to REPAIR the data TABLE. If an error occurs, run the "optimize table" or "repair table" statement to fix the problem, or even re-dump (use mysqldump ).

◆ Starting from MySQL 5.0.15, how to handle the value filled in the BINARY field has changed. The filled value is now 0x00 instead of space, and the space at the end is not removed during the value.

◆ The DECIMAL implementation method has changed since MySQL 5.0.3, and DECIMAL format is more restrictive than 5.0. After the DECIMAL field created in MyISAM and InnoDB tables between MySQL 5.0.3 and 5.0.5 is upgraded to 5.0.6, a crash occurs. 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.

◆ Previously, the lock waiting for timeout would cause InnoDB to roll back all current transactions. from 5.0.13 onwards, only the most recent SQL statements will be rolled back.

◆ Before 4.1.13/5.0.8, after adding 0 to DATETIME, it is converted to YYYYMMDDHHMMSS format. now it is converted to YYYYMMDDHHMMSS.000000 format.

◆ In 4.1, the comparison between FLOAT and DOUBLE is normal, but it may not work in 5.0.

◆ From 5.0.3, spaces at the end of VARCHAR and VARBINARY fields will not be deleted.

◆ A new startup option innodb_table_locks is added, which can also request the InnoDB table lock when locking the TABLE. This option is enabled by default, but it may cause deadlocks in AUTOCOMMIT = 1 and lock tables applications. It seems that I only need to focus on the changes in TIME (TIMESTAMP, DATETIME <DATE, TIME) and numeric (FLOAD, DOUBLE, DECIMAL). In addition, the character set issue does not need to be involved during the upgrade, so it is relatively easy.

The upgrade procedure is as follows:

Run

Flush tables with read lock;

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,

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 is okay to check the DECIMAL type. The rest is to check other items.