Help Needed Asap.

[JimZ]

I copied a database with tables with some float(4,2) fields used for US dollar amounts. They copied from the 3.23.49 database to the 5.0.45 database all as $99.99.

[peterlaursen]

hmmm …

we will look into this tomorrow morning.

it will speed up the process if you attach (and zip!) a small dump from 3.23 with such data where this is reproducable!

did you try to export/import or data sync?

[JimZ]

I was able to fix the problem. Instead of the using SQLYog's copy function, I dumped the database using mysqldump.exe and then imported it using SQLYog. When I attempted the import I got an error message about an out of range value.

So I edited the dump file to make the two 'float (4,2)' columns just simple 'float' columns and was able to successfully import it.

So there's seem to be problems with the copy feature. I think these were the only column using that data type spec, so hopefully all the other data made it across OK. There needs to be some kind of error message in the copy function and perhaps some additional options, such as whether to continue and how to handle values that throw errors.

[peterlaursen]

I do not remember details but FLOAT type implementation has changed with different MySQL versions. I think what was earlier (4,2) may be now (6,2), but not sure. We will check this of course! We will prefer to solve it and not display warnings!

I do not consider this an emergency however!

BTW: I do not understand why you do not use DECIMAL for money! That is actually what it is intended for primarily! DECIMAL(19,4) is commonly used (4 digits after decimal sign is normally what is required to compesate for rounding errors) for MONEY.

DECIMAL is a high precision numerical type, FLOAT is inaccurate (optimized for performance (and for use with very small (molecular) and very big (astronomic) numbers where exact numbers are 'out of range')).

But also DECIMALs have changed between versions! But I think that only refers to storage format (string/binary storage)

http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

… says

“types include the exact numeric data types (INTEGER, SMALLINT, DECIMAL, and NUMERIC), as well as the approximate numeric data types (FLOAT, REAL, and DOUBLE PRECISION).”

[JimZ]

peterlaursen wrote on Sep 6 2007, 03:44 PM:

I do not remember details but FLOAT type implementation has changed with different MySQL versions. I think what was earlier (4,2) may be now (6,2), but not sure. We will check this of course! We will prefer to solve it and not display warnings!

I do not consider this an emergency however!

No, no longer an emergency. Thank you very much for the quick replies.

Quote:

BTW: I do not understand why you do not use DECIMAL for money! That is actually what it is intended for primarily! DECIMAL(19,4) is commonly used (4 digits after decimal sign is normally what is required to compesate for rounding errors) for MONEY.

I'm afraid I didn't design the database.

What happens if you alter a table from SQLYog, changing the FLOAT columns to DECIMAL? Will the data be correctly preserved?

[peterlaursen]

I think you can alter, the inaccuracy occurs on less significant digits.

One reason why you should avoid FLOATs is that the inaccuracy is critical for a WHERE clause

SELECT …. WHERE float = 7.32 will fail if it is internally 7.3200000000000000001 !!!

[peterlaursen]

I am almost sure I can figure the reason!

In 3.23 FLOAT(4,2) means 4 digits before and 2 after decimal sing

In 5.0 it means totally 4 digits of which 2 after decimal sign. Everything >= 100 gets truncated to 99.99

changing from FLOAT(4,2) to (6,2) before copying is probably the easiest way!

We are checking with what version it was changed. I cannot guarantee that we will fix if only 3.23 versions are affected (sooner or later we will have to say stop using efforts with rare 3.23 compability issues like this!)

[Author]

Posts