1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

MySQL large numbers urgent issue

Discussion in 'MySQL' started by ruby, Apr 24, 2007.

  1. #1
    Hi all

    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 a large number like 99999999999999999999999999999999999999999999999999999999999999999999999999999


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



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


    Can anyone help? Its pretty URGENT!!!!
     
    ruby, Apr 24, 2007 IP
  2. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #2
    Some more info....

    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
     
    ruby, Apr 24, 2007 IP
  3. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #3
    Anyone who can help me solve this I will pay them $25 via PAYPAL!!!!! This is urgent!!
     
    ruby, Apr 25, 2007 IP
  4. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #4
    The manual states "...A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615."

    The value you've supplied is clearly out of this range. I've tried using just double, and it inserts. Only problem is that it's not going to help you as it's going to scientific notation so it's rounding.

    The only way I see you coming right here is to store those gigantic numbers as strings. Try char(200) or varchar(200), and then devise a way to crunch from there if you have to.
     
    Scolls, Apr 25, 2007 IP
  5. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #5
    I have this working with MySQL 4.0.21 so it does work just for some reason with this new version of MySQL its not.

    DOUBLE is a a double precision number which can store HUGE numbers in exponential format. When you supply precision, like DOUBLE(200,0) then you can insert or update with big numbers... it does work. The values are rounded because at the db level there stoerd as exponentials but thats ok for what I am using it for,
     
    ruby, Apr 25, 2007 IP
  6. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Then you'll have to revert back to MySQL 4.0.21. What I quoted there was straight out of the MySQL 5 manual. See half-way down this page: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html (look for BIGINT[(M)] [UNSIGNED] [ZEROFILL])

    I'm sure the developers should know what they've decided they'll set storage limits at. ;)
     
    Scolls, Apr 25, 2007 IP
  7. Scolls

    Scolls Guest

    Messages:
    70
    Likes Received:
    6
    Best Answers:
    0
    Trophy Points:
    0
    #7
    What you might also try is to break your numbers up into a few columns and then crunch from there. Quite a bit more work to do to pull it off, but then again, hard work never killed anybody I know of.

    Other than my suggestions, I'm sorry but I can't solve your problem otherwise.
     
    Scolls, Apr 25, 2007 IP
  8. lowridertj

    lowridertj Well-Known Member

    Messages:
    2,882
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    195
    #8
    set to decimal char length 254

    will solve the issue.
     
    lowridertj, Apr 25, 2007 IP
  9. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #9
    Decimals only go to max 55 in MySQL 5.

    Scolls DOUBLEs are a double precision data type, it has a massive lower and upper limit. I have alro read the manual ...

    Splitting is not possible... no way.

    Surely someone does this??? Doesn't anyone store very large numbers?
     
    ruby, Apr 25, 2007 IP
  10. selectsplat

    selectsplat Well-Known Member

    Messages:
    2,559
    Likes Received:
    121
    Best Answers:
    0
    Trophy Points:
    190
    #10
    It's still 64 bit, and therefore still has a threshold of how big a number it can hold. The largest number possible that can be held in any 64 bit number is 4,294,967,295.

    Or, in other words, this number in binary....

    1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111

    A 64 bit number is all a 64 bit server can hold. And 64 bit servers have only become available in the past few years. Before that, the largest possible number was half of that. The *ONLY* way to handle a bigger number is to store it as a character, and write a function that substings out smaller pieces of it, converts to number.

    If you want a bigger number than this right now, you'll have to write a version of MySQL that works on a 128 bit.
     
    selectsplat, Apr 25, 2007 IP
  11. selectsplat

    selectsplat Well-Known Member

    Messages:
    2,559
    Likes Received:
    121
    Best Answers:
    0
    Trophy Points:
    190
    #11
    Nope, no one does this. it currently isn't possible on any DBMS, not just MySQL.

    For what possible purpose?
     
    selectsplat, Apr 25, 2007 IP
  12. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #12
    Like I said I have this running now on a MySQL database. I insert very large numbers into a DOUBLE(200,0) field. Im talking numbers with 100 digits... I am doing it now mate and it works. Dont tell me its not possible Ive been doing it for years!

    Its an online game, that has massive numbers.
     
    ruby, Apr 25, 2007 IP
  13. lowridertj

    lowridertj Well-Known Member

    Messages:
    2,882
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    195
    #13
    I run large scale big number gaming sites.

    running in decimal (254) will more then handle any area you need.
     
    lowridertj, Apr 25, 2007 IP
  14. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #14
    As an axample I am looking at the data on the MySQL database now.... in a DOUBLE(200,0) field and one of the values is:

    99999999994364365105258936570907179930059620363034598443638458156156510823940113338185395101413475876864
     
    ruby, Apr 25, 2007 IP
  15. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #15


    In MySQL 5 decimals can only go up to 65 precision. Here Ill show you:

    
    Error
    
    SQL query:
    
    ALTER TABLE `test` ADD `figure6` DECIMAL( 255 ) NOT NULL
    
    MySQL said: Documentation
    #1426 - Too big precision 255 specified for column 'figure6'. Maximum is 65. 
    
    Code (markup):
     
    ruby, Apr 25, 2007 IP
  16. selectsplat

    selectsplat Well-Known Member

    Messages:
    2,559
    Likes Received:
    121
    Best Answers:
    0
    Trophy Points:
    190
    #16
    Ok, I think I found the answer.

    it's not actually storing that number as that number. Instead, it's using scientific notation to store the number. When you view the value, it's converting the scientific expression to number in php.

    Try inserting your number using scientific notation.

    Allowable values are -1.7976931348623157E+308 to -2.2250738585072014E-308, 0 and 2.2250738585072014E-308 to 1.7976931348623157E+308
     
    selectsplat, Apr 25, 2007 IP
  17. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #17
    You don't have to.... like I said I CURRENTLY do this with MySQL 4.... I update/insert DOUBLE(200,0) fields with MASSIVE numbers and it works FINE.

    All I want to know is how come its not for MySQL 5, its chopping and rounding all the big numebrs to 9999999999999999635896294965248

    I have this working PERFECTLY in MySQL 4 and have for years.
     
    ruby, Apr 25, 2007 IP
  18. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #18
    If you are running MySQL 5.0.27 then try it. Create a table with a DOUBLE(200,0) field and insert a new record and use a massive number, then have a look at the data.

    If you have MySQL 4 do it as well, I bet it works.

    Keep in mind if you use PHPMYADMIN then you need to make sure the PHP precision var is set to 200.
     
    ruby, Apr 25, 2007 IP
  19. lowridertj

    lowridertj Well-Known Member

    Messages:
    2,882
    Likes Received:
    40
    Best Answers:
    0
    Trophy Points:
    195
    #19
    mysql 4 - mysql 5

    run ma bit different in from the above stated. thats why its not working for him.


    In which case im not to sure. this is reasons i have yet to upgrade past 4.4 mysql. as I run large databases and it is quite fine how it is for me.

    instead of 255 max string have you tried 254 as i know the mysql setup can be finicky sometimes as well in mysql5
     
    lowridertj, Apr 25, 2007 IP
  20. ruby

    ruby Well-Known Member

    Messages:
    1,854
    Likes Received:
    40
    Best Answers:
    1
    Trophy Points:
    125
    #20
    ruby, Apr 25, 2007 IP