Urgent MySQL issue - will pay $25 for a solution

DanR · 04-25-2007, 02:32 AM

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 99999999999999999999999999999999999999999999999999 999999999999999999999999999

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

for example:

Code:

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

????: NamePros.com http://www.namepros.com/programming/320554-urgent-mysql-issue-will-pay-25-a.html
Then i do a select and I get:

Code:

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 · 04-25-2007, 03:20 AM

Originally Posted by DanR

Hi all
????: NamePros.com http://www.namepros.com/showthread.php?t=320554
...
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 · 04-25-2007, 03:22 AM

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 · 04-26-2007, 08:34 AM

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/...-overview.html :

PHP Code:

   
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.

????: NamePros.com http://www.namepros.com/showthread.php?t=320554
 
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.

????: NamePros.com http://www.namepros.com/showthread.php?t=320554
 
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 · 04-26-2007, 08:37 AM

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 · 04-26-2007, 11:54 AM

Quote:

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=320554
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/...l-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 · 04-26-2007, 08:23 PM

I really appreciate the help/assistance beleive me! This thing is driving me nuts!
????: NamePros.com http://www.namepros.com/showthread.php?t=320554

Quote:

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 · 04-26-2007, 09:17 PM

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

- Steve

DanR · 04-26-2007, 10:21 PM

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

mikesherov · 04-27-2007, 07:38 AM

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=320554

Second, in your SQL, you have:

Code:

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

try getting rid of the single quotes, like this:

Code:

insert into test (val) values (99999999999999999999999999999999999999999999999999999999999999999999999999999)

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

DanR · 04-27-2007, 07:51 AM

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 · 04-27-2007, 07:59 AM

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

Code:

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 · 04-27-2007, 08:02 AM

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 · 04-27-2007, 08:10 AM

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=320554

Quote:

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 · 04-27-2007, 08:12 AM

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 · 04-27-2007, 08:19 AM

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 · 04-27-2007, 08:31 AM

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 · 04-27-2007, 08:40 AM

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 · 04-27-2007, 08:57 AM

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.

04-25-2007, 02:32 AM	THREAD STARTER #1 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	Urgent MySQL issue - will pay $25 for a solution 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 99999999999999999999999999999999999999999999999999 999999999999999999999999999 I check the data and it is "truncated" to: 9999999999999999635896294965248 for example: Code: insert into test (val) values ('99999999999999999999999999999999999999999999999999999999999999999999999999999') ????: NamePros.com http://www.namepros.com/programming/320554-urgent-mysql-issue-will-pay-25-a.html Then i do a select and I get: Code: 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!!!! __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-25-2007, 03:22 AM	THREAD STARTER #3 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-26-2007, 08:34 AM	#4 (permalink)
mikesherov NamePros Member Join Date: Apr 2005 Posts: 117	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/...-overview.html : PHP Code: 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. ????: NamePros.com http://www.namepros.com/showthread.php?t=320554 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. ????: NamePros.com http://www.namepros.com/showthread.php?t=320554 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.

04-26-2007, 08:37 AM	THREAD STARTER #5 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-26-2007, 09:17 PM	#8 (permalink)
iNod Eating Pie Join Date: Nov 2004 Location: Canada Posts: 2,272	MySql: http://dev.mysql.com/doc/refman/5.0/...ric-types.html - Steve __________________ I feel old. Last edited by iNod; 04-26-2007 at 09:24 PM.

04-26-2007, 10:21 PM	THREAD STARTER #9 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	Yep have read that Steve.... everything in there suggests what I am doing should work as it does in Ver 4.0. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-27-2007, 07:38 AM	#10 (permalink)
mikesherov NamePros Member Join Date: Apr 2005 Posts: 117	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. ????: NamePros.com http://www.namepros.com/showthread.php?t=320554 Second, in your SQL, you have: Code: insert into test (val) values ('99999999999999999999999999999999999999999999999999999999999999999999999999999') try getting rid of the single quotes, like this: Code: insert into test (val) values (99999999999999999999999999999999999999999999999999999999999999999999999999999) P.S. Didn't mean to be snappy yesterday. I must've been having a bad day! :-)

04-27-2007, 07:51 AM	THREAD STARTER #11 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-27-2007, 07:59 AM	#12 (permalink)
mikesherov NamePros Member Join Date: Apr 2005 Posts: 117	No, Dan, you gotta use FLOAT with no argument... test this out: Code: 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.

04-27-2007, 08:02 AM	THREAD STARTER #13 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-27-2007, 08:12 AM	THREAD STARTER #15 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-27-2007, 08:19 AM	#16 (permalink)
mikesherov NamePros Member Join Date: Apr 2005 Posts: 117	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.

04-27-2007, 08:31 AM	THREAD STARTER #17 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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 __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

04-27-2007, 08:40 AM	#18 (permalink)
mikesherov NamePros Member Join Date: Apr 2005 Posts: 117	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".

04-27-2007, 08:57 AM	THREAD STARTER #19 (permalink)
DanR AdToll.com Join Date: Feb 2006 Location: Perth, WA Posts: 256	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. __________________ AdToll.com : 75% share : 20%-Adv. 10%-Pub. Aff. profit share : Text, Banner, Video & Peel Away : Paypal, Check, Wire

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)