Urgent MySQL issue - Will pay $25 for a solution

[DanR]

Hi all

I HAVE AN URGENT ISSUE!!

I have a database table where I am storing VERY large numbers.... numbers up to 200 digits long.

I had this working on my MySQL 4 database but we migrated to MySQL 5.0.27 this week and I am having issues.

I have a field set to DOUBLE(200,0).

When I try and insert/update a large number like 99999999999999999999999999999999999999999999999999999999999999999999999999999


I check the data and it is "truncated" to: 9999999999999999635896294965248

for example:

insert into test (val) values ('99999999999999999999999999999999999999999999999999999999999999999999999999999')

Then i do a select and I get:

select * from test;

val
---
9999999999999999635896294965248

I dont know why at the moment..... its not PHP as I access another database running off the same web server that works, its got to do with this new version of MySQL 5.


Our SQL MODE is set to ''

When I run an update and try update the column with the large number we get the warning:

Warning | 1264 | Out of range value adjusted for column 'money' at row 1

Can anyone help? Its pretty URGENT!!!!

 

[mvl]

Hi all
...
I have a field set to DOUBLE(200,0).
...

The behaviour of doubles/floats is platform-dependant. But what is more important is doubles are only approximates, so having a double with 0 decimals does not make sense. If you really need integers this huge you should build a custom solution. I recommend storing them as a text-type in your database and cast them to something that can be treated as an integer in your software/scripts. This is probably not supported in the programming/scripting language you are using so you should find a library supporting integers this big represented in strings or build it yourself, or hire someone to build it for you.

 

[DanR]

I realise they are approximates but thats ok for what I am using them for.

Like I said I had this working on the previous MySQL version we were running.

 

[mikesherov]

HERE'S THE $25 FIX:
change the column type from DOUBLE(200,0) to DECIMAL(200,0)

When you upgrade past 5.0.3, go back to DOUBLE.

From http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html :

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

For MySQL 5.0.3 and above:

A packed “exact” fixed-point number. M is the total number of digits (the precision) and D is the number of digits after the decimal point (the scale). The decimal point and (for negative numbers) the ‘-’ sign are not counted in M. If D is 0, values have no decimal point or fractional part. The maximum number of digits (M) for DECIMAL is 65 (64 from 5.0.3 to 5.0.5). The maximum number of supported decimals (D) is 30. If D is omitted, the default is 0. If M is omitted, the default is 10.

UNSIGNED, if specified, disallows negative values.

All basic calculations (+, -, *, /) with DECIMAL columns are done with a precision of 65 digits.

Before MySQL 5.0.3:

An unpacked fixed-point number. Behaves like a CHAR column; “unpacked” means the number is stored as a string, using one character for each digit of the value. M is the total number of digits and D is the number of digits after the decimal point. The decimal point and (for negative numbers) the ‘-’ sign are not counted in M, although space for them is reserved. If D is 0, values have no decimal point or fractional part. The maximum range of DECIMAL values is the same as for DOUBLE, but the actual range for a given DECIMAL column may be constrained by the choice of M and D. If D is omitted, the default is 0. If M is omitted, the default is 10. 


DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

A normal-size (double-precision) floating-point number. Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0, and 2.2250738585072014E-308 to 1.7976931348623157E+308. These are the theoretical limits, based on the IEEE standard. The actual range might be slightly smaller depending on your hardware or operating system.

M is the total number of digits and D is the number of digits following the decimal point. If M and D are omitted, values are stored to the limits allowed by the hardware. A double-precision floating-point number is accurate to approximately 15 decimal places.

 

[DanR]

DECIMAL(200) will not work in 5.0.27, it is limited to 65 digits. I have already tried this.

And yes I know exactly how all the numeric datatypes are supposed to behave.

 

[mikesherov]

And yes I know exactly how all the numeric datatypes are supposed to behave.

I wasn't challenging your knowledge. You asked for help, and I was trying to provide it.
According to the MySQL docs that I posted, before 5.0.3, DECIMAL can have more than 65 digits... stored as an unpacked fixed-point number. I misread you as upgrading to 5.0.2, not 5.0.27.

Here's the relevant article: http://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-changes.html

The real answer here is to convert the large number into a float first, and then insert it.

Please keep the $25.

 

[DanR]

I really appreciate the help/assistance beleive me! This thing is driving me nuts!

The real answer here is to convert the large number into a float first, and then insert it.

Can you explain? I am inserting a number..... so not sure exactly what you mean.


Dude if you help me get this running you'll get $25, I'm a man of my word.

 

[iNod]

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

- Steve

 

[DanR]

Yep have read that Steve.... everything in there suggests what I am doing should work as it does in Ver 4.0.

 

[mikesherov]

No, Dan, the link I linked to says that as of 5.0.3, inserting a number that large doesn't work anymore for DECIMAL or DOUBLE. Try a few things here:

First, try using the FLOAT numeric type instead of DECIMAL or DOUBLE. As of 5.0.3, it's the only numeric type that can represent integers with more than 65 digits.

Second, in your SQL, you have:

insert into test (val) values ('99999999999999999999999999999999999999999999999999999999999999999999999999999')

try getting rid of the single quotes, like this:

insert into test (val) values (99999999999999999999999999999999999999999999999999999999999999999999999999999)

P.S. Didn't mean to be snappy yesterday. I must've been having a bad day!

 

[DanR]

OK i tried using a FLOAT(200,0) and it capped it at 9999999848243207295109594873856

Similar issue....

Getting rid of the quotes had no effect.


I have submitted a bug to mySQL so hopefully the developers can sort it out.

 

[mikesherov]

No, Dan, you gotta use FLOAT with no argument... test this out:

CREATE TABLE `test` (
`test` FLOAT NOT NULL ,
`test2` FLOAT( 200, 0 ) NOT NULL
) ENGINE = MYISAM ;

try inserting the large number into both fields. the one without arguments should be converted properly.

 

[DanR]

OK tried that... but then its stored like 3.40282e+38

Not really want I am after.... I dont understand why it cant work as it did in the previous version. What are people supposed to do that need it to work that way and have built whole applications relying on it.

 

[mikesherov]

Well, really, 3.40282e+38 is the proper representation in this case. If you need it to store the plain text integer version, use VARCHAR or TEXT, however arithmetic operations won't work on them.

I realise they are approximates but thats ok for what I am using them for.

If you only need approximates and approximate math, that's what floats are for. In 5.0.3, MySQL decided to conform to stricter SQL standards. That means any integers longer than 65 characters needs to be a float, or stored as text.

If you need to do math but still maintain the exact integer, use two columns... one that is the text representation, and one that is the float representation.

Ahh, the wonderful world of migrating code.

 

[DanR]

Well thats going to be a massive pain because I have MANY fields like this... MANY.


arrrrrggghhhhhhh

Probably easier to stay at this version of MySQL than migrate this application to the next version.

 

[mikesherov]

I believe that unless you need or desire features of higher versions, then there is no reason to upgrade to a newer version. I use MySQL 4.X for most of my apps... but one of my clients needed a MySQL cluster, a feature that works much better in 5.0.X.

Migrate when it's appealing to do so, not when it's available to do so.

 

[DanR]

Agree for sure but I have other applications where I need the new version so it was easier to migrate all of my applications as I have one database server.

In saying that MySQL 5 does have new features I would like to take advantage of anyway like views, stored procedures, triggers etc

 

[mikesherov]

Yes, if you need views, stored procedures and triggers... MySQL 5 is the way to go.

Thanks for posting this topic Dan, I learned a lot searching for the "solution".

 

[DanR]

hahaha yes the "solution".... like I said I have submitted a bug report so will be interesting to see what the developers say. I will post up the feedback here.