upgrading to mysql 5 - FLOAT issue
This is just a small note, to cover FLOAT issues when upgrading from
MySQL version 4.0 to 5.0. In MySQL 4.0, the float type is by default 10
digits long, 4 after the decimal point. A create table statement would
look like this:
CREATE TABLE ic_bill (
....
amount float (10,4) NOT NULL default '0.0000',
....
) Type=...
Now the difference between 4 and 5 is that MySQL 4.0 allows data to overwrite the 10 digits limit, whereas MySQL 5.0 is more strict. Therefore if you dump one MySQL 4.0 table and reload it in 5.0, the float values will load as 1000000.0000. The solution is to alter the original table with a command similar to:
alter table ic_bill modify amount float(12,4); #... then replicate it back to 5.0
I'm porting a massive rating application from 4.0 upwards, and it's interesting how MySQL 4.0 (4.0.15) and MySQL 5.0 (5.0.18-max) handle this data type. The DECIMAL type seems to be even more affected. A full upgrade guide is available in deep detail on mysql.com.
CREATE TABLE ic_bill (
....
amount float (10,4) NOT NULL default '0.0000',
....
) Type=...
Now the difference between 4 and 5 is that MySQL 4.0 allows data to overwrite the 10 digits limit, whereas MySQL 5.0 is more strict. Therefore if you dump one MySQL 4.0 table and reload it in 5.0, the float values will load as 1000000.0000. The solution is to alter the original table with a command similar to:
alter table ic_bill modify amount float(12,4); #... then replicate it back to 5.0
I'm porting a massive rating application from 4.0 upwards, and it's interesting how MySQL 4.0 (4.0.15) and MySQL 5.0 (5.0.18-max) handle this data type. The DECIMAL type seems to be even more affected. A full upgrade guide is available in deep detail on mysql.com.
